Ryan Ward Ryan Ward - 1 month ago 19
C# Question

Populate data table from data reader

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

Answer

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