[C#] Populate data table from data reader


Answers

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

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



How do I fill a DataTable using DataReader

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




MVC5 / C# - Cannot perform runtime binding on a null reference

There are two issues in your code:

Consider this:

public ActionResult Index()
{
   int n = 0;
   ViewBag.speakers[n] = 5;
   return View();
}

This simplified piece of code throws Cannot perform runtime binding on a null reference since speakers is not defined (null reference).

You can fix it by defining speakers in the dynamic ViewBag before the loop:

ViewBag.speakers = new List<string>();

The second issue:

ViewBag.speakers[n] = speakers;

speakers in your code is a List, you might want to define ViewBag.speakers as a List<List<string>> and call .Add(speakers) instead of accessing using an index (you might get index was out of range)




ViewBag is a dynamic object and when you try to do assignments to its properties, the null check will happen in runtime. You get this error because ViewBag.speakers is either null, or it throws an exception while trying to access its nth slot with indexer (maybe its size is less than n, or it doesn't define an indexer at all). You should initialize ViewBag.speakers before adding elements to it.

var query = "SELECT Id, UserName, List_Order, LoggedIn " + 
            "FROM AspNetUsers" +
            "WHERE LoggedIn = 1" + 
            "ORDER BY List_Order ASC";

var conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
var cmd = new SqlCommand(query, conn);   
conn.Open();
var rdr = cmd.ExecuteReader();
ViewBag.speakers = new List<string[]>();
while(rdr.Read())
{
    if (Convert.ToString(rdr["UserName"]) != null)
    {
        var speakers = new string[4] {
            Convert.ToString(rdr["Id"]),
            Convert.ToString(rdr["UserName"]),
            Convert.ToString(rdr["List_Order"]),
            Convert.ToString(rdr["LoggedIn"]) 
        };
        ViewBag.speakers.Add(speakers);
    }
}



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.




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



We met this problem in our project an hour ago and found a solution. It is generating this error because of null values in CLOB caolumn. We have a CLOB column and it is Nullable in database. In EntityFramework model it is String but not Nullable. We changed column's Nullable property to True in EF model and it fixed problem.