This documentation is archived and is not being maintained.

Executing a Data Command that Returns a Result Set

Visual Studio .NET 2003

You can use a data command to execute a database procedure that returns a read-only result set — that is, you can execute a SQL Select statement or a stored procedure that contains a Select statement. This is similar to what happens when you fill a dataset using a data adapter, except that the result set is returned directly to you.

If you don't have other reasons to use a dataset (for example, you want to share the data with another component or application), it can be faster to use a data command to get a result set directly rather than load it into the dataset. For more information on the relative advantages of datasets versus data commands, see Recommendations for Data Access Strategies.

You can return zero or more records using a data command. You might therefore use a data command for the following:

  • To return a set of records that you fill into a control on a form. This is especially useful in Web Forms pages, where you often creating read-only listings (such as search results or inventory lists).
  • To return a single record, which you pinpoint by passing parameters to the command that identify the record by ID or other unique value. You might pass an ID to a command to get back a corresponding record, or you might be filling a single record into a data view so you can bind a set of form controls to the data view.
  • To execute a command that returns catalog information from a database.

A result set is returned to you in a data reader (an object of type OleDbDataReader, SqlDataReader, OdbcDataReader, or OracleDataReader). You can then loop through this object, extracting individual records from the result set. Because the data reader provides forward-only, read-only access to the data, it is very fast.

To execute a command that returns a result set

  1. Add a data command to your form or component. For more information, see Adding Data Commands to a Form or Component.
  2. Set the data command object's CommandText property to an SQL statement or the name of a stored procedure that returns a result set.
  3. Set the CommandType property to CommandType.Text (for a statement) or CommandType.StoredProcedure (for a stored procedure). These values are defined in the CommandType enumeration.
  4. If the command takes parameters, set them. For more information, see Setting and Getting Data Command Parameters.
  5. Create a data reader object as an instance of either the OleDbDataReader, SqlDataReader, OdbcDataReader, or OracleDataReader class.
  6. Open the connection associated with the data command.
  7. Call the command's ExecuteReader method, setting the result to the data reader you created in Step 5.
    Tip   The ExecuteReader method allows you to pass a parameter indicating command behavior, including options to close the connection immediately, return only a single record, and return keys only. For more information, see OleDbCommand.ExecuteReader Method (CommandBehavior) SqlCommand.ExecuteReader Method (CommandBehavior), OdbcCommand.ExecuteReader Method (CommandBehavior), or OracleCommand.ExecuteReader Method (CommandBehavior).
  8. Loop through the data reader using its Read method until the method returns false.
  9. Close the reader.
  10. If you did not do so in executing the ExecuteReader method, close the connection.

The following example shows how to read the records in the Authors table of the SQL Server pubs database. The example uses the command type of TableDirect and specifies the name of the Authors table as the command text. (Both of these settings can be made in the Properties window, but are shown here for completeness.) When the ExecuteReader method has been called, the example loops through the data reader and builds up a delimited string of author information that it displays in a text box.

Note   In the example, column information is extracted by referring to its column name in the data reader. You can optimize access by using more efficient ways to get the column information. For more information, see Retrieving Data Using the DataReader.
' Visual Basic
Dim dreader As System.Data.OleDb.OleDbDataReader
OleDbCommand1.CommandText = "authors"
OleDbCommand1.CommandType = CommandType.TableDirect
OleDbConnection1.Open()
dreader = OleDbCommand1.ExecuteReader(CommandBehavior.CloseConnection)
Dim s As String = ""
While dreader.Read()
   s &= dreader("au_id").ToString() _
     & vbTab & dreader("au_fname").ToString() _
     & " " & dreader("au_lname").ToString & ControlChars.CrLf
End While
TextBox1.Text = s
dreader.Close()

// C#
System.Data.OleDb.OleDbDataReader dreader;
OleDbCommand1.CommandText = "authors";
OleDbCommand1.CommandType = CommandType.TableDirect;
OleDbConnection1.Open();
dreader = OleDbCommand1.ExecuteReader(CommandBehavior.CloseConnection);
string s = "";
while (dreader.Read())
{
   s += dreader["au_id"].ToString() + "\t" 
     + dreader["au_fname"].ToString() + " " 
     + dreader["au_lname"].ToString() + "\n";
}
TextBox1.Text = s;
dreader.Close();

