Using DataReaders to Increase Speed and Reduce Memory

Rick Dobson

When it comes to database connections, .NET proponents like to highlight the benefits of the disconnected access provided by Data Adapters and Data Sets. In the process, the DataReader sometimes gets swept under the rug. But, as Rick Dobson demonstrates here, DataReaders are different–they provide forward-only, read-only, connected access to data sources, and they don't support data manipulation. So why would you want to use something that limits you so much? Performance, for starters: DataReaders are much faster. Another benefit is a smaller memory footprint–DataReaders allow you to work with data as you get it, a row at a time. So DataReaders are particularly well-suited for working with data that's too large to fit in memory comfortably.

In order to gain the best advantage from DataReaders, you need to understand their capabilities and limitations. Because DataReaders have well-defined limits, you can also benefit from an understanding of how to tap other .NET entities, such as arrays, to complement DataReader features. This article reviews DataReader capabilities with a couple of sample applications in each of three areas. First, I present efficient code patterns for generating, populating, and configuring DataReaders for use with Windows Forms controls. The second pair of samples highlights how you can work with typed data to compute expressions, which reflect the nature of the data types for the columns in a DataReader. I close by contrasting two techniques for retrieving hierarchical data via DataReaders.

From DataReader to listbox

You can easily populate a listbox with a DataReader that's pointed at a data source. This section's samples generally apply to combo box controls as well. You begin by creating the DataReader and invoking the ExecuteReader method for a Command object, usually created in-line.

The ExecuteReader method can accept CommandBehavior enumerations to customize the Command's behavior and its associated DataReader. The two samples in this section highlight the interplay between DataReaders and their Command objects, and provide other interesting application details pertaining to form and control management. See the HCVSDataReaders project to access all of the code for each sample.

Displaying raw DataReader data

The first DataReader sample in the HCVSDataReaders project is in the Click event for Button1 on Form1 along, with two methods in the ADONETObjects class and the DataReaderForTable function procedure behind Form1. For convenience, the ADONETObjects class resides in the HCVSDataReaders project. Figure 1 shows the form after a click to the button with the label Populate from DataReader. The Click event procedure for the button populates the listbox with selected column values from the Employees table in the SQL Server Northwind database.

The SqlDataReader class has many special methods for getting data in all kinds of specialized .NET and SQL Server data formats. However, for simple applications, you don't have to worry about them. All the DataReader needs to do is accept the default conversion from any non-string data type to a string and then add a computed string expression to the listbox. That's what the following code does. It's from the Button1_Click procedure. A While loop reads row after row, each time creating a str2 expression that includes four references to the drd1 DataReader. Two of these references are for numeric instances. And it's even possible that a value may be null (as is the case with the ReportsTo column value for employee number 2). Nevertheless, the expression succeeds for every row. You can designate columns either by name or by a zero-based index.

  Do While drd1.Read
   Dim str2 As String = _
    "Employee " & drd1("EmployeeID") & _
    ", " & drd1("FirstName") & _
    " " & drd1("LastName") & _
    " reports to: " & drd1("ReportsTo")
   ListBox1.Items.Add(str2)
  Loop

Perhaps the most interesting element of the first sample is how it creates the drd1 DataReader in the first place. Button1's Click event procedure creates drd1 as a SqlDataReader class and assigns it the return value from a function I created called DataReaderForTable. It passes the name of the table, Employees, for which DataReaderForTable develops a DataReader.

  Dim drd1 As SqlClient.SqlDataReader = _
   DataReaderForTable("Employees")

The DataReaderForTable procedure creates a DataReader in three steps.

  Dim drd1 As SqlClient.SqlDataReader
  Dim ADOObjs As New ADONETObjects

  'Specify connection object
  Dim cnn1 As SqlClient.SqlConnection = _
     ADOObjs.MakeNorthwindConnection

  'Specify a command object
  Dim str1 As String = _
   "SELECT * FROM " & TableName
  Dim cmd1 As _
   SqlClient.SqlCommand = _
    ADOObjs.MakeACommand(cnn1, str1)

  'Open cnn1 and create the drd1 DataReader
  'with the ExecuteReader method  cnn1.Open()
  drd1 = cmd1.ExecuteReader _
   (CommandBehavior.CloseConnection)

  Return drd1

