In this post I would like to present a step by step example on how to use stored procedures with LINQ to SQL.

Many people will wonder why I am bothering talking about LINQ to SQL so much.

First of all I give a lot of seminars where people want to learn LINQ to SQL.A lot of people like and use LINQ to SQL in their projects. There are a  lot of people right now who use it extensively.

In this post I will use two stored procedures that return data from the database. If you want to check out how to use stored procedures to insert,update,delete data check this other post of mine.

Having said that I will write an extensive series of posts covering Entity framework 4.0 in the near future.

A lot of developers ask me this question:"Well nikos, we do not trust the dynamic SQL generated by the LINQ to SQL engine. We do not know how it will affect the performance of our application."

If you search the internet you will find many sites/forums where developers,DBAS argue in favor of using LINQ to SQL, LINQ to SQL and stored procs or use no LINQ to SQL at all.

My experience tells me that the generated SQL is good but if you are in a project where the DBA is very strict on what SQL hits the database you should use stored procedures.

The one thing we should point out is that LINQ to SQL and the generated classes works fine with dynamic SQL and stored procedures.

I assume that you have access to a version of SQL Server and Northwind database.

If you do not, you can download and install the free SQL Server Express edition from here. If you need the installation scripts for the sample Northwind database, click here

1) Launch Visual Studio 2010/2008 (express editions will work fine). Create a new empty website and choose a suitable name for it. Choose C# as the development language.

2)  We are going to create a stored procedure that returns all rows ( and columns ) from the Products table according to the CategoryID that we will pass it as an input parameter.

3) Fire up SQL Server and type this T-SQL code

USE Northwind

go

CREATE PROCEDURE [dbo].[uspProductsInCategory]
   
    @categoryID int

AS
BEGIN

    SET NOCOUNT ON;

    SELECT * FROM Products
    WHERE CategoryID = @categoryID
END
 

Now you will have the stored procedure under Programmability. Test the by passing as categoryID the value 3. You will see some rows coming back from the daabase.

4) Add a new item in your site. Add a LINQ to SQL Classes and name it Northwind.dbml.Place this file in the App_Code special folder.

5) From the Server Explorer / Data Explorer window drag the Products table and drop it on the Northwind.dbml.

6)  From the Server Explorer / Data Explorer window drag the uspProductsInCategory stored procedure and drop it on the Northwind.dbml. It will show up as a method in the .dbml file.

Select the  uspProductsInCategory method  from the .dbml file and in the Properties window in the Return Type select Product.

7) Have a look at the generated code from the Linq engine in Northwind.designer.cs.

8)  In the Page_Load event handling routine type

 NorthwindDataContext ctx = new NorthwindDataContext();

        var myproducts = ctx.uspProductsInCategory(3);

        foreach (Product product in myproducts)
        {
            Response.Write(product.ProductName);
            Response.Write("<br/>");
        }

9) Run your application and you will see the rows returned  from the database. It is very easy for someone to understand what I am doing here. I create the mandatory gateaway object (ctx) and then just call the method (stored proc) by passing it the the value 3 as the CategoryID.Then I just loop through the results.

10)  I can hear you people out there shouting that this is easy but please note that we return all the columns from the Product table and all these columns match the Product entity exactly. But what if this is not the case?

11)  We must create a new stored procedure in order to demonstrate this. We need to find the Order ID and the Customer's city, country, company name and contact name.

We will create a stored procedure that will return all these columns and respective rows by passing the country as a parameter.

In order to do that we must get data from different tables and the columns returned do not match any entity we might have in our model.This is the stored procedure. Test it in the SQL Server first in order to make sure that it works.

CREATE PROCEDURE [dbo].[uspCustomerInfoByCountry]

    @country VARCHAR(40)

AS
BEGIN

    SET NOCOUNT ON;


SELECT     Orders.OrderID, Customers.CompanyName, Customers.ContactName, Customers.Country, Customers.City
FROM         Orders INNER JOIN
                      Customers ON Orders.CustomerID = Customers.CustomerID
WHERE     (Customers.Country = @country)
end

12)  From the Server Explorer / Data Explorer window drag the uspCustomerInfoByCountry stored procedure and drop it on the Northwind.dbml. It will show up as a method in the .dbml file.

13) Add a button in the default.aspx page. In the button1_click() event handling routine type

 NorthwindDataContext ctx = new NorthwindDataContext();

        var mylist = ctx.uspCustomerInfoByCountry("USA");

        
        
        foreach (var customer in mylist)
        {
           
            Response.Write(customer.OrderID.ToString());
            Response.Write("--");
            Response.Write(customer.CompanyName);
            Response.Write("--");
            Response.Write(customer.ContactName);
            Response.Write("--");
            Response.Write(customer.City);
            Response.Write("--");
            Response.Write(customer.Country);
            Response.Write("<br/>");
        }

14) The main thing to understand is that when results of your stored procedure do not map the entities in your data model, LINQ will generate a type based on the columns that are returned by the stored procedure.

15) The code is very easy to follow.Run your application and hit the button. All the results will be printed on the screen. The mylist variable is of a type that the LINQ autogenerates.

 Email me if you need the source code.

 Hope it helps.