public static DataSourceResult KendoDataByQuery <T>([DataSourceRequest] DataSourceRequest request, string Query, string CustomWhere, string sortPrefix)
{
using (IDbConnection dbConn = Helpers.OrmliteConnection.openConn())
using (var dbCommand = dbConn.CreateCommand())
{
var data = new DataSourceResult();
//filter
List <string> sort = new List <string>();
var Tname = typeof(T).Name;
if (request.Sorts.Any())
{
foreach (SortDescriptor sortDescriptor in request.Sorts)
{
if (sortDescriptor.SortDirection == ListSortDirection.Ascending)
{
sort.Add(sortPrefix + sortDescriptor.Member + " ASC");
}
else
{
sort.Add(sortPrefix + sortDescriptor.Member + " DESC");
}
}
}
string sortString = string.Join(",", sort.Select(s => s));
if (request.Filters.Any())
{
var where = KendoApplyFilter.ApplyFilter(request.Filters[0], "") + (string.IsNullOrWhiteSpace(CustomWhere) ? "" : " AND " + CustomWhere);
var order = (!String.IsNullOrEmpty(sortString) ? (" Order By " + sortString) : "");
if (request.PageSize > 0)
{
if (request.Page > 1)
{
var q = Query.Replace("SELECT", "").Replace("select", "");
string newquery = @"SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( " + (!string.IsNullOrEmpty(order) ? "a." + order : "ORDER BY (SELECT NULL)") + @") AS RowNum, " + q + @"
) AS result
WHERE RowNum > " + (request.Page - 1) * request.PageSize + @"
AND RowNum <= " + request.Page * request.PageSize + @"
ORDER BY RowNum";
data.Data = dbConn.Query <T>(newquery);
}
else
{
data.Data = dbConn.Select <T>(q => q.Select(Query).Where(where).OrderBy(order).Limit(skip: ((request.Page - 1) * request.PageSize), rows: request.PageSize));
}
}
else
{
data.Data = dbConn.Select <T>(q => q.Select(Query).Where(where).OrderBy(order));
}
data.Total = dbConn.Scalar <int>("SELECT COUNT(*) FROM (" + Query + " WHERE " + where + ") a");
}
else
{
var order = (!String.IsNullOrEmpty(sortString) ? (" Order By " + sortString) : "") + " ";
if (request.PageSize > 0)
{
data.Data = dbCommand.Select <T>(q => q.Select(Query).Where(CustomWhere).OrderBy(order).Limit(skip: ((request.Page - 1) * request.PageSize), rows: request.PageSize));
if (request.Page > 1)
{
var sql = dbCommand.CommandText.Replace(" )", " " + " WHERE " + CustomWhere + ")") + order;
data.Data = dbConn.Select <T>(sql);
}
}
else
{
data.Data = dbConn.Select <T>(q => q.Select(Query).Where(CustomWhere).OrderBy(order));
}
data.Total = dbConn.Scalar <int>("SELECT COUNT(*) FROM (" + Query + (string.IsNullOrWhiteSpace(CustomWhere) ? "" : " WHERE " + CustomWhere) + ") a");
}
return(data);
}
}
public static DataSourceResult KendoData <T>([DataSourceRequest] DataSourceRequest request, string CustomWhere)
{
using (IDbConnection dbConn = Helpers.OrmliteConnection.openConn())
using (var dbCommand = dbConn.CreateCommand())
{
var data = new DataSourceResult();
//filter
List <string> sort = new List <string>();
var Tname = typeof(T).Name;
if (request.Sorts.Any())
{
foreach (SortDescriptor sortDescriptor in request.Sorts)
{
if (sortDescriptor.SortDirection == ListSortDirection.Ascending)
{
sort.Add(sortDescriptor.Member + " ASC");
}
else
{
sort.Add(sortDescriptor.Member + " DESC");
}
}
}
string sortString = string.Join(",", sort.Select(s => s));
if (request.Filters.Any())
{
var where = KendoApplyFilter.ApplyFilter(request.Filters[0], "") + " AND " + CustomWhere;
var order = (!String.IsNullOrEmpty(sortString) ? (" Order By " + sortString) : "");
if (request.PageSize > 0)
{
data.Data = dbConn.Select <T>(q => q.Select("SELECT * FROM " + Tname + " WHERE" + where + order).Limit(skip: (request.Page - 1) * request.PageSize, rows: request.PageSize));
if (request.Page > 1)
{
var sql = dbCommand.CommandText.Replace(" )", " " + " WHERE" + where + ")") + order;
data.Data = dbConn.Select <T>(sql);
}
}
else
{
data.Data = dbConn.Select <T>(q => q.Select("SELECT * FROM " + Tname + " WHERE" + where + order));
}
data.Total = dbConn.Scalar <int>("SELECT COUNT(*) FROM " + Tname + " WHERE " + where);
}
else
{
var where = " WHERE " + CustomWhere;
var order = (!String.IsNullOrEmpty(sortString) ? (" Order By " + sortString) : "");
if (request.PageSize > 0)
{
try
{
data.Data = dbCommand.Select <T>(q => q.Select("SELECT * FROM " + Tname + where + order).Limit(skip: (request.Page - 1) * request.PageSize, rows: request.PageSize));
if (request.Page > 1)
{
var sql = dbCommand.CommandText.Replace(" )", " " + where + ")") + order;
data.Data = dbConn.Select <T>(sql);
}
}
catch (Exception ex)
{
data.Data = null;
}
}
else
{
data.Data = dbConn.Select <T>(q => q.Select("SELECT * FROM " + Tname + where + order));
}
data.Total = dbConn.Scalar <int>("SELECT COUNT(*) FROM " + Tname + where);
}
return(data);
}
}