First, it creates a connection to the Northwind database with the MakeNorthwindConnection method from my ADONETObjects class. Next, I create a Command object for the DataReader. The DataReaderForTable procedure passes two arguments to my ADONETObjects' MakeACommand method to return a new Command object. The arguments are for the SQL statement that extracts all columns for all rows in the TableName argument passed to the DataReaderForTable procedure and the Connection object returned by the MakeNorthwindConnection method.

In the third step, the procedure actually creates the DataReader with the ExcecuteReader method for the Command object. The use of the CommandBehavior.CloseConnection enumeration makes it possible for the Button1_Click procedure to close a DataReader returned to it without also needing to manipulate the associated Connection object. This is because the enumeration instructs the .NET Framework to automatically close the Connection object when the DataReader closes. The DataReaderForTable procedure concludes by returning the instantiated DataReader.

By the way, the DataReaderForTable procedure has a Shared access mode declaration so that procedures in other modules throughout the HCVSDataReaders project can call it.

Processing DataReader data

There are at least a couple of areas in which ListBox1's contents can improve. First, there's no EmployeeID value to denote to whom Andrew Fuller reports. This isn't an error, because he reports to no one else in the listbox. Still, it's potentially confusing to have a blank. Second, ListBox1 designates an employee's manager by the manager's EmployeeID. The readability of the ListBox1 contents can improve by replacing the manager's EmployeeID with the manager's last name.

The Button2_Click procedure populates ListBox1 in a way that handles both of the problems with the Button1_Click procedure. Figure 2 shows the improved output after a click to the button labeled Populate from array. The line for the employee named Andrew Fuller indicates he has no supervisors in the list. The entries for all other employees in ListBox1 show the supervisor's last name, instead of EmployeeID.

