jump to navigation

LINQ TO SQL and Stored procedures December 13, 2008

Posted by fofo in c# 3.0, LINQ, SQL Server 2008, Visual Studio 2008.
Tags: , , ,
trackback

In this post, I will go back to the issue of LINQ. I am going to show with an example how to use LINQ and stored procedures to insert,update and delete records from a particular table.

I will use visual studio 2008 to create an asp.net application (c# version) that demonstrates how to use stored procs with LINQ.

If you want to see how LINQ treats stored procedures , have a look here

I am going to use the Pubs datatabase and in particular the Authors table.

I am pretty sure, because I have used the Pubs database in almost all my examples that you have installed it and attached it in your local instance of Sql Server.

You can use Visual Studio 2008 express edition and Sql Server express edition for this example.

We often need to use stored procs to talk with our database because there have many benefits

for example

  • They allow modular programming.
  • They allow faster execution.
  • They can reduce network traffic.
  • They can be used as a security mechanism.

First we need to create the 3 stored procedures that will insert,update and delete records from the Authors table

This is not a post on how to create stored procedures, so i am just going to paste here the complete stored procs.

  • DeleteAuthor

USE [pubs]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[DeleteAuthor]

@AuthorID nvarchar(20)

AS
BEGIN

DELETE FROM authors
WHERE au_id = @AuthorID

END
GO

  • UpdateAuthor

USE [pubs]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[UpdateAuthor]

@authorID varchar(11),
@lname nvarchar(50),
@fname nvarchar(50),
@phone char(12),
@address nvarchar(40),
@city nvarchar(40),
@state char(2),
@zip char(5),
@contract bit

AS
BEGIN

UPDATE authors
SET

au_lname=@lname,
au_fname=@fname,
phone=@phone,
address=@address,
city=@city,
state=@state,
zip=@zip,
contract=@contract
WHERE au_id  = @authorID

END
GO

  • InsertAuthor

USE [pubs]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[InsertAuthor]
@id varchar(11),
@lName nvarchar(50),
@fname nvarchar(50),
@phone char(12),
@address nvarchar(40),
@city nvarchar(40),
@state char(2),
@zip char(5),
@contract bit

AS
BEGIN

INSERT INTO pubs.dbo.authors(
au_id,
au_lname,
au_fname,
phone,
address,
city,
state,
zip,
contract)
VALUES (
@id,
@lname,
@fname,
@phone,
@address,
@city,
@state,
@zip,
@contract)

END

GO

1) Launch Visual Studio 2008

2) Create an ASP.net web application. Use C# a your language of development

3) Name your project – solution as you want.

4) Open the Server Explorer and connect to the Pubs database.

5) Add a new item in your project, a Linq to SQL classes, a .dbml file. name it authors.dbml

6) Drag and drop from the Server explorer window the authors table into the designer area (on the .dbml file)

7) Right-Click on the designer area and show the “Show methods Pane”

8) Drag and drop the stored procedures from the Server explorer to the designer area

9) Select the author entity from the deisgner area and select the Properties window. In the Default methods we need to assign the correct stored procs and not to leave the default option which is “use Runtime”. So please assign the different methods to their respective stored procs. Have a look at the picture below

sql-linq

10) We will use these stored procs that are methods now as far as LINQ is concerned to update,insert and delete records from the database.

11) Add a button in the Default.aspx file. Name it “Update”.Double click on this button. In the event handling routine type:

using (authorsDataContext authdata = new authorsDataContext())
{
var author = (from a in authdata.authors
where a.au_id == “238-95-7766”
select a).Single();
Response.Write(author.au_fname);
author.au_fname = “nikolaos”;

authdata.SubmitChanges();

}

12) Add another button in the default.aspx page. Name it “After Update”.In this routine we try to see the updated value in the database.Double click on this button. In the event handling routine type:

using (authorsDataContext authdata = new authorsDataContext())
{
var author = (from a in authdata.authors
where a.au_id == “238-95-7766”
select a).Single();
Response.Write(author.au_fname);

}
12) Add another button in the default.aspx page and name it “Insert”.Double click on this button. In the event handling routine type:

using (authorsDataContext authdata = new authorsDataContext())
{

authdata.InsertAuthor(“216-49-8915”, “Jones”, “Michael”, “432423424”, “james street 123”, “New york”, “NY”, “94618”, true);

authdata.SubmitChanges();
}

