Beispiel #1
0
        public async Task <DataTablesResponseModel> PopulateTable <T>(DataTablesRequestModel request, DbSet <T> data, Func <T, T> project) where T : class
        {
            // build select
            var sql = new StringBuilder();

            sql.AppendFormat("SELECT * FROM {0} ", GetTableName(data));

            // build where
            if (!string.IsNullOrEmpty(request.Search.Value))
            {
                sql.Append("WHERE ");
                foreach (var column in request.Columns)
                {
                    if (!column.Searchable)
                    {
                        continue;
                    }
                    sql.AppendFormat("{0} LIKE '%{1}%' OR", column.Name, request.Search.Value);
                }

                sql.Remove(sql.Length - 2, 2);
            }

            // count filtered data
            var countSql    = "SELECT COUNT(*) " + sql.ToString(9, sql.Length - 9);
            var filterCount = ExecuteScalar(data, countSql);

            // build order by
            if (request.Ordering.Count > 0)
            {
                sql.Append("ORDER BY ");
                foreach (var order in request.Ordering)
                {
                    sql.AppendFormat("{0} {1}, ", request.Columns[order.Column].Name, order.Direction == DataTablesOrdering.Ascending ? "ASC" : "DESC");
                }

                sql.Remove(sql.Length - 2, 2);
                sql.Append(' ');
            }

            // build paging
            sql.AppendFormat("LIMIT {0} OFFSET {1}", request.Length, request.Start);
            var sqlString = sql.ToString();

            var recordset = await data.FromSqlRaw(sqlString).ToListAsync();

            if (project != null)
            {
                recordset = recordset.Select(x => project(x)).ToList();
            }

            return(new DataTablesResponseModel
            {
                Draw = request.Draw + 1,
                Data = recordset,
                RecordsFiltered = filterCount,
                RecordsTotal = await data.CountAsync()
            });
        }
Beispiel #2
0
 public Task <DataTablesResponseModel> PopulateTable <T>(DataTablesRequestModel request, DbSet <T> data) where T : class
 {
     return(PopulateTable(request, data, null));
 }