转换DataReader 为DataTable

转换DataReader 为DataTable

using System;
using System.Data;
using System.Data.Common ;

namespace DataUtils
{
public class DataReaderAdapter : DbDataAdapter
{
public int FillFromReader( DataTable dataTable, I DataReader dataReader)
{
return this.Fill( dataTable, dataReader);
}

protected override RowUpdatedEventArgs CreateRowUpdatedEvent(
DataRow dataRow,
IDbCommand command,
StatementType statementType,
DataTableMapping tableMapping
){return null;}

protected override RowUpdatingEventArgs CreateRowUpdatingEvent(
DataRow dataRow,
IDbCommand command,
StatementType statementType,
DataTableMapping tableMapping
){return null;}

protected override void OnRowUpdated(
RowUpdatedEventArgs value
){}
protected override void OnRowUpdating(
RowUpdatingEventArgs value
){}
}
}


With this complete, here is some console app code to illustrate its use:

using System;
using System.Data;
using System.Data.SqlClient;

namespace DataReaderAdapter
{
class Class1
{
[STAThread]
static void Main(string[] args)
{
SqlConnection cn = new SqlConnection("Server=(local);database=Northwind;user id=sa;password=;");
string sql ="Select * from Employees";
SqlCommand cmd= new SqlCommand(sql,cn);
cn.Open();
Sql DataReader dr=cmd.ExecuteReader(CommandBehavior.CloseConnection);
DataUtils. DataReaderAdapter dra = new DataUtils. DataReaderAdapter();
DataTable dt = new DataTable();
dra.FillFromReader(dt,dr);
dr.Close();
for(int i =0;i<dt.Rows.Count;i++)
Console.WriteLine(dt.Rows[i][1].ToString());
}
}
}


private void Page_Load(object sender, System.EventArgs e)
 {

  System.Data.SqlClient.SqlConnection _SqlConnection =
  new System.Data.SqlClient.SqlConnection();
 _SqlConnection.ConnectionString = "Server=LocalHost;Database=Northwind;TRUSTED_CONNECTION=true";
  System.Data.SqlClient.SqlCommand _SqlCommand =
  new System.Data.SqlClient.SqlCommand();
  _SqlCommand.CommandText = "SELECT TOP 10 * FROM Products INNER JOIN Suppliers ON Suppliers.SupplierID = Products.SupplierID";
  _SqlCommand.CommandType = System.Data.CommandType.Text;
  _SqlCommand.Connection = _SqlConnection;

  _SqlConnection.Open();

  System.Data.SqlClient.SqlDataReader _SqlDataReader;
  _SqlDataReader = _SqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
  System.Data.DataSet ds = new System.Data.DataSet();
  ds.Tables.Add(this.GetTable(_SqlDataReader));
  DataGrid1.DataSource = ds.Tables[0];
  DataGrid1.DataBind();

 }


public System.Data.DataTable GetTable(System.Data.SqlClient.SqlDataReader _reader)
 {

  System.Data.DataTable _table = _reader.GetSchemaTable();
  System.Data.DataTable _dt = new System.Data.DataTable();
  System.Data.DataColumn _dc;
  System.Data.DataRow _row;
  System.Collections.ArrayList _al = new System.Collections.ArrayList();

  for (int i = 0; i < _table.Rows.Count; i ++)
 {

  _dc = new System.Data.DataColumn();

  if (! _dt.Columns.Contains(_table.Rows[i]["ColumnName"].ToString()))
 {

  _dc.ColumnName = _table.Rows[i]["ColumnName"].ToString();
  _dc.Unique = Convert.ToBoolean(_table.Rows[i]["IsUnique"]);
  _dc.AllowDBNull = Convert.ToBoolean(_table.Rows[i]["AllowDBNull"]);
  _dc.ReadOnly = Convert.ToBoolean(_table.Rows[i]["IsReadOnly"]);
  _al.Add(_dc.ColumnName);
  _dt.Columns.Add(_dc);

 }

 }

 while (_reader.Read())
 {

  _row = _dt.NewRow();

  for ( int i = 0; i < _al.Count; i++)
  {

   _row[((System.String) _al[i])] = _reader[(System.String) _al[i]];

  }

 _dt.Rows.Add(_row);

}

return _dt;

}

#endregion

#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{

  InitializeComponent();
  base.OnInit(e);

}

private void InitializeComponent()
{

  this.Load += new System.EventHandler(this.Page_Load);

}
#endregion
}
}
///
ConvertDataReaderToDataSet:

///    <summary>          ///    Converts a SqlDataReader to a DataSet          ///    <param name='reader'>          /// SqlDataReader to convert.</param>          ///    <returns>          /// DataSet filled with the contents of the reader.</returns>          ///    </summary>          public static DataSet convertDataReaderToDataSet ( SqlDataReader reader )          {              DataSet dataSet = new DataSet ();              do              {                  // Create new data table                  DataTable schemaTable = reader . GetSchemaTable ();                  DataTable dataTable = new DataTable ();                  if ( schemaTable != null )                  {                      // A query returning records was executed                      for ( int i = 0 ; i < schemaTable . Rows . Count ; i ++ )                      {                          DataRow dataRow = schemaTable . Rows [ i ];                          // Create a column name that is unique in the data table                          string columnName = ( string ) dataRow [ "ColumnName" ]; //+ "<C" + i + "/>";                          // Add the column definition to the data table                          DataColumn column = new DataColumn ( columnName , ( Type ) dataRow [ "DataType" ] );                          dataTable . Columns . Add ( column );                      }                      dataSet . Tables . Add ( dataTable );                      // Fill the data table we just created                      while ( reader . Read () )                      {                          DataRow dataRow = dataTable . NewRow ();                          for ( int i = 0 ; i < reader . FieldCount ; i ++ )                              dataRow [ i ] = reader . GetValue ( i );                          dataTable . Rows . Add ( dataRow );                      }                  }                  else                  {                      // No records were returned                      DataColumn column = new DataColumn ( "RowsAffected" );                      dataTable . Columns . Add ( column );                      dataSet . Tables . Add ( dataTable );                      DataRow dataRow = dataTable . NewRow ();                      dataRow [ 0 ] = reader . RecordsAffected ;                      dataTable . Rows . Add ( dataRow );                  }              }              while ( reader . NextResult () );              return dataSet ;          }