DataTable To Load A List

Question

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:

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:

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.

Does this help ? http://stackoverflow.com/questions/23697467/returning-datatable-using-entity-framework — Zein Makki, Nov 28 '16 at 06:46
If you really need to use data reader then implement one yourself. But if you just need to bind your data to the report then try to use BindingSource component (I assume they you are using win forms here). BindingSource can be bound with lists. Then you can bind your report with the BindingSouce — , Nov 28 '16 at 06:47
The solution is almost identical to mine @user3185569. Thanks. I'll definitely see the alternatives in the post. @user1681317 never mind. I didn't get it I mean to create `DataReader` by myself. I am already using EF in the project (Web Form) and mixed up legacy Sql and Linq to get going with the DataTable. So I am thinking if there would be any conflict or any alternative to use EF fully. — AT-2017, Nov 28 '16 at 07:21
@AT-2016 You can convert a list to a dataTable, the code for that is available of stackOverflow.. Just search for it. — Zein Makki, Nov 28 '16 at 07:23
I tried to convert the list to `DataTable` @user3185569. But it shows no data in the report. Is there anything else I need to do or I am missing something. Not sure actually - I've edited the post with the code of converting list to `DataTable`. — AT-2017, Nov 29 '16 at 03:49

score 1 · Accepted Answer · answered Nov 30 '16 at 10:27

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

Just pass the list as a parameter of the method or function. — AT-2017, Nov 30 '16 at 12:18

DataTable To Load A List

1 Answers1