SSDL: Simple Self-Testing Data Layer

• Download source code - 73.1 KB

Introduction

Like everyone else, I occasionally write utilities to consolidate the logic of database access and object population in my applications. For just calling Stored Procedures, I think LINQ to SQL and the Entity Framework are unnecessarily verbose and stateful, both in terms of the code they generate and basic usage. Within a more limited scope, it's possible to create something that's even easier to use and has a cleaner implementation.

Background

This tool is called SSDL, Simple Self-testing Data Layer, because this time around I decided that files that change together should be grouped together.

While I think SSDL's overall design is good, this non-technical part of it is, in my opinion, the most important. It may also stir a bit of controversy; some say that tests should be in a separate project. I don't agree. Take a look at this article if you have any reservations about integrated testing, it's a good read.

File nesting is not required to use the other parts of SSDL, but it's highly recommended.

Using the code

Only once, per development machine: merge this into your Registry, then reboot. You must reboot for the changes to take effect.

;this causes CS projects to nest .sql.cs and .test.cs files under .sql files of the same name
[HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\10.0_Config\
  Projects\{FAE04EC0-301F-11d3-BF4B-00C04F79EFBC}\RelatedFiles\.sql]
".cs"=dword:00000002
".test.cs"=dword:00000001

;this causes VB projects to nest .sql.vb and .test.vb files under .sql files of the same name
[HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\10.0_Config\
  Projects\{F184B08F-C81C-45f6-A57F-5ABD9991F28F}\RelatedFiles\.vb]
".vb"=dword:00000002
".test.vb"=dword:00000001

Now you're ready to create your first SSDL project.

Step 1: Create a new test project, and add references to Pivot.dll and Pivot.Data.dll to it.

Step 2: Add a class to support Stored Procedure calls to your database. For example:

namespace Customers.Data
{
    internal static class CustomerDatabase
    {
        public static TDelegate StoredProcedure<tdelegate>() where TDelegate : class 
        {
            return Pivot.Data.StoredProcedureCaller<tdelegate>.GetMethod(() => 
                   "(your connection string)");
        }
    }
}

Step 3: Add three files for your first Stored Procedure:

• (srocname).sql
• (srocname).sql.cs
• (srocname).test.cs

These files should automatically nest. If not, make sure you reboot after merging the SSDL Registry changes.

A file trio from the sample project:

