As part of my on going Stored Procedure Framework project for .NET I wanted to create a DbCommand creator that is based upon the "builder pattern" and also uses a fluid-API for setting up the various command properties. What I came up with is the three classes below:
- StoredProcedureDbCommandCreator.cs
- DbCommandCreatorBase.cs
- StoredProcedureDbCommandCreatorTests.cs
I want to be able to call to create a new DbCommand using code similar to below:
var builder = StoredProcedureDbCommandCreator.CreateStoredProcedureDbCommandCreator(sqlConnection, storedProcedureName);
builder
.WithCommandTimeout(120)
.WithParameters(storedProcedureParameterList)
.WithTransaction(currentTransaction)
.BuildCommand();
var command = builder.Command;
I want to create the builder using a factory method an then append various attributes using `WithBlah(...)
` statements to the builder so when I create the DbCommand all of these attributes are passed to it.
So the key class for creating the command is the `StoredProcedureDbCommandCreator
`.
using Dibware.StoredProcedureFramework.Helpers.Base;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
namespace Dibware.StoredProcedureFramework.Helpers
{
public class StoredProcedureDbCommandCreator
: DbCommandCreatorBase
{
#region Constructor
private StoredProcedureDbCommandCreator(DbConnection connection)
: base(connection)
{}
#endregion
#region Public Members
public new void BuildCommand()
{
base.BuildCommand();
}
#endregion
#region Public Factory Methods
public static StoredProcedureDbCommandCreator CreateStoredProcedureDbCommandCreator(
DbConnection connection,
string procedureName)
{
if (connection == null) throw new ArgumentNullException("connection");
if (string.IsNullOrWhiteSpace(procedureName)) throw new ArgumentNullException("procedureName");
var builder = new StoredProcedureDbCommandCreator(connection)
.WithCommandText(procedureName)
.WithCommandType(CommandType.StoredProcedure);
return builder;
}
public new StoredProcedureDbCommandCreator WithCommandTimeout(int commandTimeout)
{
base.WithCommandTimeout(commandTimeout);
return this;
}
public new StoredProcedureDbCommandCreator WithParameters(IEnumerable<SqlParameter> parameters)
{
base.WithParameters(parameters);
return this;
}
public new StoredProcedureDbCommandCreator WithTransaction(SqlTransaction transaction)
{
base.WithTransaction(transaction);
return this;
}
#endregion
#region Private Members
private new StoredProcedureDbCommandCreator WithCommandText(string commandText)
{
base.WithCommandText(commandText);
return this;
}
private new StoredProcedureDbCommandCreator WithCommandType(CommandType commandType)
{
base.WithCommandType(commandType);
return this;
}
#endregion
}
}
The is very little specialised code, and most of the calls pass on to the base class. All of the `WithBlah(...)
` methods return the current instance of the builder so we can call them in a fluent API. The class inherits from the `DbCommandCreatorBase
` which holds common behaviour which may be needed for any other `DbCommandCreator
...` classes I may want to make in the future, maybe like a `ScalarFunctionDbCommandcreator
`? Yes, I am ignoring Yagni for this project, but I have plans for this!
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
namespace Dibware.StoredProcedureFramework.Helpers.Base
{
public abstract class DbCommandCreatorBase
{
#region Fields
private DbCommand _command;
private readonly DbConnection _connection;
private IEnumerable<SqlParameter> _parameters;
private string _commandText;
private int? _commandTimeout;
private SqlTransaction _transaction;
private CommandType _commandType;
#endregion
#region Constructor
protected DbCommandCreatorBase(DbConnection connection)
{
if (connection == null) throw new ArgumentNullException("connection");
_connection = connection;
}
#endregion
#region Public Members
protected void BuildCommand()
{
CreateCommand();
LoadCommandParametersIfAnyExist();
SetCommandText();
SetCommandType();
SetCommandTimeoutIfExists();
SetTransactionIfExists();
}
public DbCommand Command
{
get { return _command; }
}
#endregion
#region Private and Protected Members
private void AddParametersToCommand()
{
foreach (SqlParameter parameter in _parameters)
{
_command.Parameters.Add(parameter);
}
}
private void ClearAnyExistingParameters()
{
bool parametersRequireClearing = (_command.Parameters.Count > 0);
if (parametersRequireClearing)
{
_command.Parameters.Clear();
}
}
private void CreateCommand()
{
_command = _connection.CreateCommand();
}
private bool HasParameters
{
get { return _parameters != null; }
}
protected void LoadCommandParametersIfAnyExist()
{
if (HasParameters)
{
ClearAnyExistingParameters();
AddParametersToCommand();
}
}
private void SetCommandText()
{
_command.CommandText = _commandText;
}
private void SetCommandType()
{
_command.CommandType = _commandType;
}
private void SetCommandTimeoutIfExists()
{
bool hasCommandTimeout = _commandTimeout.HasValue;
if (hasCommandTimeout)
{
_command.CommandTimeout = _commandTimeout.Value;
}
}
private void SetTransactionIfExists()
{
bool hasTransaction = _transaction != null;
if (hasTransaction) _command.Transaction = _transaction;
}
protected void WithCommandText(string commandText)
{
_commandText = commandText;
}
protected void WithCommandTimeout(int commandTimeout)
{
_commandTimeout = commandTimeout;
}
protected void WithCommandType(CommandType commandType)
{
_commandType = commandType;
}
protected void WithParameters(IEnumerable<SqlParameter> parameters)
{
_parameters = parameters;
}
protected void WithTransaction(SqlTransaction transaction)
{
_transaction = transaction;
}
#endregion
}
}
The key call-out for this class is the `BuildCommand()
` method, where the command is created, any parameters added, the command text and command type is set. Then a command timeouts and transaction are set if any were provided.
I have included a suite of tests to test all of the functionality. At teh time of writing two tests are ignored, and these are for the transaction. This is because currently the tests are in a unit-test project but they will need to be moved to an integration test project as they will need a live database connection for the tests to work.
using Dibware.StoredProcedureFramework.Helpers;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
namespace Dibware.StoredProcedureFramework.Tests.UnitTests.Helpers
{
[TestClass]
public class StoredProcedureDbCommandCreatorTests
{
#region Fields
const string StoredProcedureName = "DummyProcedure";
const string ConnectionString = "Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;";
SqlConnection _connection;
#endregion
#region Properties
private SqlConnection Connection
{
get { return _connection; }
}
#endregion
#region Test Pre and Clear down
[TestInitialize]
public void TestSetup()
{
_connection = new SqlConnection(ConnectionString);
}
[TestCleanup]
public void TestCleanup()
{
if (_connection != null)
{
if (_connection.State != ConnectionState.Closed)
{
_connection.Close();
}
_connection.Dispose();
}
}
#endregion
#region Tests
#region Command
[TestMethod]
public void CommandProperty_WhenBuildCommmandNotCalled_ReturnsNull()
{
var builder = StoredProcedureDbCommandCreator.CreateStoredProcedureDbCommandCreator(Connection, StoredProcedureName);
var actualCommand = builder.Command;
Assert.IsNull(actualCommand);
}
[TestMethod]
public void CommandProperty_WhenBuildCommmandIsCalled_ReturnsInstance()
{
var builder = StoredProcedureDbCommandCreator.CreateStoredProcedureDbCommandCreator(Connection, StoredProcedureName);
builder.BuildCommand();
var actualCommand = builder.Command;
Assert.IsNotNull(actualCommand);
}
[TestMethod]
public void CommandProperty_WhenBuildCommmandTwice_ReturnsDistinctInstances()
{
var builder = StoredProcedureDbCommandCreator.CreateStoredProcedureDbCommandCreator(Connection, StoredProcedureName);
builder.BuildCommand();
var actualCommand1 = builder.Command;
builder.BuildCommand();
var actualCommand2 = builder.Command;
Assert.AreNotSame(actualCommand1, actualCommand2);
}
#endregion
#region CommandText
[TestMethod]
public void CommandText_WhenBuildCommmandIsCalled_ReturnsProcedureName()
{
var builder = StoredProcedureDbCommandCreator.CreateStoredProcedureDbCommandCreator(Connection, StoredProcedureName);
builder.BuildCommand();
var actualCommand = builder.Command;
var actualCommandText = actualCommand.CommandText;
Assert.AreEqual(StoredProcedureName, actualCommandText);
}
#endregion
#region CommandTimeout
[TestMethod]
public void CommandTimout_WhenWithCommandTimeoutNotCalled_ReturnsDefaultTimeout()
{
const int defaultCommandTimeout = 30;
var builder = StoredProcedureDbCommandCreator.CreateStoredProcedureDbCommandCreator(Connection, StoredProcedureName);
builder.BuildCommand();
var actualCommand = builder.Command;
var actualCommandTimeout = actualCommand.CommandTimeout;
Assert.AreEqual(defaultCommandTimeout, actualCommandTimeout);
}
[TestMethod]
public void CommandTimout_WhenWithCommandTimeoutIsCalled_ReturnsCorrectTimeout()
{
const int expectedCommandTimeout = 120;
var builder = StoredProcedureDbCommandCreator.CreateStoredProcedureDbCommandCreator(Connection, StoredProcedureName);
builder
.WithCommandTimeout(expectedCommandTimeout)
.BuildCommand();
var actualCommand = builder.Command;
var actualCommandText = actualCommand.CommandTimeout;
Assert.AreEqual(expectedCommandTimeout, actualCommandText);
}
#endregion
#region CommandType
[TestMethod]
public void CommandType_WhenBuildCommmandIsCalled_ReturnsStoredProcedureCommandType()
{
const CommandType expectedCommandType = CommandType.StoredProcedure;
var builder = StoredProcedureDbCommandCreator.CreateStoredProcedureDbCommandCreator(Connection, StoredProcedureName);
builder.BuildCommand();
var actualCommand = builder.Command;
var actualCommandType = actualCommand.CommandType;
Assert.AreEqual(expectedCommandType, actualCommandType);
}
#endregion
#region Parameters
[TestMethod]
public void Parameters_WhenBuildCommmandIsNotCalled_ReturnsEmptParameterCollection()
{
var builder = StoredProcedureDbCommandCreator.CreateStoredProcedureDbCommandCreator(Connection, StoredProcedureName);
builder.BuildCommand();
var actualCommand = builder.Command;
var actualParameters = actualCommand.Parameters;
Assert.AreEqual(0, actualParameters.Count);
}
[TestMethod]
public void Parameters_WhenBuildCommmandIsCalledAndParametersWasSupplied_ReturnsSameInstance()
{
var expectedParameters = new List<SqlParameter>
{
new SqlParameter("Id", SqlDbType.Int),
new SqlParameter("Name", SqlDbType.NVarChar),
};
var builder = StoredProcedureDbCommandCreator.CreateStoredProcedureDbCommandCreator(Connection, StoredProcedureName);
builder
.WithParameters(expectedParameters)
.BuildCommand();
var actualCommand = builder.Command;
var actualParameters = actualCommand.Parameters;
Assert.AreSame(expectedParameters[0], actualParameters[0]);
Assert.AreSame(expectedParameters[1], actualParameters[1]);
}
#endregion
#region Transaction
[TestMethod]
[Ignore] public void Transaction_WhenBuildCommmandIsNotCalled_ReturnsNull()
{
SqlTransaction expectedTransaction = Connection.BeginTransaction();
var builder = StoredProcedureDbCommandCreator.CreateStoredProcedureDbCommandCreator(Connection, StoredProcedureName);
var actualCommand = builder.Command;
var actualCommandTransaction = actualCommand.Transaction;
Assert.IsNull(actualCommandTransaction);
}
[TestMethod]
[Ignore] public void Transaction_WhenBuildCommmandIsCalled_ContainsSameInstanceAsSupplied()
{
SqlTransaction expectedTransaction = Connection.BeginTransaction();
var builder = StoredProcedureDbCommandCreator.CreateStoredProcedureDbCommandCreator(Connection, StoredProcedureName);
builder.BuildCommand();
var actualCommand = builder.Command;
var actualCommandTransaction = actualCommand.Transaction;
Assert.AreSame(expectedTransaction, actualCommandTransaction);
}
#endregion
#endregion
}
}
Hopefully you too may be able to use this code, or adapt it for another purpose, or uses it as reference for a Builder class in one of your projects.
Thanks for dropping by.
The full code is available on my StoredProcedureDbCommandCreator Gist