Converting a DataReader to a DataSet

Problem

You need to transfer data from a DataReader to a DataSet .

Solution

Create a DataTable schema in the destination DataSet using the schema information returned by the GetSchemaTable( ) method of the DataReader . Then, use the GetData( ) method of the DataReader to load each row of data into an array of objects, and add it to the DataTable using the Add( ) method of the contained DataRowCollection .

The C# code is shown in Example 5-3.

Example 5-3. File: ConvertDataReaderToDataSetForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

// Table name constants
private const String ORDERS_TABLE = "Orders";
private const String ORDERDETAILS_TABLE = "OrderDetails";

// Relation name constants
private const String ORDERS_ORDERDETAILS_RELATION =
 "Orders_OrderDetails_Relation";

// Field name constants
private const String ORDERID_FIELD = "OrderID";

// . . . 

DataSet ds = new DataSet( );

// SQL for batch query
String sqlText = "SELECT * FROM Orders; " +
 "SELECT * FROM [Order Details];";

// Create connection and command.
SqlConnection conn = new SqlConnection(
 ConfigurationSettings.AppSettings["Sql_ConnectString"]);
SqlCommand cmd = new SqlCommand(sqlText, conn);

// Open DataReader with KeyInfo.
conn.Open( );
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.KeyInfo);

// Loop over the result sets of the DataReader.
do
{
 DataTable schemaTable = dr.GetSchemaTable( );
 if (schemaTable != null)
 {
 ArrayList pkCols = new ArrayList( );

 DataTable dataTable = new DataTable( );
 foreach(DataRow schemaRow in schemaTable.Rows)
 {
 DataColumn col = new DataColumn( );
 col.ColumnName = schemaRow["ColumnName"].ToString( );
 col.DataType = (Type)schemaRow["DataType"];
 // set the length of the field for string types only
 if (schemaRow["DataType"].ToString( ) == "System.String")
 col.MaxLength = (Int32)schemaRow["ColumnSize"];
 col.Unique = (bool)schemaRow["IsUnique"];
 col.AllowDBNull = (bool)schemaRow["AllowDBNull"];
 col.AutoIncrement = (bool)schemaRow["IsAutoIncrement"];
 // If part of the key, add the column name to the
 // array of columns comprising the primary key.
 if ((bool)schemaRow["IsKey"])
 pkCols.Add(col);

 dataTable.Columns.Add(col);
 }

 // Add the primary key to the table.
 dataTable.PrimaryKey =
 (DataColumn[])pkCols.ToArray(typeof(DataColumn));
 // Add the table to the DataSet.
 ds.Tables.Add(dataTable);

 object[] aData = new object[dataTable.Columns.Count];
 // Read all rows from the DataReader.
 while(dr.Read( ))
 {
 // Read the row from the DataReader into an array.
 dr.GetValues(aData);
 // Add the row from the array to the DataTable.
 dataTable.Rows.Add(aData);
 }
 }
}
while (dr.NextResult( ));

conn.Close( );

// Name the tables added to the DataSet.
ds.Tables[0].TableName = ORDERS_TABLE;
ds.Tables[1].TableName = ORDERDETAILS_TABLE;

// Manually add a relation between the tables.
ds.Relations.Add(ORDERS_ORDERDETAILS_RELATION,
 ds.Tables[ORDERS_TABLE].Columns[ORDERID_FIELD],
 ds.Tables[ORDERDETAILS_TABLE].Columns[ORDERID_FIELD],
 true);

// Bind the Order table default view to the grid.
dataGrid.DataSource = ds.Tables[ORDERS_TABLE].DefaultView;

Discussion

While the DbDataAdapter classfrom which DataAdapter classes in .NET providers for relational databases inheritdefines an overload of the Fill( ) method that converts a DataReader to a DataSet , this method is declared protected and cannot be accessed (unless you write a custom DataAdapter ). There is no built-in mechanism for converting the connected DataReader object to a disconnected DataTable . Internally, ADO.NET uses the DataReader through the DataAdapter to populate the DataTable object. This sample demonstrates how this can be done programmatically.

Both the SQL Server and OLE DB DataReader expose a GetSchemaTable( ) method that returns a table containing the column metadata of the DataReader . You can use this table in turn to define the DataTable object, into which the DataReader data will be copied .

The GetSchemaTable( ) method returns the metadata described in Table 5-1 for each column.

Table 5-1. DataReader GetSchemaTable( ) metadata

Column name

Description

ColumnName

The name of the column.

ColumnOrdinal

The zero-based ordinal of the column.

ColumnSize

The maximum length of a column value. This is the data size of fixed-length data types.

NumericPrecision

The maximum precision of numeric data type columns. Null for non-numeric data type columns.

NumericScale

The number of digits to the right of the decimal for DBTYPE_DECIMAL or DBTYPE_NUMERIC data type columns. Otherwise, null.

IsUnique

Indicates whether the value in the column must be unique within all records.

IsKey

Indicates whether the column is part of the primary key or a unique key uniquely identifying the record.

BaseServerName

The instance name of the data server used by the DataReader .

BaseCatalogName

The name of the catalog in the data store.

BaseColumnName

The name of the column in the data store.

BaseSchemaName

The name of the schema in the data store.

BaseTableName

The name of the table in the data store.

DataType

The .NET Framework data type of the column.

AllowDBNull

Indicates whether null values are allowed in the column.

ProviderType

The .NET data provider data type of the column.

IsAliased

Indicates whether the column name is an alias.

IsExpression

Indicates whether the column is an expression.

IsIdentity

Indicates whether the column is an identity column.

IsAutoIncrement

Indicates whether values for the columns are automatically in fixed increments for each new row.

IsRowVersion

Indicates whether the column is a read-only persistent row identifier.

IsHidden

Indicates whether the column is hidden.

IsLong

Indicates whether the column contains a Binary Long Object (BLOB).

IsReadOnly

Indicates whether the column value cannot be modified.

The DataReader can contain multiple result sets. In the solution, each of these is loaded into a separate DataTable within the DataSet .

The conversion process uses the GetSchemaData( ) method to retrieve metadata about the columns in the result set of the DataReader and inserts it into a DataTable , where each row in the table corresponds to a column in the result set. A target DataTable is created to hold the result set in the DataReader . The solution iterates over the rows in the schema DataTable and constructs a DataColumn for each record in the DataReader using the metadata that is stored in the columns as described in Table 5-1. This process is repeated for each result set in the DataReader .

If the DataReader returns multiple result sets, it does not contain enough information, nor is it possible to add information, to create relations between the tables. These have to be created programmatically within the DataSet if they are needed.

Connecting to Data

Retrieving and Managing Data

Searching and Analyzing Data

Adding and Modifying Data

Copying and Transferring Data

Maintaining Database Integrity

Binding Data to .NET User Interfaces

Working with XML

Optimizing .NET Data Access

Enumerating and Maintaining Database Objects

Appendix A. Converting from C# to VB Syntax



ADO. NET Cookbook
ADO.NET 3.5 Cookbook (Cookbooks (OReilly))
ISBN: 0596101406
EAN: 2147483647
Year: 2002
Pages: 222
Authors: Bill Hamilton

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net