After the first DROP TABLE SQL command, the section of code at callout B follows the same sequence to execute a CREATE TABLE command. First, the subroutine assigns the sSQL variable a T-SQL CREATE TABLE statement that creates a two-column table named Department. The first column is an integer data type named DepartmentID, which is also the primary key; the second column is a 25-character type named DepartmentName. Next, the subroutine copies the value in the sSQL variable to the cmd object's CommandText property and calls the ExecuteNonQuery method to execute the CREATE TABLE statement. After the successful completion of the ExecuteNonQuery method, the Department table will exist in the database named in the sDB variable. If an error occurs during any of the preceding steps, during the SqlConnection object's Open method, or during either instance of the SqlCommand object's ExecuteNonQuery method, the code in the Catch block executes and a message box shows the text of the exception condition. At the end of the CommandNonQuery subroutine, the application executes the SqlConnection object's Close method to end the connection to the SQL Server database.
In your applications that execute dynamic SQL statements, be certain to validate all user input to protect your system from SQL injection attacks, which can let intruders gain access to your databases. Although this example generates all the SQL code internally, a common application practice is to build SQL execution strings based on user input. If your application does this, you need to test all input strings for valid input data and the presence of any unwanted SQL keywords and meaningful characters such as semicolons.
Executing Parameterized SQL Statements
In addition to executing dynamic SQL statements, you can use the SqlCommand object to execute parameterized SQL statements and stored procedures. The primary difference between dynamic SQL and parameterized SQL is that SQL Server must parse dynamic SQL statements and create an access plan for them before it runs them. SQL Server handles the execution of dynamic SQL statements intelligently. It stores dynamic statements in its procedure cache for a certain period of time, then when an application executes the statement, SQL Server uses the existing access plan. Even so, the statement's availability in cache depends on the database activity, and dynamic SQL carries no guarantee that the plan will be available the next time the statement is executed. However, parameterized SQL stays in the procedure cache until the application's connection closes.
You can think of parameterized 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're not persistent in the database. However, unlike with dynamic SQL, SQL Server parses parameterized SQL and creates the access plan only once—when it first prepares the statement. Subsequent statement execution takes advantage of the existing access plan.
The example code in Listing 2 shows how to use the SqlCommand object to create and execute a prepared SQL statement. At the top of the SQLCommandPreparedSQL subroutine, the application uses the sServer and sDB variables to pass the target database server name and the database name to the subroutine. Next, the subroutine creates a new SqlConnection object named cn, followed by a new SqlCommand object named cmd. In this example, the SqlCommand object's constructor takes two parameters. The first parameter assigns an SQL statement to the cmd object. This statement can be either an SQL statement or the name of a stored procedure. Here, the SQL statement is an INSERT statement that inserts values into two columns in the Department table. The second parameter supplies the name of the SqlConnection object.
The important point in this example is the format of the parameter markers in the SQL statement. Parameter markers denote the replaceable characters in a prepared SQL statement. At runtime, the subroutine replaces these parameters with the values that the SqlCommand object's Parameters collection supplied. Unlike ADO or the ADO.NET OleDbCommand object, which uses a question mark (?) to indicate replaceable parameters, the SqlCommand object requires that all parameter markers begin with the at (@) symbol. This example shows two parameter markers: @DepartmentID and @DepartmentName. The SqlCommand constructor's second argument associates the cmd SqlCommand object with the cn SqlConnection object you created earlier.
Next, the code at callout A in Listing 2 creates two SqlParameter objects. The first parameter object, parmDepartmentID, supplies values to the first parameter marker (@DepartmentID). Likewise, the second parameter object, parmDepartmentName, supplies the values that the second replaceable parameter (@DepartmentName) uses. The example code in this subroutine passes two arguments to the SqlParameter object's constructor. The first parameter supplies the name of the SqlParameter object. Here, you need to make sure that the name you supply to the SqlParameter object's constructor matches the name that you used in the parameter marker of the prepared SQL statement. The second parameter that the subroutine passes to the SqlParameter constructor specifies the parameter's data type. Next, the subroutine uses the ParameterDirection.Input enumeration to set the SqlParameter object's Direction property to input. Table 2 lists the valid enumerations for the SqlParameter Direction property.
After you create the SqlParameter objects, the next step is to add them to the SqlCommand object's Parameters collection. 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, in the Try-Catch block at callout B in Listing 2, the subroutine uses the cn SqlConnection object's Open method to open a connection to SQL Server, then uses the Prepare statement to prepare the SQL statement. Note that the subroutine executes the Prepare method after all the parameter attributes have been described. Next, a For-Next loop adds 10 rows to the newly created Department table. Within the For-Next loop, the subroutine assigns the Value property of each parameter object a new data value. For simplicity, the code uses a loop and just adds 1 to the starting value. The subroutine assigns the parmDepartmentID parameter the value of the loop counter contained in the variable i and assigns the parmDepartmentName parameter a string containing the literal "New Department" along with the current value of the loop counter.
Finally, the SqlCommand object's ExecuteNonQuery method executes the SQL statement. I used ExecuteNonQuery because this example uses an SQL action query that doesn't return any values. From a SQL Server perspective, running the ExecuteNonQuery method causes the application to issue an sp_execute command to perform the insertion. Running SQL Server Profiler can reveal exactly which SQL commands the application sends to SQL Server. If an error occurs during any operations in the Try block, the code in the Catch block executes and a message box shows the text of the exception condition. The code at the end of the subroutine executes the SqlConnection object's Close method to disconnect from the SQL Server database.
Prev. page
1
[2]
3
next page 
