C# - SqlParameter Example

SqlParameter. This C# class makes SQL queries easier to build. It is part of the System.Data.SqlClient namespace. It is an easy way to parameterize queries.

SqlClient

Constructor notes. SqlParameter has several overloaded constructors. For many simple uses, we can just invoke the 2-argument SqlParameter constructor.

An example. Here we see the simplest overload of the SqlParameter instance constructor and adds it to the SqlCommand type's Parameter collection. There are other ways to add parameters.

SqlCommand

Here The most important thing is the principle of parameterized queries when using SQL databases.

Info Many System.Data objects can be wrapped in "using" statements to ensure the best cleanup of their resources.

using

Detail Here the string "Fido" is specified to match the Name column in the Dogs1 table.

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);
                }
            }
        }
    }
}
Weight = 130, Name = Fido, Breed = Bullmastiff

Empty array tip. Sometimes we need an empty array of SqlParameter. We can use an empty array initializer to avoid having a null array.

Array

var parameters = new SqlParameter[] { };

SQL injection. The pattern shown here is ideal for preventing database attacks. Hackers insert "control characters" into queries issued over the Internet, in an attempt to gain control.

Tip The SqlParameter syntax here will avoid all such injection attacks, rejecting the command by throwing an exception.

A summary. We used SqlParameter to parameterize a query in SQL Server. The example here will not work immediately—you must have a database and connection string in your project first.

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. Some setup code is required.