Stored Procs in LINQ to SQL in March CTP (The VB Version)

In a previous interation, I managed to track down some sample code that built a LINQ call to a stored procedure, the class which it returns and executed it in LINQ to SQL.

The code has changed dramatically in the March CTP. Luckily, Mike Taulty beat me to it and I leveraged the C# code in his post to revise mine.

One of the things I was so silly to do in my previous test was to hand code the return class rather than just create it in the designer and return that.

But all of this is not totally necessary since you can now drag and drop stored procs onto the LINQ to SQL Visual Modeler and make calls to it. Interestingly the return type is just created on the fly at run time so it’s not even an issue – yay to anonymous types.

However, it’s still nice to know how to build this stuff becasue drag n drop doesn’t solve every coding problem. So check Mike’s post for the C# version.

Here is a working version in VB.

“CustOrderHist” is the name of the stored procedure in my database. 

What this is doing is

1) using the attribute to hook it up to the sproc
2) taking in a parameter of customerID and associating it with the parameter @CustomerID in the stored proceudre
3) Returning and IEnumerable of type CustOrderHistoryResult (the class I created in the designer that matches the structure of what the sproc returns).

On the client side, I can now take the result and do whatever I want with it, such as bind it to a gridview on a web page.

<StoredProcedure(Name:=”CustOrderHist”)> _
Public Function GetCustOrderHist(<Parameter(Name:=”CustomerID”)> ByVal customerID As String) As IEnumerable(Of CustOrderHistoryResult)

  Dim result As IQueryResults(Of CustOrderHistoryResult) = _
ExecuteMethodCall(Of CustOrderHistoryResult)(Me,
  MethodInfo.GetCurrentMethod, customerID)
  Return result

  End Function

  Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!  

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.