Lesson 06: Adding Parameters to Commands

his lesson shows you how to use parameters in your commands.  Here are the objectives of this lesson:

• Understand what a parameter is.
• Be informed about the benefits of using parameters. 
• Learn how to create a parameter.
• Learn how to assign parameters to commands.

Introduction

When working with data, you'll often want to filter results based on some criteria.  Typically, this is done by accepting input from a user and using that input to form a SQL query.  For example, a sales person may need to see all orders between specific dates.  Another query might be to filter customers by city.
As you know, the SQL query assigned to a SqlCommand object is simply a string.  So, if you want to filter a query, you could build the string dynamically, but you wouldn't want to.  Here is a bad example of filtering a query.

// don't ever do this!
 SqlCommand cmd = new SqlCommand(
  "select * from Customers where city = '" + inputCity + "'";

Don't ever build a query this way!  The input variable, inputCity, is typically retrieved from a TextBox control on either a Windows form or a Web Page.  Anything placed into that TextBox control will be put into inputCity and added to your SQL string.  This situation invites a hacker to replace that string with something malicious.  In the worst case, you could give full control of your computer away.
Instead of dynamically building a string, as shown in the bad example above, use parameters.  Anything placed into a parameter will be treated as field data, not part of the SQL statement, which makes your application much more secure.
Using parameterized queries is a three step process:

1. Construct the SqlCommand command string with parameters.
2. Declare a SqlParameter object, assigning values as appropriate.
3. Assign the SqlParameter object to the SqlCommand object's Parameters property.

The following sections take you step-by-step through this process.

preparing a SqlCommand Object for Parameters

The first step in using parameters in SQL queries is to build a command string containing parameter placeholders.  These placeholders are filled in with actual parameter values when the SqlCommand executes.  Proper syntax of a parameter is to use an '@' symbol prefix on the parameter name as shown below:

// 1. declare command object with parameter
 SqlCommand cmd = new SqlCommand(
  "select * from Customers where city = @City", conn);

In the SqlCommand constructor above, the first argument contains a parameter declaration, @City.  This example used one parameter, but you can have as many parameters as needed to customize the query.  Each parameter will match a SqlParameter object that must be assigned to this SqlCommand object.

Declaring a SqlParameter Object

Each parameter in a SQL statement must be defined.  This is the purpose of the SqlParameter type.  Your code must define a SqlParameter instance for each parameter in a SqlCommand object's SQL command.  The following code defines a parameter for the @City parameter from the previous section:

// 2. define parameters used in command object
 SqlParameter param  = new SqlParameter();
 param.ParameterName = "@City";
 param.Value         = inputCity;

Notice that the ParameterName property of the SqlParameter instance must be spelled exactly as the parameter that is used in the SqlCommand SQL command string.  You must also specify a value for the command.  When the SqlCommand object executes, the parameter will be replaced with this value.

Associate a SqlParameter Object with a SqlCommand Object

For each parameter defined in the SQL command string argument to a SqlCommand object, you must define a SqlParameter.  You must also let the SqlCommand object know about the SqlParameter by assigning the SqlParameter instance to the Parameters property of the SqlCommand object.  The following code shows how to do this:

// 3. add new parameter to command object
 cmd.Parameters.Add(param);

The SqlParameter instance is the argument to the Add method of the Parameters property for the SqlCommand object above.  You must add a unique SqlParameter for each parameter defined in the SqlCommand object's SQL command string.

Putting it All Together

You already know how to use SqlCommand and SqlDataReader objects.  The following code demonstrates a working program that uses SqlParameter objects.  So, everything should be familiar by now, except for the new parts presented in this article:

Listing 1: Adding Parameters to Queries

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

class ParamDemo
{
 static void Main()
 {
  // conn and reader declared outside try
  // block for visibility in finally block
  SqlConnection conn   = null;
  SqlDataReader reader = null;

  string inputCity = "London";

  try
  {
   // instantiate and open connection
   conn =  new 
    SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI");
   conn.Open();

   // don't ever do this!
//   SqlCommand cmd = new SqlCommand(
//    "select * from Customers where city = '" + inputCity + "'";

   // 1. declare command object with parameter
   SqlCommand cmd = new SqlCommand(
    "select * from Customers where city = @City", conn);

   // 2. define parameters used in command object
   SqlParameter param  = new SqlParameter();
   param.ParameterName = "@City";
   param.Value         = inputCity;

   // 3. add new parameter to command object
   cmd.Parameters.Add(param);

   // get data stream
   reader = cmd.ExecuteReader();

   // write each record
   while(reader.Read())
   {
    Console.WriteLine("{0}, {1}", 
     reader["CompanyName"], 
     reader["ContactName"]);
   }
  }
  finally
  {
   // close reader
   if (reader != null)
   {
    reader.Close();
   }

   // close connection
   if (conn != null)
   {
    conn.Close();
   }
  }
 }
}

The code in Listing 1 simply retrieves records for each customer that lives in London.  This was made more secure through the use of parameters.  Besides using parameters, all of the other code contains techniques you've learned in previous lessons.

Summary

You should use parameters to filter queries in a secure manner.  The process of using parameter contains three steps:  define the parameter in the SqlCommand command string, declare the SqlParameter object with applicable properties, and assign the SqlParameter object to the SqlCommand object.  When the SqlCommand executes, parameters will be replaced with values specified by the SqlParameter object.