Advanced Features

for RuBoard

In addition to simply retrieving data using a data reader, you can also use it to view schema information, pass result sets between tiers in a multi- tier application, and read multiple result sets generated by the command object.

Retrieving the Schema

In addition to the actual data from the result set, a data reader can also be used to retrieve the schema for the data using the GetSchemaTable method. This method is exposed by the IDataReader interface and is implemented by both the SqlClient and OleDb data readers. You can call this method anytime after the ExecuteReader method has been invoked, even after its data has been read using the Read method. You might use this method if you want to dynamically create SQL DDL statements or XSD documents based on the data from the data reader.

Tip

Although you could use GetSchemaTable to do things such as create an XSD document, it would be far simpler to use the technique we discussed on Day 7, "XML and the DataSet." In that technique, you can load the schema into a DataSet using the FillSchema method of a data adapter and then extract the schema using the WriteXmlSchema or GetXmlSchema methods .

The GetSchemaTable method returns a DataTable populated with provider-specific column information. For example, the SqlClient provider creates a table with 22 columns that provide everything from the name of the column to the CTS data type it maps to. The OleDb provider returns a table with 18 columns that contain data from the GetColumnsRowset method of the OLE DB IColumnsRowset interface.

Note

You can find the columns for both providers and their definitions in the online documentation under the GetSchemaTable method for each provider.

Which of the columns are populated depends on the behaviors passed to the ExecuteDataReader method. Typically, the minimum amount of information is returned, including the column names , their data types, and sizes. However, you can usually retrieve additional information by using the KeyInfo value of the CommandBehavior enumeration. This enables you to determine which columns are unique and the names of the underlying tables in the data store. When you couple this behavior with SchemaOnly only, you can retrieve only the metadata for the data reader, like so:

 dr = ins.ExecuteReader(CommandBehavior.KeyInfo Or _  CommandBehavior.SchemaOnly or CommandBehavior.CloseConnection) Dim dt As New DataTable() dt = dr.GetSchemaTable() dr.Close() 

Using a Data Reader Polymorphically

As we briefly touched on yesterday , the CloseConnection behavior passed to the ExecuteReader method enables you to return a data reader from a method and still ensure that its connection is closed or returned to the pool promptly when the data reader is closed. This idea, coupled with the fact that all data readers implement the IDataReader interface, enables you to take advantage of polymorphism to write code that works with any provider. For example, the WriteXml method shown in Listing 11.7 writes the data from the passed-in data reader to an XML file using the XmlTextWriter class from the System.Xml namespace.

Listing 11.7 Using polymorphism. This method can be used to write the data in any data reader to a simple XML file because all data readers inherit from the IDataReader interface.

 Private Sub WriteXml(ByVal dr As IDataReader, _   ByVal fileName As String, ByVal root As String)   ' Check the arguments   If dr Is Nothing OrElse dr.IsClosed Then Return   Dim xtr As New XmlTextWriter(fileName, System.Text.Encoding.Default)   Dim i As Integer   Dim fields As Integer = dr.FieldCount   xtr.WriteStartDocument()   xtr.WriteComment("Produced " & Now)   xtr.WriteStartElement(root)   Do While dr.Read()     xtr.WriteStartElement("row")     For i = 0 To dr.FieldCount - 1         xtr.WriteElementString(dr.GetName(i), Trim(dr.Item(i).ToString))     Next     xtr.WriteEndElement()   Loop   xtr.WriteEndElement()   ' Close the data reader and connection   dr.Close()   xtr.Close() End Sub 

You'll notice that in Listing 11.7, all the properties and methods of the IDataReader interface can be called, such as FieldCount , Read , GetName , and Close . As a result, the calling code can pass in a SqlDataReader , OleDbDataReader , or other class that implements the IDataReader interface.

Of course, this technique can't be used when you need to call methods that are exposed only by a specific data reader, such as the GetSqlValues method of the SqlDataReader class.

Returning Multiple Result Sets

As you learned on Day 8, one of the interesting things you can do with a data reader is read multiple result sets. Typically, multiple result sets are generated by creating commands that include multiple SELECT statements in a batch. When the first result set is exhausted, the NextResult method can be called and will return True and position the data reader at the next result set if one exists. The next result set can then be traversed using the Read method. Returning multiple result sets in this way enables you to create stored procedures that encapsulate multiple SELECT statements and therefore centralize the logic on both the client and the server.

