C# 从数据读取器填充数据表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18961938/
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
Populate data table from data reader
提问by Ryan Ward
I'm doing a basic thing in C# (MS VS2008) and have a question more about proper design than specific code.
我正在用 C# (MS VS2008) 做一个基本的事情，并且有更多关于正确设计而不是特定代码的问题。
I am creating a datatable and then trying to load the datatable from a datareader (which is based on an SQL stored procedure). What I'm wondering is whether the most efficient way to load the datatable is to do a while statement, or if there is a better way.
我正在创建一个数据表，然后尝试从数据读取器（基于 SQL 存储过程）加载数据表。我想知道加载数据表的最有效方法是执行 while 语句，还是有更好的方法。
To me the only drawback is I have to manually type in the fields I want to add in my while statement, but I also don't know of way to automate that anyways since I don't want all fields from the SP just select ones, but that's not a huge deal in my eyes.
对我来说唯一的缺点是我必须手动输入要添加到 while 语句中的字段，但我也不知道如何自动执行该操作，因为我不希望 SP 中的所有字段只选择一个，但这在我看来并不是什么大问题。
I've included code snippets below the totality of what I do, though to me the code itself isn't remarkable or even what I'm asking about. Moreso wondering about my methodology, I'll pester for code help later if my strategy is wrong/inefficient.
我在我所做的全部内容下面包含了代码片段，尽管对我来说代码本身并不显着，甚至我所问的也不是。更想知道我的方法论，如果我的策略错误/效率低下，我会在以后纠缠代码帮助。
var dtWriteoffUpload = new DataTable();
dtWriteoffUpload.Columns.Add("Unit");
dtWriteoffUpload.Columns.Add("Year");
dtWriteoffUpload.Columns.Add("Period");
dtWriteoffUpload.Columns.Add("Acct");
dtWriteoffUpload.Columns.Add("Descr");
dtWriteoffUpload.Columns.Add("DEFERRAL_TYPE");
dtWriteoffUpload.Columns.Add("NDC_Indicator");
dtWriteoffUpload.Columns.Add("Mgmt Cd");
dtWriteoffUpload.Columns.Add("Prod");
dtWriteoffUpload.Columns.Add("Node");
dtWriteoffUpload.Columns.Add("Curve_Family");
dtWriteoffUpload.Columns.Add("Sum Amount");
dtWriteoffUpload.Columns.Add("Base Curr");
dtWriteoffUpload.Columns.Add("Ledger");
cmd = util.SqlConn.CreateCommand();
cmd.CommandTimeout = 1000;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "proc_writeoff_data_details";
cmd.Parameters.Add("@whoAmI", SqlDbType.VarChar).Value =
WindowsIdentity.GetCurrent().Name;
cmd.Parameters.Add("@parmEndDateKey", SqlDbType.VarChar).Value = myMostRecentActualDate;
cmd.Parameters.Add("@countrykeys", SqlDbType.VarChar).Value = myCountryKey;
cmd.Parameters.Add("@nodekeys", SqlDbType.VarChar).Value = "1,2";
break;
dr = cmd.ExecuteReader();
while (dr.Read())
{
dtWriteoffUpload.Rows.Add(dr["country name"].ToString(), dr["country key"].ToString());
}
采纳答案by Sagi
You can load a DataTabledirectly from a data reader using the Load()method that accepts an IDataReader.
您可以DataTable使用Load()接受IDataReader.
var dataReader = cmd.ExecuteReader();
var dataTable = new DataTable();
dataTable.Load(dataReader);
回答by Mike Perrenoud
If you're trying to load a DataTable, then leverage the SqlDataAdapterinstead:
如果您尝试加载 a DataTable，请SqlDataAdapter改用 ：
DataTable dt = new DataTable();
using (SqlConnection c = new SqlConnection(cString))
using (SqlDataAdapter sda = new SqlDataAdapter(sql, c))
{
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
sda.SelectCommand.Parameters.AddWithValue("@parm1", val1);
...
sda.Fill(dt);
}
You don't even need to define the columns. Just create the DataTableand Fillit.
您甚至不需要定义列。只需创建DataTable和Fill它。
Here, cStringis your connection string and sqlis the stored procedure command.
在这里，cString是您的连接字符串，sql是存储过程命令。
回答by sarathkumar
Please check the below code. Automatically it will convert as DataTable
请检查以下代码。它会自动转换为 DataTable
private void ConvertDataReaderToTableManually()
{
SqlConnection conn = null;
try
{
string connString = ConfigurationManager.ConnectionStrings["NorthwindConn"].ConnectionString;
conn = new SqlConnection(connString);
string query = "SELECT * FROM Customers";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
DataTable dtSchema = dr.GetSchemaTable();
DataTable dt = new DataTable();
// You can also use an ArrayList instead of List<>
List<DataColumn> listCols = new List<DataColumn>();
if (dtSchema != null)
{
foreach (DataRow drow in dtSchema.Rows)
{
string columnName = System.Convert.ToString(drow["ColumnName"]);
DataColumn column = new DataColumn(columnName, (Type)(drow["DataType"]));
column.Unique = (bool)drow["IsUnique"];
column.AllowDBNull = (bool)drow["AllowDBNull"];
column.AutoIncrement = (bool)drow["IsAutoIncrement"];
listCols.Add(column);
dt.Columns.Add(column);
}
}
// Read rows from DataReader and populate the DataTable
while (dr.Read())
{
DataRow dataRow = dt.NewRow();
for (int i = 0; i < listCols.Count; i++)
{
dataRow[((DataColumn)listCols[i])] = dr[i];
}
dt.Rows.Add(dataRow);
}
GridView2.DataSource = dt;
GridView2.DataBind();
}
catch (SqlException ex)
{
// handle error
}
catch (Exception ex)
{
// handle error
}
finally
{
conn.Close();
}
}
回答by ghawkes
As Sagi stated in their answer DataTable.Load is a good solution. If you are trying to load multiple tables from a single reader you do not need to call DataReader.NextResult. The DataTable.Load method also advances the reader to the next result set (if any).
正如 Sagi 在他们的回答中所说 DataTable.Load 是一个很好的解决方案。如果您尝试从单个读取器加载多个表，则不需要调用 DataReader.NextResult。DataTable.Load 方法还将读取器前进到下一个结果集（如果有）。
// Read every result set in the data reader.
while (!reader.IsClosed)
{
DataTable dt = new DataTable();
// DataTable.Load automatically advances the reader to the next result set
dt.Load(reader);
items.Add(dt);
}
回答by martijn
I looked into this as well, and after comparing the SqlDataAdapter.Fill method with the SqlDataReader.Load funcitons, I've found that the SqlDataAdapter.Fill method is more than twice as fast with the result sets I've been using
我也研究了这一点，在将 SqlDataAdapter.Fill 方法与 SqlDataReader.Load 函数进行比较后，我发现 SqlDataAdapter.Fill 方法的速度是我一直使用的结果集的两倍多
Used code:
使用的代码：
[TestMethod]
public void SQLCommandVsAddaptor()
{
long AdapterFillLargeTableTime, readerLoadLargeTableTime, AdapterFillMediumTableTime, readerLoadMediumTableTime, AdapterFillSmallTableTime, readerLoadSmallTableTime, AdapterFillTinyTableTime, readerLoadTinyTableTime;
string LargeTableToFill = "select top 10000 * from FooBar";
string MediumTableToFill = "select top 1000 * from FooBar";
string SmallTableToFill = "select top 100 * from FooBar";
string TinyTableToFill = "select top 10 * from FooBar";
using (SqlConnection sconn = new SqlConnection("Data Source=.;initial catalog=Foo;persist security info=True; user id=bar;password=foobar;"))
{
// large data set measurements
AdapterFillLargeTableTime = MeasureExecutionTimeMethod(sconn, LargeTableToFill, ExecuteDataAdapterFillStep);
readerLoadLargeTableTime = MeasureExecutionTimeMethod(sconn, LargeTableToFill, ExecuteSqlReaderLoadStep);
// medium data set measurements
AdapterFillMediumTableTime = MeasureExecutionTimeMethod(sconn, MediumTableToFill, ExecuteDataAdapterFillStep);
readerLoadMediumTableTime = MeasureExecutionTimeMethod(sconn, MediumTableToFill, ExecuteSqlReaderLoadStep);
// small data set measurements
AdapterFillSmallTableTime = MeasureExecutionTimeMethod(sconn, SmallTableToFill, ExecuteDataAdapterFillStep);
readerLoadSmallTableTime = MeasureExecutionTimeMethod(sconn, SmallTableToFill, ExecuteSqlReaderLoadStep);
// tiny data set measurements
AdapterFillTinyTableTime = MeasureExecutionTimeMethod(sconn, TinyTableToFill, ExecuteDataAdapterFillStep);
readerLoadTinyTableTime = MeasureExecutionTimeMethod(sconn, TinyTableToFill, ExecuteSqlReaderLoadStep);
}
using (StreamWriter writer = new StreamWriter("result_sql_compare.txt"))
{
writer.WriteLine("10000 rows");
writer.WriteLine("Sql Data Adapter 100 times table fill speed 10000 rows: {0} milliseconds", AdapterFillLargeTableTime);
writer.WriteLine("Sql Data Reader 100 times table load speed 10000 rows: {0} milliseconds", readerLoadLargeTableTime);
writer.WriteLine("1000 rows");
writer.WriteLine("Sql Data Adapter 100 times table fill speed 1000 rows: {0} milliseconds", AdapterFillMediumTableTime);
writer.WriteLine("Sql Data Reader 100 times table load speed 1000 rows: {0} milliseconds", readerLoadMediumTableTime);
writer.WriteLine("100 rows");
writer.WriteLine("Sql Data Adapter 100 times table fill speed 100 rows: {0} milliseconds", AdapterFillSmallTableTime);
writer.WriteLine("Sql Data Reader 100 times table load speed 100 rows: {0} milliseconds", readerLoadSmallTableTime);
writer.WriteLine("10 rows");
writer.WriteLine("Sql Data Adapter 100 times table fill speed 10 rows: {0} milliseconds", AdapterFillTinyTableTime);
writer.WriteLine("Sql Data Reader 100 times table load speed 10 rows: {0} milliseconds", readerLoadTinyTableTime);
}
Process.Start("result_sql_compare.txt");
}
private long MeasureExecutionTimeMethod(SqlConnection conn, string query, Action<SqlConnection, string> Method)
{
long time; // know C#
// execute single read step outside measurement time, to warm up cache or whatever
Method(conn, query);
// start timing
time = Environment.TickCount;
for (int i = 0; i < 100; i++)
{
Method(conn, query);
}
// return time in milliseconds
return Environment.TickCount - time;
}
private void ExecuteDataAdapterFillStep(SqlConnection conn, string query)
{
DataTable tab = new DataTable();
conn.Open();
using (SqlDataAdapter comm = new SqlDataAdapter(query, conn))
{
// Adapter fill table function
comm.Fill(tab);
}
conn.Close();
}
private void ExecuteSqlReaderLoadStep(SqlConnection conn, string query)
{
DataTable tab = new DataTable();
conn.Open();
using (SqlCommand comm = new SqlCommand(query, conn))
{
using (SqlDataReader reader = comm.ExecuteReader())
{
// IDataReader Load function
tab.Load(reader);
}
}
conn.Close();
}
Results:
结果：
10000 rows:
Sql Data Adapter 100 times table fill speed 10000 rows: 11782 milliseconds
Sql Data Reader 100 times table load speed 10000 rows: 26047 milliseconds
1000 rows:
Sql Data Adapter 100 times table fill speed 1000 rows: 984 milliseconds
Sql Data Reader 100 times table load speed 1000 rows: 2031 milliseconds
100 rows:
Sql Data Adapter 100 times table fill speed 100 rows: 125 milliseconds
Sql Data Reader 100 times table load speed 100 rows: 235 milliseconds
10 rows:
Sql Data Adapter 100 times table fill speed 10 rows: 32 milliseconds
Sql Data Reader 100 times table load speed 10 rows: 93 milliseconds
For performance issues, using the SqlDataAdapter.Fill method is far more efficient. So unless you want to shoot yourself in the foot use that. It works faster for small and large data sets.
对于性能问题，使用 SqlDataAdapter.Fill 方法效率更高。所以除非你想用脚射击自己，否则请使用它。对于小型和大型数据集，它的工作速度更快。
