ADO.Net : Simplifying SqlCommand Execution

Summary

While ADO.Net itself is very simple, It is vital to make all data access calls consistently and reliably. This is often dificult when managing a team of junior developers. To this end, I have build a number of utily methods to simplify its implementation.

Source Code

Example

The following code comes directly from VS 2008 documentation 
static void GetSalesByCategory(string connectionString, string categoryName)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        // Create the command and set its properties.
        SqlCommand command = new SqlCommand();
        command.Connection = connection;
        command.CommandText = "SalesByCategory";
        command.CommandType = CommandType.StoredProcedure;

        // Add the input parameter and set its properties.
        SqlParameter parameter = new SqlParameter();
        parameter.ParameterName = "@CategoryName";
        parameter.SqlDbType = SqlDbType.NVarChar;
        parameter.Direction = ParameterDirection.Input;
        parameter.Value = categoryName;

        // Add the parameter to the Parameters collection. 
        command.Parameters.Add(parameter);

        // Open the connection and execute the reader.
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();

        if (reader.HasRows)
        {
            while (reader.Read())
            {
                Console.WriteLine("{0}: {1:C}", reader[0], reader[1]);
            }
        }
        else
        {
            Console.WriteLine("No rows found.");
        }
        reader.Close();
    }
}

Simplifying the SqlCommand

Let's create a few utility methods that will make creation of a SqlCommand simpler. These methods simply take the most common properties, and return a ready-to-use SqlCommand. As a rule, I only access the database via Stored Proceedures, so these methods are clearly designed with that purpose in mind. Similar methods could be constructed to meet the needs your development environment. 
/// <summary>
/// Creates a System.Data.SqlClient.SqlCommand which will call the specified StoredProcedure
/// </summary>
/// <param name="strCnn">The connection string to use.</param>
/// <param name="strSpName">The name of the StoredProcedure to call.</param>
/// <returns>A System.Data.SqlClient.SqlCommand</returns>
public static SqlCommand CreateStoredProcSqlCommand(string strCnn, string strSpName)
{
	return CreateStoredProcSqlCommand(strCnn, strSpName, null);

} //END: CreateStoredProcSqlCommand(string, string)

/// <summary>
/// Creates a System.Data.SqlClient.SqlCommand which will call the specified StoredProcedure
/// </summary>
/// <param name="strCnn">The connection string to use.</param>
/// <param name="strSpName">The name of the StoredProcedure to call.</param>
/// <param name="intTimeout">The time (in seconds) to wait for the command to execute. The default is 30 seconds.</param>
/// <returns>A System.Data.SqlClient.SqlCommand</returns>
public static SqlCommand CreateStoredProcSqlCommand(string strCnn, string strSpName, int? intTimeout)
{
	SqlConnection cnn = new SqlConnection(strCnn);
	return CreateStoredProcSqlCommand(cnn, strSpName, intTimeout);

} //END: CreateStoredProcSqlCommand(SqlConnection, string, int?)

/// <summary>
/// Creates a System.Data.SqlClient.SqlCommand which will call the specified StoredProcedure
/// </summary>
/// <param name="cnn">The SqlConnection to use.</param>
/// <param name="strSpName">The name of the StoredProcedure to call.</param>
/// <returns>A System.Data.SqlClient.SqlCommand</returns>
public static SqlCommand CreateStoredProcSqlCommand(SqlConnection cnn, string strSpName)
{
	return CreateStoredProcSqlCommand(cnn, strSpName, null);

} //END: CreateStoredProcSqlCommand(SqlConnection, string)