13) Add another button in the default.aspx page and name it “Delete”. Double click on this button. In the event handling routine type:

using (authorsDataContext authdata = new authorsDataContext())
{
var author = (from a in authdata.authors
where a.au_id == “216-49-8915”
select a).Single();
authdata.DeleteAuthor(author.au_id);
authdata.SubmitChanges();
}

if you named you .dbml file “authors”, then there is a file “authors.designer.cs”. Inside there you will find this code

private void Insertauthor(author obj)
{
this.InsertAuthor(default(string), default(string), default(string), obj.phone, obj.address, obj.city, obj.state, obj.zip, ((System.Nullable<bool>)(obj.contract)));
}
private void Updateauthor(author obj)
{
this.UpdateAuthor(obj.au_id, obj.au_lname, obj.au_fname, obj.phone, obj.address, obj.city, obj.state, obj.zip, ((System.Nullable<bool>)(obj.contract)));
}
private void Deleteauthor(author obj)
{
this.DeleteAuthor(default(string));
}

The methods above, are our stored procedures , which are called whenever we insert,update,delete

Hope it helps!!!

If you need the source code just email me.

Add to FacebookAdd to NewsvineAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to Ma.gnoliaAdd to TechnoratiAdd to Furl

Comments»

1. Ryza - December 26, 2008

Hello,

Thanks for the nice tutorial here…
I got a problem when I click the button update it does not update to au_fname = “nikolaos”. It still show “cheryl”. How to update it?

Regards

2. fofo - December 26, 2008

thanks Ryza, for your kind words. nice to know i a can be of some help….well if you still see that record in the table with tha same value “cheryl” you need to select the records again from the table. i have checked the code and it works fine. so if you followed everything. are you sure you have created correctly the stored procs?have you changed the right properties in the properties window? i am emailing you the source code. make sure to change in the web.config file the connection string to point to your local sql server and database. hope it helps!!!

3. ivowiblo - February 12, 2010

Hi,
The idea behind the usage of an ORM is to provide object oriented solutions. The idea of LINQ is to have a query language in c# in order to avoid sql queries. So, why use stored procedures? which is the difference between that and use strong typed datasets?

About the 4 reasons of using stored procedures:
* They allow modular programming
c# is an object oriented language, so we should talk about object oriented programming. And stored procedures kills that.

* They allow faster execution
That’s a Myth. From the Sql Server Books Online:
“SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created. A stored procedure is compiled at execution time, like any other Transact-SQL statement. SQL Server 2000 and SQL Server 7.0 retain execution plans for all SQL statements in the procedure cache, not just stored procedure execution plans.”

* They can reduce network traffic.
Using plain objects you could make use of caching and reduce database calls a lot.

* They can be used as a security mechanism.
The same to any service/method you have (you could use aspects and provide whatever validation you want)

and you are also killing refactoring and duplicating your model. increasing the development process because you are writing each insert, delete, update statement in a stored procedure (btw, why are you doing that!? why are you using an orm in the first place?).

4. fofo - February 12, 2010

i see your point and you are right if you approcah things like that.have a look at this link to see some disadvantages on using linq to sql queries instead of using linq with stored procs.

5. ivowiblo - February 12, 2010

Which link? what I say is that if you wont use the mappings at all, why use linq-to-sql?

6. Neha Verma - March 9, 2010

Hi,
I am heavily using LINQ to SQL in my current project. But wondering if LINQ to SQL is faster than datareader or dataadapter?

Can you please throw some light on the performance differences between the two?

Thanks in advance.
Neha Verma

7. fofo - March 9, 2010

well this is a big issue…

LINQ to SQL uses the datareader under the covers so it makes sense the DataReader will naturally be faster than anything that is working on top of it.

LINQ to SQL gains in performance by using compiled queries, and by using them more than once.

have a look here

http://www.devtoolshed.com/content/performance-benchmarks-linq-vs-sqldatareader-dataset-selects-part-1

http://alexpinsker.blogspot.com/2007/07/benchmarking-linq-vs.html

8. ivowiblo - April 20, 2010

It’s not always about code performance. You should consider the people performance in doing their work, the amount of bugs, the time needed to solve those bags, the time needed to add a change.
There’s a point when you can pay more bandwith, more RAM, more hard disk and have a better system.
For example, it will be a failure if you spread datareaders for the entire application just because linq-to-sql is slower. Of course it’s slower! but it matters? c# is slower than assembler too.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: