public static DataSet GetDataSet(GridOptions options, string query, string orderBy) { //string sql = "SELECT * FROM " + tableName; DataSet gridDataSet = new DataSet(); var _connection = new CommonConnection(); string condition = ""; try { query = query.Replace(';', ' '); string sqlQuery = options != null ? GridQueryBuilder <T> .Query(options, query, orderBy, condition) : query; if (!string.IsNullOrEmpty(condition)) { condition = " WHERE " + condition; } var condition1 = options != null ? GridQueryBuilder <T> .FilterCondition(options.filter) : ""; if (!string.IsNullOrEmpty(condition1)) { if (!string.IsNullOrEmpty(condition)) { condition += " And " + condition1; } else { condition = " WHERE " + condition1; } } DataTable dataTable = _connection.GetDataTable(sqlQuery); gridDataSet.Tables.Add(dataTable); var sqlCount = "SELECT COUNT(*) FROM (" + query + " ) As tbl " + condition; int totalCount = Convert.ToInt32(_connection.ExecuteScalar(sqlCount)); DataTable totalCountDt = new DataTable("TotalCount"); DataColumn col = new DataColumn("totalCount"); col.DataType = Type.GetType("System.Int32"); totalCountDt.Columns.Add(col); DataRow dr = totalCountDt.NewRow(); dr["totalCount"] = totalCount; totalCountDt.Rows.Add(dr); gridDataSet.Tables.Add(totalCountDt); return(gridDataSet); } catch (Exception ex) { throw ex; } }
public static GridEntity <T> DataSourceWithDateQuary(GridOptions options, string query, string orderBy, string condition, string withDateQuary) { //string sql = "SELECT * FROM " + tableName; var _connection = new CommonConnection(); try { query = query.Replace(';', ' '); string sqlQuery = options != null ? GridQueryBuilder <T> .Query(options, query, orderBy, condition) : query; if (!string.IsNullOrEmpty(condition)) { condition = " WHERE " + condition; } var condition1 = options != null ? GridQueryBuilder <T> .FilterCondition(options.filter) : ""; if (!string.IsNullOrEmpty(condition1)) { if (!string.IsNullOrEmpty(condition)) { condition += " And " + condition1; } else { condition = " WHERE " + condition1; } } if (withDateQuary != "") { sqlQuery = withDateQuary + sqlQuery; } DataTable dataTable = _connection.GetDataTable(sqlQuery); var sqlCount = withDateQuary + " SELECT COUNT(*) FROM (" + query + " ) As tbl " + condition; int totalCount = Convert.ToInt32(_connection.ExecuteScalar(sqlCount)); var dataList = (List <T>)ListConversion.ConvertTo <T>(dataTable); var result = new GridResult <T>().Data(dataList, totalCount); return(result); } catch (Exception ex) { throw ex; } }
private static void GetGridPagingQuery(GridOptions options, string query, string orderBy, string condition, out StringBuilder gridQuery, out StringBuilder totalQuery, DatabaseType databaseType) { try { gridQuery = new StringBuilder(); totalQuery = new StringBuilder(); query = query.Replace(';', ' '); string strQuery = options != null ? GridQueryBuilder <T> .Query(options, query, orderBy, condition) : query; if (!string.IsNullOrEmpty(condition)) { condition = " WHERE " + condition; } var condition1 = options != null ? GridQueryBuilder <T> .FilterCondition(options.filter) : ""; if (!string.IsNullOrEmpty(condition1)) { if (!string.IsNullOrEmpty(condition)) { condition += " And " + condition1; } else { condition = " WHERE " + condition1; } } string tQuery = ""; if (databaseType == DatabaseType.SQL) { tQuery = "SELECT COUNT(*) FROM (" + query + " ) As tbl " + condition; } else if (databaseType == DatabaseType.Oracle) { tQuery = "SELECT COUNT(*) FROM (" + query + " )" + condition; } gridQuery.Append(strQuery); totalQuery.Append(tQuery); } catch (Exception ex) { throw ex; } }