Populate data table from data reader

Question

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

User · Accepted 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

User · Answer

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

User · Answer

Please check the below code  Automatically it will convert as 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 lt  gt              List lt DataColumn gt  listCols   new List lt DataColumn gt                  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  lt  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

User · Answer

If you re trying to load a DataTable  then leverage 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 don t even need to define the columns  Just create the DataTable and Fill it   Here  cString is your connection string and sql is the stored procedure command

User · Answer

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  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 lt SqlConnection  string gt  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  lt  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

[c#] Populate data table from data reader

Examples related to c#

Examples related to .net

Examples related to ado.net