public static DataSourceResult ToDataSourceResult <TModel>(this DataSourceRequest request,
string fieldsList, string fromClaues, string where, string defaultOrder, object parameters, List <string> groupByColumns = null)
{
var orderBy = request.GetOrderBy();
if (string.IsNullOrEmpty(orderBy))
{
orderBy = defaultOrder;
}
var where2 = request.GetWhere(groupByColumns);
if (!string.IsNullOrEmpty(where2))
{
if (!string.IsNullOrEmpty(where))
{
where = " (" + where2 + ") and " + where;
}
else
{
where = where2;
}
}
var sql = GetSql(request, fieldsList, fromClaues, where, orderBy);
var conn = ConnectionManager.Instance.GetConnection();
DataSourceResult res = new DataSourceResult();
res.Data = Dapper.SqlMapper.Query <TModel>(conn, sql, parameters);
sql = "select " + fieldsList + " from " + fromClaues;
if (!string.IsNullOrEmpty(where))
{
sql += " where " + where;
}
List <Tuple <string, AggregateFunction> > agrs = new List <Tuple <string, AggregateFunction> >();
foreach (var a in request.Aggregates)
{
foreach (var af in a.Aggregates)
{
agrs.Add(new Tuple <string, AggregateFunction>(af.AggregateMethodName + "([" + af.SourceField + "])", af));
}
}
var sql2 = "select count(*) _count ";
if (agrs.Count > 0)
{
sql2 += "," + string.Join(",", agrs.Select(aa => aa.Item1));
}
sql2 += " from (" + sql + ") a";
var dr = SqlHelper.ExecuteReader(sql2, GetSqlParameters(parameters));
dr.Read();
res.Total = (int)dr["_count"];
List <AggregateResult> agres = new List <AggregateResult>();
for (int i = 1; i < dr.FieldCount; i++)
{
agres.Add(new AggregateResult(dr[i], agrs[i - 1].Item2));
}
dr.Close();
res.AggregateResults = agres;
return(res);
}
public static string GetSql(this DataSourceRequest request, string fieldsList, string fromClaues)
{
return(GetSql(request, fieldsList, fromClaues, request.GetWhere(), request.GetOrderBy()));
}