从数据读取器填充数据表 [英] Populate data table from data reader

问题描述

我做在C＃（MS VS2008）一个基本的东西，有一个问题更多的正确的设计不是具体的code。

我创建一个数据表，然后尝试加载从DataReader（这是基于SQL存储过程）的数据表。我想知道是要加载的数据表的最有效的方式是否是做一个while语句，或者如果有更好的办法。

对我来说，唯一的缺点就是我必须手动输入我想在我的while语句添加字段，但我也不知道的方式自动执行反正因为我不希望从SP各个领域只需选择的，但是这不是一个巨大的交易在我的眼前。

我包括我做什么的总和低于code片段，但对我的code本身并不显着，甚至就是我要问一下。 Moreso想知道我的方法，我会纠缠于code的帮助以后，如果我的策略是错误的/低效的。

在此先感谢。

  VAR dtWriteoffUpload =新的DataTable（）;
dtWriteoffUpload.C​​olumns.Add（单元）;
dtWriteoffUpload.C​​olumns.Add（年）;
dtWriteoffUpload.C​​olumns.Add（时代）;
dtWriteoffUpload.C​​olumns.Add（会计）;
dtWriteoffUpload.C​​olumns.Add（DESCR）;
dtWriteoffUpload.C​​olumns.Add（DEFERRAL_TYPE）;
dtWriteoffUpload.C​​olumns.Add（NDC_Indicator）;
dtWriteoffUpload.C​​olumns.Add（管理CD）;
dtWriteoffUpload.C​​olumns.Add（PROD）;
dtWriteoffUpload.C​​olumns.Add（节点）;
dtWriteoffUpload.C​​olumns.Add（Curve_Family）;
dtWriteoffUpload.C​​olumns.Add（款额）;
dtWriteoffUpload.C​​olumns.Add（基本货币）;
dtWriteoffUpload.C​​olumns.Add（总帐）;

CMD = util.SqlConn.CreateCommand（）;
cmd.CommandTimeout = 1000;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText =proc_writeoff_data_details;
cmd.Parameters.Add（@ WHOAMI，SqlDbType.VarChar）。价值=

WindowsIdentity.GetCurrent（）名称。

cmd.Parameters.Add（@ parmEndDateKey，SqlDbType.VarChar）。价值= myMostRecentActualDate;
cmd.Parameters.Add（@ countrykeys，SqlDbType.VarChar）。价值= myCountryKey;
cmd.Parameters.Add（@ nodekeys，SqlDbType.VarChar）。价值=1,2;
打破;


博士= cmd.ExecuteReader（）;
而（dr.Read（））
{
    dtWriteoffUpload.Rows.Add（DR [国名]的ToString（），博士[国家重点]的ToString（））;
}
 

解决方案

您可以载入数据表直接从使用负载（数据读取器）方法接受一个的IDataReader 。

  VAR的DataReader = cmd.ExecuteReader（）;
VAR的dataTable =新的DataTable（）;
dataTable.Load（DataReader的）;
 

I'm doing a basic thing in C# (MS VS2008) and have a question more about proper design than specific code.

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.

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.

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.

Thanks in advance.

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

解决方案

You can load a DataTable directly from a data reader using the Load() method that accepts an IDataReader.

var dataReader = cmd.ExecuteReader();
var dataTable = new DataTable();
dataTable.Load(dataReader);

这篇关于从数据读取器填充数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！