Quickest way to create DataTable from query?

Question

What is the code with the smallest number of lines that will generate a DataTable from this SQL query?

SELECT * 
FROM [Table1] 
WHERE ([Date] BETWEEN @Date1 AND @Date2) AND 
      ([Field1] IS NULL OR [Field2] IS NULL)

Solution 1

Use SqlDataAdapter to fill a DataTable.

DataTable dt = new DataTable();
using (SqlConnection yourConnection = new SqlConnection("connectionstring"))
{
    using (SqlCommand cmd = new SqlCommand("....your sql statement", yourConnection))
    {
        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
        {
            da.Fill(dt);
        }
    }
}

Use using block with your SqlConnection, SqlCommand and SqlDataAdapter since they implement IDisposable interface. Also use Parameterized query

Solution 2

Try this

SqlCommand command = new SqlCommand(query, conn);
DataTable dt = new DataTable();
using(SqlDataReader reader = command.ExecuteReader())
{
     dt.Load(reader);
}

Solution 3

SqlDataAdaptor and FillSchema

It will create your table on the fly

Applied On a DataSet

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.aspx

Applied On a DataTable

http://msdn.microsoft.com/en-us/library/152bda9x.aspx

Comments

[nawfal]

I would say this is the answer :)

[Asif Mushtaq]

-1, DataReader is not suitable if have to populate the dataset. It is useful in the cases where you need to utilize the data while reading it.

[NightShovel]

Wouldn't you want to include the "using" statement with the SqlCommand and SqlDataAdapter as well, since they also implement IDisposable?

[Habib]

@NightShovel, that is correct. I should have used the using blocks.