C# SqlDataReader 与 SqlDataAdapter：哪一个具有更好的返回 DataTable 的性能？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14999143/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SqlDataReader vs SqlDataAdapter: which one has the better performance for returning a DataTable?
提问by Satinder singh
I want to know which one has the better performance for returning a DataTable. Here for SqlDataReaderI use DataTable.Load(dr)
我想知道哪个在返回DataTable. 这里供SqlDataReader我使用DataTable.Load(dr)
Using SqlDataReader:
使用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:
使用SqlDataAdapter：
public DataTable populateUsingDataAdapter(string myQuery)
{
SqlDataAdapter dap = new SqlDataAdapter(myQuery,cn);
DataSet ds = new DataSet();
dap.Fill(ds);
return ds.Tables[0];
}
采纳答案by Joe
The difference will be negligible, so it's probably better to use the more concise version: SqlDataAdapter.Fill.
差异可以忽略不计，因此最好使用更简洁的版本：SqlDataAdapter.Fill.
SqlDataReader.Fillcreates an internal class LoadAdapter(derived from DataAdapter) internally, and calls its Fillmethod: performance will be very similar to SqlDataAdapter.Fill(DataTable).
SqlDataReader.Fill在内部创建一个内部类LoadAdapter（派生自DataAdapter），并调用其Fill方法：性能将非常类似于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;
}
}
回答by Rob Epstein
SqlDataReaderhas historically been significantly faster than SqlDataAdapter. Improvements may have been made in .NET 4.5, but I doubt it has improved enough to outpace the performance of the DataReader.
SqlDataReader从历史上看，速度明显快于SqlDataAdapter. .NET 4.5 可能有所改进，但我怀疑它的改进是否足以超过 DataReader 的性能。
回答by coder
SqlDataReaderwill be faster than SQlDataAdapter because it works in a connected state which means the first result is returned from query as soon as its available ..
SqlDataReader将比 SQlDataAdapter 更快，因为它在连接状态下工作，这意味着只要它可用，就会从查询中返回第一个结果。
回答by Tim Medora
This question, and more specifically, this answersuggests that your second example is faster. It is certainly not an exhaustive benchmark but it is an interesting test.
这个问题，更具体地说，这个答案表明你的第二个例子更快。这当然不是一个详尽的基准测试，但它是一个有趣的测试。
Reflecting the source code of DataTableshows that calling DataTable.Load() actually creates an internal DataAdaptersubclass called LoadAdapterand calls the Fill()method of DataAdapter. SqlDataAdapterdoes the bulk of its loading work in the exact same place.
反映的源代码DataTable显示，调用 DataTable.Load() 实际上创建了一个内部DataAdapter子类，LoadAdapter并调用了 的Fill()方法DataAdapter。SqlDataAdapter在完全相同的地方完成大部分加载工作。
More importantly, I would tend to favor the second example for readability. Neither example compares to the fast access provided by direct use of the DataReader, so I would opt for the cleaner code.
更重要的是，我倾向于支持第二个例子的可读性。这两个示例都无法与直接使用 提供的快速访问进行比较DataReader，因此我会选择更简洁的代码。
回答by just_a_guest
In addition to the selected solution, I would like to add that:
除了选定的解决方案，我想补充一点：
Using the DataReader, you don′t need to know which type of DbConnection you have.
使用 DataReader，您无需知道您拥有哪种类型的 DbConnection。
All you need is an instance which implements IDbConnection, with that you can use "connection.CreateCommand" and then "dbCommand.ExecuteReader" and then dataTable.Load.
您所需要的只是一个实现 IDbConnection 的实例，您可以使用“connection.CreateCommand”，然后使用“dbCommand.ExecuteReader”，然后使用 dataTable.Load。
But when you use DataAdapter you will need to know which connection is used (i.e. oracle, sqlserver, etc.)
但是当你使用 DataAdapter 时，你需要知道使用的是哪个连接（即 oracle、sqlserver 等）
(It′s not relevant for the thread starter, but I landed here using g**gle while looking for this topic.)
（它与线程启动器无关，但我在寻找此主题时使用 g**gle 登陆这里。）
