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 ...
Get .NET & XML now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.