private static void GetGridPagingQuery(GridOptions options, string query, string orderBy, string condition,
out StringBuilder gridQuery, out StringBuilder totalQuery)
{
//string sql = "SELECT * FROM " + tableName;
// var _connection = new CommonConnection();
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 = "";
tQuery = "SELECT COUNT(*) FROM (" + query + " ) As tbl " + condition;
gridQuery.Append(strQuery);
totalQuery.Append(tQuery);
}
catch (Exception ex)
{
throw ex;
}
finally
{
//_connection.Close();
}
}
public GridEntity <T> GenericDataSource(GridOptions options, string query, string orderBy,
string condition)
{
// var _connection = new CommonConnection();
StringBuilder gridQuery;
StringBuilder totalQuery;
GetGridPagingQuery(options, query, orderBy, condition, out gridQuery, out totalQuery);
DataTable dataTable = _connection.GetDataTable(gridQuery.ToString());
int totalCount = _connection.GetScaler(totalQuery.ToString());
var dataList = (List <T>)GenericListGenerator.GetList <T>(dataTable);
var result = new GridResult <T>().Data(dataList, totalCount);
return(result);
}
public GridEntity <T> GenericDataSource(GridOptions options, string query, string orderBy)
{
return(GenericDataSource(options, query, orderBy, ""));
}
public GridEntity <T> DataSourceWithDateQuary(GridOptions options, string query, string orderBy,
string withQuary)
{
return(DataSourceWithDateQuary(options, query, orderBy, "", withQuary));
}
public GridEntity <T> DataSource(GridOptions options, string query, string orderBy, string condition)
{
// var _connection = new CommonConnection();
try
{
query = query.Replace(';', ' ');
string orderby = "";
string sqlQuery = query;
if (options != null)
{
if (options.pageSize > 0)
{
sqlQuery = GridQueryBuilder <T> .Query(options, query, orderBy, condition);
}
else
{
if (orderBy != "")
{
if (orderBy.ToLower().Contains("asc") || orderBy.ToLower().Contains("desc"))
{
orderby = string.Format(" order by {0}", orderBy);
}
else
{
orderby = string.Format(" order by {0} asc ", orderBy);
}
}
}
}
else
{
if (orderBy != "")
{
if (orderBy.ToLower().Contains("asc") || orderBy.ToLower().Contains("desc"))
{
orderby = string.Format(" order by {0}", orderBy);
}
else
{
orderby = string.Format(" order by {0} asc ", orderBy);
}
}
}
if (!string.IsNullOrEmpty(condition))
{
condition = " WHERE " + condition;
}
var condition1 = "";
if (options != null)
{
if (options.filter != null)
{
condition1 = GridQueryBuilder <T> .FilterCondition(options.filter).Trim();
}
}
if (!string.IsNullOrEmpty(condition1))
{
if (!string.IsNullOrEmpty(condition))
{
condition += " And " + condition1;
}
else
{
condition = " WHERE " + condition1;
}
}
sqlQuery = "SELECT * FROM (" + sqlQuery + " ) As tbl " + condition;
DataTable dataTable = _connection.GetDataTable(sqlQuery + orderby);
String sqlCount = "";
sqlCount = "SELECT COUNT(*) FROM (" + query + " ) As tbl " + condition;
int totalCount = _connection.GetScaler(sqlCount);
var dataList = (List <T>)ListConversion.ConvertTo <T>(dataTable);
var result = new GridResult <T>().Data(dataList, totalCount);
return(result);
}
catch (Exception ex)
{
throw ex;
}
finally
{
_connection.Close();
}
}
public 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);
String sqlCount = "";
sqlCount = "SELECT COUNT(*) FROM (" + query + " ) As tbl " + condition;
int totalCount = _connection.GetScaler(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);
//var dataList = (List<T>)ListConversion.ConvertTo<T>(dataTable);
//var result = new GridResult<T>().Data(dataList, totalCount);
gridDataSet.Tables.Add(totalCountDt);
return(gridDataSet);
}
catch (Exception ex)
{
throw ex;
}
finally
{
_connection.Close();
}
}
public 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);
String sqlCount = "";
//if (_connection.DatabaseType == DatabaseType.SQL)
//{
sqlCount = withDateQuary + " SELECT COUNT(*) FROM (" + query + " ) As tbl " + condition;
//}
//else if (_connection.DatabaseType == DatabaseType.Oracle)
//{
// sqlCount = withDateQuary + " SELECT COUNT(*) FROM (" + query + " )" + condition;
//}
int totalCount = _connection.GetScaler(sqlCount);
var dataList = (List <T>)ListConversion.ConvertTo <T>(dataTable);
var result = new GridResult <T>().Data(dataList, totalCount);
return(result);
}
catch (Exception ex)
{
throw ex;
}
finally
{
_connection.Close();
}
}
public static string Query(GridOptions options, string query, string orderBy, string gridCondition)
{
string condition = "";
if (options != null)
{
condition = FilterCondition(options.filter).Trim();
}
if (!string.IsNullOrEmpty(condition))
{
condition = " WHERE " + condition;
}
if (!string.IsNullOrEmpty(gridCondition))
{
if (string.IsNullOrEmpty(condition))
{
condition = " WHERE " + gridCondition;
}
else
{
condition += " AND " + gridCondition;
}
}
string orderby = "";
if (options != null)
{
if (options.sort != null)
{
foreach (var gridSort in options.sort)
{
if (orderby == "")
{
orderby += "ORDER by " + gridSort.field + " " + gridSort.dir;
}
else
{
orderby += " , " + gridSort.field + " " + gridSort.dir;
}
}
}
}
if (orderby == "")
{
if (!String.IsNullOrEmpty(orderBy))
{
orderby = " ORDER BY " + orderBy;
}
else
{
throw new Exception("Must be set Orderby column Name");
}
}
int pageupperBound = 0;
int skip = 0;
if (options != null)
{
skip = options.skip;
pageupperBound = skip + options.take;
}
var sql =
string.Format(
@"SELECT * FROM (SELECT ROW_NUMBER() OVER({4}) AS ROWINDEX, T.* FROM ({0}) T {2}) tbl WHERE ROWINDEX >{1} AND ROWINDEX <={3}",
query, skip, condition, pageupperBound, orderby);
return(sql);
}
