How to generate Auto generated Grid in MVC

Gaurav Kapahi

My scenario is something like this that my stored procedure is returning differ column(like name of column and no of column) while I am passing parameter through drop down list i.e.. I do not have knowledge of fields in column at compile time ,do you have any solution how can I achieve this in MVC

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Abhishek Jain13y ago
Accepted answer
Please check the sample code if it helps you in achieving your target.

Regards
AJ
Gaurav Kapahi13y ago
Do you have any Idea of how we can use crystal reports with MVC...........As I heard It doesn't Support MVC?
Gaurav Kapahi13y ago
Thanks Abhishek, your solution is working ............ mean while I got another solution that I like to share with you, ones again thanks a lot :)

XYZ_MVC.Models.XYZEntities db = new Models.XYZEntities();
List selectMatchTypeList = new List();

public ActionResult Index(string MatchTypeName)
{

foreach (MatchTypeMaster MatchType in db.MatchTypeMasters)
{
SelectListItem selectMatchTypes = new SelectListItem
{
Text = MatchType.MatchTypeName,
Value = MatchType.MatchTypeID,

};

selectMatchTypeList.Add(selectMatchTypes);
ViewBag.MatchTypeName = selectMatchTypeList;
}

if (string.IsNullOrEmpty(MatchTypeName))
{
MatchTypeName = "All";
var bugedlist = GetList(MatchTypeName);
return View(bugedlist);
}
else
{
var GridFill = GetList(MatchTypeName);
return View(GridFill);
}

return View();

}

private List ConvertToDictionary(DataTable dtObject)
{
var columns = dtObject.Columns.Cast();

var dictionaryList = dtObject.AsEnumerable()
.Select(dataRow => columns
.Select(column =>
new { Column = column.ColumnName, Value = dataRow[column] })
.ToDictionary(data => data.Column, data => data.Value)).ToList().ToArray();

return dictionaryList.ToList();
}

public List GetList(String MatchTypeName)
{
DataSet ds = new DataSet();
//var modelList = new List();
// var result = new List();

using (SqlConnection conn = new SqlConnection(@"Server= Server_Name; Uid=sa;pwd=Pass; database=DB_Name"))
{
try
{

#region commented
conn.Open();

SqlCommand dCmd = new SqlCommand("SP_WEBBASEDREPorTS", conn);

dCmd.CommandType = CommandType.StoredProcedure;

dCmd.Parameters.Add(new SqlParameter("@MatchType", MatchTypeName));

dCmd.Parameters.Add(new SqlParameter("@ModeFlag", "All"));

SqlDataAdapter da = new SqlDataAdapter(dCmd);

DataTable table = new DataTable();
ds.Clear();

da.Fill(ds);
conn.Close();

var das = ds.Tables[0].AsEnumerable();

#endregion
}
catch
{

}
}

return ConvertToDictionary(ds.Tables[0]);

}
}

VIEW Code:

@using System.Dynamic
@model List
@{
var result = new List();

foreach (var emprow in Model)
{
var row = (IDictionary)new ExpandoObject();
Dictionary eachEmpRow = (Dictionary)emprow;

foreach (KeyValuePair keyValuePair in eachEmpRow)
{
row.Add(keyValuePair);
}
result.Add(row);
}
var grid = new WebGrid(result);
}

@using (@Html.BeginForm("Index", "ICCRpt", FormMethod.Get))
{

@Html.DropDownList("MatchTypeName", "Select All");

}

@if (@Model != null)
{
@grid.GetHtml(tableStyle: "grid", headerStyle: "head", alternatingRowStyle: "alt");
}
Abhishek Jain13y ago
Your Updated Code, i have made some changes may be you can try with this also:

public ActionResult Index(string MatchTypeName)
{
foreach (MatchTypeMaster MatchType in db.MatchTypeMasters)
{
SelectListItem selectMatchTypes = new SelectListItem
{
Text = MatchType.MatchTypeName,
Value = MatchType.MatchTypeID,
};

selectMatchTypeList.Add(selectMatchTypes);
ViewBag.MatchTypeName = selectMatchTypeList;
}

if (string.IsNullOrEmpty(MatchTypeName))
{
MatchTypeName = "XYZ";
var bugedlist = GetList(MatchTypeName);
return View(bugedlist);
}
else
{
var GridFill = GetDataTable(MatchTypeName);
return View(GridFill);
}

return View();
}

public DataTable GetDataTable(String MatchTypeName)
{
DataTable table = new DataTable();
using (SqlConnection conn = new SqlConnection(@"Server= server_name; Uid=xyz;pwd=pass; database=db_name"))
{
try
{

conn.Open();

SqlCommand dCmd = new SqlCommand("SP_WEBBASEDREPorTS", conn);

dCmd.CommandType = CommandType.StoredProcedure;

dCmd.Parameters.Add(new SqlParameter("@MatchType", MatchTypeName));

dCmd.Parameters.Add(new SqlParameter("@ModeFlag", "Batting Industry Average"));

SqlDataAdapter da = new SqlDataAdapter(dCmd);
DataSet ds = new DataSet();

ds.Clear();

da.Fill(table);
conn.Close();
}
catch
{

}
}

return table;
}

CSHTML:

@using GridMvc.Html;

@model System.Data.DataTable
@{
ViewBag.Title = "Index";
Layout = "~/Views/Shared/_Layout.cshtml";
}

@*
@Html.ActionLink("Create New", "Create")
*@

@using (@Html.BeginForm("Index", "My", FormMethod.Get))
{

@Html.DropDownList("MatchTypeName", "Select All");

}

