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: , , ,
8 comments

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

videos on visual studio 2008 January 20, 2008

Posted by fofo in Visual Studio 2008.
Tags: ,
add a comment

 Στην παρακάτω διεύθυνση

http://www.microsoft.com/heroeshappenhere/testdrive/visual-studio-2008/default.mspx

μπορείτε να βρείτε videos τα οποία θα σας βοηθήσουν να εξοικοιωθείτε με το visual studio 2008 και τα νέα features που έχει. τα videos καλύπτουν.

  • Multi-targeting in Visual Studio 2008 
  • Improving Web Developer Productivity and ASP.NET AJAX Integration
  • Developer Productivity with LINQ to SQL
  • Microsoft Open XML and LINQ to XML
Follow

Get every new post delivered to your Inbox.

Join 1,786 other followers