Please check the below code. Automatically it will convert as DataTable
private void ConvertDataReaderToTableManually()
{
SqlConnection conn = null;
try
{
string connString = ConfigurationManager.ConnectionStrings["NorthwindConn"].ConnectionString;
conn = new SqlConnection(connString);
string query = "SELECT * FROM Customers";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
DataTable dtSchema = dr.GetSchemaTable();
DataTable dt = new DataTable();
// You can also use an ArrayList instead of List<>
List<DataColumn> listCols = new List<DataColumn>();
if (dtSchema != null)
{
foreach (DataRow drow in dtSchema.Rows)
{
string columnName = System.Convert.ToString(drow["ColumnName"]);
DataColumn column = new DataColumn(columnName, (Type)(drow["DataType"]));
column.Unique = (bool)drow["IsUnique"];
column.AllowDBNull = (bool)drow["AllowDBNull"];
column.AutoIncrement = (bool)drow["IsAutoIncrement"];
listCols.Add(column);
dt.Columns.Add(column);
}
}
// Read rows from DataReader and populate the DataTable
while (dr.Read())
{
DataRow dataRow = dt.NewRow();
for (int i = 0; i < listCols.Count; i++)
{
dataRow[((DataColumn)listCols[i])] = dr[i];
}
dt.Rows.Add(dataRow);
}
GridView2.DataSource = dt;
GridView2.DataBind();
}
catch (SqlException ex)
{
// handle error
}
catch (Exception ex)
{
// handle error
}
finally
{
conn.Close();
}
}