I've just founded an usefull code snippet that returns a DataSet from a DataReader. As you can see, I'm passing a DbDataReader, because I prefer to work with the DbProviderFactory instead of using directly the System.Data.Sql namespace. A replace of the DbDataReader into SqlDataReader, will work exactly the same.
public static DataSet convertDataReaderToDataSet(DbDataReader 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"];
// 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;
}