One of the main challenges in transforming the supervisor EmployeeID column values to last names is that the DataReader only knows about one row for an employee at a time. In order to transform the supervisor EmployeeID column value, an application needs to link each EmployeeID value to a last name. By storing values from the DataReader in an array of strings, procedures can look up the last name that matches an EmployeeID value. (Of course, this particular problem could also be solved by creating a more complex Select statement in the query, but for the purposes of demonstrating the use of arrays with DataReaders, I'll show you how to solve this problem on the client side.)

The following code segment from the Button2_Click procedure shows how to populate the MyEmps array of string values with values from the drd1 DataReader, which was defined the same way as in Button1_Click.

  Const RowsCount As Integer = 99
  Dim MyEmps(RowsCount, 3) As String

  Do While drd1.Read
   If int1 <= RowsCount Then
    For int2 = 0 To drd1.FieldCount() - 1
     Select Case drd1.GetName(int2)
      Case "EmployeeID"
       MyEmps(int1, 0) = drd1(int2)
      Case "FirstName"
       MyEmps(int1, 1) = drd1(int2)
      Case "LastName"
       MyEmps(int1, 2) = drd1(int2)
      Case "ReportsTo"
       'ToString method forces conversion --
       'even for DBNull value to string
       MyEmps(int1, 3) = drd1(int2).ToString
     End Select
    Next
    int1 += 1
   Else
    MessageBox.Show( _
     "Reset RowsCount to a larger number and re-run.", _
     "Terminal Error Message", _
     MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
    Exit Sub
   End If
  Loop

The MyEmps array has four columns for EmployeeID, FirstName, LastName, and ReportsTo column values. Its maximum row specification is more than sufficient for the default number of nine rows in the Northwind database. The While loop for reading rows has code in it to perform different kinds of tasks.

A For loop iterates through all of the column values to select a subset of DataReader column values for storage in MyEmps. The FieldCount property returns the number of columns in the DataReader. A Select...End Select statement examines column names in the DataReader with the GetName method to identify into which MyEmps column to store column values.

With the exception of the ReportsTo column values, the code applies the default Visual Basic .NET conversion from a SQL Server data format to a .NET string format in the MyEmps array. Because the ReportsTo column could include a null value (DBNULL), the procedure must explicitly specify the ToString method for forcing the DBNULL to a string value–namely, an empty string ("").

After collecting all of the drd1 column values in the MyEmps array, Button2_Click closes the DataReader and frees those resources. The next major code segment, which appears below, loops through the rows of MyEmps to compute string expressions for display in ListBox1. Instead of just showing the raw ReportsTo column value for a row, the code loops a second time through the MyEmps array to find a LastName column value that matches the ReportsTo column value. Before entering the loop to decode a ReportsTo column value to a LastName column value, the code ascertains whether the ReportsTo value in the fourth column is an empty string.

  For int1 = 0 To 99
   If MyEmps(int1, 0) <> "" Then
    If MyEmps(int1, 3) <> "" Then
     strSupvrEmpID = MyEmps(int1, 3)
     For int2 = MyEmps.GetLowerBound(0) To _
      MyEmps.GetUpperBound(0)
      If MyEmps(int2, 0) = strSupvrEmpID Then
       strEmpID = MyEmps(int2, 2)
       Exit For
      End If
     Next
    Else
     strEmpID = " no one in list box"
    End If
    str1 = "EmployeeID" & MyEmps(int1, 0) & _
     ", " & MyEmps(int1, 1) & " " & _
     MyEmps(int1, 2) & " reports to: " & _
     strEmpID
    ListBox1.Items.Add(str1)
   Else
    Exit For
   End If
  Next

Processing typed data

The application for Form1 converted the contents of each DataReader column to a string value–no matter what the underlying data type for a column in a data source. There are times when you need to work with original data types, such as when you need to perform numerical or date arithmetic with column values. If you don't already know the underlying data types, you'll need a technique to discover original data types before using them in expressions.

Reporting column names and data types

The Button1_Click procedure in Form2 demonstrates a technique for writing out the column names and data types of any DataReader. While .NET offers other approaches to this task, this technique builds on your understanding of DataReaders and how to use them with arrays. The procedure starts by creating a DataReader for the Orders table in the Northwind database based on the DataReaderForTable procedure in Form1. Since the DataReaderForTable procedure was declared with a Shared access mode, Form2 can invoke with the following:

  Dim drd1 As SqlClient.SqlDataReader = _
   Form1.DataReaderForTable("Orders")

You also need an array to hold the column names and data types for the drd1 DataReader. The array columns will hold the string values designating the names of columns and their data types for the drd1 DataReader. The following code excerpt shows how to apply the CreateInstance Shared method for the Array class to create an array named OrdersColNamesTypes. This array has as many rows as there are columns in the drd1 DataReader and two columns. A For loop iterates through the columns of the DataReader to populate the array with column name and data type metadata. The SetValue method assigns values to array elements. You learned from the preceding sample how to use the GetName method to return column names. This procedure illustrates how to apply the GetDataTypeName method to recover the native data type name for a column in a DataReader.

  Dim OrdersColNamesTypes As Array = _
   Array.CreateInstance(GetType(String), _
   drd1.FieldCount, 2)

  For int1 As Integer = 0 To drd1.FieldCount - 1
   OrdersColNamesTypes.SetValue _
    (drd1.GetName(int1), int1, 0)
   OrdersColNamesTypes.SetValue _
    (drd1.GetDataTypeName(int1), int1, 1)
  Next

The final code segment for the Button1_Click procedure on Form2 merely loops through the column values for each successive row in the array OrdersColNamesTypes and prints to the Output window the column names and data types, as you can see in Figure 3. The report in the figure indicates that the Order table has 14 columns. The Order table's first column has a name of OrderID and a SQL Server int data type. Other columns have variable and fixed length string data types (nvarchar and nchar) as well as datetime and money data types.

Performing arithmetic

The trick to performing arithmetic with DataReader column values is to save them in a Visual Basic .NET data type that matches their native database data type. However, arrays force all element members to the same type. One way to use an array to store values from a DataReader, but still maintain the data source's data types, is to save the DataReader column values to an array with Object data type elements. This process essentially boxes the DataReader column values as Object instances that don't coerce them to another data type. You can later recover the basic underlying data format by assigning the array elements to a variable declared with an appropriate data type. This assignment essentially unboxes the data type.

The code behind Form2 includes a procedure, PopArray, that boxes DataReader column values in an array with Object elements. View the listing for PopArray in the HCVSDataReaders project if you're interested in the details of this process.

One main purpose of the PopArray procedure in this article is to enable the demonstration of integer and datetime arithmetic with column values from the Orders table in a Windows application. Form2's Button2_Click procedure has two main code segments. The first demonstrates how to compute the difference between the OrderID column values for the first and last rows in the Orders array, which mirrors the Orders table in the Northwind database. Before starting the first main segment, the procedure invokes the PopArray procedure to populate the Orders array. In case you're wondering, the Orders table has 830 rows. Two assignments for variables named int1 and int2 unbox the OrderID column value for the first and last row in the first column of the Orders array. The arguments for the WriteLine method include a simple expression that subtracts one Integer variable from another.

  Dim Orders As Array = PopArray("Orders", 830)

  Dim int1 As Integer = _
   Orders(Orders.GetLowerBound(0), _
   Orders.GetLowerBound(1))
  Dim int2 As Integer = _
   Orders(Orders.GetUpperBound(0), _
   Orders.GetLowerBound(1))
  Console.WriteLine(ControlChars.CrLf & _
   "An example with integer arithmetic:")
  Console.WriteLine( _
   "There are {2} order numbers between " & _
   "the first order number({0}) and the " & _
   "last order number({1})", _
   int1, int2, int2 - int1)

Button2_Click's second main code segment performs datetime arithmetic with the ShippedDate and RequiredDate column values in the first row of the Orders array. Instead of unboxing the Object elements to variables with Integer data types, this code unboxes the two columns to Date data types. You can use Date and Datetime keywords interchangeably to designate datetime values in Visual Basic .NET. A DateDiff function computes the difference in days between the two datetime variables. The WriteLine method for the Console class displays the result in the Output window.

  'Demonstrate arithmetic with dates
  Dim datRequired As Date = Orders(0, 4)
  Dim datShipped As Date = Orders(0, 5)
  Console.WriteLine(ControlChars.CrLf & _
   "An example with date arithmetic")
  Console.WriteLine( _
   "Required date({1}) - ShippedDate({0}) " & _
   "= {2} days", _
   datShipped.ToString("M/d/yyyy"), _
   datRequired.ToString("M/d/yyyy"), _
   DateDiff(DateInterval.Day, datShipped, _
   datRequired))

Generating hierarchical data

It's very common for applications to require hierarchical data, such as the line items that belong to an order. My last two samples present two tactics for returning hierarchical data via a DataReader. One approach demonstrates the use of the specialized MSDataShape provider. The second approach uses more general tools based on those presented earlier in this article. In addition, the second technique builds on the first one by adding a lookup feature for values in a related table and illustrating formatting syntax for datetime and currency values.

Using the MSDataShape provider

As I indicated earlier, the MSDataShape provider is a specialized provider for returning hierarchical data. This provider dates back to Visual Basic 6, but Microsoft issued a Knowledge Base article describing how to use the MSDataShape provider with Visual Basic .NET and ADO.NET (http://support.microsoft.com/default.aspx?scid=kb;[LN];308045). While the MSDataShape provider is exceptionally efficient at returning hierarchical result sets, the provider relies on a subset of SQL along with specialized keywords and other syntax conventions. In addition, this provider doesn't work with the .NET SQL Server data provider. Instead, you're forced to use the OleDb .NET data provider–even when working with a SQL Server database.

Making a connection to the database using the MSDataShape provider is a little different. The following code is from the Button1_Click procedure in Form3. Notice that the code designates a Connection object in the OleDb namespace. While the last three arguments for server, integrated security, and initial catalog are shared in common with those for a SqlConnection object connection string, the initial two arguments are distinct. The very first argument designates the MSDataShape provider, which works in concert with the SQLOLEDB data provider that's specified in the second argument.

   New OleDb.OleDbConnection( _
   "Provider=MSDataShape;Data Provider=SQLOLEDB;" & _
   "server=(local);Integrated Security=SSPI;" & _
   "Initial Catalog=northwind")

The next three code blocks illustrate the syntax for specifying a Command object that generates a hierarchical result set based on the Orders table and the Order Details table from the Northwind database.

  Dim cmd1 As OleDb.OleDbCommand = _
   New OleDb.OleDbCommand( _
   "SHAPE {SELECT OrderID, OrderDate " & _
   "FROM Orders " & _
   "WHERE OrderID=" & TextBox1.Text & "} " & _
   "  APPEND ({SELECT OrderID, ProductID, " & _
   "UnitPrice, Quantity, Discount " & _
   "FROM [Order Details]} " & _
   "  RELATE OrderID TO OrderID)", cnn1)

  cnn1.Open()
  Dim drd1 As OleDb.OleDbDataReader = _
   cmd1.ExecuteReader(CommandBehavior.CloseConnection)
  drd1.Read()
  Console.WriteLine("{0}, {1}", _
   drd1(0), drd1(1))

  Dim drd2 As OleDb.OleDbDataReader = drd1(2)
  Do While drd2.Read
   Console.WriteLine("{0}, {1}, {2}, {3}, {4}", _
    drd2(0), drd2(1), drd2(2), drd2(3), drd2(4))
  Loop

Notice the specialized keywords of SHAPE, APPEND, and RELATE. The SQL statement for the SHAPE clause designates the row for the master result set. This statement references TextBox1's Text property, which should always specify a valid OrderID column value. The SQL statement for the APPEND clause designates the result set for the detail member of the hierarchical result set. The RELATE clause indicates the columns on which to match rows in the master and detail data sources.

After instantiating a Command object, the code prepares to generate a couple of DataReaders by opening the cnn1 Connection object. The drd1 DataReader returns data from the master data source, and the drd2 DataReader extracts data from the detail data source. The Console.WriteLine statement for the master data source prints the master data source's first two column values, which are OrderID and OrderDate. The Console.WriteLine statement for the detail data source prints all the rows from the Order Details table with an OrderID matching the value that shows in TextBox1.

Figure 4 shows Form3 immediately after a click to the button labeled Shape. The Output window below the form presents the hierarchical result set. The first line shows the row from the master data source, including the OrderID and OrderDate column values. The next three rows show the detail line items for the order with an OrderID value of 10248. The second and third columns are for ProductID and UnitPrice column values. Printing the ProductID column value instead of the ProductName column value makes it more difficult to discern the product to which each line item refers. In addition, it's not transparent from the output that UnitPrice column values are currency values.

Return hierarchical result sets with general tools

The second sample for returning hierarchical data relies on general tools, such as adaptations of those already presented in this article. The detailed code for the second sample appears in the Button2_Click procedure and a related procedure named ComputerArrayIndex in the module for Form3 within the HCVSDataReaders project. This second approach to returning hierarchical data creates three arrays based on associated DataReaders for the Orders, Order Details, and Products tables in the Northwind database. Using arrays in this manner can reduce the load on a database server since it allows an application to close the DataReader and its associated Connection to a data source.

The following code segment from Button2_Click in the Form3 module illustrates the approach used to generate the Orders array. The PopArray procedure within Form2 was briefly described earlier. It generates an array based on a DataReader for the Northwind database. You pass it a maximum number of rows to read as well as a table name. By the way, the PopArray procedure does close its DataReader after populating the array. The ComputeArrayIndex procedure generates a one-dimensional array, IdxOrders, from the first column of a two-dimensional array, such as Orders.

   Dim intMaxOrdersRows = 830
   Orders = Form2.PopArray("Orders", _
    intMaxOrdersRows)
   IdxOrders = ComputeArrayIndex(Orders, _
    intMaxOrdersRows)

The one-dimensional index array can speed the lookup of rows in a two-dimensional array faster than scanning all the rows in the two-dimensional array for a value matching a criterion. This is because Visual Basic .NET offers an IndexOf Shared method for its Array class that returns the index corresponding to a value in a one-dimensional array. The following code sample shows the syntax for using this method with the IdxOrders array to recover OrderID and OrderDate column values from the Orders array. The code segment also formats the OrderDate column value to exclude the irrelevant time portion of the datetime value.

  Dim intIdx As Integer = _
   Array.IndexOf(IdxOrders, _
   Integer.Parse(TextBox1.Text))
  Console.WriteLine("{0}, {1}", _
   Orders(intIdx, 0), _
   DateTime.Parse( _
   Orders(intIdx, 3)).ToString("M/dd/yy"))

Figure 5 shows the final output from the Button2_Click procedure for the OrderID value appearing in Figure 4. Notice that the procedure performs a lookup for the ProductID values and displays ProductName column values in their stead. The lookup logic to recover ProductName column values based on ProductID column values is an extension of the code for looking up LastName column values based on ReportsTo column values from the second sample in this article as well as the preceding code segment. The approach to formatting the UnitPrice as a currency value simply invokes the familiar FormatCurrency function. While you can use more robust means for formatting currency values, it's nice to know that Visual Basic .NET supports the familiar and easy-to-use FormatCurrency function.

Conclusions

DataReaders are fast, flexible, and powerful tools for data access to remote data sources. This article highlights three specific kinds of applications for DataReaders in .NET applications, but there are many others. Exploiting DataReaders in your custom solutions will make them go faster and even offer you the chance to reinforce your basic .NET development skills. You'll often be able to derive additional value from DataReaders by using them in concert with arrays.

Download 407DOBSON.ZIP

To find out more about Hardcore Visual Studio and Pinnacle Publishing, visit their Web site at http://www.pinpub.com/

Note: This is not a Microsoft Corporation Web site. Microsoft is not responsible for its content.

This article is reproduced from the July 2004 issue of Hardcore Visual Studio. Copyright 2004, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Hardcore Visual Studio is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-788-1900.