public static SNDK.DBI.Query GetCount(SNDK.DBI.Connection DBConnection, string TableName, string Where, params string[] OrderByColumns)
{
string querystring = string.Empty;
querystring += "SELECT ";
if (OrderByColumns.GetLength(0) >= 0)
{
foreach (string column in OrderByColumns)
{
querystring += column + ",";
}
}
querystring += "COUNT(*) as rowcount FROM " + TableName + " ";
if (Where != string.Empty)
{
querystring += "WHERE " + Where + " ";
}
if (OrderByColumns.GetLength(0) >= 0)
{
querystring += "ORDER BY ";
foreach (string column in OrderByColumns)
{
querystring += column + ",";
}
querystring = querystring.TrimEnd(",".ToCharArray());
}
return(DBConnection.Query(querystring));
}
public SelectQuery OrderBy(IEnumerable <string> orderByColumns)
{
foreach (string orderByColumn in orderByColumns)
{
OrderByColumns.Add(Query.ColumnSyntax(orderByColumn));
}
return(this);
}
public string GetSql()
{
if (From == null || From == "")
{
throw new ArgumentNullException("From为空");
}
StringBuilder sql = new StringBuilder("select ");
if (Distinct)
{
sql.Append("Distinct ");
}
if (Select == null || Select == "")
{
sql.Append("*");
}
else
{
sql.Append(Select);
}
sql.Append($" From \"{From}\" ");
if (Where != null && Where != "")
{
sql.Append(" where " + Where);
if (Like != null && Like != "")
{
sql.Append($" {(CaseSensitive ? "glob" : "like")} { Like}");
}
}
if (Limit > 0)
{
sql.Append($" limit { Limit.ToString()}");
if (Offset > 0)
{
sql.Append($" offset { Offset.ToString()}");
}
}
if (OrderBy != 0 && OrderByColumns != null && OrderByColumns.Count() > 0)
{
sql.Append(" order by ").Append(string.Join(",", OrderByColumns)).Append(" ").Append(OrderBy > 0 ? "asc" : "desc");
}
return(sql.ToString());
}
public SelectQuery OrderBy(string orderByColumn, params string[] orderByColumns)
{
OrderByColumns.Add(Query.ColumnSyntax(orderByColumn));
return(OrderBy(orderByColumns));
}
public override string ToPlainQuery()
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT");
if (SelectDistinct)
{
sb.Append(" DISTINCT");
}
if (TopN > 0)
{
sb.AppendFormat(" TOP {0}", TopN);
}
if (SelectColumns.Count > 0)
{
for (int i = 0; i < SelectColumns.Count; i++)
{
Column col = SelectColumns[i];
sb.Append(" " + col.AliasedName);
if (i < SelectColumns.Count - 1)
{
sb.Append(",");
}
}
}
else
{
sb.Append(" *");
}
sb.AppendFormat(" FROM {0}{1}",
FromTableOrQuery(),
FromQuery == null ? "" : $" AS {FromQuery.Item2}");
sb.Append($" {JoinString}");
if (WhereCondition != null)
{
sb.AppendFormat(" WHERE {0}", WhereCondition.ToString());
}
if (GroupByColumns.Count > 0)
{
sb.AppendFormat(" GROUP BY {0}", String.Join(",", GroupByColumns.Select(r => " " + r.FullName)));
}
if (HavingCondition != null)
{
sb.AppendFormat(" HAVING {0}", HavingCondition.ToString());
}
if (OrderByColumns.Count > 0)
{
sb.AppendFormat(" ORDER BY {0}", String.Join(",", OrderByColumns.Select(r => String.Format(" {0} {1}", String.IsNullOrEmpty(r.Item1.Alias) ? r.Item1.FullName : r.Item1.Alias, r.Item2.GetStringValue()))));
}
sb.Append($" {SetOpString}");
return(sb.ToString().Trim());
}
