Name

Prepare

Synopsis

Command.Prepare();

Calling the Prepare( ) method creates a prepared version of a command in the data source, leading to improved performance if you want to reuse it multiple times with different values. However, some providers will not support this method, and others will not demonstrate any performance increase. Typically, SQL Server Version 6.5 or earlier may demonstrate an improvement, while SQL Server 7 databases perform all the necessary optimization automatically.

If you wish to use the Prepare( ) method, call it only after you have defined the Command and added all its parameters.

Example

The following example uses the Prepare( ) method before invoking a parameterized UPDATE command:

string SQL = "UPDATE Categories SET CategoryName=@CategoryName " +

             "WHERE CategoryID=@CategoryID";



SqlCommand cmd = new SqlCommand(SQL, con);



SqlParameter param;

param = cmd.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15);

param.Value = "Beverages";



param = cmd.Parameters.Add("@CategoryID", SqlDbType.Int);

param.Value = 1;



// Prepare and execute the command.

con.Open();

cmd.Prepare();

int rowsAffected = cmd.ExecuteNonQuery();

con.Close();

Notes

Because Prepare( ) requires an extra trip to the data source (to compile the initial command), it can actually reduce performance. It’s recommended that you use this method only if you have tested it and confirmed it achieves a performance increase under your operating conditions.

When using Prepare( ), make sure each Parameter object has the correct value set for its Parameter.Size properties. Otherwise, data may be truncated, and no error will occur to inform you of the problem.