The Command Object

The `Command` Object

The ADO.NET Command object should also seem very familiar to experienced ADO 2.X programmers. Like the ADO.NET Connection object, the Command object is similar to its ADO 2.X predecessor. This object allows you to execute commands against a data source and obtain the returned data and/or results, if applicable.

As expected, it has the CommandText and CommandType properties to define the actual command text and type, the Connection property to specify a connection to be used to execute the command, and the CommandTimeout property to set the waiting time for a command to complete before giving up and generating an error. It also has a Parameters property, which is a collection of parameters to be passed to and/or from the executed command. Finally, unlike the classic ADO Command object, a Transaction property specifies the transaction in which the command executes.

All three versions of our Command object (OleDb, Sql, and Odbc) have identical properties and methods, with one exception. The SqlCommand has an additional method that the other two don't have: the ExecuteXmlReader method. It takes advantage of SQL Server's ability to return data automatically in XML format (when the FOR XML clause is added to the SQL Select query).

Note

Another difference among the versions of the Command object for the different data providers has to do with the values for the CommandType property. All three support either Text or StoredProcedure, but the OledbCommand object also supports a third possible value of TableDirect. This method efficiently loads the entire contents of a table by setting the CommandType to TableDirect and the CommandText to the name of the table.

Let's continue with the form you prepared, as illustrated in Figure 4.1.

Add an additional button immediately below the btnConnection button from the Windows Forms tab of the Toolbox.
In the Properties window, set the Name property of the button to btnCommand and set the Text property to Command.
Add the code for this btnCommand button, as shown in Listing 4.2.

Listing 4.2 Code to open a database connection and prepare a command object

 Private Sub btnCommand_Click(ByVal sender As System.Object, _   ByVal e As System.EventArgs) Handles btnCommand.Click   ' Create an instance of an Connection object   Dim cnn As SqlConnection = New SqlConnection( _   "server=localhost; uid=sa;database=pubs")   ' Create instance of Command object   Dim cmd As SqlCommand = New SqlCommand()   txtResults.Clear()      ' Set command's connection and command text      cmd.Connection = cnn      cmd.CommandType = CommandType.Text      cmd.CommandText = "Select au_lname, state from authors"      ' Write out command string      txtResults.Text = "Command String:" & ControlChars.CrLf      txtResults.Text = txtResults.Text & ControlChars.Tab & _                        cmd.CommandText() & ControlChars.CrLf End Sub

When you run the DataProviderObjects project and click on the Command button, the textbox should display the SQL statement that you assigned as the CommandText of the SqlCommand object: Select au_lname, state from authors.

Note

Many of the .NET Framework classes, as well as classes written by other developers, have overloaded object constructors. In other words, there are several different ways of creating a new instance of the class, where each constructor takes a different set of arguments. You choose the version that best suits your current usage or need.

The constructor used in Listing 4.2 for the SqlConnection object is different from the one used in Listing 4.1. There, we first used the default constructor, which never takes an argument. We later assigned the connection string to the SqlConnection object by setting the ConnectionString property, which resulted in:

 ' Create an instance of an Connection object  Dim cnn As SqlConnection = New SqlConnection() ' Set the connection string cnn.ConnectionString = "server=localhost; uid=sa;database=pubs"

In Listing 4.2, we used a constructor for the SqlConnection object that accepts a connection string as a parameter. That allowed us to create the object and assign it a connection string all in one place in a single line of code, which resulted in:

 ' Create an instance of an Connection object  Dim cnn As SqlConnection = New SqlConnection( _ "server=localhost;uid=sa;database=pubs")

Using the `Command` Object with Parameters and Stored Procedures

When issuing queries or commands against a data source, you often need to pass in parameter values. That is almost always true when you're executing an action (Update, Insert, or Delete) command and calling stored procedures.To meet these needs, the Command object contains a Parameters property, which is a ParameterCollection object, containing a collection of Parameter objects. Again, this feature is very similar to ADO 2.X.

