The Use of SqlParameters

When using SqlParameters with an SqlCommand, there is a disadvantage in using the SqlParameter array. Since an array has to be defined with a hard value, it can become a problem when changing sources. Mostly, the declaration of the array is not changed while parameters were added or deleted. Despite the fact the SqlCommand needs an array, it is very simple to use a List<SqlParameter> which can be easily mapped to an array.

Background

When using a generic way in handling database traffic, this is a very simple way to prevent

• compilation errors due to a changed SqlParameter array length
• null values in sqlparameters are not sent to the database which makes a query or stored procedure go wrong

Creating the Class that will Set the SqlParameters

We start by creating a class with a static method which could reside in the BusinessLogic layer.

using System.Collections;
public class Store
{
	public static List<sqlparameter> SetSqlParameters(SomeObject myObject)
	{
		List<sqlparameter> parms = new List<sqlparameter>();
		parms.Add(new SqlParameter("@City", myObject.City));
		parms.Add(new SqlParameter("@Region", myObject.Region));
		parms.Add(new SqlParameter("@AreaCode", myObject.AreaCode));
		parms.Add(new SqlParameter("@DmaCode", myObject.DmaCode));
		parms.Add(new SqlParameter("@CountryCode", myObject.CountryCode));
		parms.Add(new SqlParameter("@CountryName", myObject.CountryName));
		parms.Add(new SqlParameter("@ContinentCode", myObject.ContinentCode));
		parms.Add(new SqlParameter("@Lattitude", myObject.Lattitude));
		parms.Add(new SqlParameter("@Longitude", myObject.Longitude));
		parms.Add(new SqlParameter("@RegionCode", myObject.RegionCode));
		parms.Add(new SqlParameter("@RegionName", myObject.RegionName));
		parms.Add(new SqlParameter("@CurrencyCode", myObject.CurrencyCode));
		parms.Add(new SqlParameter
			("@CurrencySymbol", myObject.CurrencySymbol));
		parms.Add(new SqlParameter
			("@CurrencyConverter", myObject.CurrencyConverter));
		parms.Add(new SqlParameter
			("@ReceivedResponse", myObject.ReceivedResponse));
		return parms;
	}
}

Based on the values in the SomeObject, the SqlParameters can be set. Remember that any value can be NULL!

Now we continue with a class that will reside in the DataAccess layer. What we do is a SqlConnection used by a SqlCommand that:

• checks for web.config or app.config connectionstringName if not added
• checks for null values in the SqlParameter list
• adds the SqlParameter list as an array to the SqlCommand

Don't forget to include a reference to the DataAccesslayer project to System.configuration otherwise you cannot find connectionstrings in the config file.

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Collections;

public class SQLCommands
{
	public static String DefaultConnectionName()
	{
		// get the first connectionstring from the config file
		return System.Web.Configuration.WebConfigurationManager.
						ConnectionStrings[0].Name;
	}

	#region OpenSqlCommand
	public static SqlCommand OpenCommand(String StoredProcedure, 
	List<SqlParameter> sqlParameters, String ConnectionName)
	{
		// if the connectionName is not given find the first 
		// connectionname in the config file
		ConnectionStringSettings settings = 
		ConfigurationManager.ConnectionStrings
			[ConnectionName ?? DefaultConnectionName()];
		if (settings == null) throw new Exception
					("No connectionstring found");

		SqlCommand cmd = new SqlCommand(StoredProcedure, 
			new SqlConnection(settings.ConnectionString));
		cmd.CommandType = CommandType.StoredProcedure;
		// if there are sqlParameters
		if (null != sqlParameters)
		{
			//  Check on NullValues in the SqlParameter list
			CheckParameters(sqlParameters);

			//after the check change the list to an array and 
			//add to the SqlCommand
			cmd.Parameters.AddRange(sqlParameters.ToArray());
		}
		cmd.CommandTimeout = 60; // 1 minute
		cmd.Connection.Open();

		return cmd;
	}

	private static void CheckParameters(List<SqlParameter> sqlParameters)
	{
		foreach (SqlParameter parm in sqlParameters)
		{
			// when a parm == null, the parm is not send to 
		the database so it returns with the error
			// that it misses a parameter
			// it is very possible that parameter should be null, 
			// so when set t DBNull.Value the parameter
			// is send to the database
			if (null == parm.Value)
				parm.Value = DBNull.Value;
		}
	}
	#endregion OpenSqlCommand

	#region CloseSqlCommand
	public static void CloseCommand(SqlCommand sqlCommand)
	{
		if (null != sqlCommand && 
			sqlCommand.Connection.State == ConnectionState.Open)
			sqlCommand.Connection.Close();
	}
	#endregion CloseSqlCommand
}

Usage of the Classes

We now can create a generic method that calls the SqlCommands class with the needed data. All inserts in your database can go over this class.

public static Int32 InsertData
(String StoredProcedure, List<SqlParameter> parms, String ConnectionName)
        {
            SqlCommand myCommand = null;
            Int32 affectedRows = 0;
            try
            {
                myCommand = SQLCommands.OpenCommand
			(StoredProcedure, parms, ConnectionName);
                affectedRows = myCommand.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                // do something with the error
                string error = err.ToString();
            }
            finally
            {
                SQLCommands.CloseCommand(myCommand);
            }

            return affectedRows;
        }

I call InsertData with the needed values in my code with:

int recordAdded = InsertData("mystoredprocedurename", null, null);

or:

int recordAdded = InsertData("mystoredprocedurename",
Store.SetSqlParameters(GetSomeObject()), null);  	// GetMyObject does a request, 
						// creates SOmeObject 

or:

int recordAdded = InsertData("mystoredprocedurename",
Store.SetSqlParameters(GetSomeObject()), "myConnectionName");  	// GetMyObject does a 
		// request, creates SOmeObject; a named connectionname if it is not the
		// first in my config file

History

• 5^th August, 2011: Initial version