Creating SqlParameters Best Practices

This article summarizes some nice ways to create SqlParameter arrays.  When we use SqlHelper, or even without SqlHelper when we use SqlCommands directly, and we want to pass an array of SqlParameters to the function.  The method I will discuss does not require you to hard-code the number of elements, nor does it require you to create a temporary list of some sort, but it requires you to have all the values up front that you want to insert into the list.

There are several ways to create this array.

1. One is to hardcode the number of parameters as follows:

   SqlParameter array SqlParameter[] sqlParams = new SqlParameter[5];

2. An evolution of this is to use an ArrayList
   ArrayList sqlParams = new ArrayList();
   Then add the parameters using sqlParams.Add(…) to add them
   Then the final step is to use sqlParams.ToArray() and pass that to your SqlHelper.

3. Another evolution would be to use a Generic list of SqlParameters
   System.Collections.Generic.List<SqlParameter> sqlParams = new  System.Collections.Generic.List<SqlParameter>();

4. But in my opinion the BEST way of creating your SqlParameter array is as follows:

   C# Example:

   Vb.Net Example:
   

    

    

    You can even use conditionals such as if your questionCode is null you want to insert a null (C# example):

   4.

   
         SqlParameter[] sqlParams = new SqlParameter[] {
            new SqlParameter("@username", strUserName) ,
            new SqlParameter("@password", strPassword)
         };
   
         Dim params As SqlParameter() = { _
            New SqlParameter("@CenterID", centerID), _
            New SqlParameter("@TypeID", typeID) _
         }
   
       SqlParameter[] sqlParams = new SqlParameter[] {
         new SqlParameter("@Required", required),
         questionCode == null ? new SqlParameter("@Code", DBNull.Value) : new SqlParameter("@Code", questionCode)
       };
   

Update Aug7’09 – Not specifying the DB type of the parameter will actually be slower than manually specifying the parameter sql types (ie int, varchar, etc). However, I find the tradeoff for convenience to be worth a slight loss of speed. This also allows you to change the parameter type in the stored procedure (say from datetime to string), without having to also update it here.

Update: Here is some example code using SqlParameters and DataAdapters

Related Reading:

Head First Java, 2nd Edition

Effective Java (2nd Edition)

Java The Complete Reference, 8th Edition

Other Interesting Posts

• PHP VS. ASP.NET
• Creating SqlParameters Best Practices
• Use Enum (C#) instead of magic numbers