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

Introduction to C# Params

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 salesperson 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:

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: