.NET & XML

Building a SQL Command

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 ...