Using the SQLCommand Object - Adding the System.Data.SqlClient Namespace
(Page 2 of 5 )
Although using the visual connection components provided by the Visual Studio.NET design environment is pretty easy, in the long run, you’ll probably find it simpler to use the ADO.NET objects by just writing code. To use the SqlCommand class in your project, you first need to add an import directive for the System.Data.SqlClient Namespace in your project. The following example illustrates how to do this for a VB.NET project:
Imports System.Data.SqlClient
For a C# project, you need to add an import directive for the System.Data.SqlClient Namespace as follows:
using System.Data.SqlClient;
After the import directive has been added, you’re ready to use code-based SqlCommand objects in your projects.
Executing Dynamic SQL Statements Using SqlCommand Dynamic SQL provides an extremely flexible mechanism for working with the database. Dynamic SQL allows you to execute ad hoc queries and return the results from action queries, as well as execute SQL DDL statements to create database objects. The following SQLCommandNonQuery subroutine provides an example illustrating how you can use dynamic SQL with the ADO.NET SqlCommand object to check for the existence of a table and conditionally create it if it doesn’t exist:
Private Sub SQLCommandNonQuery(ByVal sServer As String, _
ByVal sDB As String)
Dim cn As New SqlConnection("SERVER=" & sServer _
& ";INTEGRATED SECURITY=True;DATABASE=" & sDB)
Dim sSQL As String
Dim cmd As New SqlCommand(sSQL, cn)
Try
' Open the connection
cn.Open()
' First drop the table
sSQL = "IF EXISTS " _
& "(SELECT * FROM dbo.sysobjects WHERE id = " _
& "object_id(N'[Department]') " _
& "AND OBJECTPROPERTY(id, N'IsUserTable') = 1) " _
& "DROP TABLE [department]"
cmd.CommandText = sSQL
cmd.ExecuteNonQuery()
' Then create the table
sSQL = "CREATE TABLE Department " _
& "(DepartmentID Int NOT NULL, " _
& "DepartmentName Char(25), PRIMARY KEY(DepartmentID))"
cmd.CommandText = sSQL
cmd.ExecuteNonQuery()
Catch e As Exception
MsgBox(e.Message)
End Try
' Close the connection
cn.Close()
End Sub
The C# version of the SQLCommandNonQuery subroutine is shown here:
private void SQLCommandNonQuery(string sServer, string sDB)
{
SqlConnection cn = new SqlConnection("SERVER=" + sServer
+ ";INTEGRATED SECURITY=True;DATABASE=" + sDB);
string sSQL;
SqlCommand cmd = new SqlCommand("", cn);
try
{
// Open the connection
cn.Open();
// First drop the table
sSQL = "IF EXISTS "
+ "(SELECT * FROM dbo.sysobjects "
+ "WHERE id = object_id(N'[Department]') "
+ "AND OBJECTPROPERTY(id, N'IsUserTable') = 1) "
+ "DROP TABLE [department]";
cmd.CommandText = sSQL;
cmd.ExecuteNonQuery();
// Then create the table
sSQL = "CREATE TABLE Department "
+ "(DepartmentID Int NOT NULL, "
+ "DepartmentName Char(25), "
+ "PRIMARY KEY(DepartmentID))";
cmd.CommandText = sSQL;
cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
// Close the connection
cn.Close();
}
In the first part of the SQLCommandNonQuery subroutine, you can see where the sServer and sDB variables are passed as parameters. The values of these two variables are used to specify the SQL Server database server and database that will be used. Next, a new SqlConnection object named cn is created. In this example, the connection string is passed in as the first parameter of the SqlConnection object’s constructor. The connection string uses Integrated Security to connect to the server and database specified using the server and database keywords. For more information about the Sql Connection object’s connection string keywords, you can refer to Chapter 4.
After the Connection object has been created, the sSQL variable that will be used to contain the dynamic SQL statements and an instance of the SqlCommand object named cmd are instantiated. In this example, the constructor of the cmd SqlCommand object uses two parameters—the first being a string containing the SQL statement that will be executed, and the second being the SqlConnection object that will provide the connection to the target database server. Here the sSQL string is initially empty. Next, a Try-Catch structure is set up to execute the SQL commands. The first action that you can see within the Try-Catch block uses the cn SqlConnection object’s Open method to open a connection to the SQL Server database that was specified earlier in the connection string used in the SqlConnection object’s constructor. Then, the sSQL variable is assigned a SQL statement that checks for the existence of the department table. In this SQL statement, you can see that a Select statement queries the SQL Server sysobjects table to determine if a User Table named Department exists. If the Department table is found, a DROP TABLE statement will be executed to remove the table from the target database. Otherwise, if the Department table isn’t found, no further action will be taken. In order to actually execute the SQL statement, that value in the sSQL variable is then assigned to the CommandText property of the cmd object, and then the ExcuteNonQuery method of the cmd SqlCommand object is used to send the command to the SQL Server system. The ExecuteNonQuery method is used to execute a SQL statement that doesn’t return a resultset or a specific return value.
After the first DROP TABLE SQL command has been issued, the same sequence is followed to execute a Create Table command. First, the sSQL variable is assigned a SQL CREATE TABLE statement that creates a table named Department that consists of two columns. The first column is an integer data type named DepartmentID, which is also the primary key, and the second column is a 25-character data type named DepartmentName. Then the value in the sSQL variable is copied to the cmd object’s CommandText property, and the ExecuteNonQuery method is called to execute the CREATE TABLE SQL statement. Following the successful completion of the ExecuteNonQuery method, the Department Table will exist in the database that was earlier identified in the sDB variable.
If an error occurs during any of these operations, the SqlConnection object’s Open method or either instance of the SqlCommand object’s ExecuteNonQuery method, then 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 CommandNonQuery subroutine, the SqlConnection object’s Close method is executed to end the connection to the SQL Server database.
Next: Executing Parameterized SQL Statements >>
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.
|
|
