SqlDataReader vs SqlDataAdapter: which one has the better performance for returning a DataTable?

Question

I want to know which one has the better performance for returning a DataTable. Here for SqlDataReader I use DataTable.Load(dr)

Using SqlDataReader:

public static DataTable populateUsingDataReader(string myQuery)
{
    DataTable dt = new DataTable();
    using (SqlConnection con = new SqlConnection(constring))
    {
        SqlCommand cmd = new SqlCommand(myQuery, con);
        con.Open();
        SqlDataReader dr = null;
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        if (dr.HasRows)
        {
            dt.Load(dr);
        }
        return dt;
    }
}

using SqlDataAdapter:

public DataTable populateUsingDataAdapter(string myQuery)
{
    SqlDataAdapter dap = new SqlDataAdapter(myQuery,cn);
    DataSet ds = new DataSet();
    dap.Fill(ds);
    return ds.Tables[0];
}

Answer 1

The difference will be negligible, so it's probably better to use the more concise version: SqlDataAdapter.Fill.

SqlDataReader.Fill creates an internal class LoadAdapter (derived from DataAdapter) internally, and calls its Fill method: performance will be very similar to SqlDataAdapter.Fill(DataTable).

There will be some small differences in initialization / validation of arguments, but as the number of rows increases, this will become less and less significant.

Note also that your second sample should be modified to be comparable with the first:

public DataTable populateUsingDataAdapter(string myQuery)
{
    using (SqlConnection con = new SqlConnection(constring))
    {
        SqlDataAdapter dap = new SqlDataAdapter(myQuery,con);
        DataTable dt = new DataTable();
        dap.Fill(dt);
        return dt;
    }
}