Disadvantages of SqlParameters Turned into Advantages

Herman<T>.Instance

2.06/5 (10 votes)

10 Aug 2011CPOL3 min read

65.5K

When using SqlParameters in a query, the array of Parameters can give a headache. How to prevent the use of paracetamol.

The Beginning....

In this article, I describe the following situation:

The data access layer holds a generic method that communicates with Microsoft SQL Server
Data to and from the database goes via stored procedures
In the business layer, the data is collected and if needed, a sqlparameter(s) for a stored procedure is added to a sqlparameter list
The generic method can detect in the config file what the first connectionstring is when none is provided in the call
Automatic check on NULL values in sqlparameters

What I see mostly is that people create an SQL command in the onclick event of some button and do command.parameters.Add(parameternam, some value);. Example from MSDN:

private static void UpdateDemographics(Int32 customerID,
    string demoXml, string connectionString)
{
    // Update the demographics for a store, which is stored 
    // in an xml column. 
    string commandText = "UPDATE Sales.Store SET Demographics = @demographics "
        + "WHERE CustomerID = @ID;";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand(commandText, connection);
        command.Parameters.Add("@ID", SqlDbType.Int);
        command.Parameters["@ID"].Value = customerID;

        // Use AddWithValue to assign Demographics.
        // SQL Server will implicitly convert strings into XML.
        command.Parameters.AddWithValue("@demographics", demoXml);

        try
        {
            connection.Open();
            Int32 rowsAffected = command.ExecuteNonQuery();
            Console.WriteLine("RowsAffected: {0}", rowsAffected);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
}

In this situation, all the needed data and the stored procedure name and the connectionname are send to the generic method. By using a list of sqlparameters, the command.Parameters.AddRange(SqlParameter[]) is used. This is where my case comes to light.

The Disadvantage of the sqlparameter Array

The Array

By using SqlParameter[], you have to define it with a value that holds the exact number of parameters for the stored procedure, like:

SqlParameter[] myParms = new SqlParameter[10]();
myparms[0] = new SqlParameter("MyParmName","@myParmValue");
...

But what if your stored procedure changes due to rework after some time your project is running. Well, you change the sqlparameters. The changes are there that you forget about your array definition of 10..... Luckily the List<> function in .NET gives the opportunity to be transformed to an array on the fly with the ToArray() method. So when doing the command.Parameters.AddRange(ListOfParameter), you use the flexibility of the List function so you do not have to think about defined arrays.

The NULL Value

The other disadvantage of sqlparameters is when the value is null. The parameter will not be sent in the request to the database so the stored procedure will be telling you that it is missing a parameter. By setting the value to DBNULL.Value, the parameter is received in the database.

In Code Examples

The business layer holds the method that sets the properties of an object to a list of sqlparameters.

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 list of SqlParameters can be set. Remember that any value can be NULL!

Now we continue with the class that will reside in the DataAccess layer. The methods in this class are set up around the SqlCommand and SqlConnection objects. The purpose is to have a generic method that can be used anywhere in project(s).

Don't forget to include a reference to System.configuration in the DataAccesslayer project, 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
{
	private 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&lt;SqlParameter&gt; 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 (sqlParameters != null)
		{
			//  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&lt;SqlParameter&gt; sqlParameters)
	{
		foreach (SqlParameter parm in sqlParameters)
		{
			// when a parm.Value is null, the parm is not send to 
			// the database so the stored procedure returns with the error
			// that it misses a parameter
			// it is very possible that the parameter should be null, 
			// so when set it DBNull.Value the parameter
			// is send to the database

			if (parm.Value == null)
				parm.Value = DBNull.Value;
		}
	}
	#endregion OpenSqlCommand

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

What we do in the above method OpenCommand 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 and set then to DBNULL.Value if needed
adds the SqlParameter list as an array to the SqlCommand.ParametersAddRange()

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.

Even this class will be in the Data Access Layer.

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);

In this case, no sqlparameters are added to the command.Parameters because it is possible my stored procedure does not need any parameters. The OpenCommand method will search for a connectionstring in the config file.

Or call it like this:

int recordAdded = InsertData("mystoredprocedurename", 
		Store.SetSqlParameters(GetSomeObject()), null);

GetSomeObject does a request and creates SOmeObject which will be set to a list of sqlparameters. The parameters are checked for null values and are added to command.Parameters.AddRange() and set to an array. The OpenCommand method will search for a connectionstring in the config file.

Or call it like this:

int recordAdded = InsertData("mystoredprocedurename", 
Store.SetSqlParameters(GetSomeObject()), "myConnectionName");

Findings

It is of course possible to extend the class that holds the InsertData method with the next methods:

public static Int32 InsertData(String StoredProcedure)
{
	return InsertData(StoredProcedure, null, null);
}

public static Int32 InsertData(String StoredProcedure, List<SqlParameter> parms)
{
	return InsertData(StoredProcedure, parms, null);
}

public static Int32 InsertData(String StoredProcedure, String ConnectionName)
{
	return InsertData(StoredProcedure, ConnectionName);
}

History

5^th August, 2011: Initial version
9^th August, 2011: Article updated

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)