public GridModel LoadGridModel(GridParameter parameters, string cmdText) { IDataReader reader = null; try { if (parameters.ExportType.IsNotNullOrEmpty()) { return(LoadGridExport(parameters, cmdText)); } GridModel gridModel = new GridModel(); List <Dictionary <string, object> > dictionaryList = new List <Dictionary <string, object> >(); List <string> stringList = new List <string>(); int num = 0; cmdText = MakeSearchQuery(parameters, cmdText); if (parameters.searchBy.IsNotNullOrEmpty() && parameters.search.IsNotNullOrEmpty()) { reader = ExecuteReader(cmdText, (object)parameters.search); } else { reader = ExecuteReader(cmdText); } for (int i = 0; i < reader.FieldCount; ++i) { string name = reader.GetName(i); if (name != "TotalRows") { stringList.Add(name); } } if (reader.Read()) { if (parameters.ServerPagination) { num = Convert.ToInt32(reader["TotalRows"]); } dictionaryList.Add(GetFields(stringList, reader)); } while (reader.Read()) { dictionaryList.Add(GetFields(stringList, reader)); } gridModel.rows = dictionaryList; gridModel.total = parameters.ServerPagination ? num : dictionaryList.Count; return(gridModel); } finally { reader.CloseReader(); } }
private GridModel LoadGridExport(GridParameter parameters, string commandText) { IDataReader reader = null; try { GridModel gridModel = new GridModel(); commandText = MakeSearchQuery(parameters, commandText); reader = ExecuteReader(commandText); gridModel.source = DbUtil.DataReaderToDataSet(reader); return(gridModel); } finally { reader.CloseReader(); } }
private static string MakeSearchQuery(GridParameter parameters, string sql) { string str1 = string.Empty; string str2 = string.Empty; string str3 = string.Empty; if (parameters.searchBy.IsNotNullOrEmpty() && parameters.search.IsNotNullOrEmpty()) { str1 = string.Format("WHERE {0} LIKE @0", parameters.searchBy); } if (parameters.sort.IsNotNullOrEmpty() && parameters.order.IsNotNullOrEmpty()) { str2 = string.Format("ORDER BY {0} {1}", parameters.sort, parameters.order); } if (parameters.ServerPagination) { str3 = string.Format("OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", parameters.offset, parameters.Limit); } return(string.Format("SELECT *, COUNT(*) OVER () AS TotalRows\r\n FROM (\r\n\t {0}\r\n ) AS TAB\r\n {1}\r\n {2}\r\n {3}", sql, str1, str2, str3)); }