/// <summary>
/// Creates a System.Data.SqlClient.SqlCommand which will call the specified StoredProcedure
/// </summary>
/// <param name="cnn">The SqlConnection to use.</param>
/// <param name="strSpName">The name of the StoredProcedure to call.</param>
/// <param name="intTimeout">The time (in seconds) to wait for the command to execute. The default is 30 seconds.</param>
/// <returns>A System.Data.SqlClient.SqlCommand</returns>
public static SqlCommand CreateStoredProcSqlCommand(SqlConnection cnn, string strSpName, int? intTimeout)
{
	SqlCommand cmd = new SqlCommand();
	
	cmd.Connection = cnn;

	if(intTimeout.HasValue)
		cmd.CommandTimeout = intTimeout.Value;

	cmd.CommandType = CommandType.StoredProcedure;
	cmd.CommandText = strSpName;

	return cmd;

} //END: CreateStoredProcSqlCommand(SqlConnection, string, int?)

Hole-In-The-Middle Pattern

Inevitably, we create a connection and command, open the connection, execute the command, loop through the results, DO SOMETHING, close the connection, and perform other necessary clean up tasks. That is a lot of work, just to Do Something! Wouldn't it be nice if we only have to write the Do Something code, and let the system handle all of the setup and tear down. This is where the Hole-In-The-Middle Pattern (aka Higher-Level Function) comes in.

In this case we will create a block of code that does all of the setup and tear down for us, and all we need to do is provide it a function that will Do Something. In most cases what we want to do is instantiate and hydrate an object (or collection of objects). For this purpose, we create the BuildMethod deletegate which accepts a DataReader, and returns an object.

/// <summary>
/// Executes a select statement, then calls the provided method delegate to converts the recordset into an object.
/// </summary>
/// <param name="cmd">The command to execute</param>
/// <param name="buildMethod">A delegate to a method that creates some object given a recordset.</param>
/// <returns>The object created by the BuildMethodDeletegate</returns>
public static object ExecuteSelect(SqlCommand cmd, BuildMethod build)
{
	object oRetVal = null;

	try
	{
		Debug.WriteLine("SP: " + cmd.CommandText, "DBUtil");
		
		cmd.Connection.Open();

		SqlDataReader rdr = cmd.ExecuteReader();

		if (rdr.HasRows)
			oRetVal = build(rdr);

		rdr.Close();
	}
	catch (Exception ex)
	{
		Debug.WriteLine("ERROR: " + ex.Message, "DBUtil");

		ApplicationException appEx = null;
		if (cmd.CommandType == CommandType.StoredProcedure)
			appEx = new ApplicationException(String.Format("Attempt to execute {0} failed.", cmd.CommandText), ex);
		else
			appEx = new ApplicationException(String.Format("Attempt to execute SQL Server query failed."), ex);

		KXM.Fx.ExceptionManagement.ExceptionManager.Publish(appEx);

		return oRetVal;
	}
	finally
	{
		cmd.Connection.Close();
	}

	return oRetVal;

} //END: ExecuteSelect(SqlCommand, KXM.Fx.Database.BuildMethodDeletegate)

/// <summary>
/// Delegate used to build some object from a recordset.
/// </summary>
/// <param name="rdr">A SqlDataReader containing the data needed to create some object</param>
/// <returns>The object created</returns>
public delegate object BuildMethod(SqlDataReader rdr);

Example Revisted

The 34 lines of code in the MS example have now been reduced to 18. This may not seem like much, but multiply that times 100 data access methods and you saved more than 1,500 lines of code! Not to mention that this is much cleaner, easier to read, and easier to learn that the original example. 
static void GetSalesByCategory(string connectionString, string categoryName)
{
	// Create the command and set its properties.
	SqlCommand cmd = CreateStoredProcSqlCommand(connectionString, "SalesByCategory");

	// Add the input parameter and set its properties.
	cmd.Parameters.AddWithValue("@CategoryName", categoryName);

	// Execute the command.
	bool blnRowsFound = (bool)ExecuteSelect(cmd, DoSomething);

	if (!blnRowsFound) Console.WriteLine("No rows found.");
}
		
static object DoSomething(SqlDataReader rdr)
{
	while (rdr.Read())
	{
		Console.WriteLine("{0}: {1:C}", rdr[0], rdr[1]);
	}
	return true;
}