Parameterized Commands
|
Parameterized Commands |
Parameterizing a SQL Expression
Introduction
So far, when using a command, we could create a SELECT statement that specified all fields that would be shown in the result. Here is an example:
SELECT * FROM Registrations.Students;
We also saw how to select a group of fields that would be shown in the result. Here is an example:
SELECT * FROM Registrations.Students WHERE ParentsNames IS NOT NULL;
When studying data analysis, we saw how you could let the user specify the value of a particular column so the table or view would display only the result(s) based on that value. On the other hand, you could let the user enter the receipt number of a record, then locate and display that record. Instead of performing these operations manually, Transact-SQL, the .NET Framework, and Microsoft Visual Studio provide all the necessary means of performing the same action (of locating one particular record or a group of records) by following just a few steps.
Adding a Parameter to a Command
Like a method of a class, a database command can accept one or more arguments, referred to as parameters. This allows the command to serve as an intermediary between an object of the database, such as a table, a view, a function, or a stored procedure, and the user. To support the parameters passed to a command, the .NET Framework provides a class for each type of connection. The class used for a SQL connection is called SqlParameter. SqlParameter is based on the DbParameter class and it implements the IDbParameter, the IDadaParameter, and the IClonable interfaces. The DbParameter class is defined in the System.Data.Common namespace.
Like a method, a command can take more than one parameter. Unlike a method, the parameters passed to a command must be stored in a collection. To support a group of parameters, the .NET Framework provides a collection class for each type of connection. For a SQL connection, the class is called SqlParameterCollection:
public sealed class SqlParameterCollection : DbParameterCollection
As you can see, the SqlParameterCollection class is derived from the DbParameterCollection class. This class implements the IDataParameterCollection, the IList, the ICollection, and the IEnumerable interfaces:
public abstract class DbParameterCollection : MarshalByRefObject, IDataParameterCollection, IList, ICollection, IEnumerable
To be able to receive one or more parameters, the command class is equipped with a property named Parameters, which is of the type of the collection (as mentioned already, the name of the class depends on the type of database or collection).
In order to pass a parameter to a command, you must build it, which is done in various steps. As mentioned already, a parameter is an object of type SqlParameter.
public sealed class SqlParameter : DbParameter, IDbDataParameter, IDataParameter, ICloneable
Therefore, to start, you can declare a variable of type SqlParameter but this is only an option (you can pass a parameter directly to a command). To assist you with declaring a variable, the Parameter class is equipped with various constructors. The default constructor allows you to declare the variable without giving much information. Here is an example of using it:
private void btnSubmitClick(object sender, EventArgs e)
{
using (SqlConnection cnnStudents =
new SqlConnection(strConnection))
{
SqlCommand cmdStudents = new SqlCommand(strStatement,
cnnStudents);
SqlParameter prmStudent = new SqlParameter();
cnnStudents.Open();
}
}
After creating the parameter, you must pass it to the command object. As mentioned already, a command has a property named Parameters that is from a class that implements the IList interface. This allows you to call its Add() method to add the parameter. The Add() method comes in various versions. One of the versions allows you to pass a parameter object. Its syntax is:
public SqlParameter Add(SqlParameter value);
Here is an example:
private void btnSubmitClick(object sender, EventArgs e)
{
using (SqlConnection cnnStudents =
new SqlConnection(strConnection))
{
SqlCommand cmdStudents = new SqlCommand(strStatement,
cnnStudents);
SqlParameter prmStudent = new SqlParameter();
cmdStudents.Parameters.Add(prmStudent);
. . .
}
}
We will review other versions of this method as we move on.
|
Characteristics of a Parameter of a Command |
|
The Name of a Parameter |
Before passing a parameter to a command, you must specify the name of the parameter. To support this, the parameter class is equipped with a property named ParameterName. To specify the name of the argument, assign it to this property. The name of the parameter starts with @ and follows the rules for names of variables in Transact-SQL. Here is an example (the SQL code ROSH database is available):
private void btnSubmit_Click(object sender, EventArgs e)
{
using (SqlConnection cnnStudents =
new SqlConnection(strConnection))
{
SqlCommand cmdStudents = new SqlCommand(strStatement, cnnStudents);
SqlParameter prmStudent = new SqlParameter();
prmStudent.ParameterName = "@StdNumber";
cmdStudents.Parameters.Add(prmStudent);
cnnStudents.Open();
SqlDataReader rdrStudents = cmdStudents.ExecuteReader();
}
}
An alternative is to declare a parameter variable using a constructor other than the default. As we will see in future sections, the name of the argument is passed as the first argument to the constructor. You may remember that, in C#, when calling a method that takes an argument, you must provide it (the argument). You can pass a value to the method. Here is an example:
using System;
public class Calculations
{
public double CalculateSquareArea(double Side)
{
return Side * Side;
}
public void ShowResults()
{
CalculateSquareArea(15.68);
}
}
You can also pass the argument using a name. Here is an example:
using System;
public class Calculations
{
public double CalculateSquareArea(double Side)
{
return Side * Side;
}
public void ShowResults()
{
double s = 15.68;
CalculateSquareArea(s);
}
}
Notice that the name of the argument in the method implementation and the name passed to it when called are different. This is valid in C# but not in Transact-SQL. For a parameter object, you must use the same name used in the SQL statement for the parameter:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace Rosh1
{
public partial class StudentIdentification : Form
{
string strConnection = "Data Source=(local);" +
"Database='ROSH';" +
"Integrated Security='SSPI';";
string strStatement = "SELECT StudentNumber, LastName, " +
"FirstName, City, State " +
"FROM Students " +
"WHERE StudentNumber = @StdNbr;";
public StudentIdentification()
{
InitializeComponent();
}
private void btnSubmit_Click(object sender, EventArgs e)
{
using (SqlConnection cnnStudents =
new SqlConnection(strConnection))
{
SqlCommand cmdStudents = new SqlCommand(strStatement,
cnnStudents);
SqlParameter prmStudent = new SqlParameter();
prmStudent.ParameterName = "@StdNbr";
cmdStudents.Parameters.Add(prmStudent);
cnnStudents.Open();
}
}
}
}
|
The Type of Parameter |
As you should know from your knowledge of C#, when calling a method that takes an argument, you must pass the argument of the appropriate type. In the same way, a command requires that you specify the type of argument you are passing. To assist you with this, the parameter class is equipped with a property named DbType. The DbType property is of type DbType. This class provides various data types that correspond to the data types of Transact-SQL.
To specify the data type of a parameter, access the DbType property of your parameter variable. Assign it the corresponding data type accessed as a static field of the DbType class. Here is an example:
private void btnSubmit_Click(object sender, EventArgs e)
{
using (SqlConnection cnnStudents =
new SqlConnection(strConnection))
{
SqlCommand cmdStudents = new SqlCommand(strStatement,
cnnStudents);
SqlParameter prmStudent = new SqlParameter();
prmStudent.ParameterName = "@StdNumber";
prmStudent.DbType = DbType.String;
cmdStudents.Parameters.Add(prmStudent);
cnnStudents.Open();
}
}
Instead of specifying the data type from the DbType property, you can use the following constructor of the SqlParameter class:
public SqlParameter(string parameterName, SqlDbType dbType);
The first argument to this constructor is the name of the parameter. The second parameter is the data type:
private void btnSubmit_Click(object sender, EventArgs e)
{
using (SqlConnection cnnStudents =
new SqlConnection(strConnection))
{
SqlCommand cmdStudents = new SqlCommand(strStatement,
cnnStudents);
SqlParameter prmStudent = new SqlParameter("@StdNbr", DbType.String;);
cmdStudents.Parameters.Add(prmStudent);
cnnStudents.Open();
}
}
An alternative is to use the SqlParameterCollection class directly. In this case, its provides the following version of the Add() method:
public SqlParameter Add(string parameterName, SqlDbType sqlDbType);
Tu use it, pass the name of the parameter and its data type as arguments. If the parameter is a string, you can specify the number of characters. To support this, the SqlParameter class is equipped with the following constructor:
public SqlParameter(string parameterName, SqlDbType dbType, int size);
The SqlParameterCollection class provides a corresponding version of the Add() method whose syntax is:
public SqlParameter Add(string parameterName, SqlDbType sqlDbType, int size);
|
The Value of a Parameter |
In order to produce a result from a parameterized SQL statement, you must provide the value that the command will apply. To support this, the parameter class is equipped with a property named Value. This value is of type object, which means it is your responsibility to provide the appropriate value for the parameter. Here is an example:
private void btnSubmit_Click(object sender, EventArgs e)
{
using (SqlConnection cnnStudents =
new SqlConnection(strConnection))
{
SqlCommand cmdStudents = new SqlCommand(strStatement,
cnnStudents);
SqlParameter prmStudent = new SqlParameter();
prmStudent.ParameterName = "@StdNumber";
prmStudent.DbType = DbType.String;
prmStudent.Value = "723897";
cmdStudents.Parameters.Add(prmStudent);
cnnStudents.Open();
}
}
Of course, if the value is a number, you can assign it as such. If the user will provide the value from a control, you can access the value of that control and assign it to the Value property. Here is an example:
private void btnSubmit_Click(object sender, EventArgs e)
{
using (SqlConnection cnnStudents =
new SqlConnection(strConnection))
{
SqlCommand cmdStudents = new SqlCommand(strStatement,
cnnStudents);
SqlParameter prmStudent = new SqlParameter();
prmStudent.ParameterName = "@StdNumber";
prmStudent.DbType = DbType.String;
prmStudent.Value = txtStudentNumber.Text;
cmdStudents.Parameters.Add(prmStudent);
cnnStudents.Open();
}
}
An alternative is to use a constructor of the SqlParameter class that takes the parameter name and its value as arguments. Its syntax is:
public SqlParameter(string parameterName, Object value);
After providing all the information required by the command and adding the parameter(s) to the Parameters collection, you can execute the command to retrieve its (their) value(s) and use it (them) as you see fit. Here is an example:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace Rosh1
{
public partial class StudentIdentification : Form
{
string strConnection = "Data Source=(local);" +
"Database='ROSH';" +
"Integrated Security='SSPI';";
string strStatement = "SELECT StudentNumber, LastName, " +
"FirstName, Sex, City, State " +
"FROM Registrations.Students " +
"WHERE StudentNumber = @StdNbr;";
public StudentIdentification()
{
InitializeComponent();
}
private void btnSubmit_Click(object sender, EventArgs e)
{
using (SqlConnection cnnStudents =
new SqlConnection(strConnection))
{
SqlCommand cmdStudents =
new SqlCommand(strStatement, cnnStudents);
SqlParameter prmStudent = new SqlParameter();
prmStudent.ParameterName = "@StdNbr";
prmStudent.DbType = DbType.String;
prmStudent.Value = txtStudentNumber.Text;
cmdStudents.Parameters.Add(prmStudent);
cnnStudents.Open();
SqlDataReader rdrStudents = cmdStudents.ExecuteReader();
while (rdrStudents.Read())
{
txtLastName.Text = rdrStudents[1].ToString();
txtFirstName.Text = rdrStudents[2].ToString();
txtCity.Text = rdrStudents[3].ToString();
txtState.Text = rdrStudents[4].ToString();
}
}
}
private void btnClose_Click(object sender, EventArgs e)
{
Close();
}
}
}
As an alternative, the SqlParameterCollection class allows you to specify the name and value of the parameter. This is done using a method named AddWithValue. Its syntax is:
public SqlParameter AddWithValue(string parameterName, object value);
Here is an example:
using System;
using System.Xml;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Parameterization : Form
{
Button btnSubmit;
DataGridView dgvStudents;
public Parameterization()
{
InitializeComponents();
}
private void InitializeComponents()
{
btnSubmit = new Button();
btnSubmit.Text = "Submit";
btnSubmit.Location = new Point(12, 242);
btnSubmit.Click += new EventHandler(btnSubmitClick);
dgvStudents = new DataGridView();
dgvStudents.Location = new Point(12, 12);
dgvStudents.Size = new System.Drawing.Size(440, 220);
Controls.AddRange(new Control[] { btnSubmit, dgvStudents } );
Text = "Students Records";
ClientSize = new System.Drawing.Size(462, 272);
}
private void btnSubmitClick(object sender, EventArgs e)
{
using (SqlConnection cnnStudents =
new SqlConnection("Data Source=(local);" +
"Database='ROSH';" +
"Integrated Security='SSPI';"))
{
SqlCommand cmdStudents = new SqlCommand("SELECT StudentNumber, LastName, " +
"FirstName, City, State " +
"FROM Registration.Students " +
"WHERE City = @ct;",
cnnStudents);
SqlParameter prmStudent = new SqlParameter();
prmStudent.ParameterName = "@ct";
cmdStudents.Parameters.AddWithValue("@ct", "Rockville");
cnnStudents.Open();
DataSet dsStudents = new DataSet("StudentsSet");
SqlDataAdapter sdaStudents = new SqlDataAdapter();
sdaStudents.SelectCommand = cmdStudents;
sdaStudents.Fill(dsStudents);
dgvStudents.DataSource = dsStudents.Tables[0];
}
}
[STAThread]
public static int Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new Parameterization());
return 0;
}
}
Adding Many Parameters
As mentioned already, a command can take many parameters. You can pass one parameter at a time or first create a collection of parameters. To start, create a SELECT statement with a WHERE condition that includes as many conditions as you want. Here is an example:
SELECT StudentNumber,
FirstName,
LastName,
Gender,
City,
State
FROM Registration.Students
WHERE (Gender = @Gdr) AND (City = @ct)
To add many parameters one at a time to a command, build each SqlParameter object with the desired characteristics and pass it to the SqlParameterCollection collection. :
private void btnSubmitClick(object sender, EventArgs e)
{
using (SqlConnection cnnStudents = new SqlConnection("Data Source=(local);" +
"Database='ROSH';" +
"Integrated Security='SSPI';"))
{
SqlCommand cmdStudents =
new SqlCommand("SELECT StudentNumber, " +
" FirstName, " +
" LastName, " +
" Gender, " +
" City, " +
" State " +
"FROM Registration.Students " +
"WHERE (Gender = @gdr) AND (City = @ct);",
cnnStudents);
SqlParameter prmStudent;
prmStudent = new SqlParameter();
prmStudent.ParameterName = "@gdr";
prmStudent.Value = "Male";
cmdStudents.Parameters.Add(prmStudent);
prmStudent = new SqlParameter();
prmStudent.ParameterName = "@ct";
prmStudent.Value = "Rockville";
cmdStudents.Parameters.Add(prmStudent);
cnnStudents.Open();
DataSet dsStudents = new DataSet("StudentsSet");
SqlDataAdapter sdaStudents = new SqlDataAdapter();
sdaStudents.SelectCommand = cmdStudents;
sdaStudents.Fill(dsStudents);
dgvStudents.DataSource = dsStudents.Tables[0];
}
}
Of course, you can also call the SqlParameterCollection.AddWithValue() method to add each parameter. Here are examples:.
private void btnSubmitClick(object sender, EventArgs e)
{
using (SqlConnection cnnStudents = new SqlConnection("Data Source=(local);" +
"Database='ROSH';" +
"Integrated Security='SSPI';"))
{
SqlCommand cmdStudents =
new SqlCommand("SELECT StudentNumber, " +
" FirstName, " +
" LastName, " +
" Gender, " +
" City, " +
" State " +
"FROM Registration.Students " +
"WHERE (Gender = @gdr) AND (City = @ct);",
cnnStudents);
SqlParameter prmStudent;
prmStudent = new SqlParameter();
cmdStudents.Parameters.AddWithValue("@gdr", "female");
prmStudent = new SqlParameter();
cmdStudents.Parameters.AddWithValue("@ct", "Silver Spring");
cnnStudents.Open();
DataSet dsStudents = new DataSet("StudentsSet");
SqlDataAdapter sdaStudents = new SqlDataAdapter();
sdaStudents.SelectCommand = cmdStudents;
sdaStudents.Fill(dsStudents);
dgvStudents.DataSource = dsStudents.Tables[0];
}
}
As an alternative, the SqlParameterCollection class is equipped with an AddRange() method overridden from the IList interface. The SqlParameterCollection class provides two versions. One takes an array of objects as argument. Its syntax is:
public override void AddRange(Array values);
If you use this version, make sure each member is appropriate. In fact, a better alternative is through the other version that takes an array of SqlParameter values. Its syntax is:
public void AddRange(SqlParameter[] values);
Here is an example:
using System;
using System.Xml;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Parameterization : Form
{
Button btnSubmit;
DataGridView dgvStudents;
public Parameterization()
{
InitializeComponents();
}
private void InitializeComponents()
{
btnSubmit = new Button();
btnSubmit.Text = "Submit";
btnSubmit.Location = new Point(12, 242);
btnSubmit.Click += new EventHandler(btnSubmitClick);
dgvStudents = new DataGridView();
dgvStudents.Location = new Point(12, 12);
dgvStudents.Size = new System.Drawing.Size(440, 220);
Controls.AddRange(new Control[] { btnSubmit, dgvStudents } );
Text = "Students Records";
ClientSize = new System.Drawing.Size(462, 272);
}
private void btnSubmitClick(object sender, EventArgs e)
{
using (SqlConnection cnnStudents = new SqlConnection("Data Source=(local);" +
"Database='ROSH';" +
"Integrated Security='SSPI';"))
{
SqlCommand cmdStudents =
new SqlCommand("SELECT StudentNumber, " +
" FirstName, " +
" LastName, " +
" Gender, " +
" City, " +
" State " +
"FROM Registration.Students " +
"WHERE (Gender = @gdr) AND (City = @ct);",
cnnStudents);
cmdStudents.Parameters.AddRange(new SqlParameter[] { new SqlParameter("@gdr", "female"),
new SqlParameter("@ct", "Rockville") });
cnnStudents.Open();
DataSet dsStudents = new DataSet("StudentsSet");
SqlDataAdapter sdaStudents = new SqlDataAdapter();
sdaStudents.SelectCommand = cmdStudents;
sdaStudents.Fill(dsStudents);
dgvStudents.DataSource = dsStudents.Tables[0];
}
}
[STAThread]
public static int Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new Parameterization());
return 0;
}
}