How to populate a DataTable using a DataReader

Question

I want to populate a DataTable using a DataReader.

I created an object like this

SqlDataReader dr = cmd.ExecuteReader(); if(dr.HasRows) { } 

Answer 1

If all you need is a ReadOnly DataTable for reports or the web, try the following:

  conn = new SqlConnection(connString); string query = "SELECT * FROM Customers"; SqlCommand cmd = new SqlCommand(query, conn); conn.Open(); SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); DataTable dt = new DataTable(); dt.Load(dr); 

Credit at which he must: http://www.dotnetcurry.com/showarticle.aspx?ID=143

Answer 2

DataTable.load () can be used for a general approach.

 do { var table = new DataTable(); table.Load(reader); dataset.Tables.Add(table); } while(!reader.IsClosed); 

Answer 3

You can get the schema table from SqlDataReader dr to get the column names, save the names in List<string> and add them as columns to the new DataTable , and then populate this DataTable using indexing on dr with the names from the list:

 DataSet ds = new DataSet(); DataTable dtSchema = dr.GetSchemaTable(); DataTable dt = new DataTable(); List<DataColumn> listCols = new List<DataColumn>(); List<DataColumn> listTypes = 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"])); listCols.Add(column); listTypes.Add(drow["DataType"].ToString()); // necessary in order to record nulls dt.Columns.Add(column); } } // Read rows from DataReader and populate the DataTable if (dr.HasRows) { while (dr.Read()) { DataRow dataRow = dt.NewRow(); for (int i = 0; i < listCols.Count; i++) { if (!dr.IsDBNull[i]) { // If your query will go against a table with null CLOB fields // and that column is the 5th column... if (strSQL == "SELECT * FROM TableWithNullCLOBField" && i == 4) dataRow[((DataColumn)listCols[i])] = dr.GetOracleClob(i).Value; // If you might have decimal values of null... // I found dr.GetOracleDecimal(i) and dr.GetDecimal(i) do not work else if (listTypes[i] == System.Decimal) dataRow[((DataColumn)listCols[i])] = dr.GetFloat(i); else dataRow[((DataColumn)listCols[i])] = dr[i]; // <-- gets index on dr } else // value was null { byte[] nullArray = new byte[0]; switch (listTypes[i]) { case "System.String": dataRow[((DataColumn)listCols[i])] = String.Empty; break; case "System.Decimal": case "System.Int16": // Boolean case "System.Int32": // Number dataRow[((DataColumn)listCols[i])] = 0; break; case "System.DateTime": dataRow[((DataColumn)listCols[i])] = DBNull.Value; break; case "System.Byte[]": // Blob dataRow[((DataColumn)listCols[i])] = nullArray; break; default: dataRow[((DataColumn)listCols[i])] = String.Empty; break; } } } dt.Rows.Add(dataRow); } ds.Tables.Add(dt); } // Put this after everything is closed if (ds.Tables.Count > 0) return ds.Tables[0]; // there should only be one table if we got results else return null; 

Obviously, you will need your try...catch...finally around it all to handle exceptions and remove your connection, and use the last condition after finally . I found this useful in order to figure out when I had results or not, and avoided problems with dt.Load(dr) , which did not work when there were no results. ds.Fill(adapter) not much better since it failed when I tried to grab a table of 97 columns and about 80 rows using SELECT * FROM MyTable . For me, only code that managed to work in all scenarios.

Originally posted to populate a data table from a data reader using sarathkumar. I presented a summary, tailored it, added null checks and assigned if it is a null value, and added a table to the DataSet and added a DataSet condition at the end.

NOTE. For those using OracleDataReader , I found that you can get an error message if you have an NCLOB or CLOB field that is null in the table / results you are reading. I found that if I checked this column by looking at the i index and made dr.GetOracleClob(i) instead of dr[i] , I stopped getting the exception. See Answer to EF + ODP.NET + CLOB = The value cannot be Null - Parameter name: byteArray? , and I added this condition to the code above when if (!dr.IsDBNull[i]) . Similarly, if you have a null Decimal field, I needed to check it with dr.GetFloat(i); , since neither dr.GetOracleDecimal(i); , and dr.GetDecimal(i); seemed to be correctly placed for a null value.

Answer 4

To populate a DataSet , you can use something like:

 var da = new SqlDataAdapter(); da.SelectCommand = cmd; // your SqlCommand object var ds = new DataSet(); da.Fill(ds);