Im not Sure of What your doing.....
are u client side, server side, in a Stored Proc
if client side , I generallly use the schema of the table
to create my 2nd table and add the columns required
below is a smal sample using datareader -> directly into a table
/// <summary>
/// Ads some Functionality
/// does not close reader here ..mite call readToTable many times for one
Read into multiple tables
/// from multiple selects from one Stored Proc
/// user of this code must close the reader
/// </summary>
public static class MySqlReader
{
/// <summary>
/// Reades the SqlReader Result Set into A Datatable
/// Provide a New DataTable or Existing Datatable
/// this Routing only Adds Columns on a New DataTable
/// </summary>
/// <param name="Reader"></param>
/// <param name="UserTable"></param>
/// <returns></returns>
public static int ReadToTable(SqlDataReader Reader, DataTable UserTable)
{
//dont Add columns if they Already Exist only if this incomming usertable is
a empty Datatableclass
if (UserTable.Columns.Count == 0)
{
DataTable Schema = Reader.GetSchemaTable();
DataRow Row;
DataColumn Col;
//define the Columns and Add to User Table
for (int x = 0; x < Schema.Rows.Count; x++)
{
Row = Schema.Rows[x];
string ColumnName = (string)Row["ColumnName"];
Col = new DataColumn(ColumnName, (Type)Row["DataType"]);
UserTable.Columns.Add(Col);
}
}
object[] ColumnValues = new Object[Reader.FieldCount];
while (Reader.Read())
{
Reader.GetValues(ColumnValues);
UserTable.LoadDataRow(ColumnValues, true);
}
return UserTable.Rows.Count;
}
/// <summary>
/// Creates New Table When Called
/// </summary>
/// <param name="Reader"></param>
/// <returns></returns>
public static DataTable ReadToTable(SqlDataReader Reader)
{
// Creates a New Table Each Time...lets say this is used by a service
// if Result Set changes we need a new table
DataTable NewTable = new DataTable();
DataTable Schema = Reader.GetSchemaTable();
DataRow Row;
DataColumn Col;
//define the Columns and Add to User Table
for (int x = 0; x < Schema.Rows.Count; x++)
{
Row = Schema.Rows[x];
string ColumnName = (string)Row["ColumnName"];
Col = new DataColumn(ColumnName, (Type)Row["DataType"]);
NewTable.Columns.Add(Col);
}
object[] ColumnValues = new Object[Reader.FieldCount];
while (Reader.Read())
{
Reader.GetValues(ColumnValues);
NewTable.LoadDataRow(ColumnValues, true);
}
return NewTable;
}
/// <summary>
/// Creates and Defines columns for a new DataTable from Table Schema
/// Returns new Table;
/// </summary>
/// <param name="Reader"></param>
/// <returns></returns>
public static DataTable CreateTable(SqlDataReader Reader)
{
DataTable Schema = Reader.GetSchemaTable();
DataTable NewTable = new DataTable();
DataColumn Col = new DataColumn();
for (int x = 0; x < Schema.Rows.Count; x++)
{
DataRow row = Schema.Rows[x];
string ColumnName = (string)row["ColumnName"];
Col = new DataColumn(ColumnName, (Type)row["DataType"]);
NewTable.Columns.Add(Col);
}
return NewTable;
}
}
