public void DistributedTransactionRollback()
{
int field_serial1;
int field_serial2;
var connectionString = ConnectionString + ";enlist=true";
using (var scope = new TransactionScope())
{
//UseStringParameterWithNoNpgsqlDbType
using (var connection = new NpgsqlConnection(connectionString))
{
connection.Open();
var command = new NpgsqlCommand("insert into data (field_text) values (:p0)", connection);
command.Parameters.Add(new NpgsqlParameter("p0", "test"));
Assert.AreEqual(command.Parameters[0].NpgsqlDbType, NpgsqlDbType.Text);
Assert.AreEqual(command.Parameters[0].DbType, DbType.String);
object result = command.ExecuteNonQuery();
Assert.AreEqual(1, result);
field_serial1 = (int) new NpgsqlCommand("select max(field_serial) from data", connection).ExecuteScalar();
var command2 = new NpgsqlCommand("select field_text from data where field_serial = (select max(field_serial) from data)", connection);
result = command2.ExecuteScalar();
Assert.AreEqual("test", result);
}
//UseIntegerParameterWithNoNpgsqlDbType
using (var connection = new NpgsqlConnection(connectionString))
{
connection.Open();
var command = new NpgsqlCommand("insert into data(field_int4) values (:p0)", connection);
command.Parameters.Add(new NpgsqlParameter("p0", 5));
Assert.AreEqual(command.Parameters[0].NpgsqlDbType, NpgsqlDbType.Integer);
Assert.AreEqual(command.Parameters[0].DbType, DbType.Int32);
Object result = command.ExecuteNonQuery();
Assert.AreEqual(1, result);
field_serial2 = (int) new NpgsqlCommand("select max(field_serial) from data", connection).ExecuteScalar();
var command2 = new NpgsqlCommand( "select field_int4 from data where field_serial = (select max(field_serial) from data)", connection);
result = command2.ExecuteScalar();
Assert.AreEqual(5, result);
// using new connection here... make sure we can't see previous results even though
// it is the same distributed transaction
var command3 = new NpgsqlCommand("select field_text from data where field_serial = :p0", connection);
command3.Parameters.Add(new NpgsqlParameter("p0", field_serial1));
result = command3.ExecuteScalar();
// won't see value of "test" since that's
// another connection
Assert.AreEqual(null, result);
}
// not commiting here.
}
// This is an attempt to wait for the distributed transaction to rollback
// not guaranteed to work, but should be good enough for testing purposes.
System.Threading.Thread.Sleep(500);
AssertNoPreparedTransactions();
// ensure they no longer exist since we rolled back
AssertRowNotExist("field_text", field_serial1);
AssertRowNotExist("field_int4", field_serial2);
}