SqlDataAdapter.Fill() vs DataTable.Load()

Question

I come from this question here but I have a different case. I need my result in a DataTable and I have 2 potential methods:

public static DataTable SelectDataTable(string query, string ConnectionString)
{       
    using (SqlConnection myConnection = new SqlConnection(ConnectionString))
    {
        using (SqlDataAdapter myDataAdapter = new SqlDataAdapter(query, myConnection))
        {
            DataTable dt = new DataTable();
            myDataAdapter.Fill(dt);
            return dt;
        }
    }
}

and

public static DataTable SelectDataTable(string query, string ConnectionString)
{
    using (SqlConnection myConnection = new SqlConnection(ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand(query, myConnection))
        {
            myConnection.Open();
            DataTable dt = new DataTable();
            dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));
            return dt;
        }
    }
}

so my question: is there difference between

SqlDataAdapter + Fill() and SqlDataReader + DataTable + Load()

Which of there methods is to prefer?

Joel answer is pretty detailed, what makes this question not a duplicate

In fact I don't use all those mentioned advantages of the SqlDataReader I use it to fill a DataTable and that makes me expecting the answer be like: It's the same?! Unfortunately it's hard to guess what's happening under the hood.

Eurypterid

Joel answer is pretty detailed, what makes this question not a duplicate? – Ravine

You can find - Here is the answer difference between dr and da – Landslide

Answer 1

Unless you are working with big data, I wouldn't expect huge performance gains from using a dataReader as opposed to a dataAdapter.

That being said, the link Pawel posted has a pretty decent write-up explaining the differences and advantages of both.

The main takeaway is readers are for reading data. They do nothing else really than that.

Because they don't do much else, they are relatively low overhead for performance.

DataAdapters are going to allow you to do more than the Readers, but in your case, it sounds like you don't need to do anything other than read in the records.

To reiterate, unless you are working with big data (like hundreds of thousands/millions of rows) I wouldn't expect the performance savings by using the dataReader to be very noticeable.

That is something only you will be able to determine when benchmarking with your own data.

Let us know if that clears up any confusion you may have had about the differences between DataAdapter and DataReader.

Triplicate

Answer 2

Many years ago, I initially used DataTable & Load. Then I found there was data missing.

I believe I found a StackOverFlow post which suggested using DataAdapter & Fill to fix this. Which I did and it worked.

I have a vague memory of that some comment mentioned that Fill populates the Schema data correctly & Load didn't. But I maybe mistaken. As I said, it was many years ago. But I have always used Fill ever since.

Omsk