In the examples so far, I’ve built the SQL commands as simple text. There is another way that’s more flexible. Of course, more flexibility usually involves more code.

The basic concept is that an IDbCommand.Parameters property returns an IDataParameterCollection, which is a collection of IDataParameter instances. The IDataParameter interface’s properties include the name of a parameter coded into the IDbCommand, and the value you wish to bind to that name. Look at the following code snippet for an example:

SqlCommand command = new SqlCommand(
  "insert into coupons ( coupon_code, discount_amount, " +
  "discount_type, expiration_date ) " +
  "values ( @coupon_code, @discount_amount, @discount_type, " +
  "@expiration_date )", connection);

command.Parameters.Add(new SqlParameter("@coupon_code", "665RQ"));
command.Parameters.Add(new SqlParameter("@discount_amount", 15));
command.Parameters.Add(new SqlParameter("@discount_type", 
  DiscountType.Percentage));
command.Parameters.Add(new SqlParameter("@expiration_date ", 
  new DateTime(2002,11,30)));

As you can see, the names of the parameters are embedded into the SQL command itself. Each parameter is then added to the IDataParameterCollection as a SqlParameter, with its name and value. The names I’ve used in this snippet match the names of the respective columns, with an @ prefixed; while the naming of the parameters is entirely up to you, the @ prefix is required.

You can use the Parameters property on any IDbCommand, for any select, insert, update, or delete statement. There are other properties to the IDbParameter subclasses that pertain to the specific types of databases they know about.

The major benefit of building an IDbCommand this way is that every parameter can be assigned dynamically, instead of having to hard-code the command by repeatedly appending strings. Another benefit is that type conversion is automatic, so you don’t have to use the ToString( ) method or any sort of string formatting to get a value that the database will accept. Finally, most database servers actually run more efficiently when a query is built this way; the query does not need to be parsed again every time it is run again with different data values.

Example 11-3 shows how both these benefits can be exploited in a rewritten version of the AddCoupon program from Example 11-2.

using System;
using System.Data;
using System.Data.SqlClient;

public class AddCoupon {
  public static void Main(string [ ] args) {
    SqlConnection connection = new SqlConnection(
      "Initial Catalog=AngusHardware; User ID=sa");

    SqlCommand command = new SqlCommand(
      "insert into coupons ( coupon_code, discount_amount, " +
      "discount_type, expiration_date ) " +
      "values ( @coupon_code, @discount_amount, " +
      "@discount_type, @expiration_date )", connection);

    SqlParameter couponCode = command.Parameters.Add(
      new SqlParameter("@coupon_code", SqlDbType.Char));
    SqlParameter discountAmount = command.Parameters.Add(
      new SqlParameter("@discount_amount", SqlDbType.Decimal));
    SqlParameter discountType = command.Parameters.Add(
      new SqlParameter("@discount_type", SqlDbType.TinyInt));
    SqlParameter expirationDate = command.Parameters.Add(
      new SqlParameter("@expiration_date", SqlDbType.DateTime));
          
    connection.Open( );

    couponCode.Value = "99GGY";
    discountAmount.Value = 5d;
    discountType.Value = DiscountType.Percentage;
    expirationDate.Value = new DateTime(2002,12,31);
    command.ExecuteNonQuery( );

    command.Parameters["@coupon_code"].Value = "81BIN";
    command.Parameters["@discount_amount"].Value = 10d;
    command.Parameters["@discount_type"].Value = DiscountType.Fixed;
    command.Parameters["@expiration_date"].Value = 
      new DateTime(2003,1,31);
    command.ExecuteNonQuery( );

    connection.Close( );
  }
}

This example shows two ways to deal with the SqlParameter objects. Each of the SqlParameter objects is created and added to the SqlCommand’s Parameters property, which is a SqlParameterCollection. The Add( ) method returns the newly created SqlParameter, which is then assigned to a local variable.

For the first execution of the SqlCommand, the SqlParameter instances are accessed by the local variables, and their values are assigned using the Value parameter. The SqlCommand.ExecuteNonQuery( ) method causes the SQL statement to be executed with those values.

In the second SqlCommand execution, the SqlParameter instances are accessed by name using the SqlParameterCollection’s indexer (the other indexer accesses a SqlParameter by its integer index). Then, like before, its Value is set and the SqlCommand is executed with those values.