How to incorporate Parameters to Commands with C#

In this article we will see these topics:

• Explaining about the parameter
• Benefits of using parameters.
• Learn how to create a parameter.
• Learn how to assign parameters to commands.

Introduction about SqlCommand and parameters

When we are working with data, we often come into a situation when there is a need to filter the results based on some conditions. Generally, this is obtained by accepting the input data from a user and making use of that input data to form a SQL query.

For instance, we have a sales person that is looking for all the orders between specific dates. The other query might include filtering customers by city.

As we all are aware that the SQL query assigned to a SqlCommand object is nothing but simply a string, hence if one is required to filter a query, one could create the string dynamically. However, no one would ever want to do this and the below list the bad example of filtering a query.

Listing 1: Bad example of filtering a query

// one should never use this
	SqlCommand cmd = new SqlCommand(
		"select * from Customers where city = '" + inputCity + "'";

One should never create this way. The input variable, inputCity, is normally fetched from a TextBox control. This could be done on either a Windows form or a Web Page. Anything that you enter into that TextBox control will be going into inputCity thereby adding to your SQL string. In such a situation, you are inviting a hacker to replace that string with something malicious. One could give the full access to the computer in the worst case.

One should make use of the parameters instead of dynamically building a string as we have seen in the above example. Any text that you enter into a parameter will be treated as field data. This is not considered a part of the SQL statement and makes your application much more secure.

We have a three step process making use of parameterized queries:

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.

We will learn all this via step-by-step process.

Arranging a SqlCommand Object for Parameters

The very first step in making use of parameters in SQL queries is to build a command string that would comprise of parameter placeholders. When the SqlCommand runs, these placeholders are filled in with actual parameter values. The below lists the proper syntax of a parameter to use an '@' symbol prefix on the parameter name:

Listing 2: Proper syntax of a parameter

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

We have a first argument containing a parameter declaration, @City in the SQL command as mentioned above. One parameter is used by the example but one can have large number or as many parameters as required in order to customize the query. Each of the parameter will match a SqlParameter object. This must be assigned to this SqlCommand object.

Declaring a SqlParameter Object

Each of the parameter in a SQL statement must be defined appropriately. The code must declare a SqlParameter type and the code must define a SqlParameter instance for each of the parameter in a SqlCommand object's SQL command. The below code reflects a parameter for the @City parameter:

Listing 3: Parameter for the @City

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

One should keep in mind that the spelling of the ParameterName property of the SqlParameter instance must be same as the parameter that is used in the SqlCommand SQL command string. One should also define a value for the command and when the SqlCommand object is implemented or run, this value will replace the parameter.

Combining it All Together

We already are aware about the process to make use of the SqlCommand and SqlDataReader objects. The below code will illustrate a working program that utilizes SqlParameter objects. So, one must be adapt to all these now and the below listing presents the code to add parameters to queries.

Listing 4: 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 = "New York";

		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();
			}
		}
	}
}

Here the code above in listing 1 fetches the records for each of the customer that resides in New York. This is made more secure and safer via the parameters assistance.

Conclusion

We learnt in this article that one should be making use of the parameters to filter queries in a much more secure mode. The process of utilizing the parameter comprises of three steps:

• Define the parameter in the SqlCommand command string,
• Declare the SqlParameter object with applicable properties,
• Assign the SqlParameter object to the SqlCommand object.

This is all for today’s article, hope you liked and until next time. See you next time.