Creating Data Access Layer Using LINQ To SQL - Stored Procedures Support

Creating Data Access Layer Using LINQ To SQL - Stored Procedures Support

by David Hayden ( Sarasota Developer ), Filed: LINQ To SQL

 

I have been covering LINQ To SQL lately as we develop a fairly large application using LINQ To SQL in the data access layer and Visual Studio 2008. Here are a few of the LINQ To SQL Tutorials to date:

• LinqDataSource - High Performance Queries Using DataLoadOptions - Avoiding Database Roundtrips
• LINQ to SQL - Performance Tradeoffs: Less Database Roundtrips but Duplicated Data in Each Row??
• LINQ To SQL - Lazy Loading Properties and Specifying PreFetch When Needed - Sweet Query and Performance Control
• LINQ To SQL Video - Getting From Expression Trees to SQL

I plan to create a few screencasts on PnPGuidance as well starting this week when I get a bit more time.

Up until now we have been investigating how LINQ To SQL generates queries and how to tweak the model for more optimal performance. In this quick post, I want to talk about using stored procedures to get the data as opposed to letting LINQ To SQL generate the dynamic SQL. You may want to do this for performance, security, control, or other reasons for using Stored Procedures instead of Parameterized Queries.

As it turns out, using Stored Procedures with LINQ To SQL is as simple as dragging a Stored Procedure from the Server Explorer Tool to your Visual Designer Surface. Since the Stored Procedure in question, GetOrderDetailsByOrderId, generates a list of Order_Detail Classes, I drag the stored procedure from the Server Explorer and drop it on the Order_Detail Class on the Visual Designer. This tells the visual designer that the resultset of the stored procedure is a list of Order_Detail Classes. You will then see the stored procedure off to the right of the Visual Designer letting you know it has been added to the DataContext.

 

 

The drag-and-drop operation ends up creating a method on your DataContext Class:

 

[Function(Name="dbo.GetOrderDetailsByOrderId")]
public ISingleResult<Order_Detail> GetOrderDetailsByOrderId
    ([Parameter(Name="OrderId", DbType="Int")]
     System.Nullable<int> orderId)
{
    IExecuteResult result = this.ExecuteMethodCall(this,
                ((MethodInfo)(MethodInfo.GetCurrentMethod())),
                 orderId);
    return ((ISingleResult<Order_Detail>)(result.ReturnValue));
}

 

Using the Stored Procedure is as simple as:

 

using (NorthwindDataContext context
                    = new NorthwindDataContext())
{
    foreach (Order_Detail detail in
                  context.GetOrderDetailsByOrderId(10248))
      Console.WriteLine(detail.Discount);

    Console.ReadLine();
}

 

You can also update the Order_Detail Classes and have LINQ To SQL do the proper updates to the table:

 

using (NorthwindDataContext context =
                      new NorthwindDataContext())
{
    foreach (Order_Detail detail in
              context.GetOrderDetailsByOrderId(10248))
    {
        detail.Discount = 0;
    }
    
    context.SubmitChanges();
}

 

Not bad at all :) You can specify stored procedures for inserts, deletes, and updates, too if you want, which I can talk about next time.

News Feed: David Hayden ( Sarasota Developer )

Filed: LINQ To SQL

 

posted on Tuesday, August 07, 2007 6:57 PM