LINQ and Stored Procedures

Tips Search

LINQ and Stored Procedures

  • Comments 18

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"

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

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

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

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

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

    image
  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...

    image  
  8. You might even want to test it :P

    image
  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".

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

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

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

    image 
  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")

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

    image
  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]
  • PingBack from http://msdnrss.thecoderblogs.com/2007/10/24/linq-and-stored-procedures/

  • Awesome post.  I have the DevCares tomorrow on LINQ and I will definitely be sending people here for how to do this.

    Thanks Zain.

    Also, Server = Zero_Cool???  Hackers fan, much? :)

  • Not a whole lot of links this week. I have been pretty pushed getting some stuff ready for production.

  • I was wondering if anyone would get the reference to Hackers hehehe

  • I have a complex stored proc that i would like to run but can not be generated as it contains either temp tables or nested loops.

    Is there a way with linq to call a stored proc explicitly that is not in my datacontext file?

  • See if this thread does the trick:

    http://www.themssforum.com/Csharp/Linq-Temp/

  • Do you have C# example like your "LINQ and Stored Procedures" using datagridview in windows forms?

    link:

    http://blogs.msdn.com/zainnab/archive/2007/10/25/linq-and-stored-procedures.aspx

  • I am using linq 2 sql store procedures . I didn’t change the return type of any store procedure so it is returning isingleResult<storeprocedureResult>

    I make a class ”mydataconetcion” which use the datacontext class object and I am getting all store procedures in that class by

    Datacontext db=new datacontext();

    One example of my store procedure is

    public ISingleResult<dashCommerce_Store_FetchCategoryManufacturersResult> getCategoryManufecturer(int catId)

    {

    ISingleResult<dashCommerce_Store_FetchCategoryManufacturersResult> CategoryManufecture = db.dashCommerce_Store_FetchCategoryManufacturers(catId);

    return CategoryManufecture;

    }

    In my web pages I make object of mydataconetcion class

    Mydataconetcion dc=new mydataconetcion();

    I am storing the result  in var then by foreach I am traversing it . or I simply bind it to my gridview and repetors and so on .

    I am doin this

    Var selectedProduct =dc. getCategoryManufecturer(7);

    CatGridview.datasource= selectedProduct;

    It works fine but now I want something like this

    If (selectedProduct!=null )

    {

    CatGridview.visible=true;

    CatGridview.datasource= selectedProduct;

    }

    Or empty or what ever condition that tell me that there is any value in  selectedProduct

    So help me out on this how can I make sure that there is any value or not

  • This was actually a question in the thread for my post on LINQ and Stored Procedures but it was such

  • ahtesham check here for your answer.  I decided to make it into a full blog post.

    http://blogs.msdn.com/zainnab/archive/2008/05/24/checking-to-see-if-there-are-results-from-a-stored-procedure-using-linq.aspx

  • If I fill a temp table in my stored procedure where I define and fill the columns in the stored procedure, how come I can't get LINQ to recognize that there are records comming back and not an 'int'?  Seems to think its a retrun value of type int. strange.

    Thanks,

    Steve

  • What happen if the store procedures signature change in the DB. Linq will change his won signature or he will do nothing ( you will do erease the current store Procedures and readd this ) ?

  • Yep if the stored proc signature changes you have to re-add the proc to your model so the app is aware of the change

  • Can I specified to the dbml to regenerate the designer.cs, each time I build the project ?

  • Well, yes and no.  You can't do it through the IDE but you can use SQLMetal to autogenerate your dbml for you.  Check out my (very brief) post here:  http://blogs.msdn.com/zainnab/archive/2007/11/25/sqlmetal-and-linq.aspx

    And my webcast that mentions it is here:

    http://blogs.msdn.com/zainnab/pages/visual-studio-2008-and-the-net-framework-3-5-new-things.aspx

    Called "LINQ Features in Visual Studio 2008"

Page 1 of 2 (18 items) 12
Leave a Comment
  • Please add 3 and 1 and type the answer here:
  • Post