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