LINQ to SQL Dynamic Stored Procedures

[Concrete/Little bit interesting] I wanted to call stored procedures by name using a string parameter. Sounds simple and with ADO it's a breeze. But with LINQ to SQL, life is a little more complicated. Here is how I cracked the problem.

LINQ to SQL

In Visual Studio, when you add LINQ to SQL Classes to you project, you are creating a class that provides a convenient set of classes for managing tables, views, functions and stored procedures:

When you add LINQ to SQL Classes to your project, Visual Studio creates a .DBML file set. These files describe the managed entities with partial class definitions. The main class is derived from System.Data.Linq.DataContext and provides the link between your code and the database connection. LINQ to SQL works through defining class members that maps LINQ onto the entity and makes use of attributes to map columns, parameters and returns to .Net classes.

LINQ to SQL Classes takes a lot of code out of your application and is easy to use. For example, to call a stored procedure, simply open the .DBML, drag a stored procedure from a data connection on the Server Explorer or Toolbox over onto the Object Relational Designer: this creates the class entries that maps the entity onto LINQ, then from your code, call the stored procedure as shown below:

using (CatalogueDataContext db = new CatalogueDataContext())    
{
     db.usp_Biometrics_Update_OperatorFingerprint(input.OperatorId, input.Fingerprint, false);
}

In the above example, I’m calling the stored procedure usp_Biometrics_Update_OperatorFingerprint which is defined in a database called Catalogue, LINQ to SQL provides me with the data context on which the stored procedures are defined. All I need to do us use an instance of CatalogueDataContext and from this, call the stored procedure. Because it’s all about classes, Visual Studio can help me with the parameters and returns through Intellisense.

The Problem

Under most circumstances calling a stored procedure as described above is sufficient. But there are times when you need to call a stored procedure by name, not by class method. Say for example you have a bunch of stored procedures called:

usp_get_TableName_As_XML

where TableName is a name of a table, any table. The implementation of this stored procedure for any given named table may be complex, for example, they may include selecting output from child tables where foreign key are present. So in this example, a table called Customer may return the following XML:

<rows>
    <row>
        <customerId>1</customerId>
        <customerName>Nigel</customerName>
        <addresses>
            <address>First Postal Address<address>
            <address>Second Postal Address<address>
        </addresses>
    </row>
</rows>

So for each row of Customer data, there may be multiple address rows.

For another table called Car the data is simpler:

<rows>
    <row>
        <registration>AAAAAAA</registration>
        <make>Chrysler</make>
        <model>Grand Voyager</model>
    </row>
</rows>

So for each table, I need a specific stored procedure that returns table data as XML. Each one of these stored procedure returns the XML data as an output parameter and all the stored procedures have the same signature. Below is an illustration of the stored procedure for the table Car:

Create Procedure usp_get_Car_As_XML
    @xml XML OUTPUT
AS
BEGIN
    SET NOCOUNT ON

    set @xml = (
        Select
            registration, make, model
        From
            Car
    )
    For XML RAW('row'), Root('rows'))

    Return @@Error
END

So the problem is, from code, how do I call the stored procedure for a given table name, returning the XML representation of that table?

The Solution

Well of course I could use some sort of if…elseif… or big switch statement. This will work but I’m not keen on big switch statements. What I really want is to call the stored procedure by name. Fortunately, we can use .Net reflection to find the method in the data context and execute this method:

using (CatalogueDataContextEx db = new CatalogueDataContextEx())
{
    XElement xml = null;
    String script = String.Format("usp_get_{0}_As_XML", tableName);
    db.CallScriptByName(script, ref xml);
    
    //handle the results in xml variable
}

Notice that in this example, the data context is called CatalogueDataContextEx. This is a derived class based on CatalogueDataContext and implements the CallScriptByName() method:

public partial class CatalogueDataContextEx : CatalogueDataContext
{
    public void CallScriptByName(String script, ref XElement xml)
    {
        var sp = typeof(CatalogueDataContext).GetMethod(script);
        var result = this.ExecuteMethodCall(this, sp, xml);
        xml = (XElement)result.GetParameterValue(0);
    }
}

So what is going on here?

In my derived class I have access to all public and protected methods of the data context. This is important as I want to call the ExecuteMethodCall() method on the class, which is protected.

I’m using reflection to get a reference to the stored procedure by name, This is then executed using the ExecuteMethodCall. This call takes a list of the parameters to this stored procedure. In our case, there is only a single output parameter for the XML.

Once the call is made, the results are passed back in a IExecuteResult return parameter. This return can be used to access the call’s parameters, in our case the xml output parameter.

And that’s it.

Future Improvements

This implementation is not completely dynamic. Specifically, the CatalogueDataContext must have definitions for the stored procedures I’m going to call. A better implementation would use a generic data context and build up the method to call.

Search This Blog

NotNowNigel