Geeks With Blogs

News This is the *old* blog. The new one is at blog.sixeyed.com
Elton Stoneman
This is the *old* blog. The new one is at blog.sixeyed.com

[Source: http://geekswithblogs.net/EltonStoneman]

Overview

Ignoring the fashion, I still make a lot of use of DALs – typically when inheriting a codebase with an established database schema which is full of tried and trusted stored procedures. In the DAL a collection of base classes have all the scaffolding, so the usual pattern is to create a wrapper class for each stored procedure, giving typesafe access to parameter values and output. DAL calls then looks like instantiate wrapper-populate parameters-execute call

    using (var sp = new uspGetManagerEmployees())
    {
        sp.ManagerID = 16;
        using (var reader = sp.Execute())
        {
            //map entities from the output
        }
    } 

Or rolling it all into a fluent DAL call – which is nicer to read and implicitly disposes the resources: 

    var employees = Fluently.Load<List<Employee>>()
                            .With<EmployeeMap>()
                            .From<uspGetManagerEmployees>
                            (
                                i => i.ManagerID = 16,
                                x => x.Execute()
                            );

This is fine, the wrapper classes are very simple to handwrite or generate. But as the codebase grows, you end up with a proliferation of very small wrapper classes:

The wrappers don't add much other than encapsulating the stored procedure call and giving you typesafety for the parameters. With the dynamic extension in .NET 4.0 you have the option to build a single wrapper class, and get rid of the one-to-one stored procedure to wrapper class mapping.

In the dynamic version, the call looks like this: 

    dynamic getUser = new DynamicSqlStoredProcedure("uspGetManagerEmployees", Database.AdventureWorks);
    getUser.ManagerID = 16;
 
    var employees = Fluently.Load<List<Employee>>()
                            .With<EmployeeMap>()
                            .From(getUser);

 The important difference is that the ManagerId property doesn't exist in the DynamicSqlStoredProcedure class. Declaring the getUser object with the dynamic keyword allows you to dynamically add properties, and the DynamicSqlStoredProcedure class intercepts when properties are added and builds them as stored procedure parameters. When getUser.ManagerId = 16 is executed, the base class adds a parameter call (using the convention that parameter name is the property name prefixed by "@"), specifying the correct SQL Server data type (mapping it from the type of the value the property is set to), and setting the parameter value.

Code Sample

This is worked through in a sample project on github – Dynamic Stored Procedure Sample – which also includes a static version of the wrapper for comparison. (I'll upload this to the MSDN Code Gallery once my account has been resurrected). Points worth noting are:

  • DynamicSP.Data – database-independent DAL that has all the data plumbing code.
  • DynamicSP.Data.SqlServer – SQL Server DAL, thin layer on top of the generic DAL which adds SQL Server specific classes. Includes the DynamicSqlStoredProcedure base class.
  • DynamicSqlStoredProcedure.TrySetMember. Invoked when a dynamic member is added. Assumes the property is a parameter named after the SP parameter name and infers the SqlDbType from the framework type. Adds a parameter to the internal stored procedure wrapper and sets its value.
  • uspGetManagerEmployees – the static version of the wrapper.
  • uspGetManagerEmployeesTest – test fixture which shows usage of the static and dynamic stored procedure wrappers.

The sample uses stored procedures from the AdventureWorks database in the SQL Server 2008 Sample Databases.

Discussion

For this scenario, the dynamic option is very favourable. Assuming your DAL is itself wrapped by a higher layer, the stored procedure wrapper classes have very little reuse. Even if you're codegening the classes and test fixtures, it's still additional effort for very little value. The main consideration with dynamic classes is that the compiler ignores all the members you use, and evaluation only happens at runtime. In this case where scope is strictly limited that's not an issue – but you're relying on automated tests rather than the compiler to find errors, but that should just encourage better test coverage. Also you can codegen the dynamic calls at a higher level.

Performance may be a consideration, as there is a first-time-use overhead when the dynamic members of an object are bound. For a single run, the dynamic wrapper took 0.2 seconds longer than the static wrapper. The framework does a good job of caching the effort though, so for 1,000 calls the dynamc version still only takes 0.2 seconds longer than the static:

You don't get IntelliSense on dynamic objects, even for the declared members of the base class, and if you've been using class names as keys for configuration settings, you'll lose that option if you move to dynamics. The approach may make code more difficult to read, as you can't navigate through dynamic members, but you do still get full debugging support.

Posted on Wednesday, April 21, 2010 1:33 PM .NET 4.0 | Back to top


Comments on this post: Using C# 4.0’s DynamicObject as a Stored Procedure Wrapper

# re: Using C# 4.0’s DynamicObject as a Stored Procedure Wrapper
Requesting Gravatar...
Very relevant information!I appreciate the tips and the recommendation.Waiting for your next upcoming post.
Left by platinum protection-77 on Jun 12, 2010 7:22 AM

# re: Using C# 4.0’s DynamicObject as a Stored Procedure Wrapper
Requesting Gravatar...
Great article we have been looking for that for a while for implementation with our Lotus Notes and Domino environment. Thank you.
Left by Website Design Tampa on Aug 16, 2010 8:08 PM

# re: Using C# 4.0’s DynamicObject as a Stored Procedure Wrapper
Requesting Gravatar...
Gtreat point
Left by New Smyrna Beach fishing charter on Aug 19, 2010 10:19 PM

# re: Using C# 4.0’s DynamicObject as a Stored Procedure Wrapper
Requesting Gravatar...
Finally I found the source of wisdom. I mistrust automatic code generating ORM (Means snake in swedish). I love stored procedures ( Iam better at T-SQL then C#) I have not find any better approach then the advice here if you want to use stored procedures. I have tryed typed dataset and the new Entity Data model. But it always ends with that I have change the sp to fit the tools. Like adding parameters with certain names _original. I started to write my own sp handler and then duckducked this blog. Maybe you should start a community for us who for different reasons want to use stored procedures to handle the data cooking.
Left by Patrik L on Feb 22, 2011 6:30 PM

# re: Using C# 4.0’s DynamicObject as a Stored Procedure Wrapper
Requesting Gravatar...
This worked good for me, many thanks
Left by Sandra Mode on Mar 13, 2012 2:58 PM

# DynamicObject as a Stored Procedure Wrapper
Requesting Gravatar...
Temporary Tables

Using this method we can create a temporary table to hold the data while we process it. Once the processing is complete we can use a REF CURSOR to pass the recordset out to an application.
Left by sathsss on Jan 09, 2015 11:29 AM

Your comment:
 (will show your gravatar)


Copyright © Elton Stoneman | Powered by: GeeksWithBlogs.net