Using the SQLCommand Object - Executing Parameterized SQL Statements
(Page 3 of 5 )
In addition to executing dynamic SQL statements, the SqlCommand object can also be used to execute stored procedures and parameterized SQL statements. The primary difference between dynamic SQL and prepared SQL is that dynamic SQL statements must be parsed and an access plan must be created before each run. (Technically, some database systems like SQL Server are very smart about the way this is handled, and they will actually store dynamic statements for a period of time. Then when the statement is subsequently executed, the existing access plan will be used. Even so, this depends on the database activity; and with dynamic SQL, there’s no guarantee that the plan will be immediately available.) You can think of prepared SQL statements as sort of a cross between stored procedures and dynamic SQL. Like stored procedures, they can accept different parameter values at runtime. Like dynamic SQL, they are not persistent in the database. The SQL statement is parsed and the access plan is created when the application executes the SQL statements. However, unlike dynamic SQL, the prepared SQL is parsed and the access plan is only created once when the statement is first prepared. Subsequent statement execution takes advantage of the existing access plan. The access plan will typically remain in the procedure cache until the connection is terminated. The following example shows how to create and execute a prepared SQL statement using the ADO.NET SqlCommand object:
Private Sub SQLCommandPreparedSQL(ByVal sServer As String, _
ByVal sDB As String)
Dim cn As New SqlConnection("SERVER=" & sServer _
& ";INTEGRATED SECURITY=True;DATABASE=" & sDB)
'Set up the Command object's parameter types
Dim cmd As New SqlCommand("INSERT INTO department VALUES" _
& "(@DepartmentID, @DepartmentName)", cn)
Dim parmDepartmentID = _
New SqlParameter("@DepartmentID", SqlDbType.Int)
parmDepartmentID.Direction = ParameterDirection.Input
Dim parmDepartmentName = _
New SqlParameter("@DepartmentName", SqlDbType.Char, 25)
parmDepartmentName.Direction = ParameterDirection.Input
' Add the parameter objects to the cmd Parameter's collection
cmd.Parameters.Add(parmDepartmentID)
cmd.Parameters.Add(parmDepartmentName)
Try
' Open the connection & prepare the command
cn.Open()
cmd.Prepare()
' Execute the prepared SQL statement to insert 10 rows
Dim i As Integer
For i = 0 To 10
parmDepartmentID.Value = i
parmDepartmentName.Value = "New Department " & CStr(i)
cmd.ExecuteNonQuery()
Next
Catch e As Exception
MsgBox(e.Message)
End Try
cn.Close()
End Sub
The C# version of the SQLCommandPrepareSQL subroutine is shown in the following listing:
private void SQLCommandPreparedSQL(string sServer, string sDB)
{
SqlConnection cn = new SqlConnection("SERVER=" + sServer
+ ";INTEGRATED SECURITY=True;DATABASE=" + sDB);
//Set up the Command object's parameter types
SqlCommand cmd = new SqlCommand("INSERT INTO department VALUES"
+ "(@DepartmentID, @DepartmentName)", cn);
SqlParameter parmDepartmentID =
new SqlParameter("@DepartmentID", SqlDbType.Int);
parmDepartmentID.Direction = ParameterDirection.Input;
SqlParameter parmDepartmentName =
new SqlParameter("@DepartmentName", SqlDbType.Char, 25);
parmDepartmentName.Direction = ParameterDirection.Input;
// Add the parameter objects to the cmd Parameter's collection
cmd.Parameters.Add(parmDepartmentID);
cmd.Parameters.Add(parmDepartmentName);
try
{
// Open the connection & prepare the command
cn.Open();
cmd.Prepare();
// Execute the prepared SQL statement to insert 10 rows
for (int i = 1; i <= 10; i++)
{
parmDepartmentID.Value = i;
parmDepartmentName.Value = "New Department " + i;
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
// Close the connection
cn.Close();
}
At the top of the CommandPrepareSQL subroutine, you can see where the target database server name and the database name are passed into the subroutine using the sServer and sDB variables. Next, a new SqlConnection object named cn is created, followed by a new SqlCommand object named cmd. In this example, the constructor takes two arguments. The first argument is used to assign a SQL statement to the cmd object. This can be either SQL statement or it can be the name of a stored procedure. Here, the SQL statement is an INSERT statement that adds that values of two columns to the Department table.
Note The Department table was created in the earlier section of this chapter.
The important point to note in this example is the format of the parameter markers that are used in the SQL statement. Parameter markers are used to indicate the replaceable characters in a prepared SQL statement. At runtime, these parameters will be replaced with the actual values that are supplied by the SqlCommand object’s Parameters collection. Unlike ADO or the OleDbCommand object, which uses the question mark character (?) to indicate replaceable parameters, the SqlCommand object requires that all parameter markers begin with the @ symbol. This example shows two parameter markers: @DepartmentID and @DepartmentName. The second argument of the SqlCommand constructor associates the cmd SqlCommand object with the cn SqlConnection object that was created earlier.
Next, you can see where two SqlParameter objects are created. The first parameter object named parmDepartmentID will be used to supply values to the first parameter marker (@DepartmentID). Likewise, the second parameter object named parmDepartmentName will supply the values used by the second replaceable parameter (@DepartmentName). The code example used in this subroutine shows three parameters being passed to the SqlParameter’s constructor. The first parameter supplies the parameter name. Here you need to make sure that the name supplied to the SqlParameter object’s constructor matches the name that was used in the parameter marker of the prepared SQL statement. The second parameter that’s passed to this overloaded version of the SqlParameter constructor specifies the parameter’s data type. Table 8-2 lists all of the valid DbType enumerations that can be used to specify the SqlParameter’s data type.
Here the Direction property is set to input using the ParameterDirection.Input enumeration. Table 8-3 lists the valid enumerations for the SqlParameter Direction property.
DbType Enumeration | .NET Data Type |
BigInt | Int64 |
Binary | Array of type Byte |
Bit | Boolean |
Char | String |
DateTime | DateTime |
Decimal | Decimal |
Float | Double |
Image | Array of type Byte |
Int | Int32 |
Money | Decimal |
nChar | String |
nText | String |
nVarChar | String |
Real | Single |
SmallDateTime | DateTime |
SmallInt | Int16 |
SmallMoney | Decimal |
Text | String |
Timestamp | DateTime |
TinyInt | Byte |
UniqueIdentifier | Guid |
VarBinary | Array of type Byte |
VarChar | String |
Variant | Object |
Table 8-2. DbType Enumerations
Enumeration | Description |
ParameterDirection.Input | The parameter is an input parameter. |
ParameterDirection.InputOutput | The parameter is capable of both input and output. |
ParameterDirection.Output | The parameter is an output parameter. |
ParameterDirection.ReturnValue | The parameter represents a return value. |
Table 8-3. SqlParameterDirection Enumeration
After the SqlParameter objects have been created, the next step is to add them to the SqlCommand object’s Parameters collection. In the previous listings, you can see that you use the Add method of the SqlCommand object’s Parameters collection to add both the parmDepartmentID and parmDepartmentName SqlParameter objects to the cmd SqlCommand object. The order in which you add the SqlParameter objects isn’t important. Next, within the Try-Catch block, the cn SqlConnection object’s Open method is used to open a connection to SQL Server and then the Prepare statement is used to prepare the statement. Note that the Prepare method is executed after all of the parameter attributes have been described.
NOTE Using the Prepare operation provides an important performance benefit for parameterized queries because it instructs SQL Server to issue an sp_prepare statement, thereby ensuring that the statement will be in the Procedure cache until the statement handle is closed.
Next, a For-Next loop is used to add 10 rows to the newly created Department table. Within the For-Next loop, the Value property of each parameter object is assigned a new data value. For simplicity, the parmDepartmentID parameter is assigned the value of the loop counter contained in the variable i while the parmDepartmentName parameter is assigned a string containing the literal "New Department" along with the current value of the loop counter. Last, the SqlCommand object’s ExecuteNonQuery method is used to execute the SQL statement. In this case, ExecuteNonQuery was used because this example is using a SQL action query that doesn’t return any values. From the SQL Server perspective, running the ExecuteNonQuery method results in the server issuing an sp_execute command to actually perform the insert.
NOTE If you need to pass a null value as a parameter, you need to set the parameter to the value DBNull.Value.
If an error occurs during any of these operations, the code in the Catch block will be executed and a message box will be displayed showing the text of the exception condition.
At the end of the subroutine, the SqlConnection object’s Close method is executed to end the connection to the SQL Server database.
Next: Executing Stored Procedures with Return Values >>
More Database Articles
More By McGraw-Hill/Osborne
|
 This article is taken from chapter 8 of ADO.NET: The Complete Reference, written by Michael Otey and Denielle Otey (McGraw-Hill/Osborne, 2004; ISBN: 0072228989). Check it out at your favorite bookstore. Buy this book now.
|
|
