[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);
}
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>
<rows>
<row>
<registration>AAAAAAA</registration>
<make>Chrysler</make>
<model>Grand Voyager</model>
</row>
</rows>
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
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
}
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.
