///<summary>Applies an update row to the database.</summary>
public override void ApplyUpdate(TransactionContext context, SchemaMapping schema, Row row)
{
if (context == null) throw new ArgumentNullException("context");
if (schema == null) throw new ArgumentNullException("schema");
if (row == null) throw new ArgumentNullException("row");
var sql = new StringBuilder();
//UPDATE [SchemaName].[TableName]
//SET [FirstColumn] = @Col0, [SecondColumn] = @Col1
//WHERE @IDColumn = @ID AND RowVersion = @version;
sql.Append("UPDATE ").AppendLine(QualifyTable(schema));
sql.Append("SET").AppendJoin(
schema.Columns.Select((c, i) => c.SqlName.EscapeSqlIdentifier() + " = @Col" + i.ToString(CultureInfo.InvariantCulture)), ", "
).AppendLine();
sql.Append("WHERE ").Append(schema.PrimaryKey.SqlName.EscapeSqlIdentifier()).Append(" = @Col").Append(schema.Columns.IndexOf(schema.PrimaryKey))
.Append(" AND RowVersion = @version;");
using (var command = context.CreateCommand(sql.ToString())) {
PopulateParameters(command, schema, row);
var versionParameter = command.CreateParameter();
versionParameter.ParameterName = "@version";
versionParameter.Value = row.RowVersion;
command.Parameters.Add(versionParameter);
if (command.ExecuteNonQuery() != 1)
ThrowRowModified(context, schema, row);
}
RefreshVersion(context, schema, row);
}
///<summary>Applies an inserted row to the database.</summary>
public override void ApplyInsert(TransactionContext context, SchemaMapping schema, Row row)
{
if (context == null) throw new ArgumentNullException("context");
if (schema == null) throw new ArgumentNullException("schema");
if (row == null) throw new ArgumentNullException("row");
var sql = new StringBuilder();
//INSERT INTO [SchemaName].[TableName]
// ([FirstColumn], [SecondColumn])
//VALUES(@Col0, @Col1);
sql.Append("INSERT INTO ").AppendLine(QualifyTable(schema));
sql.Append("\t(").AppendJoin(
schema.Columns.Select(c => c.SqlName.EscapeSqlIdentifier()), ", "
).AppendLine(")");
sql.Append("VALUES(").AppendJoin(
schema.Columns.Select((c, i) => "@Col" + i.ToString(CultureInfo.InvariantCulture)), ", "
).Append(");");
using (var command = context.CreateCommand(sql.ToString())) {
PopulateParameters(command, schema, row);
if (command.ExecuteNonQuery() != 1)
throw new DataException("INSERT command didn't work");
}
RefreshVersion(context, schema, row);
}
public SqlTransactionalCommandsContext CreateSqlTransactionalCommandsContext(TransactionContext transactionContext)
{
var connection = this.OpenConnection();
try
{
return this.CreateSqlTransactionalCommandsContext(connection, transactionContext);
}
catch
{
ActionUtils.IgnoreExceptions(() => connection.Dispose());
throw;
}
}
///<summary>Applies a deleted row to the database.</summary>
public void ApplyDelete(TransactionContext context, SchemaMapping schema, Row row)
{
if (context == null) throw new ArgumentNullException("context");
if (schema == null) throw new ArgumentNullException("schema");
if (row == null) throw new ArgumentNullException("row");
//Should work fine for SQLCE too
using (var command = context.CreateCommand(
"DELETE FROM " + QualifyTable(schema) + " WHERE " + schema.PrimaryKey.SqlName.EscapeSqlIdentifier() + " = @ID AND RowVersion = @version",
new { ID = row[row.Schema.PrimaryKey], version = row.RowVersion }
)) {
if (command.ExecuteNonQuery() != 1)
ThrowRowModified(context, schema, row);
}
}
///<summary>Applies an inserted row to the database.</summary>
public virtual void ApplyInsert(TransactionContext context, SchemaMapping schema, Row row)
{
if (context == null) throw new ArgumentNullException("context");
if (schema == null) throw new ArgumentNullException("schema");
if (row == null) throw new ArgumentNullException("row");
var sql = new StringBuilder();
//INSERT INTO [SchemaName].[TableName]
// ([FirstColumn], [SecondColumn])
//OUTPUT INSERTED.RowVersion
//VALUES(@Col0, @Col1);
sql.Append("INSERT INTO ").AppendLine(QualifyTable(schema));
sql.Append("\t(").AppendJoin(
schema.Columns.Select(c => c.SqlName.EscapeSqlIdentifier()), ", "
).AppendLine(")");
sql.AppendLine("OUTPUT INSERTED.RowVersion");
sql.Append("VALUES(").AppendJoin(
schema.Columns.Select((c, i) => "@Col" + i.ToString(CultureInfo.InvariantCulture)), ", "
).Append(");");
using (var command = context.CreateCommand(sql.ToString())) {
PopulateParameters(command, schema, row);
using (var reader = command.ExecuteReader()) {
if (!reader.Read())
throw new DataException("INSERT command returned no rows");
row.RowVersion = reader.GetValue(0);
if (reader.Read())
throw new DataException("INSERT command returned multiple rows");
}
}
}
protected SqlTransactionalCommandsContext(SqlDatabaseContext sqlDatabaseContext, IDbConnection dbConnection, TransactionContext transactionContext)
{
this.TransactionContext = transactionContext;
try
{
this.DbConnection = dbConnection;
this.SqlDatabaseContext = sqlDatabaseContext;
this.DataAccessModel = sqlDatabaseContext.DataAccessModel;
this.emulateMultipleActiveResultSets = !sqlDatabaseContext.SqlDialect.SupportsCapability(SqlCapability.MultipleActiveResultSets);
if (transactionContext?.DataAccessTransaction != null)
{
this.dbTransaction = dbConnection.BeginTransaction(ConvertIsolationLevel(transactionContext.DataAccessTransaction.IsolationLevel));
}
}
catch
{
this.Dispose(true);
throw;
}
}
protected virtual SqlTransactionalCommandsContext CreateSqlTransactionalCommandsContext(IDbConnection connection, TransactionContext transactionContext)
{
return new DefaultSqlTransactionalCommandsContext(this, connection, transactionContext);
}
public SqlServerSqlTransactionsCommandContext(SqlDatabaseContext sqlDatabaseContext, IDbConnection connection, TransactionContext transactionContext)
: base(sqlDatabaseContext, connection, transactionContext)
{
}
protected override SqlTransactionalCommandsContext CreateSqlTransactionalCommandsContext(IDbConnection connection, TransactionContext transactionContext)
{
return new SqlServerSqlTransactionsCommandContext(this, connection, transactionContext);
}
public PostgresSqlTransactionalCommandsContext(SqlDatabaseContext sqlDatabaseContext, IDbConnection connection, TransactionContext transactionContext)
: base(sqlDatabaseContext, connection, transactionContext)
{
}
///<summary>Refreshes a row's RowVersion.</summary>
///<remarks>.Net's SQL CE client doesn't
///support multiple SQL statements in a
///single command. Therefore, I need to
///use a separate command to update the
///RowVersion after updating or adding a
///row. This method will update the field.</remarks>
void RefreshVersion(TransactionContext context, SchemaMapping schema, Row row)
{
using (var command = context.CreateCommand(
"SELECT RowVersion FROM " + QualifyTable(schema) + " WHERE " + schema.PrimaryKey.SqlName.EscapeSqlIdentifier() + " = @ID",
new { ID = row[schema.PrimaryKey.Column] }
)) {
context.SetRowVersion(row, command.ExecuteScalar());
}
}
///<summary>Throws a RowModifiedException for a row, reading the row's current values from the database.</summary>
protected void ThrowRowModified(TransactionContext context, SchemaMapping schema, Row row)
{
if (context == null) throw new ArgumentNullException("context");
if (schema == null) throw new ArgumentNullException("schema");
if (row == null) throw new ArgumentNullException("row");
//SELECT [FirstColumn], [SecondColumn], [RowVersion]
//FROM [SchemaName].[TableName]
//WHERE [IDColumnName] = @ID
var sql = BuildSelectCommand(schema);
sql.AppendLine().Append("WHERE ").Append(schema.PrimaryKey.SqlName.EscapeSqlIdentifier()).Append(" = @ID");
using (var command = context.CreateCommand(sql.ToString(), new { ID = row[schema.PrimaryKey.Column] }))
using (var reader = command.ExecuteReader()) {
if (!reader.Read()) {
row.RowVersion = null;
throw new RowDeletedException(row);
}
var dict = schema.Columns.ToDictionary(cm => cm.Column, cm => reader[cm.SqlName]);
row.RowVersion = reader["RowVersion"];
if (reader.Read())
throw new InvalidOperationException("Duplicate ID");
throw new RowModifiedException(row, dict);
}
}
///<summary>Applies an update row to the database.</summary>
public virtual void ApplyUpdate(TransactionContext context, SchemaMapping schema, Row row)
{
if (context == null) throw new ArgumentNullException("context");
if (schema == null) throw new ArgumentNullException("schema");
if (row == null) throw new ArgumentNullException("row");
var sql = new StringBuilder();
//UPDATE [SchemaName].[TableName]
//SET [FirstColumn] = @Col0, [SecondColumn] = @Col1
//OUTPUT INSERTED.RowVersionINTO @version
//WHERE [IDColumnName] = @ColN AND RowVersion = @version;
sql.Append("UPDATE ").AppendLine(QualifyTable(schema));
sql.Append("SET").AppendJoin(
schema.Columns.Select((c, i) => c.SqlName.EscapeSqlIdentifier() + " = @Col" + i.ToString(CultureInfo.InvariantCulture)), ", "
).AppendLine();
sql.AppendLine("OUTPUT INSERTED.RowVersion");
sql.Append("WHERE ").Append(schema.PrimaryKey.SqlName.EscapeSqlIdentifier()).Append(" = @Col").Append(schema.Columns.IndexOf(schema.PrimaryKey))
.Append(" AND RowVersion = @version;");
using (var command = context.CreateCommand(sql.ToString())) {
PopulateParameters(command, schema, row);
var versionParameter = command.CreateParameter();
versionParameter.ParameterName = "@version";
versionParameter.Value = row.RowVersion;
command.Parameters.Add(versionParameter);
using (var reader = command.ExecuteReader()) {
if (!reader.Read()) {
reader.Close(); //A single connection cannot have two DataReaders at once
ThrowRowModified(context, schema, row);
}
row.RowVersion = reader.GetValue(0);
if (reader.Read())
throw new DBConcurrencyException("Concurrency FAIL!"); //Exception will be handled by TableSynchronizer
}
}
}
protected override SqlTransactionalCommandsContext CreateSqlTransactionalCommandsContext(IDbConnection connection, TransactionContext transactionContext)
{
return new PostgresDotConnectSqlTransactionalCommandsContext(this, connection, transactionContext);
}