Nikolaos Kantzelis ASP.Net Blog

This blog will focus on ASP.NET Framework

Sponsors

About Me

Great Blogs

Stored procedures with Linq to Sql in an ASP.Net application

 
In this post, 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,like

  •     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 theDefault 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



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.

Comments

Souka said:

The ability to think like that shows you're an exeprt

# January 21, 2013 11:03 PM