Using the SQLCommand Object
(Page 1 of 5 )
In this article, you will learn how to use the .NET Framework Data Provider for DQL Server's SqlCommand object to execute commands on a target SQL Server system. It is taken from chapter 8 of
ADO.NET: The Complete Reference, written by Michael Otey and Denielle Otey (McGraw-Hill/Osborne, 2004; ISBN: 0072228989).
In this chapter, you see how to use the .NET Framework Data Provider for SQL Server’s SqlCommand object to execute commands on a target SQL Server system. In the first part of this chapter, you learn how to execute simple SQL action queries that don’t return any values. Next, you see how to execute scalar queries using both SQL statements and stored procedures that return single values. The chapter closes with a discussion of how to use the SqlCommand object to commit and roll back transactions.
Executing SQL Statements and Stored Procedures Using the SqlCommand Object Executing dynamic SQL statements and stored procedures are two of the most common database actions that are required by an application. Dynamic SQL statements are SQL statements that are read by the database server and executed when they are sent to the database server from the client application. When the database receives these SQL statements, they are first parsed to ensure that their syntax is correct, and then the database engine creates an access plan—essentially determining the best way to process the SQL statement—and then executes the statements. Unlike dynamic SQL statements, which are often used for executing SQL DML operations like creating tables or for data access operations like performing ad hoc queries, stored procedures are typically used to perform predefined queries and database update operations. Stored procedures form the backbone of most database applications. The primary difference between dynamic SQL statements and stored procedures is that stored procedures are typically created before the application is executed and they reside in the database itself. This gives stored procedures a significant performance advantage over dynamic SQL statements because the jobs of parsing the SQL statement and creating the data access plan have already been completed. It’s worth noting that changes made to data contained in an ADO.NET DataSet either can be posted back to the database using dynamic SQL statements created by the SqlCommandBuilder class, or can be written back to the database using stored procedures. However, you don’t need to use the DataSet and DataAdapter in order to update the database. In cases where you don’t need the data binding and navigation functions provided by the DataSet, the Command objects can provide a much lighter-weight and more-efficient method of updating the database. In the next sections, you’ll see how to use the SqlCommand object to execute an ad hoc query, and then to execute a SQL DDL statement to build a table on the target database, followed by two examples using the stored procedure. The first stored procedure example illustrates passing parameters to a stored procedure, and the second example illustrates executing a stored procedure that supplies a return value.
Using the Visual Studio SqlCommand Object The easiest place to get started with the SqlCommand object is probably using the visual component provided as a part of Visual Studio.NET. Using the same drag-and-drop paradigm that’s implemented for interface components, you can drag and drop an instance of the SqlCommand from the Visual Studio.NET Data Toolbox onto the visual designer. After you’ve done this, you’ll see a new SqlCommand object in the components pane, as shown in Figure 8-1.
Figure 8-1. Adding the Visual SqlCommand object to a Visual Studio.NET project
Dragging and dropping the SqlCommand component from the Data tab to the Design window results in the creation of the SqlCommand1 object that is displayed in the component window at the bottom of the screen. After that, the SqCommand1 component is ready to use. The follow example illustrates executing a simple ad hoc query with the visual SqlCommand component:
Private Sub SQLCommandComponent(ByVal sServer As String, _
ByVal sDB As String)
SqlConnection1.ConnectionString = "SERVER=" & sServer _
& ";" & "INTEGRATED SECURITY=True;DATABASE=" & sDB
'Set up a simple query
SqlCommand1.CommandText = "SELECT * FROM customers"
' Set the active connection
SqlCommand1.Connection = SqlConnection1
Try
' Open a connection
SqlConnection1.Open()
' Execute the query
Dim dr As System.Data.SqlClient.SqlDataReader = _
SqlCommand1.ExecuteReader()
' The reader can now be processed
Catch ex As Exception
MessageBox.Show("Connection error: :" & ex.ToString())
End Try
SqlConnection1.Close()
End Sub
An example of the SQLCommandComponent subroutine written in C# is shown here:
private void SQLCommandComponent(string sServer, string sDB)
{
sqlConnection1.ConnectionString = "SERVER=" + sServer + ";"
+ "INTEGRATED SECURITY=True;DATABASE=" + sDB;
sqlCommand1.CommandText = "SELECT * FROM customers";
// Set the active connection
sqlCommand1.Connection = sqlConnection1;
try
{
// Open a connection
sqlConnection1.Open();
// Execute the query
system.Data.SqlClient.SqlDataReader dr =
sqlCommand1.ExecuteReader();
// The reader can now be processed
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
sqlConnection1.Close();
}
At the top of the SQLCommandComponent routine, you can see where two variables are passed in as parameters. The sServer variable contains the name of the SQL Server system that will be the database server; the sDB variable contains the name of the database that will be accessed. These examples all use the Northwind database that's supplied as a sample database with SQL Server.
The first action within the routine sets the SqlConnection1 object’s connection string using the server and database values that were passed into the routine. The Integrated Security keyword indicates that Windows authentication will be used for the connection. You should note that even though this example is using the visual SqlCommand component, it still requires a SqlConnection object to actually connect to the target database. Although this example uses the visual SqlConnection1 object, this could also use a code-based SqlConnection object as well.
Next the CommandText property of the SqlCommand1 object is assigned a simple SQL SELECT statement that will query all of the rows and columns from the Customers table found in the Northwind database. The next statement sets the Sql1Command1 object’s Connection property to the SqlConnect1 object. Then a Try-Catch block is set up to open the connection and execute the command. The SqlConnection1.Open method is used to initiate a connection to the target SQL Server system; then the SqlCommand1 object’s ExecuteReader command is used to create a SqlDataReader object named dr. The ExecuteReader method is used to return a fast forward-only data stream from the target data source. Table 8-1 lists all of the different SQL command execution methods supported by both the SqlCommand object and the OleDbCommand object.
After the ExecuteReader method has completed, the results of the query can then be accessed using the dr SqlDataReader object. The detailed usage of the SqlDataReader will be presented in the next chapter. If an error occurs during the execution of either the Open method or the ExecuteReader, a message box will be displayed to the user showing the exception text.
Method | Description |
ExecuteNonQuery | The ExecuteNonQuery method is used to execute a SQL statement on the connected data source. It is used for DDL statements and action queries like insert, update, and Delete operations, as well as ad hoc queries. The number of rows affected is returned, but no output parameters or resultsets are returned. |
| |
ExecuteReader | The ExecuteReader method is used to execute a SQL Select statement on the data source. A fast forward-only result is returned. More examples of the ExecuteReader method are shown in Chapter 6. |
ExecuteScalar | The ExecuteScalar method is used to execute a stored procedure or a SQL statement that returns a single scalar value. The first row of the first column of the resultset is returned to the calling application. Any other returned values are ignored. |
ExecuteXMLReader | The ExecuteXMLReader method is used to execute a FOR XML SELECT statement that returns an XML data stream from the data source. More examples of the ExecuteXMLReader method are shown in Chapter 30. The ExecuteXMLReader command is compatible only with SQL Server 2000 and later. |
| |
| |
Table 8-1. SqlCommand SQL Statement Execution Methods
Next: Adding the System.Data.SqlClient Namespace >>
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.
|
|
