ADO.NET 2.0 Performance Guidelines - Parameters
- 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
- The code samples for the parameter caching approach that is shown above are based on samples from the Data Access Application Block. The Data Access Application Block implements this functionality in a generic data access component. For more information, see the Data Access Application Block on MSDN at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp.