Populating a data table from a data reader

Question

I do the main thing in C # (MS VS2008) and ask a question about the correct design than the specific code.

I create a datatable and then try to load a datatable from a datareader (which is based on an SQL stored procedure). I am wondering if the most efficient way to load data is to do while statements or if there is a better way.

The only drawback for me is that I need to manually enter the fields that I want to add to my while statement, but I also don’t know how to automate this, since I don’t want all the fields from SP just select them, but this is not a huge deal in my eyes.

I have included code snippets below everything I do, although for me the code itself is not noticeable or even about what I ask. Moreso is interested in my methodology, I will pester code help later if my strategy is incorrect / ineffective.

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 1

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

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

Answer 2

Please check the code below. It is automatically converted as a DataTable automatically.

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

Answer 3

If you are trying to load a DataTable , use the SqlDataAdapter instead:

 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 do not even need to define columns. Just create a DataTable and Fill .

Here cString is your connection string, and sql is the stored procedure command.

Answer 4

As Sagi said in his 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 set of results (if any).

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

Answer 5

I also studied this, and by comparing the SqlDataAdaptor.Fill method with the SqlDataReader.Load functions, I found that the SqlDataAdaptor.Fill method is more than twice as fast with the result sets that I used

Used code:

  [TestMethod] public void SQLCommandVsAddaptor() { long adaptorFillLargeTableTime, readerLoadLargeTableTime, adaptorFillMediumTableTime, readerLoadMediumTableTime, adaptorFillSmallTableTime, readerLoadSmallTableTime, adaptorFillTinyTableTime, 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 adaptorFillLargeTableTime = MeasureExecutionTimeMethod(sconn, LargeTableToFill, ExecuteDataAdapterFillStep); readerLoadLargeTableTime = MeasureExecutionTimeMethod(sconn, LargeTableToFill, ExecuteSqlReaderLoadStep); // medium data set measurements adaptorFillMediumTableTime = MeasureExecutionTimeMethod(sconn, MediumTableToFill, ExecuteDataAdapterFillStep); readerLoadMediumTableTime = MeasureExecutionTimeMethod(sconn, MediumTableToFill, ExecuteSqlReaderLoadStep); // small data set measurements adaptorFillSmallTableTime = MeasureExecutionTimeMethod(sconn, SmallTableToFill, ExecuteDataAdapterFillStep); readerLoadSmallTableTime = MeasureExecutionTimeMethod(sconn, SmallTableToFill, ExecuteSqlReaderLoadStep); // tiny data set measurements adaptorFillTinyTableTime = 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", adaptorFillLargeTableTime); 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", adaptorFillMediumTableTime); 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", adaptorFillSmallTableTime); 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", adaptorFillTinyTableTime); 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)) { // adaptor 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:

10,000 lines:
Sql Data Adapter 100x table fill speed 10,000 rows: 11,782 milliseconds
Sql Data Reader 100 times table download speed 10,000 rows: 26047 milliseconds
1000 lines:
Sql Data Adapter 100x table fill speed 1000 rows: 984 milliseconds
Sql Data Reader 100x table load speed 1000 rows: 2031 milliseconds
100 lines:
Sql Data Adapter 100x table fill speed 100 rows: 125 milliseconds
Sql Data Reader 100 times table download speed 100 rows: 235 milliseconds
10 lines:
Sql Data Adapter 100x table fill speed 10 rows: 32 milliseconds
Sql Data Reader 100 times table download speed 10 rows: 93 milliseconds

For performance issues, using the SqlDataAdaptor.Fill method is much more efficient. Therefore, if you do not want to shoot in the leg, use this. It works faster for small and large datasets.