ADO.NET in a Nutshell

Parameterized Commands

Parameterized commands are executed in the same way as normal commands. They simply use placeholders to separate literal values from the query itself. For example, consider the following dynamically constructed command (used in Example 4-1):

UPDATE Categories SET CategoryName='Beverages'

  WHERE CategoryID=1

As a parameterized command with the SQL Server provider, it takes this form:

UPDATE Categories SET CategoryName=@CategoryName

  WHERE CategoryID=@CategoryID

You then add two Parameter objects to the Command, with the names @CategoryName and @CategoryID. Now set the values for both these Parameter objects to Beverages and 1, respectively, and invoke the command. Example 4-3 shows a full example that rewrites Example 4-1 to use a parameterized command.

Example 4-3. Updating a record with a parameterized command (SQL Server)

// ParameterizedUpdateSQL.cs - Updates a single Category record using System; using System.Data; using System.Data.SqlClient; public class UpdateRecord { public static void Main() { string connectionString = "Data Source=localhost;" + "Initial Catalog=Northwind;Integrated Security=SSPI"; string SQL = "UPDATE Categories SET CategoryName=@CategoryName " + "WHERE CategoryID=@CategoryID"; // Create ADO.NET objects. SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(SQL, con); SqlParameter param; param = cmd.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15); param.Value = "Beverages"; param = cmd.Parameters.Add("@CategoryID", ...