C# Fluid API DbCommand Creator for use with Stored Procedures

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

        /// <summary>
        /// Builds and sets up the command based upon the settings that have 
        /// been previously passed to this builder.
        /// </summary>
        /// <remarks>
        /// Should call into base implementation before executing any addtional code
        /// </remarks>
        public new void BuildCommand()
        {
            base.BuildCommand();
        }

        #endregion

        #region Public Factory Methods

        /// <summary>
        /// Creates the stored procedure database command creator.
        /// </summary>
        /// <param name="connection">
        /// The connection to be passed to the command when it is constructed.
        /// </param>
        /// <param name="procedureName">
        /// The name of the stored procedure for which the commmand is to call.
        /// </param>
        /// <returns></returns>
        /// <exception cref="System.ArgumentNullException">
        /// connection
        /// or
        /// procedureName
        /// </exception>
        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;
        }

        /// <summary>
        /// Adds a command timeout to the builder which will be passed to the command
        /// when it is construted.
        /// </summary>
        /// <param name="commandTimeout">The value of the command timeout.</param>
        /// <returns></returns>
        public new StoredProcedureDbCommandCreator WithCommandTimeout(int commandTimeout)
        {
            base.WithCommandTimeout(commandTimeout);
            return this;
        }

        /// <summary>
        /// Adds the specified parameters to the builder, and these will be added
        /// to the command when it is built.
        /// </summary>
        /// <param name="parameters">The parameters to add to the command.</param>
        /// <returns></returns>
        public new StoredProcedureDbCommandCreator WithParameters(IEnumerable<SqlParameter> parameters)
        {
            base.WithParameters(parameters);
            return this;
        }

        /// <summary>
        /// Adds the specified transaction to the builder, and these will be added
        /// to the command when it is built.
        /// </summary>
        /// <param name="transaction">The transaction to add to teh command.</param>
        /// <returns></returns>
        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

        /// <summary>
        /// Initializes a new instance of the <see cref="DbCommandCreatorBase"/> class.
        /// </summary>
        /// <param name="connection">
        /// The DbConnection to run the command against.
        /// </param>
        /// <exception cref="System.ArgumentNullException">connection</exception>
        protected DbCommandCreatorBase(DbConnection connection)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            _connection = connection;
        }

        #endregion

        #region Public Members

        /// <summary>
        /// Builds and sets up the command based upon the settings that have 
        /// been previously passed to this builder.
        /// </summary>
        protected void BuildCommand()
        {
            CreateCommand();
            LoadCommandParametersIfAnyExist();
            SetCommandText();
            SetCommandType();
            SetCommandTimeoutIfExists();
            SetTransactionIfExists();
        }

        /// <summary>
        /// Gets the command or null if it has not been built.
        /// </summary>
        /// <value>
        /// The command.
        /// </value>
        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()
        {
            // ARRANGE
            var builder = StoredProcedureDbCommandCreator.CreateStoredProcedureDbCommandCreator(Connection, StoredProcedureName);

            // ACT
            var actualCommand = builder.Command;

            // ASSERT
            Assert.IsNull(actualCommand);
        }

        [TestMethod]
        public void CommandProperty_WhenBuildCommmandIsCalled_ReturnsInstance()
        {
            // ARRANGE
            var builder = StoredProcedureDbCommandCreator.CreateStoredProcedureDbCommandCreator(Connection, StoredProcedureName);

            // ACT
            builder.BuildCommand();
            var actualCommand = builder.Command;

            // ASSERT
            Assert.IsNotNull(actualCommand);
        }

        [TestMethod]
        public void CommandProperty_WhenBuildCommmandTwice_ReturnsDistinctInstances()
        {
            // ARRANGE
            var builder = StoredProcedureDbCommandCreator.CreateStoredProcedureDbCommandCreator(Connection, StoredProcedureName);

            // ACT
            builder.BuildCommand();
            var actualCommand1 = builder.Command;
            builder.BuildCommand();
            var actualCommand2 = builder.Command;

            // ASSERT
            Assert.AreNotSame(actualCommand1, actualCommand2);
        }

        #endregion

        #region CommandText

        [TestMethod]
        public void CommandText_WhenBuildCommmandIsCalled_ReturnsProcedureName()
        {
            // ARRANGE
            var builder = StoredProcedureDbCommandCreator.CreateStoredProcedureDbCommandCreator(Connection, StoredProcedureName);

            // ACT
            builder.BuildCommand();
            var actualCommand = builder.Command;
            var actualCommandText = actualCommand.CommandText;

            // ASSERT
            Assert.AreEqual(StoredProcedureName, actualCommandText);
        }

        #endregion

        #region CommandTimeout

        [TestMethod]
        public void CommandTimout_WhenWithCommandTimeoutNotCalled_ReturnsDefaultTimeout()
        {
            // ARRANGE
            const int defaultCommandTimeout = 30;
            var builder = StoredProcedureDbCommandCreator.CreateStoredProcedureDbCommandCreator(Connection, StoredProcedureName);

            // ACT
            builder.BuildCommand();
            var actualCommand = builder.Command;
            var actualCommandTimeout = actualCommand.CommandTimeout;

            // ASSERT
            Assert.AreEqual(defaultCommandTimeout, actualCommandTimeout);
        }

        [TestMethod]
        public void CommandTimout_WhenWithCommandTimeoutIsCalled_ReturnsCorrectTimeout()
        {
            // ARRANGE
            const int expectedCommandTimeout = 120;
            var builder = StoredProcedureDbCommandCreator.CreateStoredProcedureDbCommandCreator(Connection, StoredProcedureName);

            // ACT
            builder
                .WithCommandTimeout(expectedCommandTimeout)
                .BuildCommand();
            var actualCommand = builder.Command;
            var actualCommandText = actualCommand.CommandTimeout;

            // ASSERT
            Assert.AreEqual(expectedCommandTimeout, actualCommandText);
        }

        #endregion

        #region CommandType

        [TestMethod]
        public void CommandType_WhenBuildCommmandIsCalled_ReturnsStoredProcedureCommandType()
        {
            // ARRANGE
            const CommandType expectedCommandType = CommandType.StoredProcedure;
            var builder = StoredProcedureDbCommandCreator.CreateStoredProcedureDbCommandCreator(Connection, StoredProcedureName);

            // ACT
            builder.BuildCommand();
            var actualCommand = builder.Command;
            var actualCommandType = actualCommand.CommandType;

            // ASSERT
            Assert.AreEqual(expectedCommandType, actualCommandType);
        }

        #endregion

        #region Parameters

        [TestMethod]
        public void Parameters_WhenBuildCommmandIsNotCalled_ReturnsEmptParameterCollection()
        {
            // ARRANGE
            var builder = StoredProcedureDbCommandCreator.CreateStoredProcedureDbCommandCreator(Connection, StoredProcedureName);

            // ACT
            builder.BuildCommand();
            var actualCommand = builder.Command;
            var actualParameters = actualCommand.Parameters;

            // ASSERT
            Assert.AreEqual(0, actualParameters.Count);
        }

        [TestMethod]
        public void Parameters_WhenBuildCommmandIsCalledAndParametersWasSupplied_ReturnsSameInstance()
        {
            // ARRANGE
            var expectedParameters = new List<SqlParameter>
            {
                new SqlParameter("Id", SqlDbType.Int),
                new SqlParameter("Name", SqlDbType.NVarChar),
            };
            var builder = StoredProcedureDbCommandCreator.CreateStoredProcedureDbCommandCreator(Connection, StoredProcedureName);

            // ACT
            builder
                .WithParameters(expectedParameters)
                .BuildCommand();
            var actualCommand = builder.Command;
            var actualParameters = actualCommand.Parameters;

            // ASSERT
            Assert.AreSame(expectedParameters[0], actualParameters[0]);
            Assert.AreSame(expectedParameters[1], actualParameters[1]);
        }

        #endregion

        #region Transaction

        [TestMethod]
        [Ignore] // Requires a valid connection first!
        public void Transaction_WhenBuildCommmandIsNotCalled_ReturnsNull()
        {
            // ARRANGE
            SqlTransaction expectedTransaction = Connection.BeginTransaction();
            var builder = StoredProcedureDbCommandCreator.CreateStoredProcedureDbCommandCreator(Connection, StoredProcedureName);

            // ACT
            var actualCommand = builder.Command;
            var actualCommandTransaction = actualCommand.Transaction;

            // ASSERT
            Assert.IsNull(actualCommandTransaction);
        }

        [TestMethod]
        [Ignore] // Requires a valid connection first!
        public void Transaction_WhenBuildCommmandIsCalled_ContainsSameInstanceAsSupplied()
        {
            // ARRANGE
            SqlTransaction expectedTransaction = Connection.BeginTransaction();
            var builder = StoredProcedureDbCommandCreator.CreateStoredProcedureDbCommandCreator(Connection, StoredProcedureName);

            // ACT
            builder.BuildCommand();
            var actualCommand = builder.Command;
            var actualCommandTransaction = actualCommand.Transaction;

            // ASSERT
            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