CODEWITHSUNDEEP
c#performancedatatabledatasetsqldataadapter
Lee Tae-young
Lee Tae-young

Reputation: 11

DataTable.Load() is taking too much time compared to SqlDataAdapter.Fill()

I have to implement an asynchronous DB connecting method with DataSet, so first I tried to use SqlDataReader to fill DataSet. But it takes way too much time.

Here's the codes :

Using DataTable.Load

DataSet ds = new DataSet();
using (SqlConnection conn = new SqlConnection(conString)
{
    SqlCommand cmd = new Sqlcommand(query, conn);
    conn.Open();
    SqlDataReader reader = cmd.ExecuteReader();

    while (!reader.IsClosed)
    {
        DataTable dt = new DataTable();
        dt.BeginLoadData();
        //////////////////////////////////////
        dt.Load(reader);     // takes too much
        //////////////////////////////////////
        dt.EndLoadData();
        ds.Tables.Add(dt);
    }
}

Using SqlDataAdapter.Fill

DataSet ds = new DataSet();
using (SqlConnection conn = new SqlConnection(conString))
{
    ...
    SqlDataAdapter adapter = new SqlDataAdapter();
    adapter.SelectCommand = cmd;
    adapter.Fill(ds);
}

I think these two methods do exactly same thing. But when reading 2400 rows in DB the first method takes 20ms in average, otherwise the second one takes just 7ms.

Is there something that I've missed or is the code wrong?

Upvotes: 1

Views: 2203

Answers (2)

Rajeev Kumar
Rajeev Kumar

Reputation: 4963

Try this:

SqlDataReader reader = cmd.ExecuteReader();
DataTable dt = new DataTable();           
dt.Load(reader);             
ds.Tables.Add(dt);

Upvotes: 0

songsuyong
songsuyong

Reputation: 11

I recommend that you use SqlDataAdapter::Fill(DataSet):

DataSet ds = new DataSet();
ds.Clear();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(ds);
DataTable dt = ds.Tables[0];

Upvotes: 1

Related Questions