Saturday, July 11, 2009

Call a stored procedure with LINQ to SQL and without the designer

by sweisfeld via Shawn Weisfeld [MVP] on 7/11/2009 10:15:00 PM

In my last post I talked about how to do a simple select with LINQ to SQL without the designer (http://drowningintechnicaldebt.com/blogs/shawnweisfeld/archive/2009/07/11/a-simple-select-with-linq-to-sql-and-without-the-designer.aspx). This is all well and good, but what if you need to use a stored procedure. Like this one. . . .

The first step is to add to our data context a method that maps to our stored procedure. You can see we start off with a Function attribute that tells LINQ to SQL what stored procedure to use. Additionally we decorate the parameters on our method with Parameter attributes that tell LINQ to SQL how to map the parameters. Then we just call our base classes ExecuteMethodCall passing along all the attributes (using the .NET reflection MethodInfo class), and our parameter.

Now we just need to use our new method.

Here we can see we get the same results as we did in my last post.

In my next post we will use Multiple-Rowset to return multiple result sets from my stored procedure.