A Parameter (and the ParameterCollection) object is closely tied to its respective data provider, so it is one of the objects that must be implemented as part of an ADO.NET Data Provider. There is a significant difference between programming with the SqlParameter-Collection versus the OdbcParameterCollection and the OledbParameterCollection. The OdbcParameterCollection and the OledbParameterCollection are based on positional parameters, whereas the SqlParameterCollection is based on named parameters. This difference affects the way you define both queries and parameters.

Let's start with a simple parameter query against the authors table in the pubs database. Say that you want to return all the authors from a particular state. On the one hand, if you were using the Oledb or Odbc Data Provider, the query would look like

 Select state, au_fname, au_lname from authors where state = ?

where the placeholder for the parameter is a question mark. Placeholders for additional parameters would also be question marks. The way the parameters are differentiated from each other is by position. That is, the order in which the parameters are added to the ParameterCollection must match exactly the order in which they appear in the query or stored procedure.

On the other hand, if you were using the SqlClient Data Provider, the query would look like

 Select state, au_fname, au_lname from authors where state = @MyParam

where the placeholder for the parameter is the name of the specific parameter; additional parameters would also be indicated by their specific names. Because parameters are differentiated from each other by name, they can be added to the ParameterCollection in any order.

You can create a Parameter object explicitly by using the Parameter constructor (that is, New) or by passing the required arguments to the Add method of the ParameterCollection object the Parameters property of the Command object. Remember also that each of these two methods the Parameter constructor and the Add method have several overloaded options.

Here is one way to add a parameter to a command by explicitly creating the parameter object:

 Dim myParameter As New OdbcParameter("@MyParam", OdbcType.Char, 2)  myParameter.Direction = ParameterDirection.Input myParameter.Value = "CA" cmd.Parameters.Add (myParameter)

And here is one way to add a parameter to a command by passing the arguments to the Add method:

 cmd.Parameters.Add("@MyParam", OdbcType.Char, 2)  cmd.Parameters("@MyParam").Direction = ParameterDirection.Input cmd.Parameters("@MyParam").Value = "CA"

The second method is shorter and is normally preferred, unless there is a reason to reuse the same Parameter object.

You need to provide the parameter name along with its type and length (if appropriate) to the Parameter's Add method. You can then set the direction to be either Input, Output, InputOutput, or ReturnValue. The default direction is Input. Finally, if providing a value for the parameter, you assign this value to the Value property of the parameter object. You could set several additional properties, including Scale, Precision, and IsNullable.

If you were using the SqlClient Data Provider, you would have nearly identical code. The only differences are that the Odbc prefixes would be replaced by Sql prefixes and that the type enumeration is named SqlDbType:

 Dim myParameter As New SqlParameter("@MyParam", SqlDbType.Char, 2)  myParameter.Direction = ParameterDirection.Input myParameter.Value = "CAcmd.Parameters.Add (myParameter)

 cmd.Parameters.Add("@MyParam", SqlDbType.Char, 2)  cmd.Parameters("@MyParam").Direction = ParameterDirection.Input cmd.Parameters("@MyParam").Value = "CA"

Tip

The way to properly (successfully) pass in a null value for a parameter is by using the Value property of the DBNull object. The line of code is

 cmd.Parameters("@MyParam").Value = DBNull.Value

Modify the code for the btnCommand button, as shown in Listing 4.3. When you run the program and click on the btnCommand button, the text of the query and the name and value of the parameter will be displayed.

