public virtual string DeleteSql(SqlTableNameWithAlias tableName, string sqlWhere)
{
if (tableName.Alias != null)
return "delete from " + QuoteTable(tableName.TableName) + (tableName.Alias != null ? (" " + tableName.Alias + " ") : "") + " where " + sqlWhere;
else
return "delete from " + QuoteTable(tableName.TableName) + " where " + sqlWhere;
}
public override string SelectSql(SqlTableNameWithAlias tableName, IEnumerable<SqlExpressionWithAlias> columns, string sqlWhere, IEnumerable<SqlJoinDefinition> joins = null, string sqlSortExpression = null, int? start = null, int? numRecords = null, string afterSelect = null)
{
if (start == null && numRecords == null)
return base.SelectSql(tableName, columns, sqlWhere, joins, sqlSortExpression);
if (start == null)
return base.SelectSql(tableName, columns, sqlWhere, joins, sqlSortExpression, afterSelect: "TOP " + numRecords);
string subQueryAlias = SqlNameGenerator.NextTableAlias();
string rowNumAlias = SqlNameGenerator.NextFieldAlias();
int end = (numRecords == null ? int.MaxValue : (numRecords.Value + start - 1)).Value;
SqlExpressionWithAlias rowNumExpression = new SqlExpressionWithAlias("row_number() over (order by " + sqlSortExpression + ")", rowNumAlias);
string[] parts =
{
"select",
subQueryAlias + ".*",
"from",
"(",
base.SelectSql(tableName, columns.Union(new[] {rowNumExpression}), sqlWhere, joins),
")",
"as",
subQueryAlias,
"where",
rowNumAlias,
"between",
start.ToString(),
"and",
end.ToString(),
"order by",
rowNumAlias
};
return string.Join(" ", parts);
}
public override string DeleteSql(SqlTableNameWithAlias tableName, string sqlWhere)
{
if (tableName.Alias != null)
sqlWhere = sqlWhere.Replace(QuoteTable(tableName.Alias) + ".", "");
return "delete from " + QuoteTable(tableName.TableName) + " where " + sqlWhere;
}
public virtual string SelectSql(SqlTableNameWithAlias tableName, IEnumerable<SqlExpressionWithAlias> columns, string sqlWhere, IEnumerable<SqlJoinDefinition> joins = null, string sqlSortExpression = null, int? start = null, int? numRecords = null, string afterSelect = null)
{
var parts = new List<string>
{
"select",
string.Join(",", columns.Select(c => $"{(c.ShouldQuote ? QuoteField(c.Expression) : c.Expression)} as {c.Alias}")),
"from",
QuoteTable(tableName.TableName)
};
if (afterSelect != null)
parts.Insert(1,afterSelect);
if (tableName.Alias != null)
parts.Add(tableName.Alias);
if (joins != null)
parts.Add(string.Join(" ", joins.Select(j => j.ToSql(this))));
if (!string.IsNullOrWhiteSpace(sqlWhere))
{
parts.Add("where");
parts.Add(sqlWhere);
}
if (!string.IsNullOrWhiteSpace(sqlSortExpression))
{
parts.Add("order by");
parts.Add(sqlSortExpression);
}
if (start != null || numRecords != null)
{
parts.Add("limit");
if (start != null && numRecords == null)
numRecords = int.MaxValue;
if (start != null)
parts.Add((start.Value - 1).ToString());
if (start != null)
parts.Add(",");
parts.Add(numRecords.ToString());
}
return string.Join(" ", parts);
}
public virtual string UpdateSql(SqlTableNameWithAlias table, IEnumerable<Tuple<string, string>> setColumns, string sqlWhere)
{
return string.Format("update {0} set {1} where {2}",
QuoteTable(table.TableName),
string.Join(",", setColumns.Select(c => $"{QuoteField(c.Item1)}={c.Item2}")),
sqlWhere
);
}