Tag Archives: stored procedure

SqlCommand Parameter Caching

Last week, I wrote a short article about the SqlCommandBuilder.DeriveParameters method that can be used to automatically populate the parameters of a stored procedure command. One of the downsides to this approach is that each call to the method will result in a call to the database. Since stored procedure parameters aren’t typically changing very often, this can result in a lot of unnecessary chatter. So what’s the solution? Cache the parameter collection, of course!

Here’s a very simple way to do just that. I have an abstract class that maintains a static dictionary of SqlParameterCollections, indexed by SQL command. When the static DeriveParameters method is called from within derived classes, the dictionary is checked. If a matching parameter collection is found, it will be used to create a new parameter collection using the existing collection as a blueprint. If no collection is found, SqlCommandBuilder.DeriveParameters is used, and the resulting collection is cached.

public abstract class BaseDataProvider
{
    private static readonly Dictionary<string, SqlParameterCollection> _sqlParameterCollections;

    static BaseDataProvider()
    {
        _sqlParameterCollections = new Dictionary<string, SqlParameterCollection>();
    }

    private static void DeriveParameters(SqlCommand cmd)
    {
        if (_sqlParameterCollections.ContainsKey(cmd.CommandText))
        {
            var paramCollection = _sqlParameterCollections[cmd.CommandText];
            cmd.Parameters.AddRange(
                paramCollection.OfType<SqlParameter>()
                    .Select(x => new SqlParameter
                    {
                        ParameterName = x.ParameterName,
                        SqlDbType = x.SqlDbType,
                        Size = x.Size,
                        Direction = x.Direction
                    })
                    .ToArray()
                );
            return;
        }

        SqlCommandBuilder.DeriveParameters(cmd);
        _sqlParameterCollections.Add(cmd.CommandText, cmd.Parameters);
    }
}

Note that caching stored procedure parameters can have a negative side-effect: if the parameters DO change, your cache may need to be refreshed. This could be handled in a number of ways. You could have the cached parameters expire after a certain amount of time, you could have a manual trigger, or you could build logic into your application that automatically refreshes the cache if the appropriate error condition is detected.

Update 10/10/2012:

The SqlParameter class also implements ICloneable. I’ve updated my caching logic to take advantage of this. Note that you must cast the parameter to ICloneable in order to invoke its Clone method.

private static readonly Dictionary<string, SqlParameter[]> ParameterCache;

// retrieve from cache
if (ParameterCache.ContainsKey(cmd.CommandText))
{
	var paramCollection = ParameterCache[cmd.CommandText];
	cmd.Parameters.AddRange(
		paramCollection.OfType<SqlParameter>()
			.Select(x => ((ICloneable)x).Clone() as SqlParameter)
			.ToArray()
		);
	return;
}

// add to cache
var parameters = cmd.Parameters
	.OfType<SqlParameter>()
	.Select(x => ((ICloneable)x).Clone() as SqlParameter)
	.ToArray();
ParameterCache.Add(cmd.CommandText, parameters);
Advertisements

SqlCommandBuilder.DeriveParameters

One of the first things I learned when getting into .NET was how to access a SQL database. Most of the data access I need to do is stored procedure-based, so I did this by using the Enterprise Library Data Access Block. It was magical, and it worked, so I never asked questions.

The main thing that’s kept me from deviating is the DiscoverParameters method. We use so many stored procedures, and many of them have a large number of parameters. Manually creating parameters in code was just not an option. Today I learned about a fantastic new method that has liberated me, though: SqlCommandBuilder.DeriveParameters.

This handy little method gives me the same benefit of automatically populating a stored procedure command’s SqlParametersCollection. Here’s an example:

var cs = ConfigurationManager.ConnectionStrings["NamedDbConnection"];
using (var conn = new SqlConnection(cs.ConnectionString))
{
	conn.Open();
	using (var cmd = new SqlCommand("SpName", conn))
	{
		cmd.CommandType = CommandType.StoredProcedure;
		SqlCommandBuilder.DeriveParameters(cmd);
		
		cmd.Parameters["@SomeParameter"].Value = someValue;

		using (var reader = cmd.ExecuteReader())
		{
			while (reader.Read())
			{
				var col = reader["SomeColumn"] as string;
			}
		}
	}
}

Unit Testing Stored Procedure Calls with Rhino Mocks

Database stored procedure calls are one of the trickiest things to unit test, and there are many different approaches that can be taken. My team has run the gamut: test DBs that rollback with each run, no testing for direct data access functions (!), virtual functions w/ partial mocks (see here).

The latest approach that I’ve been using is much more straightforward and feels like a more natural use of Rhino Mocks. Let’s look at some examples of how to test some common database stored procedure tasks. (Note that these examples assume use of the Microsoft Enterprise Library.)

