how to bind datatable to webgrid using MVC?

Question

This is my first post. help me. how to bind datatable to webgrid? My code:

SqlConnection con = new SqlConnection(CS);
SqlCommand cmd = new SqlCommand("select * from candidate", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return View(dt);

I want to bind datatable to webgrid..help me...

Pyemia

This is not how MVC works and is a hold over from how WebForms operates - please review some tutorials on .NET MVC usage - asp.net/mvc/tutorials – Contagium

Yes, please look into LINQ and EntityFramework as these are the ORMs used with MVC. – Billfish

pls give me any tutorial link... – Pyemia

Answer 1

This is best solution I found :) hope it can help you:

SqlConnection con = new SqlConnection(CS);
SqlCommand cmd = new SqlCommand("select * from candidate", con);

DataTable dt = new DataTable();
SqlDataAdapter a = new SqlDataAdapter(cmd)
a.Fill(dt);

var result = new List<dynamic>();
foreach (DataRow row in dt.Rows)
{
    var obj = (IDictionary<string, object>)new ExpandoObject();
    foreach (DataColumn col in dt.Columns)
    {
        obj.Add(col.ColumnName, row[col.ColumnName]);
    }
    result.Add(obj);
}

WebGrid grid = new WebGrid(Model, canPage: true, rowsPerPage: 15);

Then in view you can use:

@grid.GetHtml(htmlAttributes: new { id = "empTable" },
            tableStyle: "table table-striped table-hover",
            headerStyle: "header",
            alternatingRowStyle: "alt",
            selectedRowStyle: "select",
            columns: grid.Columns(
                grid.Column("col1name", "Column title"),
                grid.Column("col2name", "Column2 title")
     ))

where grid is your WebGrid grid variable.

Aesthetic

Answer 2

I had to use a DataTable as the data was coming from 3rd party code via a DataTable. I had issues getting WebGrid to detect/reflect columns that were added to the DataTable. Converting to a dynamic list as per mrfazolka's answer worked. I ended up making it into a static method:

   public static List<dynamic> DataTable2List(DataTable dt)
   {
      var list = new List<dynamic>();
      foreach (DataRow row in dt.Rows)
      {
         var obj = (IDictionary<string, object>) new ExpandoObject();
         foreach (DataColumn col in dt.Columns)
         {
            obj.Add(col.ColumnName, row[col.ColumnName]);
         }
         list.Add(obj);
      }
      return list;
   }

Snipe