Sending Commands to a Data Source | MCAD/MCSD: Visual Basic .NET XML Web Services and Server Components Study Guide

In this section we will see how to use methods of the Command object to send different types of commands to the data source. The most commonly used commands are likely to be SQL SELECT queries, which will return rows of data to your application. We will see how to use a DataReader object to access the data that is retrieved from this type of command. We will discuss important parameters that can modify the Command object’s behavior when executing commands. Finally, we will learn how to use the Command object to send queries to the database that do not return rows of data. These may be SQL INSERT, UPDATE, and DELETE queries, or queries that perform calculations.

The SqlCommand and OleDbCommand objects have a few important properties. These are shown in Table 5.2.

Table 5.2: Selected Properties of the Command Object
Property	Description
CommandText	Gets or sets the SQL statement or stored procedure name to execute at the data source.
CommandTimeout	Gets or sets the wait time before terminating the attempt to execute a command and generating an error. The default is 30 seconds.
CommandType	Gets or sets a value indicating how the CommandText property is to be interpreted (Text, Stored Procedure or TableDirectThe default is CommandType.Text.
Connection	Gets or sets the connection used by this command.
Parameters	Gets the ParameterCollection.
Transaction	Gets or sets the transaction in which the command executes.

The CommandText property and CommandType property indicate the type of instruction that you will be sending to the database. There are three possibilities:

If you would like to build a SQL statement in your code and submit this query to the database, the CommandType property is set to Text (that is, CommandType.Text), and the corresponding CommandText property to a string that contains your SQL statement.
If you would like to call a stored procedure, the CommandType property is set to StoredProcedure, and the CommandText property is set to a string that contains the name of the stored procedure as defined in the database.
If you would like to access an entire table (small tables only, such as a list of categories), the CommandType property is set to TableDirect, and the CommandText property is set to a string that contains the name of the table as defined in the database.
You must also set the command’s Connection property to reference an existing Connection object that you have already created in your code.

As with most ADO.NET objects, SqlCommand and OleDbCommand have a set of overloaded constructor methods that enable you to create the objects in your code in various ways. With the Command objects, you can use the default constructor, with no parameters, to instantiate the objects and then set properties in separate lines of code. Listing 5.2 shows an example of this, by expanding on the code from Listing 5.1 (which showed how to create a connection).

Listing 5.2: Creating a Connection and Command

Imports System.Data Imports System.Data.SqlClient Public Sub GetDataList()   Dim myConn As SqlConnection = New SqlConnection()   Dim myQuery As SqlCommand = New SqlCommand()   myConn.ConnectionString = _     "Data Source=localhost; Initial " & _     "Catalog=pubs; Integrated Security=SSPI; "   myConn.Open()   With myQuery      .Connection = myConn      .CommandType = CommandType.Text      .CommandText = "SELECT * FROM publishers"   End With   'continue working with the data from the database   myConn.Close() End Sub

The other constructor methods for the Command object enable you to accomplish some of the property settings shown in Listing 5.2 all in one step, at the time you declare and instantiate the object. One of the constructors accepts a single string argument that contains the CommandText property. Another accepts two arguments: CommandText and a reference to the Connection object. Yet another constructor accepts three arguments: the text string, the Connection object, and a reference to an ADO.NET Transaction object. (Transaction objects are introduced later in this chapter, in the section titled “Understanding New Objects in the ADO.NET Object Model.”) The following code example creates a Command object that is equivalent to the longer code in Listing 5.2:

Dim myQuery As SqlCommand = New SqlCommand( _    "SELECT * FROM publishers", con) myQuery.CommandType = CommandType.Text

After you have created a Command object and set its properties to define how it will work, the next step is to use one of the command methods to carry out your instruction against the database. Table 5.3 lists those methods.

Table 5.3: Methods of the *SqlCommand* and *OleDbCommand* Objects
Method	Description
Cancel	Cancels the execution of a command.
CreateParameter	Creates a new instance of a Parameter object.
ExecuteNonQuery	Executes a Transact-SQL statement against the connection and returns the number of rows affected, but not resultset data. Primarily used with SQL INSERT, UPDATE and DELETE statements.
ExecuteReader	Creates a DataReader based on the CommandText property. The DataReader is used to access the resultset data.
ExecuteScalar	Executes the query and returns a single value.
ExecuteXmlReader	Creates an XmlReader object based on the CommandText property. This method is available only for the SqlClient object and is used with queries that include the SQL Server 2000 FOR XML clause.
Prepare	Creates a prepared version of the command on the data source.
ResetCommandTimeout	Resets the CommandTimeout property to its default value.

Often your command will retrieve rows of data from the database, but there are other methods available for issuing other types of commands. The ExecuteReader method creates a DataReader object to retrieve rows of data, and the ExeuteNonQuery method and ExecuteScalar method issue commands that do not return rows of data. These latter two methods can be used with SQL INSERT, UPDATE, and DELETE statements or with SQL statements that calculate and aggregate values, such as a sum, count, or average.

The ExecuteXMLReader method is supported only by SqlDataReader for use with the special FOR XML clause of a SQL query that is unique to Microsoft SQL Server 2000. Executing this method returns data from the database in the form of an XML document rather than as a rowset. This method will create an object of type System.XML.XMLTextReader to enable you to work with the data. The XMLTextReader object and working with XML data is covered in Chapter 7.

Using the DataReader

The SqlDataReader class and the OleDbDataReader class provide the same functionality that was available in the original ADO object model by using a forward-only, read-only recordset. This is the object typically used when you are retrieving the data from the database only for the purpose of displaying that data for the user. When you use a DataReader, you can access each row in the resultset only once. The DataReader holds the connection to the database open until you have completed your work with the data, and then you must explicitly close the DataReader and the connection. The DataReader is always created by using the ExecuteReader method of a Command object. You cannot instantiate a DataReader by using the New keyword.

Before you look at an example of the DataReader, let’s review the properties and methods that you will use while working with it. The properties of both the SqlDataReader and the OleDbDataReader are the same. Table 5.4 lists these properties.

Table 5.4: Properties of the *SqlDataReader* and the *OleDbDataReade*r
Property	Description
Depth	Gets a value indicating the depth of nesting for the current row.
FieldCount	Gets the number of columns in the current row.
IsClosed	Indicates whether the DataReader is closed.
Item	Gets the value of a column in its native format. (This is Indexer in C#.)
RecordsAffected	Gets the number of rows changed, inserted, or deleted by execution of the SQL statement. This property will always return -1 for SQL SELECT statements.

Some of the methods for the SqlDataReader and the OleDbDataReader classes are different from one another. Table 5.5 lists those methods that they have in common. The SqlDataReader adds methods that work with Microsoft SQL Server 2000 native data types as discussed later in this section.

Table 5.5: Methods That Are Common to *SqlDataReader* and *OleDbDataReader*
Methods	Description
Read	Reads the next row of the DataReader.
Close	Closes the DataReader object.
IsDBNull	Gets a value indicating whether a specific column (by ordinal) is DBNull.
NextResult	Advances the DataReader to the next resultset, when reading the results of batch SQL statements.
GetDataType	Gets the value of the specified column as a specific .NET Framework data type.
GetBytes	Reads a stream of bytes, used primarily for binary large objects (BLOB data).
GetChars	Reads a stream of characters, used primarily for binary large objects (BLOB data).
GetDataTypeName	Gets the name of the source data type.
GetFieldType	Gets the type that is the data type of the object.
GetName	Gets the name of the specified column (by ordinal).
GetOrdinal	Gets the column ordinal (by name).
GetSchemaTable	Returns a DataTable that describes the column metadata of the DataReader.
GetValue	Gets the value of a specific column (by ordinal) as a .NET Framework data type.
GetValues	Gets the values for all the columns in the current row as an Object array.

The Read and Close methods are used every time you work with a DataReader. The IsDBNull method enables you to test individual columns to see that their value is null. The NextResult method is used only when a single DataReader is used to retrieve the results of multiple SQL queries—for example, if you call a stored procedure that performs SELECT statements on multiple tables. Unlike the prior versions of ADO, you do not need to use any recordset navigation methods to iterate through all the rows. An example of this is shown in Listing 5.3.

Notice that to retrieve individual column values from a given row, you will use a method designed to retrieve the specific data type that each column contains (GetString or GetDateTime, for example). For the sake of brevity, we have summarized the set of GetDataType methods into one entry in Table 5.5. Please consult the Visual Studio .NET documentation for a complete list of all data type methods that are available.

The SqlDataReader class has an additional set of GetSqlDataType methods. The methods that are supported by both DataReader classes are based on the data types that are defined by the .NET Framework. The GetSqlDataType methods return values in the form of the native data types defined by SQL Server. Consult the Visual Studio .NET documentation for a complete listing of these under System.Data.SqlTypes.

In addition to the methods designed to retrieve a specific data type there are also methods that enable you to retrieve column data without knowing the data type in advance: GetValue and GetValues. Both of these return values as the .NET Framework type Object. The GetValues method will return all of the column values from a row at once, as an array of Object types. At first it might seem more convenient to use these methods rather than the methods that are specific to a particular data type. Keep in mind that you will most likely have to write additional code to test each value’s data type and then do an explicit conversion before you can do any work with it.

Now that you have learned about the important methods of the SqlDataReader and OleDbDataReader objects, and some of the differences between the two, you are ready to see how they are used. Assuming that we are using the same Connection and Command objects that were shown in Listing 5.2, Listing 5.3 shows a section of code that creates and reads the data from a DataReader.

Listing 5.3: Creating a DataReader and Retrieving Column Values

Dim myReader As SqlDataReader Dim outString As String 'use the existing Command object to create the DataReader myReader = myQuery.ExecuteReader() 'set up a simple loop Do While myReader.Read    outString = myReader.GetString(0) & _       myReader.GetString(1) & "<BR>"    Response.Write(outString) Loop myReader.Close() myConn.Close()

The ExecuteReader method of the existing Command object named myQuery will create the SqlDataReader. Then we will set up a loop. At the beginning of each iteration through the loop, the SqlDataReader object’s Read method is called. This method will return True as long as there are more data rows available to read. Each time through the loop, we are simply building and outputting a string that consists of the values from the first two columns in the resultset. When we reach the end of the resultset and there are no more rows of data available, The Read method will return False and the code will exit the loop. Remember to use the SqlDataReader object’s Close method when you are finished reading all the data and to also use the SqlConnection object’s Close method when you have completed all your work with the database.

Modifying Command Behavior

The ExecuteReader method has an optional parameter called CommandBehavior. The most common use for this parameter is to take advantage of the CloseConnection option. This ensures that the connection will be closed at the same time that the DataReader is closed. You will see this option used in the examples in Exercise 5.1, where you will pass a SqlDataReader back from a function; then it is up to the code in the procedure that called the function to close the SqlDataReader when it is through using the data. Table 5.6 shows all the possible values for the CommandBehavior parameter. CommandBehavior values can be combined.

Table 5.6: The *Command.ExecuteReader (CommandBehavior)* Enumeration
Value	Description
CloseConnection	The associated Connection object is closed when the DataReader object is closed.
Default	No parameters are set.
KeyInfo	The query returns column and primary key information. The query is executed without any locking on the selected rows.
SchemaOnly	The query returns column information only and does not affect the database state.
SequentialAccess	Provides an efficient way for the DataReader to handle rows that contain columns with binary large objects (BLOB).
SingleResult	The query returns a single resultset.
SingleRow	The query is expected to return a single row. Some .NET data providers might, but are not required to, use this information to optimize the performance of the command.

Exercise 5.1 creates a simple web page application that retrieves and displays data in ASP.NET server controls. ASP.NET server controls can simply use data binding to read the values from the DataReader. Unfortunately, this ability in not available in Windows forms controls. Exercise 5.1 shows an example of data binding to controls and also has an example similar to Listing 5.3, which reads the individual values from the DataReader.

Note

The exercises in this chapter use the Microsoft SQL Server 2000 sample database called pubs.

Exercise 5.1: Using Connection, Command, and DataReader Objects

Start Visual Studio .NET and open a new ASP.NET Web application project. Set the location to http://localhost/DataReaderExamples. Use your own web server name in place of localhost if appropriate.
Change the name of WebForm1.aspx to default.aspx.
Use the Properties window to change the pageLayout property of the document to FlowLayout.
Using the Visual Studio .NET Toolbox, drag the Web Forms DropDownList, Label, and DataGrid controls to the design surface of default.aspx. Use the Properties window to set the AutoPostBack property of the DropDownList control to True. Your page should look like the following screen.
Right-click default.aspx in the Solution Explorer and choose View Code. Add the Imports System.Data.SqlClient statement at the top of the code module.

Create a Function procedure called GetPublisherList. This function will return a SqlDataReader object to the calling procedure. Add the following code to create and open a SqlConnection object:

Public Function GetPublisherList() As SqlDataReader    Dim myConn As SqlConnection = New SqlConnection()    myConn.ConnectionString = "Data Source=localhost; Initial " & _       "Catalog=pubs; Integrated Security=SSPI; "    myConn.Open()

Complete this function by writing the code to create SqlCommand and SqlDataReader objects to retrieve rows from the Publishers table and to return the DataReader to the calling procedure:

   Dim myPublishers As SqlCommand = New SqlCommand( _       "SELECT pub_ID, pub_name FROM publishers", myConn)    myPublishers.CommandType = CommandType.Text    Dim myPubReader As SqlDataReader    myPubReader = myPublishers.ExecuteReader(CommandBehavior.CloseConnection)    Return myPubReader End Function

In the Page_Load procedure for default.aspx, write the code to call the GetPublisherList function and display the data from the Publishers table in the DropDownList1 control:

Private Sub Page_Load(ByVal sender As System.Object, _    ByVal e As System.EventArgs) Handles MyBase.Load    Dim pubReader As SqlDataReader    If Not Page.IsPostBack Then        pubReader = GetPublisherList()        With DropDownList1            .DataSource = pubReader            .DataValueField = "pub_ID"            .DataTextField = "pub_name"            .DataBind()            .SelectedIndex = 0        End With        pubReader.Close()    End If End Sub

Save and test your work. You should see the DropDownList control populated with the names of eight publishers. You will not see the DataGrid yet.

Create the GetTitleList function to retrieve data from the Titles table, based on publisher ID. This function takes one argument, the publisher ID, and will also return a SqlDataReader:

Public Function GetTitleList(ByVal pubID As String) As SqlDataReader    Dim myConn As SqlConnection = New SqlConnection()    myConn.ConnectionString = "Data Source=localhost; Initial " & _      "Catalog=pubs; Integrated Security=SSPI; "    myConn.Open()    Dim sqlString As String = _       "SELECT title, price, pubdate FROM titles " & _          "WHERE pub_id = " & pubID    Dim myTitles As SqlCommand = New SqlCommand(sqlString, myConn)    myTitles.CommandType = CommandType.Text    Dim myTitleReader As SqlDataReader    myTitleReader = myTitles.ExecuteReader( _       CommandBehavior.CloseConnection)    Return myTitleReader End Function

Declare a class level variable named pubID.
```
Private pubID As String 
```
With the Page_Load procedure, declare another local variable as type SqlDataReader. Then, directly after the code from step 8, determine the ID value of the publisher that is currently selected in DropDownList1 and store it in a variable. Then call the GetTitleList function, passing the publisher ID from the DropDownList selection.
```
'at the top of the page_load procedure    Dim titleReader As SqlDataReader 'directly after the code from step 8    pubID = DropDownList1.SelectedItem.Value    titleReader = GetTitleList(pubID)
```

Write the code to display information from the Titles table in the DataGrid.

With DataGrid1    .DataSource = titleReader    .DataBind() End With TitleReader.Close()

Save and test your work. The complete code for the Page_Load event procedure is shown in Listing 5.4. Your finished page should look like the following graphic. Each time you change the publisher name that is selected in the DropDownList, a post back to the web application will occur, the GetTitleList function will be called, and the DataGrid will display the results of the new query. Not all publishers in the list have associated books in the Titles table.

Listing 5.4: The Complete Code for the Page_Load Event Procedure for Exercise 5.1

 Private Sub Page_Load(ByVal sender As System.Object, _    ByVal e As System.EventArgs) Handles MyBase.Load    Dim pubReader As SqlDataReader    Dim titleReader As SqlDataReader    If Not Page.IsPostBack Then   'this code runs only the first time the page is loaded       pubReader = GetPublisherList()       With DropDownList1          .DataSource = pubReader          .DataValueField = "pub_ID"          .DataTextField = "pub_name"          .DataBind()          .SelectedIndex = 0       End With       PubReader.Close()    End If    pubID = DropDownList1.SelectedItem.Value    titleReader = GetTitleList(pubID)    With DataGrid1       .DataSource = titleReader       .DataBind()    End With    titleReader.Close() End Sub

Now you are familiar with the basics of using a Command object and the very useful DataReader for retrieving and displaying data from the database. There are other types of queries that you might need to perform against your database. You might want to issue SQL INSERT, UPDATE, or DELETE queries. You might want to execute a query that returns a single value, such as a count of rows in a table or a count of rows that match a SQL WHERE clause in your query. You can even use ADO.NET commands to issue Data Definition Language (DDL) queries that are used to make changes to the database structure. Next, you will look at other methods of the Command object.

Using Queries That Don’t Return Rows

The Command object has two other methods you can use when you want to issue an instruction against your database that does not return rows of data: the ExecuteNonQuery method and the ExecuteScalar method.

ExecuteNonQuery is used for SQL statements that don’t return rows. This method can also be used for calling stored procedures that return data via the Command object’s Parameter collection (you will learn about stored procedures and parameters in the next section). ExecuteNonQuery will return the number of rows that were changed in the database as a result of your SQL instruction. You can check the RecordsAffected property after the query is run to verify that the operation completed as expected.

ExecuteScalar is used when you are performing a query that will return a single value, such as one of the aggregate functions (Count, Sum, Average) or perhaps a stored procedure that does some calculations.

Listing 5.5 shows an example of using the ExecuteNonQuery method to perform a SQL UPDATE statement.

Listing 5.5: Using the ExecuteNonQuery Method

Private Function DoUpdate() As Integer    Dim recsUpdated As Integer    Dim myConn As SqlConnection = New SqlConnection()    myConn.ConnectionString = _       "Data Source=localhost; Initial " & _       "Catalog=pubs; Integrated Security=SSPI; "    myConn.Open()    Dim sqlString As String = "UPDATE titles SET " & _       "price = price * 1.1"    Dim myUpdate As SqlCommand = _       New SqlCommand(sqlString, myConn)    myUpdate.CommandType = CommandType.Text    recsUpdated = myUpdate.ExecuteNonQuery()    myConn.Close()    Return recsUpdated End Function

We have a function that performs an update on the database and returns the number of records affected. First we create a SqlConnection object. We have a SQL UPDATE statement that will change the value of the price column for every row in the database. Each price value will be increased by 10 percent. Now we can create the SqlCommand object. We need an integer variable to hold the return value of the ExecuteNonQuery method, which will tell us how many rows in the database were changed.

Listing 5.6 shows how to use ExecuteScalar to return the average price of a book in the Titles table.

Listing 5.6: Using the ExecuteScalar Method

Private Function GetAveragePrice() As Decimal    Dim objPrice As Object    Dim avgPrice As Decimal    Dim myConn As SqlConnection = New SqlConnection()    myConn.ConnectionString = _       "Data Source=localhost; Initial " & _       "Catalog=pubs; Integrated Security=SSPI; "    myConn.Open()    Dim sqlString As String = _       "SELECT Avg(price) FROM titles"    Dim myCalc As SqlCommand = _       New SqlCommand(sqlString, myConn)    myCalc.CommandType = CommandType.Text    objPrice = myCalc.ExecuteScalar()    myConn.Close()    avgPrice = CType(objPrice, Decimal)    Return avgPrice End Function

This procedure is similar to Listing 5.5, which uses the ExecuteNonQuery method. The main difference is that ExecuteScalar returns an Object type. We need a variable of Object to hold the return value and then we need to convert the value to the appropriate data type before we can use it. In this example, we are calculating an average on a column that is defined as a SQL Server money data type, which is compatible with the .NET Framework data type of decimal. In Exercise 5.2 you will create an application that uses the ExecuteNonQuery method to update values in the database and the ExecuteScalar method to run a query that returns a single result.

Exercise 5.2: Using Queries That Don’t Return Rows

Start Visual Studio .NET and create a new Windows Application project named NoRowSetExample.
Change the name of the default Form1.vb to frmNoRowset.vb.
Add two TextBox controls and two Command Button controls to the form. Name them:
- txtUpdate
- txtAverage
- btnUpdate
- btnAverage
Your form should look like this:
Right-click frmNoRowset.vb in the Solution Explorer and choose View Code.
At the top of the code module for the form, add an Imports statement:
```
Imports System.Data.SqlClient 
```

Create a new function named GetAveragePrice. This function will run a SQL query to calculate the average price of items in the Titles table of the pubs sample database. Your code should look like this:

Private Function GetAveragePrice() As Decimal    Dim objPrice As Object    Dim avgPrice As Decimal    Dim myConn As SqlConnection = New SqlConnection()    myConn.ConnectionString = "Data Source=localhost; Initial " & _       "Catalog=pubs; Integrated Security=SSPI; "    myConn.Open()    Dim sqlString As String = "SELECT Avg(price) FROM titles"    Dim myCalc As SqlCommand = New SqlCommand(sqlString, myConn)    myCalc.CommandType = CommandType.Text    objPrice = myCalc.ExecuteScalar()    myConn.Close()    avgPrice = CType(objPrice, Decimal)    Return avgPrice End Function

Create a new function named DoUpdate. This function will run a SQL UPDATE query that will increase the price of every item in the Titles table by ten percent. Your code should look like this:

Private Function DoUpdate() As Integer    Dim recsUpdated As Integer    Dim myConn As SqlConnection = New SqlConnection()    myConn.ConnectionString = "Data Source=localhost; Initial " & _       "Catalog=pubs; Integrated Security=SSPI;"    myConn.Open()    Dim sqlString As String = _       "UPDATE titles SET price = price * 1.1"    Dim myUpdate As SqlCommand = New SqlCommand(sqlString, myConn)    myUpdate.CommandType = CommandType.Text    recsUpdated = myUpdate.ExecuteNonQuery()    myConn.Close()    Return recsUpdated End Function

In the Form Load event procedure for the form, add code to call the GetAveragePrice function and display the return value in txtAverage:

Private Sub frmNowRowset_Load(ByVal sender As System.Object, _    ByVal e As System.EventArgs) Handles MyBase.Load    txtAverage.Text = CType(GetAveragePrice(), String) End Sub

In the Button Click event procedure for btnAverage, add code to call the GetAveragePrice function and display the return value in txtAverage:

Private Sub btnAverage_Click(ByVal sender As System.Object, _    ByVal e As System.EventArgs) Handles btnAverage.Click    txtAverage.Text = CType(GetAveragePrice(), String) End Sub

In the Button Click event procedure for btnUpdate, add code to call the DoUpdate function and display the return value in txtUpdate:

Private Sub btnUpdate_Click(ByVal sender As System.Object, _    ByVal e As System.EventArgs) Handles btnUpdate.Click    txtUpdate.Text = CType(DoUpdate(), String) End Sub

Save and test your work. Once the form loads, you will see the average price displayed in txtAverage.
Click the Update button. You will see the number of records that were updated displayed in txtUpdate.
Click the Get Average button to see a new value displayed in txtAverage. Because we used the DoUpdate function to increase the price of every book, the calculated average price increased as well.