DataReader into DataTable
I know how to read a datareader.
I know how to fill a datatable manually.
I know how to populate a datatable from a dataadapter.
There are times when I don't want to use a dataadapter.
I can use a datareader and fill a datatable with its rows inside a FOR loop thusly (ad-lib code follows). Is there a better way? A faster way? A built-in way? An object method that I'm missing?
//get a datareader.
SqlConnection con = new SqlConnection("data source=mydbserver;user id=admin;password=;initial catalog=northwind");
try
{
con.Open();
}
catch
{
MessageBox.Show("Cannot connect");
exit;
}
SqlCommand cmd = new SqlCommand("select top 10 * from publishers", con);
SqlDataReader dr = cmd.ExecuteReader();
//create a table
DataTable dt = new DataTable("mytable");
//add fields.
dt.Columns.Clear();
for (int j=0; j < dr.FieldCount; j++)
{
dt.Columns.Add("Col" + j.ToString(), dr.GetFieldType(j).GetType());
}
//add rows.
System.Object[] rowdata;
dt.Rows.Clear();
while(dr.Read())
{
dr.GetValues(rowdata);
dt.Rows.Add(rowdata);
}