As an example, consider the generic method shown in Listing 11.8 that reads multiple result sets from a stored procedure and populates ArrayList objects with the results. The array lists can then be bound to controls such as the ComboBox on a Windows Form.

Listing 11.8 Retrieving multiple result sets. This method extracts all the result sets from a data reader and places them in an ArrayList of ArrayList objects that contain the structure LookupData .

 Private Function ExtractLookups(ByVal dr As IDataReader) As Array   ' Make sure the data reader is not closed   If dr Is Nothing OrElse dr.IsClosed Then Return Nothing   Dim results As New ArrayList()   Dim moreResults As Boolean = True   Try     ' Keep looping while there are more results     Do While moreResults       Dim res As New ArrayList()       results.Add(res)       Do While dr.Read         res.Add(New LookupData(dr.Item(0), dr.Item(1)))       Loop       ' Check to see if there are more results       moreResults = dr.NextResult()     Loop     Return results.ToArray()   Catch e As Exception     Throw e   Finally     dr.Close()   End Try End Function Private Structure LookupData   Public key As Object   Public value As Object   Sub New(ByVal newKey As Object, ByVal newValue As Object)     key = newKey     value = newValue   End Sub End Structure 

As you can see in Listing 11.8, the ExtractLookups method accepts a data reader using the IDataReader interface and returns an Array object. In the Try block, the method loops while there are more result sets to read. Each time through the loop, the method creates a new ArrayList object called res and adds it to the results ArrayList used to temporarily hold the results until the method returns. Each row from the data reader is then read into a structure called LookupData that simply exposes key and value fields. The structure is then placed into the inner ArrayList .

Note

This method assumes that each result set contains at least two columns, the first being the primary key value and the second a description that would be displayed to the user .

After all the data has been read, the NextResult method is called to determine whether another result set is present. If so, the loop will be executed again and a new ArrayList will be added the results ArrayList . After all the results have been extracted, the results ArrayList is converted into an Array of type System.Object using the ToArray method.

Tip

An alternative and simpler technique is simply to read both result sets into the tables of a DataSet and then bind them to the controls.

A client could then call the ExtractLookups method like so:

 Dim results As Array results = ExtractLookups(bus.GetLookups()) results(0) = cbPublishers.DataSource cbPublishers.ValueMember = "Key" cbPublishers.DisplayMember = "Value" results(1) = cbCategories.DataSource cbCategories.ValueMember = "Key" cbCategories.DisplayMember = "Value" 

In this case, the bus variable represents a business object whose GetLookups method executes a stored procedure. This stored procedure returns a SqlDataReader containing data from the Publishers and Categories tables in the ComputeBooks database. The data reader is passed to the ExtractLookups method, which returns an array in the results variable. Because the array contains ArrayList objects, they can be used to populate the DataSource property of the cbPublishers and cbCategories ComboBox controls ( System.Windows.Forms.ComboBox ). The fields from the LookupData structure can then be used to populate the ValueMember and DisplayMember properties of the control so that the proper data will be displayed. This works because controls such as ComboBox that derive from System.Windows.Forms.ListControl can bind to any class that implements the IList interface like an ArrayList .

Note

For this code to work efficiently , the GetLookups method would need to use the CloseConnection command behavior when opening the data reader.

Although the controls in the System.Windows.Forms namespace can't bind directly to a data reader, the controls in the System.Web.UI.WebControls , such as the DataGrid , DataList , DropDownList , and CheckBoxList , can because they can bind to any object that supports the IEnumerable interface, which the SqlDataReader and OleDbDataReader do. This makes for a highly efficient way to read static data and bind it to controls on a Web Form.

