4.4 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", SqlDbType.Int);
        param.Value = 1;

        // Execute the command.
        con.Open();
        int rowsAffected = cmd.ExecuteNonQuery();
        con.Close();

        // Display the result of the operation.
        Console.WriteLine(rowsAffected.ToString() + " row(s) affected");
    }
}

Note that in order for this to work, the Command.CommandType property must be CommandType.Text, which is the default.

The SQL Server provider matches the parameter values to the query placeholders by using the parameter name. With the OLE DB provider, parameterized queries take a slightly different syntax. Instead of using named parameters, you use question-mark placeholders:

SELECT * FROM Customers WHERE CustomerID = ?

If you have more than one question mark in the same query, the OLE DB provider matches them to the question marks based on their order. Thus the first parameter you add should correspond to the first question mark in your query. Example 4-4 shows how you would approach the same task using the OLE DB provider. In this case, both Parameter objects are still assigned the same names, but these names aren't used in the query. The position alone is significant.

Example 4-4. Updating a record with a parameterized command (OLE DB)

// ParameterizedUpdateOLEDB.cs - Updates a single Category record

using System;
using System.Data.OleDb;

public class UpdateRecord
{
    public static void Main() 
    {
        string connectionString = "Data Source=localhost;" +
                     "Initial Catalog=Northwind;Provider=SQLOLEDB;" +
                     "Integrated Security=SSPI";
        string SQL = "UPDATE Categories SET CategoryName=? " +
                     "WHERE CategoryID=?";

        // Create ADO.NET objects.
        OleDbConnection con = new OleDbConnection(connectionString);
        OleDbCommand cmd = new OleDbCommand(SQL, con);

        OleDbParameter param;
        param = cmd.Parameters.Add("@CategoryName", OleDbType.VarWChar, 15);
        param.Value = "Beverages";

        param = cmd.Parameters.Add("@CategoryID", OleDbType.Integer);
        param.Value = 1;

        // Execute the command.
        con.Open();
        int rowsAffected = cmd.ExecuteNonQuery();
        con.Close();

        // Display the result of the operation.
        Console.WriteLine(rowsAffected.ToString() + " row(s) affected");
    }
}

Parameterized commands have several benefits:

• They are less error-prone. You can code the SQL statement in a single long string, rather than piece it together, where it is notoriously easy to mistype.

• They are more secure. Metacharacters within parameters are escaped automatically, reducing the risk of some classes of SQL injection attacks.

• They prevent syntax errors with different data types. In SQL, you need to escape different data types (strings, numbers, and dates) differently. In a parameterized query, this is performed automatically.

• They are reusable. Parameterized queries make it easy to reuse Command objects. For example, you can use the Command in Example 4-3 to update multiple category records, simply by modifying the value of the parameters and executing the query again.

A parameterized command won't improve performance as compared to the original dynamic SQL statement. Unlike a stored procedure, a parameterized query isn't stored in the database and isn't precompiled. The difference is simply one of syntax.