Listing 4.3 Code to prepare and display command and parameter objects

 Private Sub btnCommand_Click(ByVal sender As System.Object, _   ByVal e As System.EventArgs)Handles btnCommand.Click   ' Create an instance of an Connection object   Dim cnn As SqlConnection = New SqlConnection( _    "server=localhost;uid=sa;database=pubs")   ' Create instances of Command and paramter objects   Dim cmd As SqlCommand = New SqlCommand()   Dim prm As SqlParameter = New SqlParameter()   txtResults.Clear()   ' Open the Connection   cnn.Open()   ' Set command's connection and command text   cmd.Connection = cnn   cmd.CommandType = CommandType.Text   cmd.CommandText = _   "Select au_lname, state from authors where state = @MyParam"   ' Create parameter and set value   cmd.Parameters.Add(New SqlParameter("@MyParam", SqlDbType.Char, 2))   cmd.Parameters("@MyParam").Value = "CA"   ' Write out command string   txtResults.Text = "Command String:" & ControlChars.CrLf   txtResults.Text = txtResults.Text & ControlChars.Tab & _     cmd.CommandText() & ControlChars.CrLf   ' Write out command parameters and values   txtResults.Text = txtResults.Text & "Command parameters:" & _                     ControlChars.CrLf   For Each prm In cmd.Parameters     txtResults.Text = txtResults.Text & ControlChars.Tab & _     prm.ParameterName & "=" & prm.Value & ControlChars.CrLf   Next End Sub

You call stored procedures in the same way, except that the CommandType is CommandType.StoredProcedure rather than CommandType.Text. The name of the stored procedure is assigned to the CommandText property. Thus, calling the stored procedure named GetAuthorsFromState, which expects a two-character parameter, would look like

 cmd.CommandType = CommandType.StoredProcedure  cmd.CommandText = "GetAuthorsFromState" cmd.Parameters.Add("@MyParam", SqlDbType.Char, 2) cmd.Parameters("@MyParam").Direction = ParameterDirection.Input cmd.Parameters("@MyParam").Value = "CA"

Tip

When specifying a stored procedure to be called by using the OdbcCommand, you must take care to use the standard ODBC stored procedure escape sequences, rather than just specifying the procedure name for the CommandText. Question marks are used as placeholders for the parameters in the escape sequence. The OdbcCommand equivalent of the previous code section is

 cmd.CommandType = CommandType.StoredProcedure  cmd.CommandText = "{GetAuthorsFromState ?}" cmd.Parameters.Add("@MyParam", OdbcType.Char, 2) cmd.Parameters("@MyParam").Direction = Pa ram e terParameterDirection.Input cmd.Parameters("@MyParam").Value = "CA"

If the stored procedure also returns a return value, it is specified by preceding the procedure name with "? =", as in

 cmd.CommandText = "{? = GetAuthorsFromState ?}"

If you're expecting a called stored procedure to return a value, you would specify the direction to be Output and then read the Value property of the parameter after calling the stored procedure. In this example, we also define a return value to be returned from the stored procedure. Because an SQL Server Int type is specified, there is no need to specify a length for the parameter, as it is by definition four bytes long:

 cmd.Parameters.Add(New SqlParameter("result", SqlDbType.Int))  cmd.Parameters("result").Direction = ParameterDirection.ReturnValue cmd.Parameters.Add(New SqlParameter("@MyParam", SqlDbType.Int)) cmd.Parameters("@MyParam").Direction = ParameterDirection.Output ' Call stored procedure here MsgBox (cmd.Parameters("@MyParam").Value)

Note

When defining a parameter to be a ReturnValue of a called stored procedure, you should define it to be the first parameter added to the Parameters collection. This definition is required for the Oledb and Odbc parameters because, as we pointed out earlier, they are treated as position-based and a return value is expected to be in the first position. However, when working with Sql parameters, you can place the return value parameter in any position because Sql parameters are treated as named-based parameters.

Shortly, we present additional code examples involving the use of parameters as we show how to execute these commands.

Executing the Commands

So far, you've seen how to set the various properties and parameters of a Command object, but you haven't yet actually executed any of these commands! The time has come to do that. There are three standard methods for executing the commands defined by a Command object and one additional method that is available only with the SqlCommand object:

