How do I fill a DataTable using DataReader

I want to fill DataTable using DataReader.

I have created object like this

SqlDataReader dr = cmd.ExecuteReader();

if(dr.HasRows)
{

}
开发者_运维技巧

If all you want is a ReadOnly DataTable for reporting or web, try this:

  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 where it's due: http://www.dotnetcurry.com/showarticle.aspx?ID=143

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

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

You can get the Schema Table from your SqlDataReader dr to get the column names, save the names to a List<string> and add them as columns on a new DataTable, then fill that 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'd need your try...catch...finally block around it all to handle exceptions and disposing your connection, and use the last condition after the finally. I found this helpful in order to handle finding out when I had results or not, and avoided issues with dt.Load(dr) that was failing when there were no results. ds.Fill(adapter) wasn't much better, as it failed when I tried to grab a table of 97 columns and about 80 rows with SELECT * FROM MyTable. Only the code above managed to work in all scenarios, for me.

Originally posted on Populate data table from data reader by sarathkumar. I provided the summary, condensed it, added the null checks and assigning if it's a null value, and added the table to a DataSet and added the DataSet condition at the end.

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

To fill a DataSet, you can use something like:

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