ADO.NET 2.0 Performance Guidelines - Parameters

From Guidance Share
Jump to navigationJump to search

- J.D. Meier, Srinath Vasireddy, Ashish Babbar, and Alex Mackman


Use the Parameters Collection When You Call a Stored Procedure

Use the Parameters collection property of the SqlCommand object to pass parameters to a stored procedure. By using strongly typed parameters, types do not need to be discovered at run time. You can also save round trips by checking the data type on the client; for example, you can save round trips by checking the Web server. This prevents wasted cycles and wasted bandwidth that is caused by passing invalid data to the database server. The following code fragment shows how to add a typed parameter to the Parameters collection. 

SqlDataAdapter adapter = new SqlDataAdapter("GetProductDesc",
                                            conn);
adapter.SelectCommand.CommandType =  CommandType.StoredProcedure;
SqlParameter parm = adapter.SelectCommand.Parameters.Add(
                                   "@ProdID", SqlDbType.Int);
parm.Value = 10;



Use the Parameters Collection When You Build SQL Statements

Even if you do not use stored procedures for data access, you should still use the Parameters collection when you build your SQL statements in code. By using the Parameter collection and by explicitly setting the data type, you reduce the likelihood that the Parameter object could set an invalid type. The following code fragment shows how to use the Parameters collection when you build your SQL statements in code. 

SqlDataAdapter adapter = new SqlDataAdapter(
 "SELECT ProductID, ProductName FROM Products WHERE ProductID = @ProdID", conn);
// Set the parameter including name and type.
SqlParameter parm = adapter.SelectCommand.Parameters.Add(
                 "@ProdID", SqlDbType.Int);
// Set the parameter value.
parm.Value = 10;


Explicitly Create Stored Procedure Parameters

Identifying parameters at run time requires a round trip to the server for each use of a stored procedure. This is an expensive operation. Explicitly create parameters for stored procedures. Explicitly supply the parameter type, size, precision, and scale information to prevent the Command object from recreating them every time a command is run. The following code demonstrates how to set the type, size, and direction. 

void GetProductDetails( int productID,
                   out string productName, out decimal unitPrice)
{
  using( SqlConnection conn = new SqlConnection(
       "server=(local);Integrated Security=SSPI;database=Northwind") )
 {
   // Set up the command object used to run the stored procedure.
   SqlCommand cmd = new SqlCommand( "GetProductDetails", conn );
   cmd.CommandType = CommandType.StoredProcedure;
   // Establish stored procedure parameters.
   //  @ProductID int INPUT
   //  @ProductName nvarchar(40) OUTPUT
   //  @UnitPrice money OUTPUT

   // Must explicitly set the direction of output parameters.
   SqlParameter paramProdID =
            cmd.Parameters.Add( "@ProductID", SqlDbType.Int );
   paramProdID.Direction = ParameterDirection.Input;
   SqlParameter paramProdName =
            cmd.Parameters.Add( "@ProductName", SqlDbType.VarChar, 40 );
   paramProdName.Direction = ParameterDirection.Output;
   SqlParameter paramUnitPrice =
            cmd.Parameters.Add( "@UnitPrice", SqlDbType.Money );
   paramUnitPrice.Direction = ParameterDirection.Output;

   conn.Open();
   cmd.ExecuteNonQuery( );
   // Return output parameters from the stored procedure.
   productName = paramProdName.Value.ToString();
   unitPrice = (decimal)paramUnitPrice.Value;
 }
}


Specify Parameter Types

When you create a new parameter, use the relevant enumerated type to specify the data type of the parameter. Use an enumerated type such as SqlDbType or OleDbType. This prevents unnecessary type conversions that are otherwise performed by the data provider.


Cache Stored Procedure SqlParameter Objects

Often, applications must run commands multiple times. To avoid recreating the SqlParameter objects each time, cache them so that they can be reused later. A good approach is to cache parameter arrays in a Hashtable object. Each parameter array contains the parameters that are required by a particular stored procedure that is used by a particular connection. The following code fragment shows this approach. 

public static void CacheParameterSet(string connectionString,
                                    string commandText,
                                    params SqlParameter[] commandParameters)
{
 if( connectionString == null || connectionString.Length == 0 )
   throw new ArgumentNullException( "connectionString" );
 if( commandText == null || commandText.Length == 0 )
   throw new ArgumentNullException( "commandText" );

 string hashKey = connectionString + ":" + commandText;
 paramCache[hashKey] = commandParameters;
}

The following function shows the equivalent parameter retrieval function 

public static SqlParameter[] GetCachedParameterSet(string connectionString, string commandText)
{
 if( connectionString == null || connectionString.Length == 0 )
   throw new ArgumentNullException( "connectionString" );
 if( commandText == null || commandText.Length == 0 )
   throw new ArgumentNullException( "commandText" );

 string hashKey = connectionString + ":" + commandText;

 SqlParameter[] cachedParameters = paramCache[hashKey] as SqlParameter[];
 if (cachedParameters == null)
 {
   return null;
 }
 else
 {
   return CloneParameters(cachedParameters);
 }
}

When parameters are retrieved from the cache, a cloned copy is created so that the client application can change parameter values, without affecting the cached parameters. The CloneParameters method is shown in the following code fragment. 

private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters)
{
 SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length];

 for (int i = 0, j = originalParameters.Length; i < j; i++)
 {
   clonedParameters[i] =
     (SqlParameter)((ICloneable)originalParameters[i]).Clone();
 }
 return clonedParameters;
}


References