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.
SqlCommandHere: 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.
UsingParameters: Here the string "Fido" is specified to match the Name column in the Dogs1 table.
C# program that uses SqlParameter on command
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
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.
ArrayTip: Thanks to Dean Goddard for writing in with a tip on how to use an empty SqlParameter array.
C# program that creates empty array
var parameters = new SqlParameter[] { };
