Traversing a Data Reader

for RuBoard

As mentioned yesterday in discussing the command object, the ExecuteReader method is used to create a data reader object that can then be traversed. Before calling the method, the command must be associated with a connection object and the connection opened using its Open method.

After the data reader has been opened, it can be traversed using its Read method. The Read method positions the data reader at the next available row and so must be called prior to reading data from the first row. In the process, the Read method returns True if more rows are available and False after all the rows have been read. When the data reader is exhausted, the Close method of the data reader and the connection should be called to ensure that the connection is destroyed or released to the pool. The typical pattern for using a data reader is shown in Listing 11.1, assuming that con is a valid connection object:

Listing 11.1 Opening a data reader. This listing opens and traverses a data reader.

 Dim dr As SqlDataReader Try    con.Open()    dr = com.ExecuteReader()    Do While dr.Read()      '  Process the row    Loop Catch e As Exception    '  Handle the error Finally    dr.Close()    con.Close() End Try 

Of course, you can alternatively pass the CloseConnection command behavior to the ExecuteReader method to automatically close the connection when the data reader is closed. As you learned on Day 1, "ADO.NET in Perspective," the fact that developers don't have to check an EOF or BOF property (as in an ADO Recordset ) to determine when the result set is exhausted reduces programming errors. This is because the Read method must be placed at the top of the loop and therefore can't be forgotten as the MoveNext method sometimes was.

Tip

If you attempt to use the pattern shown in Listing 11.1 in C#, you'll notice that you get the compiler error Use of unassigned local variable dr referring to the Finally block. To avoid this, you need to initialize dr to null in the declaration.

While the data reader is open, the associated connection object will be busy, so it can't be used to execute other statements. Attempting to execute a second command on the connection results in an exception.

Note

It should be stressed that this is even the case with the SqlClient provider because this behavior is different from what you might have experienced in the past working with SQL Server. Previously, it was possible for ADO to generate multiple connections automatically when the original connection was busy handling results for a fire-hose cursor.

While in the loop, you can use any of the methods exposed in the IDataRecord interface or those custom implemented by the data reader class to access the data.

Retrieving Single Values

To retrieve single values from each column returned, you can use any of the 15 methods shown in Table 8.6 that return strongly typed data and accept the ordinal value of the column. For example, to retrieve the values from the usp_GetTitles stored procedure, you would use the code in Listing 11.2.

Listing 11.2 Retrieving values. This listing shows the use of the various methods of the data reader to retrieve strongly typed values.

 Dim isbn, title, desc, author, publisher As String Dim price, discount, bulkDiscount As Decimal Dim pubDate As Date Dim bulkAmount As Short Dim catId As Guid dr = com.ExecuteReader()   Do While dr.Read()     isbn = dr.GetString(0)  'nchar(10)     title = dr.GetString(1)  'nvarchar(100)     If Not dr.IsDBNull(2) Then desc = dr.GetString(2)  'nvarchar(2048)     author = dr.GetString(3)  'nvarchar(250)     pubDate = dr.GetDateTime(4)  'smalldatetime     price = dr.GetDecimal(5)  'money     If Not dr.IsDBNull(6) Then discount = dr.GetDecimal(6)  'money     If Not dr.IsDBNull(7) Then bulkDiscount = dr.GetDecimal(7)  'money     If Not dr.IsDBNull(8) Then bulkAmount = dr.GetInt16(8)  'smallint     catId = dr.GetGuid(10)  'uniqueidentifier     If Not dr.IsDBNull(11) Then publisher = dr.GetString(11)  'nchar(5) Loop 

When a specific method is called, such as GetDecimal , the data returned must be of the correct type or an exception will result because no conversion is attempted. This implies that null values returned from the data store will cause exceptions. You can check for this using the IsDBNull method and passing in the ordinal of the column to check. In this case, only those columns that can accept null values in SQL Server need to be checked for nulls.

Notice that the SQL Server data types are shown in the comment at the end of each line. As mentioned on Day 8, "Understanding .NET Data Providers," the data types for the data store will map to types in the DbType enumeration, which in turn map to types in the Common Type System (CTS). Behind-the-scenes conversions will be done to convert from the SQL Server types to the appropriate DbType value.

Note

You can find definitions of each of the DbType enumeration values in the online documentation.

Data providers can also include their own set of types to provide the mappings, as is done for both the SqlClient and the OleDb providers through the SqlDbType and OleDbType enumerations. In addition, the SqlDataReader class exposes a set of methods that return data from a data reader using the types from the System.Data.SqlTypes namespace. As a result, you could rewrite Listing 11.2 as shown in Listing 11.3.