ExecuteNonQuery Executes an SQL command that does not return any records.
ExecuteScalar Executes an SQL command and returns the first column of the first row.
ExecuteReader Executes an SQL command and returns the resulting set of records via a DataReader object.
ExecuteXmlReader (SqlCommand only) Executes an SQL command and returns the resulting set of records as XML via a XmlReader object.

We now look at the first three shared execution methods. In Chapter 10, we discuss the ExecuteXmlReader method as we explore the topic of ADO.NET and XML.

`ExecuteNonQuery`

The ExecuteNonQuery method is perhaps the most powerful way to execute commands against a data source. This method allows you to execute commands that don't return any values (result set or scalar) other than a value indicating the success or failure of the command. This method is also the most efficient way to execute commands against a data source. You can execute an SQL statement or stored procedure that is either (1) a Catalog or Data Definition Language (DDL) command, which can create or modify database structures such as tables, views, or stored procedures; or (2) an Update command (Update, Insert, or Delete) that modifies data in the database.

Note

The ExecuteNonQuery method returns a single integer value. The meaning of this return value depends on the type of command being executed.

If you're executing a Catalog or DDL command to modify database structures, the value of the method's return value is -1 if the operation completed successfully. If you're updating records with an Update, Insert, or Delete statement, the return value is the number of rows affected by the operation. In either case, the return value of the method is 0 if the operation fails.

Continuing with the DataProviderObjects project, you will now use the objects in the Oledb namespace and work with pubs database. Your task is to create a new table for this database by executing the required DDL command. This table will map between zip codes and states. The field definitions match those used in the pubs database (which are different from those used in the Novelty database). The table is to have two fields one for the zip code and another for the corresponding state. The SQL statement to create this table is

 CREATE TABLE tblStateZipCodes (    ZipCode char (5) NOT NULL,   State char (2) NOT NULL )

Now modify the original Form1 by doing the following.

Open Form1 in the Visual Studio IDE.
In the upper left corner of the form, add another button from the Windows Forms tab of the Toolbox.
In the Properties window, set the Name property of the button to btnNonQuery and set the Text property to ExecuteNonQuery.

Then add the code shown in Listing 4.4 for the Click event of this new button.

Listing 4.4 Code to create a database table, using the objects from the Oledb namespace

 Private Sub btnNonQuery_Click(ByVal sender As System.Object, _      ByVal e As System.EventArgs) Handles btnNonQuery.Click      'Create an instance of an Connection object      Dim cnn As OleDbConnection = New OleDbConnection( _        "provider=SQLOLEDB;server=localhost;uid=sa;database=pubs")      Dim sql As String      Dim result As Integer      'Create instance of Command object      Dim cmd As OleDbCommand = New OleDbCommand()      'Set command's connection and command text      cmd.Connection = cnn      cmd.CommandType = CommandType.Text      ' Assign SQL statement to create a new table      sql = "CREATE TABLE tblStateZipCodes ( " & _      "ZipCode char (5) NOT NULL," & _       "State char (2) NOT NULL )"      cmd.CommandText = sql      ' Open the Connection before calling ExecuteNonQuery()      cnn.Open()      ' We need to put the code inside a Try- Catch block      ' since a failed command ALSO generates a run-      ' time error      Try        result = cmd.ExecuteNonQuery()      Catch ex As Exception        ' Display error message        MessageBox.Show(ex.Message)      End Try      ' Show results of command execution      If result = -1 Then        MessageBox.Show("Command completed successfully")      Else        MessageBox.Show("Command execution failed")      End If      cnn.Close() End Sub

When you run the DataProviderObjects project and click on the ExecuteNonQuery button for the first time, a message box should appear, indicating that the command completed successfully. You can verify that the table was created correctly by looking at the list of tables for the Novelty database, using either the Visual Studio Server Explorer (Chapter 1) or the SQL Server Enterprise Manager (Chapter 3).

If you then click on the ExecuteNonQuery button again, two message boxes will appear. The first is the text of the message from the exception generated and is displayed from within the catch block, which offers the specific reason for the failure. In this case the command was rejected because a table by that name already exists in the database. A second message box is then displayed, notifying you that the command execution failed.

