Adding Parameters to Commands - Ado.net Tutorials

Unknown

23 Feb, 2015

Adding Parameters to Commands

This lesson shows you how to use parameters in your commands.2 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:

Construct the SqlCommand command string with parameters.
Declare a SqlParameter object, assigning values as appropriate.
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"], 

finally
reader["ContactName"]);
   }
  }


{

// close reader

if (reader != null)

{

reader.Close();
}


// close connection

if (conn != null)

{

conn.Close();
}
  }
 }

}

The code in Listing 1 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.