Click here to Skip to main content
15,115,930 members
Articles / Programming Languages / C#
Article
Posted 6 Aug 2011

Tagged as

Stats

57.9K views
6 bookmarked

Disadvantages of SqlParameters Turned into Advantages

Rate me:
Please Sign up or sign in to vote.
2.06/5 (10 votes)
10 Aug 2011CPOL3 min read
When using SqlParameters in a query, the array of Parameters can give a headache. How to prevent the use of paracetamol.

SqlParameters/parameters.gif

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:

C#
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:

C#
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.

C#
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.

C#
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.

C#
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:

C#
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:

C#
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:

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

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 use the given connectionstring name.

Findings

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

C#
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

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

License

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

Share

About the Author

Herman<T>.Instance
Software Developer (Senior)
Netherlands Netherlands
I started in the eightees with Basic and QuickBasic. When the ninetees started dBase was my thing. Close to the millenium years I swapped to C++ and since 2003 it is only programming in c#.

There is one thing about me.
Not that kind of OO guy and definately not an ORM-fan. I know a database to well to give the importance of data out in the hands of a tool.

Comments and Discussions

 
QuestionNice one Pin
poiuytrewq1234987654331-Aug-12 23:32
Memberpoiuytrewq1234987654331-Aug-12 23:32 
QuestionDisadvantage of arrays?? Pin
HaBiX10-Aug-11 23:36
MemberHaBiX10-Aug-11 23:36 
AnswerRe: Disadvantage of arrays?? Pin
Herman<T>.Instance11-Aug-11 0:29
MemberHerman<T>.Instance11-Aug-11 0:29 
GeneralRe: Disadvantage of arrays?? Pin
HaBiX11-Aug-11 0:41
MemberHaBiX11-Aug-11 0:41 
GeneralRe: Disadvantage of arrays?? Pin
Herman<T>.Instance11-Aug-11 0:47
MemberHerman<T>.Instance11-Aug-11 0:47 
GeneralRe: Disadvantage of arrays?? Pin
HaBiX11-Aug-11 0:52
MemberHaBiX11-Aug-11 0:52 
GeneralRe: Disadvantage of arrays?? Pin
Herman<T>.Instance11-Aug-11 0:54
MemberHerman<T>.Instance11-Aug-11 0:54 
GeneralRe: Disadvantage of arrays?? Pin
HaBiX11-Aug-11 1:00
MemberHaBiX11-Aug-11 1:00 
GeneralRe: Disadvantage of arrays?? Pin
Herman<T>.Instance11-Aug-11 1:02
MemberHerman<T>.Instance11-Aug-11 1:02 
GeneralMy vote of 1 Pin
Paul Conrad7-Aug-11 18:47
professionalPaul Conrad7-Aug-11 18:47 
GeneralRe: My vote of 1 Pin
Herman<T>.Instance7-Aug-11 22:29
MemberHerman<T>.Instance7-Aug-11 22:29 
GeneralRe: My vote of 1 Pin
Paul Conrad8-Aug-11 10:03
professionalPaul Conrad8-Aug-11 10:03 
QuestionSometimes it pays... Pin
Dewey6-Aug-11 21:26
MemberDewey6-Aug-11 21:26 
AnswerRe: Sometimes it pays... Pin
Herman<T>.Instance7-Aug-11 10:32
MemberHerman<T>.Instance7-Aug-11 10:32 
GeneralRe: Sometimes it pays... Pin
PIEBALDconsult7-Aug-11 19:11
professionalPIEBALDconsult7-Aug-11 19:11 
General[My vote of 1] I have no idea what you are talking about, but I agree that you must be doing something wrong Pin
PIEBALDconsult6-Aug-11 12:39
professionalPIEBALDconsult6-Aug-11 12:39 
GeneralRe: [My vote of 1] I have no idea what you are talking about, but I agree that you must be doing something wrong Pin
Herman<T>.Instance7-Aug-11 10:40
MemberHerman<T>.Instance7-Aug-11 10:40 
GeneralRe: [My vote of 1] I have no idea what you are talking about, but I agree that you must be doing something wrong Pin
PIEBALDconsult7-Aug-11 11:33
professionalPIEBALDconsult7-Aug-11 11:33 
GeneralRe: [My vote of 1] I have no idea what you are talking about, but I agree that you must be doing something wrong Pin
Herman<T>.Instance7-Aug-11 12:06
MemberHerman<T>.Instance7-Aug-11 12:06 
GeneralRe: [My vote of 1] I have no idea what you are talking about, but I agree that you must be doing something wrong Pin
PIEBALDconsult7-Aug-11 14:56
professionalPIEBALDconsult7-Aug-11 14:56 
GeneralRe: [My vote of 1] I have no idea what you are talking about, but I agree that you must be doing something wrong Pin
Herman<T>.Instance7-Aug-11 22:28
MemberHerman<T>.Instance7-Aug-11 22:28 
GeneralRe: [My vote of 1] I have no idea what you are talking about, but I agree that you must be doing something wrong Pin
Pete O'Hanlon8-Aug-11 1:35
mvePete O'Hanlon8-Aug-11 1:35 
GeneralRe: [My vote of 1] I have no idea what you are talking about, but I agree that you must be doing something wrong Pin
PIEBALDconsult8-Aug-11 3:36
professionalPIEBALDconsult8-Aug-11 3:36 
GeneralRe: [My vote of 1] I have no idea what you are talking about, but I agree that you must be doing something wrong Pin
Pete O'Hanlon8-Aug-11 3:49
mvePete O'Hanlon8-Aug-11 3:49 
GeneralRe: [My vote of 1] I have no idea what you are talking about, but I agree that you must be doing something wrong Pin
PIEBALDconsult8-Aug-11 3:50
professionalPIEBALDconsult8-Aug-11 3:50 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.