In the same way, you can create a view or a stored procedure. To create a view named Employee-Jobs_view that returns a result set containing job titles and employee names (sorted by job description), change the SQL statement in Listing 4.3 to

 sql = "CREATE VIEW EmployeeJobs_view AS" & _       "SELECT TOP 100 PERCENT jobs.job_desc," & _      "employee.fname, employee.lname" & _      "FROM jobs INNER JOIN" & _      "employee ON jobs.job_id = employee.job_id" & _      "ORDER BY jobs.job_desc"

Note

To include an ORDER BY clause in a view definition to sort the results, you must include a TOP clause in the select statement.

To create a stored procedure that accepts a single parameter and returns a value as a return value, change the SQL statement to that shown in Listing 4.5.

Listing 4.5 Code containing an SQL statement to create the AuthorsInState1 stored procedure

 sql = "CREATE PROCEDURE AuthorsInState1 @State char(2)" & _      "AS declare @result int" & _      "select @result = count (*) from authors" & _      "where state = @State" & _      "return (@result)"

Note

Although the ExecuteNonQuery method returns only a single value, if you define any output or return value parameters for the command, they are correctly filled with the parameter's data. This approach is more efficient than executing a command that returns a result set or a scalar value.

Let's now turn to the second type of nonquery command a database update command, which can be an Update, Insert, or Delete command. These commands usually require parameters, especially when you're using stored procedures (which you usually want to do, for performance reasons) to carry out these operations.

Continuing with Form1 in the DataProviderObjects project, suppose that the publisher that has implemented the pubs database is in a generous mood and has decided to increase the royalty percentage paid to its authors. Adding a command button and a textbox to the form allows the publisher's CFO to enter the royalty increase as a parameter to the Update command. You can do so as follows.

Add an additional button immediately below the cmdExecuteNonQuery button.
In the Properties window, set the Name property of the button to cmdUpdate and set the Text property to Update.
Add a textbox immediately below this new button from the Windows Forms tab of the Toolbox.
In the Properties window, set the Name property of the button to txtParam1 and set the Text property to 0. Setting the value of the txtParam1 to 0 ensures that if you run the program and forget to set this value before clicking on the Update button, you won't do any damage or cause a run-time error.
Add the code for this new button, as shown in Listing 4.6.

Listing 4.6 Code to update database table, using SQL statement with a parameter

 Private Sub btnUpdate_Click(ByVal sender As System.Object, _   ByVal e As System.EventArgs) Handles btnUpdate.Click   Dim result As Integer   ' Create an instance of an Connection object   Dim cnn As SqlConnection = New SqlConnection( _           "server=localhost;uid=sa;database=pubs")   ' Create instance of Command object   Dim cmd As SqlCommand = New SqlCommand()   txtResults.Clear()   ' Set command's connection and command text   cmd.Connection = cnn   cmd.CommandType = CommandType.Text   cmd.CommandText = "UPDATE roysched SET royalty = royalty + @param1"   ' Create parameter and set value   cmd.Parameters.Add(New SqlParameter("@param1", SqlDbType.Int))   cmd.Parameters("@param1").Direction = ParameterDirection.Input   cmd.Parameters("@param1").Value = Val(txtParam1.Text)   ' Open the Connection before calling ExecuteReader()   cnn.Open()   result = cmd.ExecuteNonQuery()   MessageBox.Show(result & "records updated", "DataProviderObjects")   cnn.Close() End Sub

You can update the royalty table by running the DataProviderObjects project, setting an integer value in the parameter textbox, and then clicking on the Update button. A message box should appear, indicating the number of records modified. You can verify this result by using the SQL Server Enterprise Manager and displaying the data from the roysched table before and after executing the update command from the demo program.

