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