LINQ and Stored Procedures

Okay so last quarter I was asked if you can call stored procedures using Language Integrated Query (LINQ).  The answer, of course, is YES!  It would be kind of silly if we didn't include this type of functionality. :P

NOTE:  You need the following items to do this example

SQL Server (pretty much any version will do but this example uses 2005)

Visual Studio 2008 (Beta 2 was used for this example)

A little time to sling some code

So how exactly DO you call a stored proc using LINQ?  Easy just follow these simple steps:

1. Create a new Windows Forms Application in Visual Studio 2008, let's call it "LINQ_SP_Coolness"

   

2. Next, make sure you have a connection to some data source in your Server Explorer.  You may need to add a new connection.

   

3. For the purposes of this discussion I am using the Northwind Database on SQL Server.

   

4. Take a look at the existing stored procedures because what you need may already be there.

   

5. Aaaaaand since I'm bored watching re-runs tonight, why don't we make our own stored procedure?

   

6. Just write a simple SELECT to yank out some data AND remember to save :)

   

7. Just to make sure, refresh the stored procedure folder on your data source in Server Explorer to see your shiny, new stored proc listed...

     

8. You might even want to test it :P

   

9. To get this to a point we can manipulate it using LINQ we need to have our classes and mappings in place.  Fortunately, this is a VERY easy thing to do.  Just right-click your project, choose Project...Add New Item from the Menu Bar.  Let's add a "LINQ to SQL Classes" item with the default name of "DataClasses1.dbml".

   

10. Click and drag the stored procedure from Server Explorer on to the LINQ design surface.

     

11. You should see the procedure showing up on the upper right of the design surface.

    

12. Now let's slap some controls on our Windows Form.  How about a list box and a button?  Live on the edge!

     

13. Now we need to work with database by establishing a context to it.  Next, we need to create a query expression that treats the result of the stored procedure as a table and query against it selecting only the LastName column.  Just for fun why don't we sort all the last names as well?  Finally, we will take the list of names returned and loop through them adding each one to our listBox as we go along.

    Double-click on the button to code for the Click event and write the following lines of code:
    (NOTE: make sure to substitute your server name in place of "zero_cool" and if you are using SQL Express you need to modify the server piece to read like this "SQLEXPRESS\myserver")

     

14. If all goes well, you should get the following result:

    

15. And that's how you use a stored procedure with LINQ.  This was a very simple example but if you really want to see a great series on LINQ go to my buddy Benko's site.   He has a webcast series on LINQ that kicks major butt! :)

    [Updated:  added tags]
    Technorati Tags: LINQ , SQL , stored procedure , smoking crack , database , LINQ to SQL