In addition to returning multiple result sets serially , the OleDb provider can be used to return hierarchical result sets using the Microsoft Data Shape (MSDataShape) OLE DB provider. Although largely superceded by the DataSet , the basic idea is that a nested result set (or chapter, as it's called in OLE DB) is accessible through a column in the data reader. The column can then be cast to an OleDbDataReader object and traversed. A simple example adapted from the online documentation is shown in Listing 11.9.

Listing 11.9 Using the MSDataShape provider. This code uses the data shape provider to read through the Orders and OrderDetails tables in the ComputeBooks database.

 Dim con As OleDbConnection = New OleDbConnection( _  "Provider=MSDataShape;Data Provider=SQLOLEDB;" & _  "Data Source=localhost;Integrated Security=SSPI;Initial Catalog=ComputeBooks") Dim com As OleDbCommand = New OleDbCommand( _  "SHAPE {SELECT OrderID, OrderDate FROM Orders}  " & _  "  APPEND ({SELECT OrderID, ISBN, Quantity FROM OrderDetails}  AS Details " & _  "  RELATE OrderId TO OrderId)", con) Dim orders, details As OleDbDataReader con.Open() orders = com.ExecuteReader() Do While orders.Read()   ' Read the order data   details = CType(orders.GetValue(2), OleDbDataReader)   Do While details.Read()     ' Read the details rows   Loop   details.Close() Loop orders.Close() con.Close() 

In Listing 11.9, the OleDbConnection and OleDbCommand objects are instantiated and populated with the appropriate ConnectionString and CommandText arguments in their constructors. Although the syntax of the MSDataShape provider is beyond the scope of this book, it should be noted that rather than using inline SELECT statements to retrieve the Orders and OrderDetails tables, you can, with SQL Server, call stored procedures in their place. After the connection and data reader are open, the Orders result set can be traversed as normal. However, the third column (ordinal 2) will be appended to the result set and consist of a chapter that contains the related OrderDetails rows. This column is already of type OleDbDataReader , so you can simply cast it to the appropriate type using the CType function. After the inner results are exhausted, the details data reader is closed. Finally, the orders data reader is closed along with the connection object.

Note

You shouldn't use the CloseConnection command behavior when opening the data reader. Doing so causes the connection to be closed when the details data reader is closed within the outer loop.

As you can imagine, while in their respective loops, the Depth property of the orders data reader will return 0, whereas the Depth property of the details data reader will return 1.

Although using the data shape provider might come in handy if you have existing code that uses it, keep in mind that you get the same result by filling a DataSet with tables that contain the Orders and OrderDetails rows and then creating a relationship between the tables, as you learned during the first week.

Data Modification Statements

Although I'm reluctant to mention it, the command used to call the ExecuteReader method on can also execute other statements that don't return results. For example, the CommandText property for a SqlCommand object might look as follows :

 SELECT PubCode, Name FROM Publishers;UPDATE PubStats SET Access = Access + 1 

Of course, alternatively, and more appropriately, a stored procedure would be used to encapsulate both these statements. In this case, the database would not only retrieve the Publishers but would update the PubStats table as well. Although mixing SELECT s and other statements isn't recommended, the UPDATE statement will execute on the data store as soon as the ExecuteReader method is called and before the Read method is called, even though it appears after the SELECT statement. After the data reader is closed, its RecordsAffected property will be set to the number of rows inserted, updated, or deleted by the UPDATE statement. In the event that more than one data modification statement is included in the batch or stored procedure, the sum of all the inserted, updated, and deleted rows will be placed in the RecordsAffected property.

Note

If the command simply returns rows, the RecordsAffected property will be set to “1.

One note of caution and a reason you should avoid mixing data retrieval and modification statements is that your ability to determine whether the data modification statements failed is dependent on the order of the statements in the stored procedure or batch. For example, if the statements shown earlier were executed and the UPDATE statement caused an error because the PubStats table didn't exist, the data reader would appear to execute normally and you could read its values with the Read method. However, the RecordsAffected property would be set to “1. Also, depending on the provider, you might not be able to even see the error message if it's not returned in the InfoMessage event of the connection (as it's not when using the SqlClient provider). If the statements were reversed , however, a SqlException would be thrown before the SELECT statement was executed and so you could catch it with a Try Catch block. The situation is further complicated if you have more than one data modification statement, in which case everything up to the statement that failed would execute and everything after it would not.

Because of the inherent indeterminacy in mixing retrieval and modification statements, you should stay away from such designs in your applications and segregate the statements that retrieve data from those that modify data in separate stored procedures.

Note

In future versions of the SqlClient providers, look for InfoMessage events to be fired for each data modification statement.

for RuBoard