The following example shows two ways to use a data reader to get data. The first part is similar to the previous example, but it uses a data reader to load a ListBox control in a Windows Form with just the author_id column from the authors table.

The second part is a handler for the list box control's SelectedIndexChanged event. The method uses the au_id value that the user has selected as the parameter for a SQL Select statement written to return only one record (by selecting on the primary key). Because the data reader is expected to return only one record, it is not in a loop; instead, its Read method is called only one. (The example does check that there is at least one record by calling the Read method in an if statement.)

The two event handlers use different data commands; the first one takes no parameters, and the second one takes a single parameter for the author id.

' Visual Basic
Private Sub btnLoadListBox_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnLoadListBox.Click
   ' This method fills a ListBox control with author ids.
   Dim dreader As System.Data.OleDb.OleDbDataReader
   OleDbCommand1.CommandText = "authors"
   OleDbCommand1.CommandType = CommandType.TableDirect
   OleDbConnection1.Open()
   dreader = OleDbCommand1.ExecuteReader()
   While dreader.Read()
      ListBox1.Items.Add(dreader("au_id"))
   End While
   dreader.Close()
   OleDbConnection1.Close()
End Sub

Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
   Dim dreader As System.Data.OleDb.OleDbDataReader
   ' The following two properties can be set in the Properties window
   ' but are shown here for completeness.
   OleDbCommand2.CommandText = _
      "select au_id, au_lname, au_fname from authors where au_id = ?"
   OleDbCommand2.CommandType = CommandType.Text
   OleDbCommand2.Parameters("au_id").Value = ListBox1.Text
   OleDbConnection1.Open()
   dreader = OleDbCommand2.ExecuteReader()
   If dreader.Read() Then
      txtAuthorID.Text = dreader("au_id").ToString()
      txtAuthorLastName.Text = dreader("au_lname").ToString()
      txtAuthorFirstName.Text = dreader("au_fname").ToString()
   End If
   dreader.Close()
   OleDbConnection1.Close()
End Sub

// C#
private void btnLoadListBox_Click(object sender, System.EventArgs e)
{
   // This method fills a ListBox control with author ids.
   System.Data.OleDb.OleDbDataReader dreader;
   OleDbCommand1.CommandText = "authors";
   OleDbCommand1.CommandType = CommandType.TableDirect;
   OleDbConnection1.Open();
   dreader = OleDbCommand1.ExecuteReader();
   while( dreader.Read())
   {
      ListBox1.Items.Add(dreader["au_id"]);
   }
   
   dreader.Close();
   OleDbConnection1.Close();
   Button6.Enabled = true;
}

private void ListBox1_SelectedIndexChanged(object sender, 
System.EventArgs e)
{
   System.Data.OleDb.OleDbDataReader dreader;
   // The following two properties can be set in the Properties window
   // but are shown here for completeness.
   OleDbCommand2.CommandText = 
      "select au_id, au_lname, au_fname from authors where au_id = ?";
   OleDbCommand2.CommandType = CommandType.Text;
   OleDbCommand2.Parameters["au_id"].Value = ListBox1.Text;
   OleDbConnection1.Open();
   dreader = OleDbCommand2.ExecuteReader();
   if (dreader.Read())
   {
      txtAuthorID.Text = dreader["au_id"].ToString();
      txtAuthorLastName.Text = dreader["au_lname"].ToString();
      txtAuthorFirstName.Text = dreader["au_fname"].ToString();
   }
   dreader.Close();
   OleDbConnection1.Close();
}

See Also

Introduction to DataCommand Objects in Visual Studio | Working with Data Commands: High-Level Process | Setting and Getting Data Command Parameters | Executing Updates or Database Commands using a Data Command | Executing a Data Command that Returns a Single Value | Obtaining Data As XML From SQL Server

Show: