private string GetCountCommandText(AllColumns allColumns, QuerySourceFilterDto filter, string table) { string where = String.Empty; foreach (var col in filter.Columns) { if (String.IsNullOrEmpty(where)) { where += col + $" like '%{filter.Filter}%'"; } else { where += "or " + col + $" like '%{filter.Filter}%'"; } } string cmd = $"select count ({allColumns.PrimeryKeyColumn}) as Count from {table} where ({where})"; return(cmd); }
private string GetCommandText(AllColumns allColumns, QuerySourceFilterDto filter, string table) { string columns = String.Empty; string where = String.Empty; foreach (var col in filter.Columns) { if (String.IsNullOrEmpty(columns)) { columns += col; where += col + $" like '%{filter.Filter}%'"; } else { columns += ", " + col; where += "or " + col + $" like '%{filter.Filter}%'"; } } string order_by = filter.Sort.ColumnName + " " + filter.Sort.Direction.ToString(); string skip = String.Empty; if (filter.Page > 1) { skip += $" {allColumns.PrimeryKeyColumn} not in (select top {filter.Rows * (filter.Page - 1)} {allColumns.PrimeryKeyColumn} from {table} order by {order_by}) and "; } string top = String.Empty; if (filter.Rows > 0) { top = $"top { filter.Rows}"; } string cmd = $"select {top} {columns} from {table} where {skip} ({where}) order by {order_by}"; return(cmd); }