public static void ConfigureFilterCommand(ObjectDef m, string schemaName, SqlCommand cmd, Condition cond, string orderByExpression, int pageNumber, int pageSize)
{
cmd.CommandType = CommandType.Text;
string entityName = m.EntityName;
StringBuilder sb = new StringBuilder();
StringBuilder sbColumns = new StringBuilder();
var q = m.Properties.Where(p => !p.HasIgnoreColumn(DataOperation.Read)).OrderBy(p => p.PropertyOrder); //.Where(p => p.AllowRead).OrderBy(p => p.PropertyOrder);
for (int i = 0; i < q.Count(); i++)
{
ObjectDefProperty mp = q.ElementAt(i);
var entityPropName = mp.ColumnName;
if (i == 0)
{
sbColumns.AppendLine(string.Format("\t[{0}]", entityPropName));
}
else
{
sbColumns.AppendLine(string.Format("\t, [{0}]", entityPropName));
}
}
string columns = sbColumns.ToString();
string filters = string.Empty;
if (cond != null)
{
string condString = cond.ToSqlString(true);
if (!string.IsNullOrEmpty(condString))
{
filters = string.Format("WHERE {0}", condString);
cmd.Parameters.AddRange(cond.GetSqlParameters().ToArray());
}
}
sb.AppendLine($"SELECT {TotalCountParamName} = COUNT(*)");
sb.AppendLine(string.Format("FROM [{0}].[{1}]", schemaName, entityName));
sb.AppendLine(filters);
sb.AppendLine(string.Format(";WITH {0}CTE AS", entityName));
sb.AppendLine("(");
sb.AppendLine("\tSELECT");
sb.AppendLine(columns);
var orderByCols = GetOrderByCols(q);
if (!string.IsNullOrEmpty(orderByExpression))
{
sb.AppendLine(string.Format("\t, ROW_NUMBER() OVER (ORDER BY {0}) AS RowNumber", orderByExpression));
}
else
{
if (!string.IsNullOrEmpty(orderByCols))
{
sb.AppendLine(string.Format("\t, ROW_NUMBER() OVER (ORDER BY {0}) AS RowNumber", orderByCols));
}
else
{
var key = m.GetKey();
sb.AppendLine(string.Format("\t, ROW_NUMBER() OVER (ORDER BY {0}) AS RowNumber", key.Name));
}
}
sb.AppendLine(string.Format("FROM [{0}].[{1}]", schemaName, entityName));
sb.AppendLine(filters);
sb.AppendLine(")");
sb.AppendLine($"SELECT TOP({PageSizeParamName})");
sb.AppendLine(columns);
sb.AppendLine(string.Format("FROM {0}CTE", entityName));
sb.AppendLine($"WHERE RowNumber > ({PageNumberParamName} - 1) * {PageSizeParamName}");
cmd.Parameters.AddWithValue(PageNumberParamName, pageNumber);
cmd.Parameters.AddWithValue(PageSizeParamName, pageSize);
SqlParameter param = new SqlParameter(TotalCountParamName, SqlDbType.Int);
param.Direction = ParameterDirection.InputOutput;
param.Value = 0;
cmd.Parameters.Add(param);
cmd.CommandText = sb.ToString();
}