Database applications commonly need to execute dynamic SQL statements and stored procedures. Dynamic SQL statements are useful for executing Data Definition Language (DDL) operations such as creating tables or for data-access operations such as performing ad hoc queries. And stored procedures, which form the backbone of most database applications, typically perform predefined queries and database-update operations. SqlCommand is the ADO.NET object that executes dynamic SQL statements and stored procedures for a SQL Server database. Knowing how to use the SqlCommand object is essential for developing applications with ADO.NET right now. And understanding Microsoft .NET coding and ADO.NET is an important step in getting ready for Yukon, the next release of SQL Server, which will let you use the .NET languages to build database objects. In this article, I provide an introductory guide to using the SqlCommand object, showing you how to use SqlCommand to execute dynamic SQL DDL statements and pass parameters to a stored procedure. I also demonstrate how to use SqlCommand to execute a stored procedure and return values. Note that although you don't absolutely need to have Visual Studio .NET for these examples, using it is much easier than the alternative of creating these programs manually by using a text editor and the .NET Framework Software Development Kit (SDK).
Before you can use the SqlCommand class (the source code to create a SqlCommand object), you need to add an import directive for the System.Data.SqlClient namespace in your project. The Import directive lets you refer to classes in System.Data.SqlClient in "shorthand" so that you don't always have to prefix each class with the name System
.Data.SqlClient. For a Visual Basic .NET (VB.NET) project, add the following line at the top of your source file:
Imports System.Data.SqlClient
After adding the import directive, you're ready to use ADO.NET SqlCommand objects in your projects.
Executing Dynamic SQL Statements
Dynamic SQL provides a highly flexible mechanism for working with a relational database. For example, dynamic SQL lets you execute ad hoc queries and action queries; it also lets you execute SQL DDL statements to create database objects. The example SQLCommandNonQuery subroutine in Listing 1 illustrates how you can use dynamic SQL with the ADO.NET SqlCommand object to check for the existence of a table and conditionally create the table if it doesn't exist.
The first line of the subroutine passes the sServer and sDB variables as parameters. The values of these two variables specify the SQL Server database server and database that the application will use. Next, the subroutine creates a new ADO.NET SqlConnection object named cn to connect your VB .NET application to the SQL Server database, then passes in the connection string as the first parameter of the SqlConnection object's constructor. The connection string uses Integrated Security to connect to the server because Integrated Security is easier and more secure.
After creating the Connection object, the subroutine instantiates the sSQL variable, which will contain the dynamic SQL statements, and an instance of the SqlCommand object named cmd. In this example, the cmd SqlCommand object's constructor uses two parameters. The first parameter is a string containing the SQL statement that the subroutine will execute; the second is the SqlConnection object that will connect to the target database server. Here, the sSQL string is initially empty.
Next, the subroutine sets up a Try-Catch structure to execute the SQL commands. The first action in the Try-Catch block at callout A in Listing 1 uses the cn SqlConnection object's Open method to open a connection to the SQL Server database that you specified in the connection string. Then, the subroutine assigns the sSQL variable an SQL statement that checks for the existence of the Department table. In this SQL statement, a SELECT statement queries the SQL Server sysobjects table to determine whether a user table named Department exists. If the query finds the Department table, the subroutine executes a DROP TABLE statement to remove the table from the target database. Otherwise, it takes no further action.
So that the subroutine can execute the SQL statement, it assigns the value in the sSQL variable to the CommandText property of the cmd SqlCommand object, then uses the cmd object's ExecuteNonQuery method to send the command to the SQL Server system. The ExecuteNonQuery method executes an SQL statement that doesn't return a result set or a specific value; in this case, it creates a table. Table 1 lists all the SQL command execution methods that the SqlCommand object supports for executing SQL commands on the target database.
Prev. page
[1]
2
3
next page 