@{

var result = new List();
foreach (System.Data.DataRow row in Model.Rows)
{
var obj = (IDictionary)new System.Dynamic.ExpandoObject();
foreach (System.Data.DataColumn col in Model.Columns)
{
obj.Add(col.ColumnName, row[col.ColumnName]);
}
result.Add(obj);
}

var grid = new WebGrid(result, defaultSort: "Name");

}

@grid.GetHtml()
Gaurav Kapahi13y ago
Many Thanks for sending the code You are right My stored procedure is like this that it is showing the column according to parameter i am passing through dropdownlist then only it is filling datatable so I directly wanted to fill grid through data table.. can you please send me the sample code for dynamic list with expando object as I also did like this but not able to fetch the grid may be I am doing something wrong. Here is my code:

public ActionResult Index(string MatchTypeName)
{

foreach (MatchTypeMaster MatchType in db.MatchTypeMasters)
{
SelectListItem selectMatchTypes = new SelectListItem
{
Text = MatchType.MatchTypeName,
Value = MatchType.MatchTypeID,

};

selectMatchTypeList.Add(selectMatchTypes);
ViewBag.MatchTypeName = selectMatchTypeList;
}

if (string.IsNullOrEmpty(MatchTypeName))
{
MatchTypeName = "XYZ";
var bugedlist = GetList(MatchTypeName);
return View(bugedlist);
}
else
{
var GridFill = GetList(MatchTypeName);
return View(GridFill);
}

return View();

}

public List GetList(String MatchTypeName)
{
//var modelList = new List();
var result = new List();

using (SqlConnection conn = new SqlConnection(@"Server= server_name; Uid=xyz;pwd=pass; database=db_name"))
{
try
{

conn.Open();

SqlCommand dCmd = new SqlCommand("SP_WEBBASEDREPorTS", conn);

dCmd.CommandType = CommandType.StoredProcedure;

dCmd.Parameters.Add(new SqlParameter("@MatchType", MatchTypeName));

dCmd.Parameters.Add(new SqlParameter("@ModeFlag", "Batting Industry Average"));

SqlDataAdapter da = new SqlDataAdapter(dCmd);
DataSet ds = new DataSet();
DataTable table = new DataTable();
ds.Clear();

da.Fill(table);
conn.Close();

foreach (DataRow row in table.Rows)
{
var obj = (IDictionary)new ExpandoObject();
foreach (DataColumn col in table.Columns)
{
obj.Add(col.ColumnName, row[col.ColumnName]);
}
result.Add(obj);
}
}
catch
{

}
}

return result;

-----------------------------------------------------------------------
CSHTML Code is:

@using GridMvc.Html;

@model IEnumerable(LearningMVC.User)
@{
ViewBag.Title = "Index";
Layout = "~/Views/Shared/_Layout.cshtml";
}

@*
@Html.ActionLink("Create New", "Create")
*@

@using (@Html.BeginForm("Index", "My", FormMethod.Get))
{

@Html.DropDownList("MatchTypeName", "Select All");

}

@Html.Grid(Model).AutoGenerateColumns().Sortable(true)

Improve question Permalink
Abhishek Jain13y ago
Ok, Sorry about that, may be the below description will help you

If you want that whateever is in your list just gets binded to the WebGrid then, please refer this code:

@{

var grid = new WebGrid(Model, defaultSort: "Name");
}

@grid.GetHtml()

If you need that some columns are static and some will be decided based on some other parameters, then please refer this code:

@{

var grid = new WebGrid(Model, defaultSort: "Name");
List cols = new List();

cols.Add(grid.Column("Id", header: "Id",

format: @@item.Id));

cols.Add(grid.Column("Name", header: "Name",

format: @@item.Name));

if (@ViewBag.IsDescriptionRequired)
{
cols.Add(grid.Column("Description", header: "Description",

format: @@item.Description));
}
}

  @grid.GetHtml(tableStyle: "table",
htmlAttributes: new { id = "MyGrid" },
columns: cols
)

If you want to bind a WebGrid directly to a DataTable, it means DataTable only contains the columns which needs to be shown into the grid right, but the data source of the WebGrid is of type IEnumerable so you have to convert the DataTable into an IEnumerable collection. So you have to do something like this:

@model System.Data.DataTable

@{

var gridData = new List();
foreach (System.Data.DataRow row in Model.Rows)
{
var expandoObject = (IDictionary)new System.Dynamic.ExpandoObject();
foreach (System.Data.DataColumn column in Model.Columns)
{
  expandoObject.Add(column.ColumnName, row[column.ColumnName]);
}
  gridData.Add(expandoObject);
}

var grid = new WebGrid(gridData, defaultSort: "Name");

}

@grid.GetHtml()

For your reference i have created a dummy DataTable on Server side but you can get this DataTable from anywhere you want, Dummy data is something like this:

DataTable dt = new DataTable();
dt.Columns.Add("Id");
dt.Columns.Add("Name");

DataRow dr1 = dt.NewRow();
dr1["Id"] = 1;
dr1["Name"] = "Name1";

DataRow dr2 = dt.NewRow();
dr2["Id"] = 2;
dr2["Name"] = "Name2";

dt.Rows.Add(dr1);
dt.Rows.Add(dr2);

return View(dt);

Hope it helps...

Regards
AJ
Gaurav Kapahi13y ago
Thanks for reply,I need a solution in which I do not need to pass column name but you passed that in both controller and view eg: like we are binding dataset directly in asp .net Gridview i need the same in MVC
Abhishek Jain13y ago
Please try the following code:

@{

var grid = new WebGrid(Model, defaultSort: "Name");

}

@grid.GetHtml(columns: grid.Columns(

grid.Column("Id", header: "Id",

format: @@item.Id),

grid.Column("Name", header: "Name",

format: @@item.Name),

grid.Column("Description", header: "Description",

format: @@item.Description)
)
)

I have also attached the sample code for your reference, the code is working on a sample data.