C# SqlParameter

SqlParameter makes SQL queries easier to build. It is part of the System.Data.SqlClient namespace. It provides an easy and fast way to parameterize queries. This yields bullet-proof and simple code that accesses data.

Example

First we see an example that uses the simplest overload of the SqlParameter instance constructor and adds it to the SqlCommand type's Parameter collection. Note that there are many other ways to add parameters to a SQL query such as the query shown here, but the most important thing is the principle of parameterized queries in the C# programming language and .NET Framework when using SQL databases.

This C# program uses the SqlParameter type. It relies on System.Data.SqlClient.

Program that uses SqlParameter on command [C#]

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
	//
	// The name we are trying to match.
	//
	string dogName = "Fido";
	//
	// Use preset string for connection and open it.
	//
	string connectionString = ConsoleApplication1.Properties.Settings.Default.ConnectionString;
	using (SqlConnection connection = new SqlConnection(connectionString))
	{
	    connection.Open();
	    //
	    // Description of SQL command:
	    // 1. It selects all cells from rows matching the name.
	    // 2. It uses LIKE operator because Name is a Text field.
	    // 3. @Name must be added as a new SqlParameter.
	    //
	    using (SqlCommand command = new SqlCommand("SELECT * FROM Dogs1 WHERE Name LIKE @Name", connection))
	    {
		//
		// Add new SqlParameter to the command.
		//
		command.Parameters.Add(new SqlParameter("Name", dogName));
		//
		// Read in the SELECT results.
		//
		SqlDataReader reader = command.ExecuteReader();
		while (reader.Read())
		{
		    int weight = reader.GetInt32(0);
		    string name = reader.GetString(1);
		    string breed = reader.GetString(2);
		    Console.WriteLine("Weight = {0}, Name = {1}, Breed = {2}", weight, name, breed);
		}
	    }
	}
    }
}

Output
    (This varies depending on your database contents.)

Weight = 130, Name = Fido, Breed = Bullmastiff

SqlConnection and SqlCommand. The program demonstrates the SqlConnection, SqlCommand, and SqlDataReader pattern that is common when using SQL Server. These objects can all be wrapped in 'using' statements to ensure the best cleanup of their resources. The important part of the example is the part where a new SqlParameter is added.

SqlParameter constructor and options. The SqlParameter type has several overloaded constructors, but you will not need most of them. For simple cases, you can simply use the constructor with two parameters as shown here. The first parameter specifies a string that must match the query variable. The second parameter specifies the value you want to be specified for that field. In the example, the string "Fido" is specified to match the Name column in the Dogs1 table.

SQL injection

Here we emphasize why the SqlParameter pattern shown here is ideal for preventing hacker attempts on valuable web sites or other databases. Hackers try to insert "control characters" into queries issued over the Internet, in an attempt to gain control over the database and issue commands to it.

The SqlParameter syntax here will avoid all such injection attacks, rejecting the command by throwing an exception, thus preventing data theft. For an illustration of SQL injection, see the XKCD webcomic that shows how a student's name could influence the database integrity.

Summary

We looked at simple example of using SqlParameter to parameterize a query in SQL Server using the C# programming language. The example here will not work for you immediately because you must have a database and connection string in your project first. However, the general idea of using SqlParameter in this way to avoid SQL attacks is useful. Performing database queries is a multi-step process in the .NET Framework and some setup code is required.