Turning a SqlCommand with parameters into a DataTable

cdub picture cdub · Dec 14, 2012 · Viewed 58.4k times · Source

I'm adapting some code that someone else wrote and need to return a DataTable for time's sake.

I have code like this:

using (SqlCommand command = new SqlCommand(query, conn))
{
      //add parameters and their values

      using (SqlDataReader dr = command.ExecuteReader())
      {
          return dr;
      }

But what's the best way to return a datatable?

Answer

Thomas C. G. de Vilhena picture Thomas C. G. de Vilhena · Dec 14, 2012

Use the DataTable.Load method to fill your table with values from the SqlDataReader:

using (SqlDataReader dr = command.ExecuteReader())
{
    var tb = new DataTable();
    tb.Load(dr);
    return tb;
}