public void AddWhere(string fieldName, WhereOperator whereOperator, bool doNotFilterIfEmpty, DateTime?value1, DateTime?value2, DateTimePrecision precission)
{
Add(SqlFilterHelper.GetWhereClause(fieldName, whereOperator, doNotFilterIfEmpty, value1, value2, precission));
}
public void AddWhere(string fieldName, WhereOperator whereOperator, bool doNotFilterIfEmpty, object value)
{
Add(SqlFilterHelper.GetWhereClause(fieldName, whereOperator, doNotFilterIfEmpty, value));
}
public string ToSql()
{
var sb = new StringBuilder();
if (string.IsNullOrEmpty(Table?.TableName))
{
throw new ArgumentException("TableName is required.", nameof(Table.TableName));
}
sb.Append("SELECT");
if (Count)
{
sb.Append(" COUNT(1)");
}
else
{
if (Distinct)
{
sb.Append(" DISTINCT");
}
if (Top != null)
{
sb.AppendFormat(" TOP ").Append("(" + Top.Value.ToString("F0") + ")");
if (Percent)
{
sb.Append(" PERCENT");
}
}
if (Columns != null && Columns.Count > 0)
{
foreach (var column in Columns)
{
sb.AppendLine().Append("\t");
if (!string.IsNullOrEmpty(column.Alias))
{
sb.Append(column.Alias + ".");
}
sb.Append(column.ColumnName);
if (!string.IsNullOrEmpty(column.AsName))
{
sb.Append(" AS [" + column.AsName + "]");
}
sb.Append(",");
}
sb.Remove(sb.Length - 1, 1);
}
else
{
sb.Append(" *");
}
}
sb.AppendLine();
sb.Append("FROM ");
sb.Append(Table.ToSql());
if (Where != null && Where.Count > 0)
{
var whereAnd = "WHERE";
foreach (var where in Where)
{
var sql = SqlFilterHelper.GetWhereClause(where.ToSql(), where.Operator, !where.FilterEmpty, where.Value1, where.Value2);
if (!string.IsNullOrEmpty(sql))
{
sb.AppendLine().Append(whereAnd + " " + sql);
whereAnd = "AND";
}
}
}
if (GroupBy != null && GroupBy.Count > 0)
{
sb.AppendLine();
sb.Append("GROUP BY ");
foreach (var gb in GroupBy)
{
sb.Append(gb.ToSql() + ", ");
}
sb.Remove(sb.Length - 2, 2);
}
if (OrderBy != null && OrderBy.Count > 0 && !Count)
{
sb.AppendLine();
sb.Append("ORDER BY ");
foreach (var ob in OrderBy)
{
if (!string.IsNullOrEmpty(ob.Alias))
{
sb.Append(ob.Alias + ".");
}
sb.Append("[" + ob.ColumnName + "]");
if (ob.Asc != null)
{
sb.Append(ob.Asc.Value ? " ASC" : " DESC");
}
if (!string.IsNullOrEmpty(ob.CollationName))
{
sb.Append(" COLLATE " + ob.CollationName);
}
sb.Append(", ");
}
sb.Remove(sb.Length - 2, 2);
}
if (Paging != null && !Count)
{
if (OrderBy == null || OrderBy.Count == 0)
{
sb.AppendLine();
sb.Append("ORDER BY 1");
}
sb.AppendLine();
sb.Append(Paging.ToSql());
}
return(sb.ToString());
}
