SqlDataReader 与 SqlDataAdapter:哪一个具有更好的返回 DataTable 的性能? [英] SqlDataReader vs SqlDataAdapter: which one has the better performance for returning a DataTable?

问题描述

我想知道哪个返回 DataTable 的性能更好.这里对于 SqlDataReader 我使用 DataTable.Load(dr)

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

使用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;
    }
}

使用SqlDataAdapter:

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

推荐答案

差异可以忽略不计，所以最好使用更简洁的版本:SqlDataAdapter.Fill.

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

SqlDataReader.Fill 在内部创建一个内部类LoadAdapter(派生自DataAdapter)，并调用其Fill方法:性能将非常类似于SqlDataAdapter.Fill(DataTable).

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;
    }
}

这篇关于SqlDataReader 与 SqlDataAdapter:哪一个具有更好的返回 DataTable 的性能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！