/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer"></param>
/// <returns><see cref="AccessCommandExecutionToken" /></returns>
public override CommandExecutionToken <OleDbCommand, OleDbParameter> Prepare(Materializer <OleDbCommand, OleDbParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
if (!Table.IsTable)
{
throw new MappingException($"Cannot perform an update operation on the view {Table.Name}.");
}
if (!Table.HasPrimaryKey && !m_Options.HasFlag(UpdateOptions.UseKeyAttribute) && KeyColumns.Count == 0)
{
throw new MappingException($"Cannot perform an update operation on {Table.Name} unless UpdateOptions.UseKeyAttribute or .WithKeys() is specified.");
}
var sqlBuilder = Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyArgumentValue(DataSource, ArgumentValue, m_Options);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
if (KeyColumns.Count > 0)
{
sqlBuilder.OverrideKeys(KeyColumns);
}
var sql = new StringBuilder($"UPDATE {Table.Name.ToQuotedString()}");
sqlBuilder.BuildSetClause(sql, " SET ", null, null);
sqlBuilder.BuildWhereClause(sql, " WHERE ", null);
sql.Append(";");
var updateCommand = new AccessCommandExecutionToken(DataSource, "Update " + Table.Name, sql.ToString(), sqlBuilder.GetParametersKeysLast()).CheckUpdateRowCount(m_Options);
updateCommand.ExecutionMode = AccessCommandExecutionMode.NonQuery;
var desiredColumns = materializer.DesiredColumns();
if (desiredColumns == Materializer.NoColumns)
{
return(updateCommand);
}
if (m_Options.HasFlag(UpdateOptions.ReturnOldValues))
{
var result = PrepareRead(desiredColumns, "before");
result.NextCommand = updateCommand;
return(result);
}
else
{
updateCommand.NextCommand = PrepareRead(desiredColumns, "after");
return(updateCommand);
}
}
public override CommandExecutionToken <OleDbCommand, OleDbParameter> Prepare(Materializer <OleDbCommand, OleDbParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = m_Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
List <OleDbParameter> parameters;
var sql = new StringBuilder();
sql.Append("DELETE FROM " + m_Table.Name.ToQuotedString());
if (m_FilterValue != null)
{
sql.Append(" WHERE " + sqlBuilder.ApplyFilterValue(m_FilterValue, m_FilterOptions));
parameters = sqlBuilder.GetParameters();
}
else if (!string.IsNullOrWhiteSpace(m_WhereClause))
{
sql.Append(" WHERE " + m_WhereClause);
parameters = SqlBuilder.GetParameters <OleDbParameter>(m_ArgumentValue);
parameters.AddRange(sqlBuilder.GetParameters());
}
else
{
parameters = sqlBuilder.GetParameters();
}
sql.Append(";");
if (m_Parameters != null)
{
parameters.AddRange(m_Parameters);
}
var deleteCommand = new AccessCommandExecutionToken(DataSource, "Delete from " + m_Table.Name, sql.ToString(), parameters);
var desiredColumns = materializer.DesiredColumns();
if (desiredColumns == Materializer.NoColumns)
{
return(deleteCommand);
}
var result = PrepareRead(desiredColumns);
result.NextCommand = deleteCommand;
return(result);
//var sqlBuilder2
// sqlBuilder.BuildSelectClause(sql, "SELECT ", null, " FROM " + m_Table.Name.ToQuotedString());
// sql.AppendLine(" WHERE " + m_WhereClause + ";");
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer"></param>
/// <returns><see cref="AccessCommandExecutionToken" /></returns>
public override CommandExecutionToken <OleDbCommand, OleDbParameter> Prepare(Materializer <OleDbCommand, OleDbParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = m_Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyArgumentValue(DataSource, m_NewValues, m_Options);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
var sql = new StringBuilder($"UPDATE {m_Table.Name.ToQuotedString()}");
sqlBuilder.BuildSetClause(sql, " SET ", null, null);
sql.Append(" WHERE " + m_WhereClause);
sql.Append(";");
var parameters = sqlBuilder.GetParameters();
if (m_Parameters != null)
{
parameters.AddRange(m_Parameters);
}
var updateCommand = new AccessCommandExecutionToken(DataSource, "Update " + m_Table.Name, sql.ToString(), parameters).CheckUpdateRowCount(m_Options, m_ExpectedRowCount);
updateCommand.ExecutionMode = AccessCommandExecutionMode.NonQuery;
var desiredColumns = materializer.DesiredColumns();
if (desiredColumns == Materializer.NoColumns)
{
return(updateCommand);
}
if (m_Options.HasFlag(UpdateOptions.ReturnOldValues))
{
var result = PrepareRead(desiredColumns);
result.NextCommand = updateCommand;
return(result);
}
else
{
updateCommand.NextCommand = PrepareRead(desiredColumns);
return(updateCommand);
}
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer">The materializer.</param>
/// <returns>ExecutionToken<TCommand>.</returns>
public override CommandExecutionToken <OleDbCommand, OleDbParameter> Prepare(Materializer <OleDbCommand, OleDbParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = m_Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyArgumentValue(DataSource, m_NewValues, m_Options);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
var prefix = m_Options.HasFlag(UpdateOptions.ReturnOldValues) ? "Deleted." : "Inserted.";
var sql = new StringBuilder($"UPDATE {m_Table.Name.ToQuotedString()}");
sqlBuilder.BuildAnonymousSetClause(sql, " SET ", null, null);
sqlBuilder.BuildSelectClause(sql, " OUTPUT ", prefix, null);
sql.Append(" WHERE " + m_WhereClause);
sql.Append(";");
var parameters = sqlBuilder.GetParameters();
parameters.AddRange(m_Parameters);
return(new OleDbCommandExecutionToken(DataSource, "Update " + m_Table.Name, sql.ToString(), parameters).CheckUpdateRowCount(m_Options, m_ExpectedRowCount));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer"></param>
/// <returns><see cref="SQLiteCommandExecutionToken" /></returns>
public override CommandExecutionToken <SQLiteCommand, SQLiteParameter> Prepare(Materializer <SQLiteCommand, SQLiteParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyArgumentValue(DataSource, ArgumentValue, m_Options);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
var sql = new StringBuilder();
sqlBuilder.BuildUpdateByKeyStatement(sql, Table.Name.ToQuotedString(), ";");
sql.AppendLine();
sqlBuilder.BuildInsertClause(sql, $"INSERT OR IGNORE INTO {Table.Name.ToQuotedString()} (", null, ")");
sqlBuilder.BuildValuesClause(sql, " VALUES (", ");");
sql.AppendLine();
if (sqlBuilder.HasReadFields)
{
var keys = sqlBuilder.GetKeyColumns().ToList();
if (keys.Count != 1)
{
throw new NotSupportedException("Cannot return data from a SQLite Upsert unless there is a single primary key.");
}
var key = keys[0];
sqlBuilder.BuildSelectClause(sql, "SELECT ", null, $" FROM {Table.Name.ToQuotedString()} WHERE {key.QuotedSqlName} = CASE WHEN {key.SqlVariableName} IS NULL OR {key.SqlVariableName} = 0 THEN last_insert_rowid() ELSE {key.SqlVariableName} END;");
}
return(new SQLiteCommandExecutionToken(DataSource, "Insert or update " + Table.Name, sql.ToString(), sqlBuilder.GetParameters(), lockType: LockType.Write));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer"></param>
/// <returns><see cref="PostgreSqlCommandExecutionToken" /></returns>
public override CommandExecutionToken <NpgsqlCommand, NpgsqlParameter> Prepare(Materializer <NpgsqlCommand, NpgsqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var identityInsert = m_Options.HasFlag(InsertOptions.IdentityInsert);
if (identityInsert)
{
throw new NotImplementedException("See issue 256. https://github.com/TortugaResearch/Tortuga.Chain/issues/256");
}
var sqlBuilder = Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyArgumentValue(DataSource, ArgumentValue, m_Options);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
if (KeyColumns.Count > 0)
{
sqlBuilder.OverrideKeys(KeyColumns);
}
var sql = new StringBuilder();
sqlBuilder.BuildInsertClause(sql, $"INSERT INTO {Table.Name.ToString()} (", null, ")", identityInsert);
sqlBuilder.BuildValuesClause(sql, " VALUES (", ")", identityInsert);
sqlBuilder.BuildSelectClause(sql, " RETURNING ", null, ";");
return(new PostgreSqlCommandExecutionToken(DataSource, "Insert into " + Table.Name, sql.ToString(), sqlBuilder.GetParameters()));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer">The materializer.</param>
/// <returns>ExecutionToken<TCommand>.</returns>
public override CommandExecutionToken <OleDbCommand, OleDbParameter> Prepare(Materializer <OleDbCommand, OleDbParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyArgumentValue(DataSource, ArgumentValue, m_Options);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
var sql = new StringBuilder();
string header;
string intoClause;
string footer;
sqlBuilder.UseTableVariable(Table, out header, out intoClause, out footer);
sql.Append(header);
sql.Append("DELETE FROM " + Table.Name.ToQuotedString());
sqlBuilder.BuildSelectClause(sql, " OUTPUT ", "Deleted.", intoClause);
sqlBuilder.BuildAnonymousWhereClause(sql, " WHERE ", null, true);
sql.Append(";");
sql.Append(footer);
return(new OleDbCommandExecutionToken(DataSource, "Delete from " + Table.Name, sql.ToString(), sqlBuilder.GetParameters()));
}
public override CommandExecutionToken <SQLiteCommand, SQLiteParameter> Prepare(Materializer <SQLiteCommand, SQLiteParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = m_Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
var sql = new StringBuilder();
if (sqlBuilder.HasReadFields)
{
sqlBuilder.BuildSelectClause(sql, "SELECT ", null, " FROM " + m_Table.Name.ToQuotedString());
sql.AppendLine(" WHERE " + m_WhereClause + ";");
}
sql.Append("DELETE FROM " + m_Table.Name.ToQuotedString());
sql.AppendLine(" WHERE " + m_WhereClause + ";");
var parameters = sqlBuilder.GetParameters();
if (m_Parameters != null)
{
parameters.AddRange(m_Parameters);
}
return(new SQLiteCommandExecutionToken(DataSource, "Delete from " + m_Table.Name, sql.ToString(), parameters, lockType: LockType.Write));
}
public override CommandExecutionToken <SqlCommand, SqlParameter> Prepare(Materializer <SqlCommand, SqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = m_Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyTableType(DataSource, OperationTypes.Insert, m_TableType.Columns);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
var sql = new StringBuilder();
sqlBuilder.BuildInsertClause(sql, $"INSERT INTO {m_Table.Name.ToQuotedString()} (", null, ")");
sqlBuilder.BuildSelectClause(sql, " OUTPUT ", "Inserted.", null);
sqlBuilder.BuildSelectTvpForInsertClause(sql, " SELECT ", null, " FROM @ValuesParameter ");
sql.Append(";");
var parameters = sqlBuilder.GetParameters();
parameters.Add(new SqlParameter()
{
ParameterName = "@ValuesParameter",
Value = m_Source,
SqlDbType = SqlDbType.Structured,
TypeName = m_TableType.Name.ToQuotedString()
});
return(new SqlServerCommandExecutionToken(DataSource, "Insert batch into " + m_Table.Name, sql.ToString(), parameters));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer"></param>
/// <returns><see cref="PostgreSqlCommandExecutionToken" /></returns>
public override CommandExecutionToken <NpgsqlCommand, NpgsqlParameter> Prepare(Materializer <NpgsqlCommand, NpgsqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = Table.CreateSqlBuilder(StrictMode);
var desiredColumns = materializer.DesiredColumns();
sqlBuilder.ApplyArgumentValue(DataSource, ArgumentValue, m_Options, desiredColumns == Materializer.NoColumns);
sqlBuilder.ApplyDesiredColumns(desiredColumns);
if (KeyColumns.Count > 0)
{
sqlBuilder.OverrideKeys(KeyColumns);
}
var sql = new StringBuilder();
sqlBuilder.BuildDeleteStatement(sql, Table.Name.ToString(), null);
sqlBuilder.BuildSelectClause(sql, " RETURNING ", null, ";");
return(new PostgreSqlCommandExecutionToken(DataSource, "Delete from " + Table.Name, sql.ToString(), sqlBuilder.GetParameters()).CheckDeleteRowCount(m_Options));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer">The materializer.</param>
/// <returns>ExecutionToken<TCommand>.</returns>
public override CommandExecutionToken <SqlCommand, SqlParameter> Prepare(Materializer <SqlCommand, SqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyArgumentValue(DataSource, ArgumentValue, m_Options);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
if (KeyColumns.Count > 0)
{
sqlBuilder.OverrideKeys(KeyColumns);
}
var sql = new StringBuilder();
string?header;
string?intoClause;
string?footer;
sqlBuilder.UseTableVariable(Table, out header, out intoClause, out footer);
sql.Append(header);
sql.Append("DELETE FROM " + Table.Name.ToQuotedString());
sqlBuilder.BuildSelectClause(sql, " OUTPUT ", "Deleted.", intoClause);
sqlBuilder.BuildWhereClause(sql, " WHERE ", null);
sql.Append(";");
sql.Append(footer);
return(new SqlServerCommandExecutionToken(DataSource, "Delete from " + Table.Name, sql.ToString(), sqlBuilder.GetParameters()).CheckDeleteRowCount(m_Options));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer">The materializer.</param>
/// <returns>ExecutionToken<TCommand>.</returns>
public override CommandExecutionToken <SqlCommand, SqlParameter> Prepare(Materializer <SqlCommand, SqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyArgumentValue(DataSource, ArgumentValue, m_Options);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
var sql = new StringBuilder();
string header;
string intoClause;
string footer;
sqlBuilder.UseTableVariable(Table, out header, out intoClause, out footer);
sql.Append(header);
sqlBuilder.BuildInsertClause(sql, $"INSERT INTO {Table.Name.ToQuotedString()} (", null, ")");
sqlBuilder.BuildSelectClause(sql, " OUTPUT ", "Inserted.", intoClause);
sqlBuilder.BuildValuesClause(sql, " VALUES (", ")");
sql.Append(";");
sql.Append(footer);
return(new SqlServerCommandExecutionToken(DataSource, "Insert into " + Table.Name, sql.ToString(), sqlBuilder.GetParameters()));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer">The materializer.</param>
/// <returns>ExecutionToken<TCommand>.</returns>
public override CommandExecutionToken <OleDbCommand, OleDbParameter> Prepare(Materializer <OleDbCommand, OleDbParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyArgumentValue(DataSource, ArgumentValue, m_Options);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
var availableColumns = sqlBuilder.GetParameterizedColumns().ToList();
var sql = new StringBuilder($"MERGE INTO {Table.Name.ToQuotedString()} target USING ");
sql.Append("(VALUES (" + string.Join(", ", availableColumns.Select(c => "?")) + ")) AS source (" + string.Join(", ", availableColumns.Select(c => c.QuotedSqlName)) + ")");
sql.Append(" ON ");
sql.Append(string.Join(" AND ", sqlBuilder.GetKeyColumns().ToList().Select(c => $"target.{c.QuotedSqlName} = source.{c.QuotedSqlName}")));
sql.Append(" WHEN MATCHED THEN UPDATE SET ");
sql.Append(string.Join(", ", sqlBuilder.GetUpdateColumns().Select(x => $"{x.QuotedSqlName} = source.{x.QuotedSqlName}")));
var insertColumns = sqlBuilder.GetInsertColumns();
sql.Append(" WHEN NOT MATCHED THEN INSERT (");
sql.Append(string.Join(", ", insertColumns.Select(x => x.QuotedSqlName)));
sql.Append(") VALUES (");
sql.Append(string.Join(", ", insertColumns.Select(x => "source." + x.QuotedSqlName)));
sql.Append(" )");
sqlBuilder.BuildSelectClause(sql, " OUTPUT ", "Inserted.", null);
sql.Append(";");
return(new OleDbCommandExecutionToken(DataSource, "Insert or update " + Table.Name, sql.ToString(), sqlBuilder.GetParameters()));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer">The materializer.</param>
/// <returns>ExecutionToken<TCommand>.</returns>
public override CommandExecutionToken <SqlCommand, SqlParameter> Prepare(Materializer <SqlCommand, SqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyArgumentValue(DataSource, ArgumentValue, m_Options);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
var prefix = m_Options.HasFlag(UpdateOptions.ReturnOldValues) ? "Deleted." : "Inserted.";
var sql = new StringBuilder();
string header;
string intoClause;
string footer;
sqlBuilder.UseTableVariable(Table, out header, out intoClause, out footer);
sql.Append(header);
sql.Append($"UPDATE {Table.Name.ToQuotedString()}");
sqlBuilder.BuildSetClause(sql, " SET ", null, null);
sqlBuilder.BuildSelectClause(sql, " OUTPUT ", prefix, intoClause);
sqlBuilder.BuildWhereClause(sql, " WHERE ", null);
sql.Append(";");
sql.Append(footer);
return(new SqlServerCommandExecutionToken(DataSource, "Update " + Table.Name, sql.ToString(), sqlBuilder.GetParameters()).CheckUpdateRowCount(m_Options));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer"></param>
/// <returns><see cref="PostgreSqlCommandExecutionToken" /></returns>
public override CommandExecutionToken <NpgsqlCommand, NpgsqlParameter> Prepare(Materializer <NpgsqlCommand, NpgsqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyArgumentValue(DataSource, ArgumentValue, m_Options);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
var sql = new StringBuilder();
if (m_Options.HasFlag(UpdateOptions.ReturnOldValues))
{
sqlBuilder.BuildSelectByKeyStatement(sql, Table.Name.ToQuotedString(), ";");
sql.AppendLine();
}
sqlBuilder.BuildUpdateByKeyStatement(sql, Table.Name.ToQuotedString(), null);
if (!m_Options.HasFlag(UpdateOptions.ReturnOldValues))
{
sqlBuilder.BuildSelectClause(sql, " RETURNING ", null, null);
}
sql.Append(";");
return(new PostgreSqlCommandExecutionToken(DataSource, "Update " + Table.Name, sql.ToString(), sqlBuilder.GetParameters()).CheckUpdateRowCount(m_Options));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer"></param>
/// <returns><see cref="AccessCommandExecutionToken" /></returns>
public override CommandExecutionToken <OleDbCommand, OleDbParameter> Prepare(Materializer <OleDbCommand, OleDbParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = Table.CreateSqlBuilder(StrictMode);
var desiredColumns = materializer.DesiredColumns();
sqlBuilder.ApplyArgumentValue(DataSource, ArgumentValue, m_Options, desiredColumns == Materializer.NoColumns);
sqlBuilder.ApplyDesiredColumns(desiredColumns);
if (KeyColumns.Count > 0)
{
sqlBuilder.OverrideKeys(KeyColumns);
}
var sql = new StringBuilder();
sql.Append("DELETE FROM " + Table.Name.ToQuotedString());
sqlBuilder.BuildWhereClause(sql, " WHERE ", null);
sql.Append(";");
return(new AccessCommandExecutionToken(DataSource, "Delete from " + Table.Name, sql.ToString(), sqlBuilder.GetParameters(DataSource)).CheckDeleteRowCount(m_Options));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer"></param>
/// <returns><see cref="SQLiteCommandExecutionToken" /></returns>
public override CommandExecutionToken <SQLiteCommand, SQLiteParameter> Prepare(Materializer <SQLiteCommand, SQLiteParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyArgumentValue(DataSource, ArgumentValue, m_Options);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
if (KeyColumns.Count > 0)
{
sqlBuilder.OverrideKeys(KeyColumns);
}
var sql = new StringBuilder();
sqlBuilder.BuildSelectByKeyStatement(sql, Table.Name.ToQuotedString(), ";");
sql.AppendLine();
sqlBuilder.BuildDeleteStatement(sql, Table.Name.ToQuotedString(), ";");
return(new SQLiteCommandExecutionToken(DataSource, "Delete from " + Table.Name, sql.ToString(), sqlBuilder.GetParameters(), lockType: LockType.Write).CheckDeleteRowCount(m_Options));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer"></param>
/// <returns><see cref="SQLiteCommandExecutionToken" /></returns>
public override CommandExecutionToken <SQLiteCommand, SQLiteParameter> Prepare(Materializer <SQLiteCommand, SQLiteParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var identityInsert = m_Options.HasFlag(InsertOptions.IdentityInsert);
var sqlBuilder = Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyArgumentValue(DataSource, ArgumentValue, m_Options);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
if (KeyColumns.Count > 0)
{
sqlBuilder.OverrideKeys(KeyColumns);
}
var sql = new StringBuilder();
sqlBuilder.BuildInsertStatement(sql, Table.Name.ToQuotedString(), ";", identityInsert);
sql.AppendLine();
sqlBuilder.BuildSelectClause(sql, "SELECT ", null, $" FROM {Table.Name.ToQuotedString()} WHERE ROWID=last_insert_rowid();");
return(new SQLiteCommandExecutionToken(DataSource, "Insert into " + Table.Name, sql.ToString(), sqlBuilder.GetParameters(), lockType: LockType.Write));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer"></param>
/// <returns><see cref="PostgreSqlCommandExecutionToken" /></returns>
public override CommandExecutionToken <NpgsqlCommand, NpgsqlParameter> Prepare(Materializer <NpgsqlCommand, NpgsqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var identityInsert = m_Options.HasFlag(UpsertOptions.IdentityInsert);
if (identityInsert)
{
throw new NotImplementedException("See issue 256. https://github.com/TortugaResearch/Tortuga.Chain/issues/256");
}
//var primaryKeyNames = Table.PrimaryKeyColumns.Select(x => x.QuotedSqlName);
var sqlBuilder = Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyArgumentValue(DataSource, ArgumentValue, m_Options);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
if (KeyColumns.Count > 0)
{
sqlBuilder.OverrideKeys(KeyColumns);
}
var sql = new StringBuilder();
List <NpgsqlParameter> keyParameters;
var isPrimaryKeyIdentity = sqlBuilder.PrimaryKeyIsIdentity(out keyParameters);
if (isPrimaryKeyIdentity && KeyColumns.Count == 0)
{
var areKeysNull = keyParameters.Any(c => c.Value == DBNull.Value || c.Value == null) ? true : false;
if (areKeysNull)
{
sqlBuilder.BuildInsertStatement(sql, Table.Name.ToString(), null);
}
else
{
sqlBuilder.BuildUpdateByKeyStatement(sql, Table.Name.ToString(), null);
}
sqlBuilder.BuildSelectClause(sql, " RETURNING ", null, ";");
}
else
{
string conflictNames = string.Join(", ", sqlBuilder.GetKeyColumns().Select(x => x.QuotedSqlName));
sqlBuilder.BuildInsertClause(sql, $"INSERT INTO {Table.Name.ToString()} (", null, ")");
sqlBuilder.BuildValuesClause(sql, " VALUES (", ")");
sqlBuilder.BuildSetClause(sql, $" ON CONFLICT ({conflictNames}) DO UPDATE SET ", null, null);
sqlBuilder.BuildSelectClause(sql, " RETURNING ", null, ";");
}
//Looks like ON CONFLICT is useful here http://www.postgresql.org/docs/current/static/sql-insert.html
//Use RETURNING in place of SQL Servers OUTPUT clause http://www.postgresql.org/docs/current/static/sql-insert.html
return(new PostgreSqlCommandExecutionToken(DataSource, "Insert or update " + Table.Name, sql.ToString(), sqlBuilder.GetParameters()));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer">The materializer.</param>
/// <returns>ExecutionToken<TCommand>.</returns>
public override CommandExecutionToken <SqlCommand, SqlParameter> Prepare(Materializer <SqlCommand, SqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyArgumentValue(DataSource, ArgumentValue, m_Options);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
if (KeyColumns.Count > 0)
{
sqlBuilder.OverrideKeys(KeyColumns);
}
var availableColumns = sqlBuilder.GetParameterizedColumns().ToList();
var sql = new StringBuilder();
sqlBuilder.UseTableVariable(Table, out var header, out var intoClause, out var footer);
sql.Append(header);
var identityInsert = m_Options.HasFlag(UpsertOptions.IdentityInsert);
if (identityInsert)
{
sql.AppendLine($"SET IDENTITY_INSERT {Table.Name.ToQuotedString()} ON;");
}
sql.Append($"MERGE INTO {Table.Name.ToQuotedString()} WITH ( UPDLOCK, SERIALIZABLE ) target USING "); sql.Append("(VALUES (" + string.Join(", ", availableColumns.Select(c => c.SqlVariableName)) + ")) AS source (" + string.Join(", ", availableColumns.Select(c => c.QuotedSqlName)) + ")");
sql.Append(" ON ");
sql.Append(string.Join(" AND ", sqlBuilder.GetKeyColumns().ToList().Select(c => $"target.{c.QuotedSqlName} = source.{c.QuotedSqlName}")));
sql.Append(" WHEN MATCHED THEN UPDATE SET ");
sql.Append(string.Join(", ", sqlBuilder.GetUpdateColumns().Select(x => $"{x.QuotedSqlName} = source.{x.QuotedSqlName}")));
var insertColumns = sqlBuilder.GetInsertColumns(m_Options.HasFlag(UpsertOptions.IdentityInsert));
sql.Append(" WHEN NOT MATCHED THEN INSERT (");
sql.Append(string.Join(", ", insertColumns.Select(x => x.QuotedSqlName)));
sql.Append(") VALUES (");
sql.Append(string.Join(", ", insertColumns.Select(x => "source." + x.QuotedSqlName)));
sql.Append(" )");
sqlBuilder.BuildSelectClause(sql, " OUTPUT ", "Inserted.", intoClause);
sql.Append(";");
sql.Append(footer);
if (identityInsert)
{
sql.AppendLine($"SET IDENTITY_INSERT {Table.Name.ToQuotedString()} OFF;");
}
return(new SqlServerCommandExecutionToken(DataSource, "Insert or update " + Table.Name, sql.ToString(), sqlBuilder.GetParameters()));
}
public override CommandExecutionToken <SqlCommand, SqlParameter> Prepare(Materializer <SqlCommand, SqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = m_Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
//This sets up the sqlBuilder. We're not actually going to build the parameters now
sqlBuilder.ApplyArgumentValue(DataSource, m_SourceList[0], m_Options);
var sql = new StringBuilder();
bool identityInsert = m_Options.HasFlag(InsertOptions.IdentityInsert);
if (identityInsert)
{
sql.AppendLine($"SET IDENTITY_INSERT {m_Table.Name.ToQuotedString()} ON;");
}
sqlBuilder.BuildInsertClause(sql, $"INSERT INTO {m_Table.Name.ToQuotedString()} (", null, ")", identityInsert);
sqlBuilder.BuildSelectClause(sql, " OUTPUT ", "Inserted.", null);
sql.AppendLine("VALUES");
var parameters = new List <SqlParameter>();
for (var i = 0; i < m_SourceList.Count; i++)
{
var parameterSuffix = "_" + i;
var footer = (i == m_SourceList.Count - 1) ? ");" : "),";
sqlBuilder.OverrideArgumentValue(DataSource, AuditRules.OperationTypes.Insert, m_SourceList[i]);
sqlBuilder.BuildValuesClause(sql, "(", footer, identityInsert, parameterSuffix, parameters, Utilities.ParameterBuilderCallback);
}
var maxParams = DataSource.DatabaseMetadata.MaxParameters !.Value;
if (parameters.Count > maxParams)
{
var parametersPerRow = parameters.Count / m_SourceList.Count;
var maxRows = maxParams / parametersPerRow;
throw new InvalidOperationException($"Batch insert exceeds SQL Server's parameter limit of {DataSource.DatabaseMetadata.MaxParameters}. Supply a table type, break the call into batches of {maxRows}, use InsertMultipleBatch, or use BulkInsert");
}
if (identityInsert)
{
sql.AppendLine($"SET IDENTITY_INSERT {m_Table.Name.ToQuotedString()} OFF;");
}
return(new SqlServerCommandExecutionToken(DataSource, "Insert batch into " + m_Table.Name, sql.ToString(), parameters));
}
public override CommandExecutionToken <MySqlCommand, MySqlParameter> Prepare(Materializer <MySqlCommand, MySqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = m_Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
List <MySqlParameter> parameters;
var sql = new StringBuilder();
if (sqlBuilder.HasReadFields)
{
sqlBuilder.BuildSelectClause(sql, "SELECT ", null, " FROM " + m_Table.Name.ToQuotedString());
if (m_FilterValue != null)
{
sql.Append(" WHERE " + sqlBuilder.ApplyFilterValue(m_FilterValue, m_FilterOptions));
}
else if (!string.IsNullOrWhiteSpace(m_WhereClause))
{
sql.Append(" WHERE " + m_WhereClause);
}
sql.AppendLine(";");
}
sql.Append("DELETE FROM " + m_Table.Name.ToQuotedString());
if (m_FilterValue != null)
{
sql.Append(" WHERE " + sqlBuilder.ApplyFilterValue(m_FilterValue, m_FilterOptions));
parameters = sqlBuilder.GetParameters();
}
else if (!string.IsNullOrWhiteSpace(m_WhereClause))
{
sql.Append(" WHERE " + m_WhereClause);
parameters = SqlBuilder.GetParameters <MySqlParameter>(m_ArgumentValue);
parameters.AddRange(sqlBuilder.GetParameters());
}
else
{
parameters = sqlBuilder.GetParameters();
}
sql.Append(";");
if (m_Parameters != null)
{
parameters.AddRange(m_Parameters);
}
return(new MySqlCommandExecutionToken(DataSource, "Delete from " + m_Table.Name, sql.ToString(), parameters));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer"></param>
/// <returns><see cref="MySqlCommandExecutionToken" /></returns>
public override CommandExecutionToken <MySqlCommand, MySqlParameter> Prepare(Materializer <MySqlCommand, MySqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyArgumentValue(DataSource, ArgumentValue, m_Options);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
var identityInsert = m_Options.HasFlag(UpsertOptions.IdentityInsert);
var sql = new StringBuilder();
List <MySqlParameter> keyParameters;
var isPrimaryKeyIdentity = sqlBuilder.PrimaryKeyIsIdentity(out keyParameters);
if (isPrimaryKeyIdentity && !identityInsert)
{
var areKeysNull = keyParameters.Any(c => c.Value == DBNull.Value || c.Value == null) ? true : false;
if (areKeysNull)
{
sqlBuilder.BuildInsertStatement(sql, Table.Name.ToString(), null);
}
else
{
sqlBuilder.BuildUpdateByKeyStatement(sql, Table.Name.ToString(), null);
}
sql.Append(";");
}
else
{
sqlBuilder.BuildInsertClause(sql, $"INSERT INTO {Table.Name.ToString()} (", null, ")", identityInsert);
sqlBuilder.BuildValuesClause(sql, " VALUES (", ")", identityInsert);
sqlBuilder.BuildSetClause(sql, $" ON DUPLICATE KEY UPDATE ", null, null);
sql.Append(";");
}
if (sqlBuilder.HasReadFields)
{
var keys = sqlBuilder.GetKeyColumns().ToList();
if (keys.Count != 1)
{
throw new NotSupportedException("Cannot return data from a MySQL Upsert unless there is a single primary key.");
}
var key = keys[0];
sqlBuilder.BuildSelectClause(sql, "SELECT ", null, $" FROM {Table.Name.ToQuotedString()} WHERE {key.QuotedSqlName} = CASE WHEN {key.SqlVariableName} IS NULL OR {key.SqlVariableName} = 0 THEN LAST_INSERT_ID() ELSE {key.SqlVariableName} END;");
}
return(new MySqlCommandExecutionToken(DataSource, "Insert or update " + Table.Name, sql.ToString(), sqlBuilder.GetParameters()));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer"></param>
/// <returns><see cref="MySqlCommandExecutionToken" /></returns>
public override CommandExecutionToken <MySqlCommand, MySqlParameter> Prepare(Materializer <MySqlCommand, MySqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var identityInsert = m_Options.HasFlag(InsertOptions.IdentityInsert);
var sqlBuilder = Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyArgumentValue(DataSource, ArgumentValue, m_Options);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
if (KeyColumns.Count > 0)
{
sqlBuilder.OverrideKeys(KeyColumns);
}
var sql = new StringBuilder();
sqlBuilder.BuildInsertClause(sql, $"INSERT INTO {Table.Name.ToString()} (", null, ")", identityInsert);
sqlBuilder.BuildValuesClause(sql, " VALUES (", ");", identityInsert);
if (sqlBuilder.HasReadFields)
{
var identityColumn = Table.Columns.Where(c => c.IsIdentity).SingleOrDefault();
if (!identityInsert && identityColumn != null)
{
sqlBuilder.BuildSelectClause(sql, "SELECT ", null, null);
sql.Append(" FROM " + Table.Name.ToQuotedString());
sql.Append(" WHERE " + identityColumn.QuotedSqlName + " = LAST_INSERT_ID()");
sql.Append(";");
}
else
{
var primaryKeys = Table.PrimaryKeyColumns;
if (primaryKeys.Count == 0)
{
throw new MappingException($"Insert operation cannot return any values for { Table.Name} because it doesn't have a primary key.");
}
sqlBuilder.BuildSelectClause(sql, "SELECT ", null, null);
sql.Append(" FROM " + Table.Name.ToQuotedString());
sqlBuilder.BuildWhereClause(sql, " WHERE ", null);
sql.Append(";");
}
}
return(new MySqlCommandExecutionToken(DataSource, "Insert into " + Table.Name, sql.ToString(), sqlBuilder.GetParameters()));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer"></param>
/// <returns><see cref="PostgreSqlCommandExecutionToken" /></returns>
public override CommandExecutionToken <NpgsqlCommand, NpgsqlParameter> Prepare(Materializer <NpgsqlCommand, NpgsqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var identityInsert = m_Options.HasFlag(InsertOptions.IdentityInsert);
if (identityInsert)
{
throw new NotImplementedException("See issue 256. https://github.com/docevaad/Chain/issues/256");
}
var sqlBuilder = m_Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
//This sets up the sqlBuilder. We're not actually going to build the parameters now
sqlBuilder.ApplyArgumentValue(DataSource, m_SourceList[0], m_Options);
var sql = new StringBuilder();
sqlBuilder.BuildInsertClause(sql, $"INSERT INTO {m_Table.Name.ToQuotedString()} (", null, ")", identityInsert);
sql.AppendLine("VALUES");
var parameters = new List <NpgsqlParameter>();
for (var i = 0; i < m_SourceList.Count; i++)
{
var parameterSuffix = "_" + i;
var footer = (i == m_SourceList.Count - 1) ? ")" : "),";
sqlBuilder.OverrideArgumentValue(DataSource, AuditRules.OperationTypes.Insert, m_SourceList[i]);
sqlBuilder.BuildValuesClause(sql, "(", footer, identityInsert, parameterSuffix, parameters, Utilities.ParameterBuilderCallback);
}
sqlBuilder.BuildSelectClause(sql, " RETURNING ", null, ";");
var maxParams = DataSource.DatabaseMetadata.MaxParameters !.Value;
if (parameters.Count > maxParams)
{
var parametersPerRow = parameters.Count / m_SourceList.Count;
var maxRows = maxParams / parametersPerRow;
throw new InvalidOperationException($"Batch insert exceeds PostgreSql's parameter limit of {DataSource.DatabaseMetadata.MaxParameters}. Break the call into batches of {maxRows} or use InsertMultipleBatch");
}
return(new PostgreSqlCommandExecutionToken(DataSource, "Insert batch into " + m_Table.Name, sql.ToString(), parameters));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer">The materializer.</param>
/// <returns>ExecutionToken<TCommand>.</returns>
public override CommandExecutionToken <OleDbCommand, OleDbParameter> Prepare(Materializer <OleDbCommand, OleDbParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = m_Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
List <OleDbParameter> parameters;
var sql = new StringBuilder();
sqlBuilder.UseTableVariable(m_Table, out var header, out var intoClause, out var footer);
sql.Append(header);
sql.Append("DELETE FROM " + m_Table.Name.ToQuotedString());
sqlBuilder.BuildSelectClause(sql, " OUTPUT ", "Deleted.", intoClause);
if (m_FilterValue != null)
{
sql.Append(" WHERE " + sqlBuilder.ApplyAnonymousFilterValue(m_FilterValue, m_FilterOptions));
parameters = sqlBuilder.GetParameters();
}
else if (!string.IsNullOrWhiteSpace(m_WhereClause))
{
sql.Append(" WHERE " + m_WhereClause);
parameters = SqlBuilder.GetParameters <OleDbParameter>(m_ArgumentValue);
parameters.AddRange(sqlBuilder.GetParameters());
}
else
{
parameters = sqlBuilder.GetParameters();
}
sql.Append(";");
sql.Append(footer);
if (m_Parameters != null)
{
parameters.AddRange(m_Parameters);
}
return(new OleDbCommandExecutionToken(DataSource, "Delete from " + m_Table.Name, sql.ToString(), parameters));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer">The materializer.</param>
/// <returns>ExecutionToken<TCommand>.</returns>
public override CommandExecutionToken <SqlCommand, SqlParameter> Prepare(Materializer <SqlCommand, SqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyArgumentValue(DataSource, ArgumentValue, m_Options);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
if (KeyColumns.Count > 0)
{
sqlBuilder.OverrideKeys(KeyColumns);
}
var sql = new StringBuilder();
string?header;
string?intoClause;
string?footer;
sqlBuilder.UseTableVariable(Table, out header, out intoClause, out footer);
sql.Append(header);
bool identityInsert = m_Options.HasFlag(InsertOptions.IdentityInsert);
if (identityInsert)
{
sql.AppendLine($"SET IDENTITY_INSERT {Table.Name.ToQuotedString()} ON;");
}
sqlBuilder.BuildInsertClause(sql, $"INSERT INTO {Table.Name.ToQuotedString()} (", null, ")", identityInsert);
sqlBuilder.BuildSelectClause(sql, " OUTPUT ", "Inserted.", intoClause);
sqlBuilder.BuildValuesClause(sql, " VALUES (", ")", identityInsert);
sql.Append(";");
sql.Append(footer);
if (identityInsert)
{
sql.AppendLine($"SET IDENTITY_INSERT {Table.Name.ToQuotedString()} OFF;");
}
return(new SqlServerCommandExecutionToken(DataSource, "Insert into " + Table.Name, sql.ToString(), sqlBuilder.GetParameters()));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer"></param>
/// <returns><see cref="AccessCommandExecutionToken" /></returns>
public override CommandExecutionToken <OleDbCommand, OleDbParameter> Prepare(Materializer <OleDbCommand, OleDbParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var desiredColumns = materializer.DesiredColumns();
var identityInsert = m_Options.HasFlag(InsertOptions.IdentityInsert);
var sqlBuilder = Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyArgumentValue(DataSource, ArgumentValue, m_Options);
sqlBuilder.ApplyDesiredColumns(desiredColumns);
if (KeyColumns.Count > 0)
{
sqlBuilder.OverrideKeys(KeyColumns);
}
var sql = new StringBuilder();
sqlBuilder.BuildInsertClause(sql, $"INSERT INTO {Table.Name.ToQuotedString()} (", null, ")", identityInsert);
sqlBuilder.BuildValuesClause(sql, " VALUES (", ")", identityInsert);
sql.Append(";");
var result = new AccessCommandExecutionToken(DataSource, "Insert into " + Table.Name, sql.ToString(), sqlBuilder.GetParameters());
if (desiredColumns == Materializer.AutoSelectDesiredColumns)
{
result.ExecutionMode = AccessCommandExecutionMode.NonQuery;
result.NextCommand = new AccessCommandExecutionToken(DataSource, "Fetch autonumber", "SELECT @@IDENTITY", new List <OleDbParameter>());
}
else if (desiredColumns.Count > 0)
{
result.ExecutionMode = AccessCommandExecutionMode.NonQuery;
result.NextCommand = new AccessCommandExecutionToken(DataSource, "Fetch autonumber", "SELECT @@IDENTITY", new List <OleDbParameter>());
result.NextCommand.ExecutionMode = AccessCommandExecutionMode.ExecuteScalarAndForward;
result.NextCommand.ForwardResult = value => { result.NextCommand.NextCommand = PrepareNext(desiredColumns, value); };
}
return(result);
}
public override CommandExecutionToken <NpgsqlCommand, NpgsqlParameter> Prepare(Materializer <NpgsqlCommand, NpgsqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = m_Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
List <NpgsqlParameter> parameters;
var sql = new StringBuilder();
sql.Append("DELETE FROM " + m_Table.Name.ToQuotedString());
if (FilterValue != null)
{
sql.Append(" WHERE " + sqlBuilder.ApplyFilterValue(FilterValue, FilterOptions));
parameters = sqlBuilder.GetParameters();
}
else if (!string.IsNullOrWhiteSpace(WhereClause))
{
sql.Append(" WHERE " + WhereClause);
parameters = SqlBuilder.GetParameters <NpgsqlParameter>(ArgumentValue);
parameters.AddRange(sqlBuilder.GetParameters());
}
else
{
parameters = sqlBuilder.GetParameters();
}
sqlBuilder.BuildSelectClause(sql, " RETURNING ", null, null);
sql.Append(";");
if (Parameters != null)
{
parameters.AddRange(Parameters);
}
return(new PostgreSqlCommandExecutionToken(DataSource, "Delete from " + m_Table.Name, sql.ToString(), parameters).CheckDeleteRowCount(Options, ExpectedRowCount));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer">The materializer.</param>
/// <returns>ExecutionToken<TCommand>.</returns>
public override CommandExecutionToken <OleDbCommand, OleDbParameter> Prepare(Materializer <OleDbCommand, OleDbParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
if (!Table.HasPrimaryKey && !m_Options.HasFlag(UpdateOptions.UseKeyAttribute) && KeyColumns.Count == 0)
{
throw new MappingException($"Cannot perform an update operation on {Table.Name} unless UpdateOptions.UseKeyAttribute or .WithKeys() is specified.");
}
var sqlBuilder = Table.CreateSqlBuilder(StrictMode);
var desiredColumns = materializer.DesiredColumns();
sqlBuilder.ApplyArgumentValue(DataSource, ArgumentValue, m_Options, desiredColumns == Materializer.NoColumns);
sqlBuilder.ApplyDesiredColumns(desiredColumns);
if (KeyColumns.Count > 0)
{
sqlBuilder.OverrideKeys(KeyColumns);
}
var prefix = m_Options.HasFlag(UpdateOptions.ReturnOldValues) ? "Deleted." : "Inserted.";
var sql = new StringBuilder();
string?header;
string?intoClause;
string?footer;
sqlBuilder.UseTableVariable(Table, out header, out intoClause, out footer);
sql.Append(header);
sql.Append($"UPDATE {Table.Name.ToQuotedString()}");
sqlBuilder.BuildAnonymousSetClause(sql, " SET ", null, null);
sqlBuilder.BuildSelectClause(sql, " OUTPUT ", prefix, intoClause);
sqlBuilder.BuildAnonymousWhereClause(sql, " WHERE ", null, false); //second pass parameters
sql.Append(";");
sql.Append(footer);
return(new OleDbCommandExecutionToken(DataSource, "Update " + Table.Name, sql.ToString(), sqlBuilder.GetParameters()).CheckUpdateRowCount(m_Options));
}