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 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, numRecords: numRecords)); } string sql = base.SelectSql(tableName, columns, sqlWhere, joins, sqlSortExpression, numRecords: numRecords); if (start.Value > 1) { sql += " OFFSET " + (start - 1); } return(sql); }
public override string DeleteSql(SqlTableNameWithAlias tableName, string sqlWhere, IEnumerable <SqlJoinDefinition> joins) { if (joins != null && joins.Any()) { string sql = "delete from " + QuoteTable(tableName.TableName) + (tableName.Alias != null ? (" " + tableName.Alias + " ") : ""); sql += "using " + string.Join(",", joins.Select(join => QuoteTable(join.Right.Schema.MappedName) + " " + join.Right.Alias)); if (sqlWhere != null) { sql += $" where ({sqlWhere})"; } sql += " and "; sql += string.Join(" and ", joins.Select(join => QuoteField(join.Left.Alias + "." + @join.Left.Field.MappedName) + "=" + QuoteField(join.Right.Alias + "." + @join.Right.Field.MappedName))); return(sql); } return("delete from " + QuoteTable(tableName.TableName) + (tableName.Alias != null ? (" " + tableName.Alias + " ") : "") + (sqlWhere != null ? (" where " + sqlWhere) : "")); }