Recipe 13.3. Retrieving Results from a Database Query

Problem

You've issued INSERT, UPDATE, and DELETE statements through a command object, but you need to retrieve some data with a SELECT statement.

Solution

Use a DataReader object to quickly review the results of a SELECT statement.

Discussion

The following code retrieves a set of records from Table1:

 ' ----- Connect to the database. Dim connectionString As String = _    "Data Source=MySystem\SQLEXPRESS;" & _    "Initial Catalog=MyDatabase;Integrated Security=true" Dim theDatabase As New SqlClient.SqlConnection(connectionString) theDatabase.Open() ' ----- Prepare the SQL statement for the reader. Dim sqlStatement As New SqlClient.SqlCommand( _    "SELECT * FROM Table1 WHERE Column2 = 25", _    theDatabase) Dim dataResults As SqlClient.SqlDataReader = _    sqlStatement.ExecuteReader() ' ----- Clean up. sqlStatement = Nothing theDatabase.Close() theDatabase.Dispose() 

Assuming that the query returned records, the dataResults object now makes those records available, one at a time. The Read() method retrieves each successive record from the database:

 Do While dataResults.Read()    MsgBox("Column1 = " & CStr(dataResults!Column1)) Loop dataResults.Close() 

Read() returns False when there are no more records available.

To check for the presence of any records before using the Read() method, use the HasRows property:

 If (dataResults.HasRows = False) Then MsgBox("No data.") 

Data readers provide basic and direct access to result sets. They are no-frills objects, but they are quick and simple to use. Their basic and essential features form the basis of other, more complex data-gathering actions in ADO.NET. When you retrieve table results and store them in a DataSet object (described in Recipe 13.7), the DataSet indirectly uses a data reader to transfer the records from the database into the data set.

Records returned by a data reader can be accessed by name or position. For example, if you retrieve data with the columns Column1, Column2, and Column3 (in that order), you can use any of the following statements to access Column2:

 dataResults!Column2 dataResults("Column2") dataResults(1)  ' Zero-based array. 

The fields returned by the reader are stored as System.Object values. You must convert them to their proper data types manually, using the available conversion functions.

Data readers are "forward-only" objects; once you have retrieved a record using Read(), you cannot return to it without reissuing the SQL command to create a new data reader.

While data readers are most often used to process SELECT statements and stored procedures, there are other statements that return data results. SQL Server 2005 includes a new OUTPUT clause in INSERT statements that you can use to retrieve one or more data fields from the newly inserted record. It's common to create a database table with an automatically generated numeric primary key. After inserting a new record into such a table, you have to use a separate SELECT statement to retrieve the new primary key value. The OUTPUT clause lets you retrieve the new primary key value directly from the INSERT statement:

 INSERT INTO Table1 (Column2, Column3)    OUTPUT INSERTED.Column1    VALUES (10, 20) 

Sending this INSERT statement to the database using the ExecuteReader() method returns a single record with a single field containing the value of the new Column1 field. You can also use the ExecuteScalar() method because only a single value is returned:

 Dim sqlStatement As New SqlClient.SqlCommand( _    "INSERT INTO Table1 (Column2, Column3) " & _    "OUTPUT INSERTED.Column1 VALUES (10, 20)", _    theDatabase) Dim newID As Integer = CInt(sqlStatement.ExecuteScalar()) sqlStatement = Nothing 

See Also

Recipe 13.7 discusses how to simply replace the new data values in the SQL statement without having to build the SQL statement from scratch each time.