Using LINQ to SQL with a Stored Procedure

By | May 10, 2010

In this post I’ll go over a very bare bones way to call a stored procedure using LINQ to SQL.

Scenario

So I was writing up a bare bones tool to send me the results of a handful of queries I wanted to run against our OnTime database. This tool would have at most three clients, would run every hour, and was certainly not mission critical. I didn’t really want to spin up SubSonic or IBatis.NET, and I certainly didn’t want to spend time learning EF just for this mini-project. Amazingly enough I’d gotten away without having to learn LINQ to SQL up to this point, but alas, it seemed like the time was right. It seemed like the right choice, since all I wanted to do was to call a stored procedure in a database and massage the results into some text I’d be emailing out every hour.

For our sample below, we’ll be using Visual Studio 2010 RC and the AdventureWorks database.

Generating the Model

While there a lot of pictures in the below steps, they are pretty straightforward and self-explanatory. What’s really powerful here is the options you’d have if you were after something more than a bare bones way of calling a stored procedure.

Right click on the project node in the Solution Explorer and add a new ADO.NET Entity Data Model.

addNewItem

Name the model AdventureWorks, for obvious reasons.

nameTheModel

Click Add.

dataModelWizard1

Ensure that “Generate from database” is selected and click Next.

dataModelWizard2

Set up a new connection to the AdventureWorks database (this will be dependent on your database so I’ll skip the steps). After you have your connection your dialog will look something similar to the below.

dataModelWizard3

And yes, my laptop is a cheapo, so I’m accepting donations. Email me for details! The next step brings up a list of all the objects in the target database that we can model. Since our goal is a bare bones, easy way of calling a stored procedure without a lot boilerplate code, we just need to select the ones we are interested in. For this example, we are going to call uspGetManagerEmployees.

dataModelWizard4

Leave the defaults selected for not and click Finish. Visual Studio will work its magic and add the needed references to your project to support the newly introduced entity data model. Your IDE will appear similar to the below.

freshEntityDataModel

We need to tell Visual Studio to generate code so that we can call our stored procedure in a bare bones way. To do this, select the Model Browser tab in the Solution Explorer.

modelBrowserSelection

Expand the Stored Procedures node and find the one we’re interested in, uspGetManagerEmployees.

sprocPreGen

Right click on the stored procedure and select Add Function Import.

addFunctionImport

As you can see, we have lots of options available to us when adding a function import. For now, we’ll keep most everything set to the default selections. Press the “Get Column Information” button to have Visual Studio go out to the database to determine what our stored procedure returns.

columnInformationRetrieved

Pretty nifty if you ask me. Even niftier is the button that’s now enabled, “Create New Complex Type.” Click that.

complexTypeCreated

So what we’ve basically done is tell Visual Studio create a new type based on the return columns of the stored procedure we want to call. Click OK. You’ll get taken back out to Visual Studio, but you’ll see now that the stored procedure we were working with has its mapping details filled in.

mappingDetails

Again, pretty nifty.

The Code

I saved the best for last in this one. To call our newly modeled stored procedure, we use the code below.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;

namespace SimpleLINQtoSQLWithSproc
{
    class Program
    {
        static void Main(string[] args)
        {
            using (AdventureWorksEntities db = new AdventureWorksEntities())
            {
                ObjectResult<uspGetManagerEmployees_Result> result =
                    db.uspGetManagerEmployees(3);
            }
        }
    }
}

The result object will contain the rows returned from our stored procedure call – that simple. It’s even better than that though. Because of our prior modeling, we have full IntelliSense support on our results object.

resultIntelliSense

So if we wanted to produce a simple list of employees and who they report to, we modify our console app as below.

static void Main(string[] args)
{
    using (AdventureWorksEntities db = new AdventureWorksEntities())
    {
        ObjectResult<uspGetManagerEmployees_Result> result =
            db.uspGetManagerEmployees(3);

        foreach (var x in result)
        {
            Console.WriteLine(
                "{0} {1} reports to {2} {3}",
                x.FirstName,
                x.LastName,
                x.ManagerFirstName,
                x.ManagerLastName);
        }
    }

    Console.WriteLine("press any key...");
    Console.ReadLine();
}

And that’s it. We’ve modeled our stored procedure, which enables us to execute it and return a strongly-typed result set. This approach is even better when you realize you can throw the full power of LINQ at your result set. So for small apps that don’t need a full blown “architecture,” LINQ to SQL has become my data access tool of choice. Always remember your context when choosing the tool for the job.