Listing 11.3 Using provider types. This listing shows the use of SqlClient-specific types.

 Dim isbn, title, desc, author, publisher As SqlTypes.SqlString Dim price, discount, bulkDiscount As SqlTypes.SqlMoney Dim pubDate As SqlTypes.SqlDateTime Dim bulkAmount As SqlTypes.SqlInt16 Dim catId As SqlTypes.SqlGuid dr = com.ExecuteReader   Do While dr.Read()         isbn = dr.GetSqlString(0)         title = dr.GetSqlString(1)         If Not dr.IsDBNull(2) Then desc = dr.GetSqlString(2)         author = dr.GetSqlString(3)         pubDate = dr.GetSqlDateTime(4)         price = dr.GetSqlMoney(5)         If Not dr.IsDBNull(6) Then discount = dr.GetSqlMoney(6)         If Not dr.IsDBNull(7) Then bulkDiscount = dr.GetSqlMoney(7)         If Not dr.IsDBNull(8) Then bulkAmount = dr.GetSqlInt16(8)         catId = dr.GetSqlGuid(10)         If Not dr.IsDBNull(11) Then publisher = dr.GetSqlString(11)  Loop 

Using these provider-specific types is both faster and prevents loss of precision. This is because the conversion that is done when calling the generic methods such as GetDecimal isn't performed, and the data returned from SQL Server can be mapped directly to the types in the SqlTypes namespace. In addition, the SqlClient types handle null values as you would expect for SQL Server, which makes coding with them simpler. As you might have guessed, the OleDb provider provides no other types because it's a generic provider.

To retrieve the data without strong-typing, you can use the GetValue method, which places the value in an Object that later can be cast to the proper type. Because the field values are also placed in a collection, they can be alternatively accessed using the Item collection either through the Item property (in VB only because Item is the indexer) or the shortcut syntax. In other words, the following three lines of code are identical in their results:

 o = dr.GetValue(1) o = dr.Item(1) o = dr(1) 

The advantage, however, of using either of the second two techniques is that the Item property is overloaded to accept either the ordinal or the name of the column.

As a result, if you're using the strongly typed methods rather than use the ordinal values, you can instead call the GetOrdinal method, which accepts the name of the column and returns the ordinal like so:

 Dim ordIsbn, ordTitle As Integer ordIsbn = dr.GetOrdinal(ISBN) ordTitle = dr.GetOrdinal(Title) isbn = dr.GetString(ordIsbn) title = dr.GetString(ordTitle) 

Obviously, the first two statements should be executed before the loop so as not to incur the overhead of looking up the ordinal value with each iteration. In addition, GetOrdinal first performs a case-sensitive search, so using the proper case for the column names will also increase performance.

Although it wasn't shown in the previous code snippets, you can also retrieve binary data from a data reader in several ways. If the amount of data is small, you can simply read the contents directly into an array of bytes as follows :

 Dim buff() As Byte ' Open the data reader and call Read buff = CType(dr.Item(4), Byte()) 

In this case, the cover column from the Titles table in the ComputeBooks database is retrieved as ordinal 4 and its data is placed directly into the Byte array. As in this example, this technique is good for data that you know will be of a small size , such as the cover image of a book. Of course, for providers that support it, you can also use the specific types; in this case, SqlBinary in conjunction with the GetSqlBinary method.

When the data grows larger, however, you might want to read the data in smaller chunks and buffer it in a MemoryStream object before working with it. This can be accomplished using the GetBytes or GetChars methods of the IDataRecord interface. These methods enable you to populate a Byte array using an offset read from the data reader. As implied by the names, GetBytes reads the data as individual bytes, whereas GetChars reads the data as characters . To illustrate the concept, consider the ReadBinaryData method in Listing 11.4.

Listing 11.4 Reading binary data. This method is used to read data from a column in a data reader that stores binary data.

 Private Function ReadBinaryData(ByRef dr As IDataRecord, _   ByVal ordinal As Integer) As MemoryStream   Dim len, offset As Long   Dim buff(4095) As Byte   ' Memory stream to hold the result   Dim memoryBuffer As New MemoryStream()   Do     ' Get the next 4096 bytes     len = dr.GetBytes(4, offset, buff, 0, 4096)     ' Write the data to the memory stream     memoryBuffer.Write(buff, 0, CInt(len))     ' Increment the offset     offset = offset + len   Loop Until len < 4096   ' Reset the position to the beginning   memoryBuffer.Position = 0   Return memoryBuffer End Function 

As you can see in Listing 11.4, the method is passed in a reference to an object, such as SqlDataReader or OleDbDataReader , that implements the IDataRecord interface along with the ordinal of the column that contains the binary data. Using the interface rather than the actual type is an example of polymorphism, which allows your code to be more flexible in working with any object that implements the IDataRecord interface. After instantiating the MemoryStream to hold the result, the GetBytes method is called in a loop to read each 4KB chunk of data and write it to the MemoryStream using the Write method. The offset is then incremented to make sure that data is read starting with the appropriate location the next time through the loop. After all the data is read, the Position property of the MemoryStream is used to place the pointer back to the beginning of the stream before returning the stream to the client.

The calling code would then look as follows:

 Dim memoryBuffer As New MemoryStream() ' Execute the data reader Do While dr.Read()     ' Read other columns     memoryBuffer = ReadBinaryData(CType(dr, IDataRecord), 4)     ' Process the memoryBuffer perhaps writing it to a file Loop 

