public void SyncGridDefinitions(string appDataPath)
{
//Sync the list of grids
using (jqGridExampleDbContext db = new jqGridExampleDbContext())
{
IEnumerable<string> gridProcedures = db.GetGridProcedures();
foreach (string procedure in gridProcedures)
{
Grid savedGrid = GetGridDefinition(procedure, appDataPath);
IEnumerable<GridParameter> parameters = db.DeriveParameters(procedure);
Dictionary<string, object> parameterList = new Dictionary<string, object>();
foreach (GridParameter p in parameters)
{
parameterList.Add(p.Name, string.Empty);
}
IEnumerable<GridColumn> columns = db.DeriveColumnList(procedure, parameterList);
if (savedGrid == null)
{
//There is no existing file so build the entire grid model and persist it
savedGrid = new Grid();
savedGrid.Name = procedure;
savedGrid.StoredProcedure = procedure;
savedGrid.Title = procedure;
savedGrid.Columns = columns.ToList();
savedGrid.Parameters = parameters.ToList();
SaveGridDefinition(procedure, appDataPath, savedGrid);
}
else
{
//This is an existing file. Check for new or removed columns and parameters
var removedColumns = from col in savedGrid.Columns
where !columns.Any(c => c.Name == col.Name)
select col;
foreach (var col in removedColumns)
{
savedGrid.Columns.Remove(col);
}
var addedColumns = from col in columns
where !savedGrid.Columns.Any(c => c.Name == col.Name)
select col;
foreach (var col in addedColumns)
{
savedGrid.Columns.Add(col);
}
var removedParameters = from param in savedGrid.Parameters
where !parameters.Any(p => p.Name == param.Name)
select param;
foreach (var param in removedParameters)
{
savedGrid.Parameters.Remove(param);
}
var addedParameters = from param in parameters
where !savedGrid.Parameters.Any(p => p.Name == param.Name)
select param;
foreach (var param in addedParameters)
{
savedGrid.Parameters.Add(param);
}
SaveGridDefinition(procedure, appDataPath, savedGrid);
}
}
}
}
public ActionResult GridData(string sidx, string sord, int page, int rows)
{
//parameters added by jqGrid that we want to ignore
string[] parametersToIgnore = { "sidx", "sord", "page", "rows", "_search", "GridName", "nd" };
string gridName = this.Request.QueryString.Get("GridName");
//populate a list of stored procedure parameters
IDictionary<string,object> parameters = new Dictionary<string, object>();
foreach (string key in this.Request.QueryString.Keys)
{
if (!parametersToIgnore.Contains(key) && !string.IsNullOrEmpty(key))
{
parameters.Add(key, this.Request.QueryString[key]);
}
}
//run the stored procedure and return the data table
DataTable table = new DataTable();
using (jqGridExampleDbContext context = new jqGridExampleDbContext())
{
table = context.GetDataTable(gridName, parameters);
}
//Grab the page, page size
int pageIndex = Convert.ToInt32(page) - 1;
int pageSize = rows;
if (pageSize > table.Rows.Count)
pageIndex = 0;
int totalRecords = table.Rows.Count;
int totalPages = (int)Math.Ceiling((float)totalRecords / (float)pageSize);
//Only sort, skip and take if we have row counts
if (table.Rows.Count > 0)
{
string sort = string.Empty;
if (sidx.Trim().EndsWith(","))
{
//sidx looks like "GroupingField asc, "
sort = sidx.Trim().TrimEnd(',');
}
else if (!string.IsNullOrEmpty(sidx))
{
sort += sidx + " " + sord;
}
table.DefaultView.Sort = sort;
table = table.DefaultView.ToTable();
table.AcceptChanges();
table = table.AsEnumerable().Skip(pageIndex * pageSize).Take(pageSize).CopyToDataTable();
}
table.TableName = gridName;
GridManager gridManager = new GridManager();
string appDataPath = Server.MapPath("~/App_Data");
Grid model = gridManager.GetGridDefinition(gridName, appDataPath);
//create the json for the rows of data
StringBuilder jsonRows = new StringBuilder();
jsonRows.Append("\"rows\" : [ ");
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow dr = table.Rows[i];
if (i > 0)
jsonRows.Append(",");
jsonRows.AppendFormat("{{\"id\":\"{0}\",\"cell\":[", i.ToString());
bool firstColumn = true;
foreach (GridColumn col in model.Columns)
{
if (!firstColumn)
jsonRows.Append(",");
if (col.DataType == typeof(DateTime).ToString())
jsonRows.AppendFormat("\"{0}\"", DateTime.Parse(dr[col.Name].ToString()).ToString("MM/dd/yyyy hh:mm tt"));
else
jsonRows.AppendFormat("\"{0}\"", dr[col.Name]);
firstColumn = false;
}
jsonRows.Append("]}");
}
jsonRows.Append("]");
//create the returned json structure
StringBuilder jsonData = new StringBuilder();
jsonData.AppendFormat("{{ \"page\":\"{0}\", \"total\":\"{1}\", \"records\":\"{2}\", {3} }}", pageIndex + 1, totalPages, totalRecords, jsonRows.ToString());
return Content(jsonData.ToString(), "application/json");
}
