/// <inheritdoc />
public SqlCommand MakeGetRangeCommand(string conditions, object parameters, TableSchema tableSchema)
{
var sql = new SqlCommandBuilder("SELECT ").AppendSelectPropertiesClause(tableSchema.Columns);
sql.AppendClause("FROM ").Append(tableSchema.Name);
sql.AppendClause(conditions);
return(sql.ToCommand(parameters));
}
/// <inheritdoc />
public SqlCommand MakeCountCommand(string conditions, object parameters, TableSchema tableSchema)
{
var sql = new SqlCommandBuilder("SELECT COUNT(*)");
sql.AppendClause("FROM ").Append(tableSchema.Name);
sql.AppendClause(conditions);
return(sql.ToCommand(parameters));
}
/// <inheritdoc />
public override SqlCommand MakeInsertReturningPrimaryKeyCommand(object entity, TableSchema tableSchema)
{
Func <ColumnSchema, bool> include = p => p.Usage.IncludeInInsertStatements;
var columns = tableSchema.Columns;
var sql = new SqlCommandBuilder("INSERT INTO ").Append(tableSchema.Name).Append(" (").AppendColumnNames(columns, include).Append(")");
sql.AppendClause("VALUES (").AppendParameterNames(columns, include).Append(")");
sql.AppendClause("GET IDENTITY");
return(sql.ToCommand(entity));
}
public override SqlCommand MakeGetFirstNCommand(int take, string conditions, object parameters, string orderBy, TableSchema tableSchema)
{
var sql = new SqlCommandBuilder("SELECT TOP ").Append(take).Append(" ").AppendSelectPropertiesClause(tableSchema.Columns);
sql.AppendClause("FROM ").Append(tableSchema.Name);
sql.AppendClause(conditions);
if (!string.IsNullOrWhiteSpace(orderBy))
{
sql.AppendClause("ORDER BY ").Append(orderBy);
}
return(sql.ToCommand(parameters));
}
/// <inheritdoc />
public override SqlCommand MakeGetPageCommand(Page page, string conditions, object parameters, string orderBy, TableSchema tableSchema)
{
if (string.IsNullOrWhiteSpace(orderBy))
{
throw new ArgumentException("orderBy cannot be empty");
}
var sql = new SqlCommandBuilder("SELECT ").AppendSelectPropertiesClause(tableSchema.Columns);
sql.AppendClause("FROM ").Append(tableSchema.Name);
sql.AppendClause(conditions);
sql.AppendClause("ORDER BY ").Append(orderBy);
sql.AppendLine().AppendFormat("SKIP {0} TAKE {1}", page.FirstItemIndex, page.PageSize);
return(sql.ToCommand(parameters));
}
public override SqlCommand MakeGetFirstNCommand(int take, string conditions, object parameters, string orderBy, TableSchema tableSchema)
{
Ensure.NotNull(conditions, nameof(conditions));
var sql = new SqlCommandBuilder("SELECT ").AppendSelectPropertiesClause(tableSchema.Columns);
sql.AppendClause("FROM ").Append(tableSchema.Name);
sql.AppendClause(conditions);
if (!string.IsNullOrWhiteSpace(orderBy))
{
sql.AppendClause("ORDER BY ").Append(orderBy);
}
sql.AppendLine().AppendFormat("TAKE {0}", take);
return(new SqlCommand(sql.ToString()));
}
public SqlCommand MakeSetColumnNullStatement(string tableName, string columnName)
{
var sql = new SqlCommandBuilder("UPDATE ").Append(tableName);
sql.AppendClause("SET ").Append(columnName).Append(" = NULL");
return(sql.ToCommand(null));
}
/// <inheritdoc />
public SqlMultipleCommand <TEntity> MakeInsertRangeCommand <TEntity>(IEnumerable <TEntity> entities, TableSchema tableSchema)
{
bool Include(ColumnSchema p) => p.Usage.IncludeInInsertStatements;
var columns = tableSchema.Columns;
var sql = new SqlCommandBuilder("INSERT INTO ").Append(tableSchema.Name).Append(" (").AppendColumnNames(columns, Include).Append(")");
sql.AppendClause("VALUES (").AppendParameterNames(columns, Include).Append(");");
return(sql.ToMultipleCommand(entities));
}
/// <inheritdoc />
public SqlCommand MakeInsertCommand(object entity, TableSchema tableSchema)
{
bool Include(ColumnSchema p) => p.Usage.IncludeInInsertStatements;
var columns = tableSchema.Columns;
var sql = new SqlCommandBuilder("INSERT INTO ").Append(tableSchema.Name).Append(" (").AppendColumnNames(columns, Include).Append(")");
sql.AppendClause("VALUES (").AppendParameterNames(columns, Include).Append(");");
return(sql.ToCommand(entity));
}
/// <inheritdoc />
public SqlCommand MakeFindCommand(object id, TableSchema tableSchema)
{
Ensure.NotNull(id, nameof(id));
var primaryKeys = tableSchema.GetPrimaryKeys();
var sql = new SqlCommandBuilder("SELECT ").AppendSelectPropertiesClause(tableSchema.Columns);
sql.AppendClause("FROM ").Append(tableSchema.Name);
sql.AppendWherePrimaryKeysClause(primaryKeys);
return(sql.ToPrimaryKeySql(tableSchema, id));
}
public SqlMultipleCommand <TEntity> MakeUpdateRangeCommand <TEntity>(IEnumerable <TEntity> entities, TableSchema tableSchema)
where TEntity : class
{
bool Include(ColumnSchema p) => p.Usage.IncludeInUpdateStatements;
var sql = new SqlCommandBuilder("UPDATE ").Append(tableSchema.Name);
sql.AppendClause("SET ").AppendColumnNamesEqualParameters(tableSchema.Columns, ", ", Include);
sql.AppendWherePrimaryKeysClause(tableSchema.GetPrimaryKeys());
return(sql.ToMultipleCommand(entities));
}
/// <inheritdoc />
public SqlCommand MakeUpdateCommand(object entity, TableSchema tableSchema)
{
Ensure.NotNull(entity, nameof(entity));
bool Include(ColumnSchema p) => p.Usage.IncludeInUpdateStatements;
var sql = new SqlCommandBuilder("UPDATE ").Append(tableSchema.Name);
sql.AppendClause("SET ").AppendColumnNamesEqualParameters(tableSchema.Columns, ", ", Include);
sql.AppendWherePrimaryKeysClause(tableSchema.GetPrimaryKeys());
return(sql.ToCommand(entity));
}
/// <summary>
/// Generates a SQL Delete statement which chooses which row to delete by the <paramref name="conditions"/>.
/// </summary>
public SqlCommand MakeDeleteRangeCommand(string conditions, object parameters, TableSchema tableSchema)
{
if (conditions == null || conditions.IndexOf("WHERE ", StringComparison.OrdinalIgnoreCase) < 0)
{
throw new ArgumentException(
"DeleteRange<TEntity> requires a WHERE clause, use DeleteAll<TEntity> to delete everything.");
}
var sql = new SqlCommandBuilder("DELETE FROM ").Append(tableSchema.Name);
sql.AppendClause(conditions);
return(sql.ToCommand(parameters));
}
/// <summary>
/// Appends a WHERE clause which selects equality of primary keys.
/// </summary>
public static SqlCommandBuilder AppendWherePrimaryKeysClause(this SqlCommandBuilder sql, ImmutableArray <ColumnSchema> primaryKeys)
{
return(sql.AppendClause("WHERE ")
.AppendColumnNamesEqualParameters(primaryKeys, " AND ", p => true));
}