As mentioned yesterday, the SequentialAccess command behavior can also be used to ensure that the data from the data reader is only accessed sequentially. In other words, you can read from the columns only in their ordinal sequence (although columns can be skipped ). In addition, you must read the data within a column in order as well. Violating either of these rules causes an InvalidOperationException to be generated that contains an error message detailing the violation. Using SequentialAccess allows the data reader to retrieve large binary columns on demand, as streams, rather than retrieving the entire column at once.

Retrieving Multiple Values

The second way to retrieve data from a data reader is to use the methods that return all the column values as an array. The IDataRecord interface supports only one such method, GetValues , which populates an array of type System.Object passed to it with the values of the columns. The method then returns the number of elements in the array. Because the returned values are in an array of type System.Object , the elements of the array can later be cast to the appropriate types in order to work with them in the application. For example, the code in Listing 11.5 could be used to retrieve all the customers from the ComputeBooks database who have valid addresses for creating a text file for use in an application.

Listing 11.5 Reading multiple values. This listing reads all of the items in each row of the data reader using the GetValues method.

 Dim values(6) As Object Dim fs As New FileStream("customers.txt", FileMode.OpenOrCreate) Dim outputFile As New StreamWriter(fs) con.Open() dr = ins.ExecuteReader(CommandBehavior.CloseConnection) Do While dr.Read()   dr.GetValues(values)   WriteCustomer(values, outputFile) Loop dr.Close() fs.Close() 

You'll notice in Listing 11.5 that the customers.txt file is opened or created and a StreamWriter from the System.IO namespace is used to write data to the file. After the connection and data reader are opened, the GetValues method retrieves the values on the current row and passes them along with the StreamWriter to the WriteCustomer method shown in Listing 11.6.

In actuality, the array passed to the GetValues method can either have fewer or more elements than the number of fields returned from the data reader. If fewer elements are in the array, the GetValues method will simply populate as many as it can. Likewise, if the array is larger, the extra elements won't be populated . Although this behavior is ultimately flexible, it can lead to problems if you don't make sure that you're getting all the values you need. In addition, passing an array that contains fewer elements than the number of fields returned from the data reader wastes server resources. You should always ask only for the data you need.

If you want to size the array correctly, you can first inspect the value of the FieldCount property of the data reader and then initialize the array accordingly , like so:

 Dim values(dr.FieldCount - 1) As Object 

Of course, this statement must follow the call to ExecuteReader . Alternatively, VB developers could use the ReDim statement after first declaring the array without the rank, as in

 Dim values() As Object ReDim values(dr.FieldCount - 1) 

C# developers, on the other hand, would simply do the following:

 Object[] values; values = new Object[dr.FieldCount]; 

Tip

In VB, the array declaration denotes the upper bound of the array, whereas in C#, the declaration contains the number of elements in the array. Because both are zero-based , in VB you need to subtract 1 from the FieldCount because the first element is 0.

Listing 11.6 Writing the array. This method accepts the values from the GetValues method and uses it to write data as a StreamWriter .

 Private Sub WriteCustomer(ByVal custValues() As Object, _   ByRef tw As StreamWriter)   Dim fName, lName, city, stateProv, postalCode As String   Dim name, location As String   ' Extract the data   fName = Trim(custValues(1).ToString)   lName = Trim(custValues(2).ToString)   city = Trim(custValues(3).ToString)   stateProv = Trim(custValues(5).ToString)   postalCode = Trim(custValues(6).ToString)   ' Concatenate   name = lName & ", " & fName   location = city & ", " & stateProv & " " & postalCode   ' Write Address   tw.WriteLine()   tw.WriteLine(name)   tw.WriteLine(custValues(4).ToString)   tw.WriteLine(location) End Sub 

In Listing 11.6, it happens that each of the elements of the custValues array is ultimately of type String , so the ToString method can be called to create strings for each of the data elements. One of the interesting side effects of this technique is that you don't need to check the IsDBNull method we discussed earlier. This is because the ToString method simply returns an empty string ("") rather than Nothing (null in C#). If the CType function were used to cast the data to a String , an exception would have been thrown because a String can't contain a null value. An empty string is also returned from the ToString method of the Convert class. Of course, if you use this technique, you need to be sure that you can live with empty strings in your data.

After the data has been retrieved, it's manipulated to create a crude address label and then written to the file using the WriteLine method of the StreamWriter object.

In addition to the GetValues method that all data readers will expose, data readers can also expose their own GetValues method to return the data in the types appropriate for the data store. SqlClient does this by exposing a GetSqlValues method that populates an array of type System.Object with data in the native SQL Server data types from the SqlTypes namespace.

Note

As you learned on Day 8, data readers don't support functionality like the GetRows method found in ADO 2.x because there is no reason for it. If you want to read all the data and cache it, you should simply use the DataSet instead.

for RuBoard