C# articles and tutorials on SharpDeveloper.NET

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

   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: Here is some example code using SqlParameters and DataAdapters

Print | posted on Friday, May 25, 2007 9:35 AM | Filed Under [ .NET articles ]