Create a mockable Database

The primary challenge that I’ve found with testing database code is that Microsoft.Practices.EnterpriseLibrary.Data.Database isn’t mock-friendly. However, the other “pieces” such as DbCommand and DbCommandParameterCollection are very easy to work with. So, we can solve the Database problem by creating a simple wrapper (Important! Note that the methods have the virtual keyword, which will allow them to be overridden.):

public class DatabaseWrapper
{
    private Database _database;
    private Database Database
    {
        get { return _database = _database ?? DatabaseFactory.CreateDatabase(); }
        set { _database = value; }
    }

    public virtual DbCommand GetStoredProcCommand(string storedProcedureName)
    {
        return Database.GetStoredProcCommand(storedProcedureName);
    }

    public virtual void DiscoverParameters(DbCommand command)
    {
        Database.DiscoverParameters(command);
    }
}

Executing a stored procedure

Now that we are able to mock the database object, we can write some useful tests. Let’s say you want to execute a stored procedure named “MyStoredProcedure,” and you want to write a test to verify that your code handles an exception thrown when it’s executed. That’s very easy!

Here’s my class with the function I want to test:

public class MyDataAccess
{
    public DatabaseWrapper Database { get; set; }
    public Thingy GetThingy()
    {
        Thingy thingy = null;
        try
        {
            var dbCommand = Database.GetStoredProcCommand("MyStoredProcedure");
            Database.DiscoverParameters(dbCommand);
            var result = dbCommand.ExecuteNonQuery();
            // populate thingy
        }
        catch (Exception ex)
        {
            // handle exception
        }
        return thingy;
    }
}

And here’s my test that will throw an exception when the stored procedure is executed. I create my DatabaseWrapper as a PartialMock, allowing me to override its methods.

[TestMethod]
public void GetThingyHandlesException()
{
    // Arrange
    var target = new MyDataAccess();
    var mockDatabase = MockRepository.GeneratePartialMock<DatabaseWrapper>();
    target.Database = mockDatabase;

    // mock Database
    const string storedProc = "MyStoredProcedure";
    var mockDbCommand = MockRepository.GenerateMock<DbCommand>();
    mockDatabase.Expect(x => x.GetStoredProcCommand(storedProc))
        .Return(mockDbCommand);
    mockDatabase.Expect(x => x.DiscoverParameters(mockDbCommand));
    
    // mock DbCommand
    var ex = new Exception("Oh noes!");
    mockDbCommand.Expect(x => x.ExecuteNonQuery())
        .Throw(ex);

    // Act
    var actual = target.GetThingy();

    // Assert
    mockDatabase.VerifyAllExpectations();
    mockDbCommand.VerifyAllExpectations();
    Assert.IsNull(actual);
}

Setting input parameters

Need to set some input parameters? No problem!

dbCommand.Parameters["@id"].Value = id;

And, in your test, you add this:

var mockParams = MockRepository.GenerateMock<DbParameterCollection>();
var mockParam = MockRepository.GenerateMock<DbParameter>();

mockDbCommand.Expect(x => x.Parameters).Return(mockParams);

mockParams.Expect(x => x["@id"]).Return(mockParam);

const int id = 123;
mockParam.Expect(x => x.Value = id);

mockParams.VerifyAllExpectations();
mockParam.VerifyAllExpectations();

Reading output parameters

How about output parameters?

thingy.Value = dbCommand.Parameters["@Value"].Value as string;

Add the additional mocks and assertions:

var mockOutParam = MockRepository.GenerateMock<DbParameter>();

mockParams.Expect(x => x["@Value"]).Return(mockOutParam);

const string value = "MyValue";
mockOutParam.Expect(x => x.Value).Return(value);

mockParams.VerifyAllExpectations();
mockOutParam.VerifyAllExpectations();
Assert.AreEqual(value, actual.Value);

Working with sets of parameters

When you have more than a few parameters to work with, the unit test code can get quite lengthy. I like to keep it clean by extracting the duplicated logic into a separate function, like so:

var paramsToVerify = new List<DbParameter>();
mockParams.Expect(x => x["@whammyparammy"])
    .Return(MockParameter<int>(paramsToVerify));

My function allows you to specify and verify the type of each parameter, but you could easily modify it to expect a specific value.

private static DbParameter MockParameter<T>(List<DbParameter> paramsCollection)
{
    // set Expect with Arg<T>.Is.TypeOf to force the specific type
    var mockParam = MockRepository.GenerateMock<DbParameter>();
    mockParam.Expect(x => x.Value = Arg<T>.Is.TypeOf);

    if (paramsCollection != null)
        paramsCollection.Add(mockParam);

    return mockParam;
}

I keep the parameters in a list so I can verify them during my assertions.

paramsToVerify.ForEach(x => x.VerifyAllExpectations());