Converting a DataReader to a DataSet

[ Team LiB ] Recipe 5.3 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.
Nội dung trích xuất từ tài liệu:
Converting a DataReader to a DataSet[ Team LiB ]Recipe 5.3 Converting a DataReader to a DataSetProblemYou need to transfer data from a DataReader to a DataSet.SolutionCreate a DataTable schema in the destination DataSet using the schema informationreturned 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 tothe 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 constantsusing System;using System.Configuration;using System.Collections;using System.Data;using System.Data.SqlClient;// Table name constantsprivate const String ORDERS_TABLE = Orders;private const String ORDERDETAILS_TABLE = OrderDetails;// Relation name constantsprivate const String ORDERS_ORDERDETAILS_RELATION = Orders_OrderDetails_Relation;// Field name constantsprivate const String ORDERID_FIELD = OrderID;// . . .DataSet ds = new DataSet( );// SQL for batch queryString 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;DiscussionWhile the DbDataAdapter class—from which DataAdapter classes in .NET providers forrelational databases inherit—defines an overload of the Fill( ) method that converts aDataReader 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 convertingthe connected DataReader object to a disconnected DataTable. Internally, ADO.NETuses the DataReader through the DataAdapter to populate the DataTable object. Thissample demonstrates how this can be done programmatically.Both the SQL Server and OLE DB DataReader expose a GetSchemaTable( ) method thatreturns a table containing the column metadata of the DataReader. You can use this tablein 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 eachcolumn. Table 5-1. DataReader GetSchemaTable( ) metadata Column name DescriptionColumnName The name of the column.ColumnOrdinal The zero-based ordinal of the column. The maximum length of a column value. This is the data size ofColumnSize fixed-length data types. The maximum precision of numeric data type columns. Null forNumericPrecision non-numeric data type columns. The number of digits to the right of the decimal forNumericScale DBTYPE_DECIMAL or DBTYPE_NUMERIC data type columns. Otherwise, null. Indicates whether the value in the column must be unique within allIsUnique records. Indicates whether the column is part of the primary key or a uniqueIsKey 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.IsAlias ...