/// <summary> /// get data /// </summary> /// <param name="oGrid">grid configuration object</param> /// <returns>returns datatable</returns> public DataTable GetDataTable(GridParams oGrid) { DatabaseHelper dbHelper = new DatabaseHelper(ConfigurationSettings.Default); dbHelper.AddParameter("@TableName", oGrid.TableName); dbHelper.AddParameter("@ColumnsName", oGrid.ColumnsName); dbHelper.AddParameter("@SortOrder", GetSortOrder()); dbHelper.AddParameter("@SortColumn", GetSortColumn(oGrid.SortColumn)); dbHelper.AddParameter("@PageNumber", GetPageNumber()); dbHelper.AddParameter("@RecordPerPage", GetRecordPerPage()); dbHelper.AddParameter("@WhereClause", GetWhereClause(oGrid.WhereClause)); DataSet ds = dbHelper.ExecuteDataSet("GetDataForGridWeb", CommandType.StoredProcedure); dbHelper.Dispose(); return(ds.Tables[0]); }
/// <summary> /// export data to specific format /// </summary> /// <param name="oGrid">grid configuration object</param> public void Export(GridParams oGrid) { DataTable dt = GetDataTable(oGrid); oGrid.ExportedColumns = Convert.ToString(_HttpContext.Request.Form["Columns"]).Replace("null,", ""); if (string.Equals(Convert.ToString(_HttpContext.Request.Form["mode"]), "banner" , StringComparison.OrdinalIgnoreCase) || string.Equals(Convert.ToString(_HttpContext.Request.Form["mode"]), "category" , StringComparison.OrdinalIgnoreCase) || string.Equals(Convert.ToString(_HttpContext.Request.Form["mode"]), "warrantymaster" , StringComparison.OrdinalIgnoreCase) || string.Equals(Convert.ToString(_HttpContext.Request.Form["mode"]), "eventmaster", StringComparison.OrdinalIgnoreCase)) { var columnList = Convert.ToString(_HttpContext.Request.Form["Columns"]).Split(',').ToList(); for (int i = 0; i < columnList.Count; i++) { if (columnList[i].Contains("photo", StringComparison.OrdinalIgnoreCase) || columnList[i].Contains("prebanner", StringComparison.OrdinalIgnoreCase) || columnList[i].Contains("photo", StringComparison.OrdinalIgnoreCase) || columnList[i].Contains("purchaseproof", StringComparison.OrdinalIgnoreCase)) { columnList.RemoveAt(i); } } oGrid.ExportedColumns = string.Join(",", columnList); } string[] c = oGrid.ExportedColumns.Split(','); string[] s = oGrid.ExportedColumns.Split(','); for (int i = 0; i < c.Length; i++) { c[i] = c[i].Split(' ')[0]; } DataTable dtTemp = dt.Copy(); int j = 0; foreach (DataColumn dc in dtTemp.Columns) { if (!c.Contains(dc.ColumnName)) { dt.Columns.Remove(dc.ColumnName); } else { dt.Columns[s[j].Split(' ')[0]].SetOrdinal(j); dt.Columns[s[j].Split(' ')[0]].ColumnName = s[j].Split('[').Length > 1 ? s[j].Split('[')[1].Replace("]", "") : s[j]; j++; } } if (dt.Columns.Contains("TotalRows")) { dt.Columns.Remove("TotalRows"); } string type = Convert.ToString(_HttpContext.Request.Form["type"]); if (string.Equals(type, "excel", StringComparison.OrdinalIgnoreCase)) { dt.ExportToExcel(oGrid.ExportedFileName); } else if (string.Equals(type, "word", StringComparison.OrdinalIgnoreCase)) { dt.ExportToWord(oGrid.ExportedFileName); } else if (string.Equals(type, "pdf", StringComparison.OrdinalIgnoreCase)) { dt.ExportToPdf(oGrid.ExportedFileName); } else if (string.Equals(type, "csv", StringComparison.OrdinalIgnoreCase)) { dt.ExportToCSV(oGrid.ExportedFileName); } else { dt.ExportToExcel(oGrid.ExportedFileName); } }
/// <summary> /// return json as a datatable response /// </summary> /// <param name="oGrid">grid configuration object</param> /// <returns>retuns datatable as json response</returns> public string GetJson(GridParams oGrid, DataTableAjaxPostModel AjaxPostModel) { DataTable dt = GetDataTable(oGrid); return(dt.GetJsonForDataTableJS(AjaxPostModel)); }