AT-2016 AT-2016 - 10 days ago 4
C# Question

DataTable To Load A List

I've created a stored procedure (SP) and integrated that in the following way that works just fine:

Edited:

private DataTable GetSPResult()
{
int m = Convert.ToInt32(DropDownList1.SelectedValue);
int k = Convert.ToInt32(DropDownList2.SelectedValue);

DataTable ResultsTable = new DataTable();

var context = new DemoEntities();
var con = context.Database.Connection;
var connectionState = con.State;

try
{
using (context)
{
con.Open();

using (var cmd = con.CreateCommand())
{
cmd.CommandText = "MonthlyConsumption"; //Here is the SP
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@Para1", SqlDbType.Int));
cmd.Parameters["@Para1"].Value = m;
cmd.Parameters.Add(new SqlParameter("@Para2", SqlDbType.Int));
cmd.Parameters["@Para2"].Value = k;

using (var reader = cmd.ExecuteReader())
{
ResultsTable.Load(reader);
}
}
}
}

catch (Exception ex)
{
throw ex;
}

finally
{
if (con != null)
{
con.Close();
}
}

return ResultsTable;
}


Finally done the below: On button click, I am able to see data in the report

protected void Button1_Click(object sender, EventArgs e)
{
DataTable dt = GetSPResult();
ReportViewer1.Visible = true;

ReportViewer1.LocalReport.ReportPath = Server.MapPath("Report1.rdlc");
ReportViewer1.LocalReport.DataSources.Clear();
ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("DataSet1", dt));
}


Output:

SP_02

But when I try to convert a list to DataTable with ORM, it throws no exception but no data in the report as follows:

Output:

SP_01

This is the code that I've done so far with ORM - Entity Framework that also works: By the way, I put breakpoint for debugging purpose and it gets the value but doesn't return data in the report

public DataTable GetSPResult()
{
int m = Convert.ToInt32(DropDownList1.SelectedValue);
int k = Convert.ToInt32(DropDownList2.SelectedValue);

DataTable ResultsTable = new DataTable();

var context = new DemoEntities();

using (context)
{
var query = context.MonthlyConsumption(m, k).ToList();

foreach (var item in query)
{
ResultsTable.Columns.Add("Store");
ResultsTable.Columns.Add("Product");
ResultsTable.Columns.Add("Jan");
ResultsTable.Columns.Add("Feb");
ResultsTable.Columns.Add("Mar");
ResultsTable.Columns.Add("Apr");
ResultsTable.Columns.Add("May");
ResultsTable.Columns.Add("Jun");
ResultsTable.Columns.Add("Jul");
ResultsTable.Columns.Add("Aug");
ResultsTable.Columns.Add("Sep");
ResultsTable.Columns.Add("Oct");
ResultsTable.Columns.Add("Nov");
ResultsTable.Columns.Add("Dec");

ResultsTable.Rows.Add(item.StoreName);
ResultsTable.Rows.Add(item.ItemName);
ResultsTable.Rows.Add(item.M1.Value);
ResultsTable.Rows.Add(item.M2.Value);
ResultsTable.Rows.Add(item.M3.Value);
ResultsTable.Rows.Add(item.M4.Value);
ResultsTable.Rows.Add(item.M5.Value);
ResultsTable.Rows.Add(item.M6.Value);
ResultsTable.Rows.Add(item.M7.Value);
ResultsTable.Rows.Add(item.M8.Value);
ResultsTable.Rows.Add(item.M9.Value);
ResultsTable.Rows.Add(item.M10.Value);
ResultsTable.Rows.Add(item.M11.Value);
ResultsTable.Rows.Add(item.M12.Value);
}
}

return ResultsTable;
}


Note and Updated: Could I convert the List to a IDataReader to load it or is there any simple way to make it done? I've seen some of the tutorials where the author has used
foreach
loop to iterate the list and then bind it to the DataTable. But I am just trying to simply load the list to the DataTable.

Answer

Finally got it done. Just converted the list to DataTable using the following method:

public DataTable ToDataTable<T>(List<T> items)
{
   DataTable ResultsTable = new DataTable(typeof(T).Name);

   //Gets all the properties
   PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

   foreach (PropertyInfo prop in Props)
   {
      //Sets column names as Property names
      ResultsTable.Columns.Add(prop.Name);
   }

   foreach (T item in items)
   {
      var values = new object[Props.Length];

      for (int i = 0; i < Props.Length; i++)
      {
         //Inserts property values to datatable rows
         values[i] = Props[i].GetValue(item, null);
      }

      ResultsTable.Rows.Add(values);
   }

   return ResultsTable;
}