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