You could perform the same update by using a stored procedure. That has the advantages of better performance and centralized location. A possible disadvantage is that you may need a database administrator (DBA) or at least someone who knows how to write stored procedures as part of your development team. In a large organization, it could take days to get a DBA to modify some stored procedure(s). If you can do the job yourself, it should take less than a minute. You can add it by using either the SQL Server Enterprise Manager or the SQL Query Analyzer, as described in Chapter 3. Alternatively, you can use the DataProviderObjects project, by changing the SQL statement, as we have done previously.

Here is what the stored procedure would look like

 CREATE PROCEDURE UpdateRoyalties  @param1 int AS UPDATE roysched SET royalty = royalty + @param1

In Listing 4.6, we need to change the Command object's CommandType and CommandText properties to call the stored procedure. These two lines of code now are

 cmd.CommandType = CommandType.StoredProcedure  cmd.CommandText = "UpdateRoyalties"

Running the modified program should produce the same results as before. Now, though, the update is performed by a stored procedure rather than by an SQL statement from our application code.

`ExecuteScalar`

At times you may want to execute a database command that returns a scalar value that is, a single value. Typical examples of such commands are SQL statements that perform an aggregate function, such as SUM or COUNT. Other examples are lookup tables that return a single value or commands that return a Boolean result. The ExecuteScalar method executes the given command and returns the first column of the first row in the returned result set. Other columns or rows are ignored.

Let's add the following stored procedure to the pubs database:

 CREATE PROCEDURE AuthorsInState2@param1 char(2)  AS select count(*) from authors where state = @param1

The procedure AuthorsInState2 accepts a parameter that is a two-character state code and returns from the authors table the number of authors in that state. This procedure is functionally equivalent to AuthorsInState1, which was shown in Listing 4.5 but returns a result set rather than a value.

Note

There is a slight performance penalty when you use ExecuteScalar instead of ExecuteNonQuery and pass the scalar value as a ReturnValue parameter. Then why use the ExecuteScalar method? It's simpler and less work, as you don't have to deal with parameter definitions in both the command definition and the calling code.

You call this procedure with the Odbc Data Provider objects.

Add an additional button immediately below the txtParam1 textbox.
In the Properties window, set the Name property of the button to cmdScalar and set the Text property to ExecuteScalar.
Add the code for this new button, as shown in Listing 4.7.

Listing 4.7 Code to retrieve a scalar value from a stored procedure, using the Odbc Data Provider objects

 Private Sub btnExecuteScalar_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnExecuteScalar.Click      Dim result As Integer        ' Create an instance of an Connection object      Dim cnn As OdbcConnection = New OdbcConnection( _      "DRIVER={SQL Server};server=localhost;uid=sa;database=pubs")      ' Create instance of Command object      Dim cmd As odbcCommand = New odbcCommand()      txtResults.Clear()      ' Set command's connection and command text      cmd.Connection = cnn        cmd.CommandType = CommandType.StoredProcedure      cmd.CommandText = "{call AuthorsInState2(?)}"      ' Create parameter and set value      cmd.Parameters.Add("@param1", OdbcType.Char, 2)      cmd.Parameters("@param1").Value = txtParam1.Text      ' Open the Connection before calling ExecuteReader()      cnn.Open()      result = cmd.ExecuteScalar()      MessageBox.Show("Count is" & result, "DataProviderObjects")      cnn.Close () End Sub

Run the application and enter a two-character state code into the parameter textbox. When you click on the ExecuteScalar button, a message box should appear, indicating the count of authors in that state. You can verify this result by using the SQL Server Enterprise Manager to display the data from the authors table.

Note

The default data for the pubs database should yield a count of 2 for the state UT and of 15 for the state CA.

`ExecuteReader`

In some ways, we saved the best (or most important) for last. The ExecuteReader method is what you call in order to execute a command that returns a set of rows (records). In most database applications, it is probably the execution method that you will use most of the time. This method executes a command that returns a result set of data rows by means of a DataReader object. You scan the rows one at a time, sequentially from the first one. We present more detail about the DataReader and give examples in the next section.