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