IF  EXISTS (SELECT * FROM sys.objects WHERE 
  object_id = OBJECT_ID(N'[dbo].[FindCustomers]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[FindCustomers]
GO
CREATE PROCEDURE [dbo].[FindCustomers]
@NameContains varchar(50)
AS

SELECT

    [CustomerID]
    ,[CustomerName]
    ,[EmailAddress]

FROM [Customer]
WHERE [CustomerName] LIKE '%' + @NameContains + '%'

namespace Customers.Data.StoredProcedures
{
    public static partial class FindCustomers
    {

        public static definition Execute = 
          CustomerDatabase.StoredProcedure<definition>();
        public delegate Result[] definition(string NameContains);

        public class Result
        {
            public int CustomerID;
            public string CustomerName;
            public string EmailAddress;
        }
    }
}

In this code, FindCustomers.Execute is the method that will call the Stored Procedure [FindCustomers]. It does what you would expect it to; it creates an array of Result and populates its fields with the data it retrieves.

More generally...

The Stored Procedure's name is taken from the name of the delegate, or as in this case, if the delegate's name is 'definition', the name of its declaring class.

The arguments in the delegate definition should match the parameters the Stored Procedure takes.

You may represent multiple returned columns as fields (not properties) in your delegate's return type as shown here in the Result class. Fields included here are assumed to be required. If your Stored Procedure returns only one value or column, you may use a primitive type (such as string or int), or an array of primitive types instead.

Note: Yes, it would be pretty easy to generate the code in this file automatically. We could also build a tool to check to see if all .sql files match the contents of a target database, or automatically generate many SQL-code-test file trios from an existing set of Stored Procedures. I may support these and other features with an add-in included in the next release.

Finally, the test:

using System;
using System.Linq;
using Customers.Data.TestHelpers;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace Customers.Data.StoredProcedures
{
    partial class FindCustomers
    {
        [TestClass]
        public class Tests : RollbackTest
        {
            [TestMethod]
            public void FindCustomers()
            {

                var uniqueTag = Guid.NewGuid().ToString();
                var customersBeforeAdds = Execute(uniqueTag);
                var addCustomerName1 = "test customer 1 " + uniqueTag + "###";
                CreateNewCustomer.Execute(addCustomerName1, "");
                var customersAfterAdds = Execute(uniqueTag);
                Assert.AreEqual(customersBeforeAdds.Count() + 1, 
                                customersAfterAdds.Count());
                Assert.AreEqual(1, customersAfterAdds.
                    Where(o => o.CustomerName == addCustomerName1).Count());
            }
        }
    }
}

When you're done with your first three files, you can cut and paste the .sql file to use it as a template for adding more. The other two files come along for the ride and get renamed with this top-level file. Open all three and do a search and replace to update the class, test, and Stored Procedure names all at once.

Points of interest

SSDL's Stored Procedure calls are made possible by one very useful function, which I call a generalized method pivoter.

/// <summary>
/// Returns a method of type TDelegate that calls
/// a pivot function you supply. All functions must be static.
/// </summary>
/// <typeparam name="TDelegate">The delegate you wish to create a method
/// for using the supplied pivot function. Must have a return
/// type of void, IConvertable, or new().</typeparam>
/// <typeparam name="TContext">the type of execution context
/// used by the supplied pivoters</typeparam>
/// <param name="PivotMethodRetriever">a static function that
/// will supply a method with the pivot signature
/// [return type of TDelegate] (object[] args, TContext context)</param>
/// <param name="ContextRetreiver">a static function
/// that the supplied pivoters will use to retrieve a context of execution</param>
/// <returns>a method of type TDelegate that calls the supplied pivot function</returns>
public static TDelegate Pivot<TDelegate, TContext>(
    Func<MethodInfo> PivotMethodRetriever,
    Func<TContext> ContextRetreiver
    ) where TDelegate : class
{

    TDelegate ret = null;
    string pivotMethodRetrieverName = "";

    try
    {

        if (PivotMethodRetriever == null)
            throw new ArgumentNullException("PivotMethodRetriever");
        if (!PivotMethodRetriever.Method.IsStatic)
            throw new ArgumentException("This function must be static.", 
                  "PivotMethodRetriever");

        if (ContextRetreiver == null)
            throw new ArgumentNullException("ContextRetreiver");
        if (!ContextRetreiver.Method.IsStatic)
            throw new ArgumentException("This function must be static.", 
                  "ContextRetreiver");

        Type delegateType = typeof(TDelegate);
        if (!typeof(MulticastDelegate).IsAssignableFrom(delegateType))
            throw new ArgumentException(string.Format(
              "type {0} is not a delegate type", delegateType.FullName));

        MethodInfo pivotMethod = PivotMethodRetriever.Invoke();
        CreatedMethods<TDelegate>.Pivots.TryGetValue(pivotMethod, out ret);

        if (ret != null) //prevent duplication
            return ret;

        MethodInfo delegateInvokeMethod = delegateType.GetMethod("Invoke");
        Type delegateReturnType = delegateInvokeMethod.ReturnType;
        Type[] paramTypes = 
          delegateInvokeMethod.GetParameters().Select(
          (ParameterInfo p) => p.ParameterType).ToArray();

        pivotMethodRetrieverName = pivotMethod.DeclaringType.FullName + 
                                   "." + pivotMethod.Name;
        var newMethodName = "dyn__" + pivotMethodRetrieverName + 
                            "__" + delegateType.FullName;

        // check to see if the pivot method has the required
        // signature here, otherwise a scary and confusing
        // SecurityVerificationException ("Operation could destabilize
        // the runtime") could be thrown later when the constructed method is invoked

        if (!pivotMethod.ReturnType.IsAssignableFrom(delegateReturnType))
            ThrowInvalidSignatureError(pivotMethodRetrieverName);

        var pivotMethodParams = pivotMethod.GetParameters();
        if (pivotMethodParams.Count() != 2)
            ThrowInvalidSignatureError(pivotMethodRetrieverName);

        var firstPivotMethodParamType = pivotMethodParams.First().ParameterType;
        if (firstPivotMethodParamType != typeof(object[]))
            ThrowInvalidSignatureError(pivotMethodRetrieverName);

        var secondPivotMethodParamType = pivotMethodParams.Skip(1).First().ParameterType;
        if (secondPivotMethodParamType != typeof(TContext))
            ThrowInvalidSignatureError(pivotMethodRetrieverName);

        DynamicMethod dyn = new DynamicMethod(newMethodName, 
                                delegateReturnType, paramTypes, true);
        ILGenerator il = dyn.GetILGenerator();

        //load all the arguments this method was called with into
        //an object array and push it onto the stack
        LocalBuilder locArgs = il.DeclareLocal(typeof(object[]));
        il.Emit(OpCodes.Ldc_I4, dyn.GetParameters().Count());
        il.Emit(OpCodes.Newarr, typeof(object));
        for (int i = 0; i <= paramTypes.GetUpperBound(0); i++)
        {
            il.Emit(OpCodes.Stloc, locArgs.LocalIndex);
            il.Emit(OpCodes.Ldloc, locArgs.LocalIndex);
            il.Emit(OpCodes.Ldc_I4, i);
            il.Emit(OpCodes.Ldarg, i);
            il.Emit(OpCodes.Box, paramTypes[i]);
            il.Emit(OpCodes.Stelem, typeof(object));
            il.Emit(OpCodes.Ldloc, locArgs.LocalIndex);
        }

        //call the context retriever method to load a context value onto the stack
        il.Emit(OpCodes.Call, ContextRetreiver.Method);

        //call the supplied method
        il.Emit(OpCodes.Call, pivotMethod);

        //mandatory return at end of method call
        il.Emit(OpCodes.Ret);

        ret = (TDelegate)(object)dyn.CreateDelegate(delegateType);
        CreatedMethods<TDelegate>.Pivots.Add(pivotMethod, ret);
        Debug.WriteLine(string.Format("created method {0}", dyn.Name), "Pivoter");

    }
    catch (InvalidProgramException ex)
    //this should be impossible if the above code
    // is correct, but we'll catch it just the same
    {
        throw new InvalidOperationException(string.Format(
          "Method supplied for pivoter {0} for delegate {1} is invalid.", 
          pivotMethodRetrieverName, typeof(TDelegate).FullName, ex));
    }

    return ret;
}

Calling this function returns a method of the delegate type TDelegate. This constructed method, in turn, calls another method supplied by the PivotMethodRetriever argument, which must supply a function with this signature:

TReturnType PivotFunction<TDelegate, TReturnType>(object[] callingArgs, TContext context)

where TReturnType must be the return type of TDelegate.

For calling Stored Procedures, SSDL (Pivot.Data.dll) supplies Pivot with one of five different pivoter methods, depending on TDelegate's return type of one of the following:

• void (execute non-query)
• a single IConvertible type
• an array with elements of IConvertible type
• a single constructible type
• an array with elements of a constructible type

Here is the pivoter for the last case, when TDelegate expects to return an array of elements of a constructible type.

private static TReturnType[] GetItems<TReturnType>(object[] callingArgs, 
        string ConnectionString) where TReturnType : new()
{
    TReturnType[] ret = new TReturnType[0];
    using (DataTable dt = ExecuteReturnTable(callingArgs, ConnectionString))
    {
        if ((dt != null) && dt.Columns.Count > 0)
        {
            int rowUpperBound = dt.Rows.Count - 1;
            ret = new TReturnType[rowUpperBound + 1];
            DataColumn[] cols = GetDataColumns(dt);
            for (int rowIndex = 0; rowIndex <= rowUpperBound; rowIndex++)
            {
                var n = new TReturnType();
                ret[rowIndex] = n;
                for (int colIndex = 0; colIndex < ReturnTypeFieldCount; colIndex++)
                {
                    PopulateField(n, dt.Rows[rowIndex], 
                         ReturnTypeFields[colIndex], cols[colIndex]);
                }
            }
        }
    }
    return ret;
}

Method-pivoting can be used for more than just calling Stored Procedures; it can help manage the transition between any two application layers, when you need a general way of simultaneously processing both runtime I/O and the static metadata associated with it. I will provide another example of this in a different project.

Conclusion

If you use Stored Procedures, SSDL can make it easy to integrate test creation into your development process so that coverage is more likely to always be complete. As an added bonus, code usage is very simple, and all SQL, code, and tests are organized into neat little self-contained units.

History

• November 7^th, 2011: Initial release.