Simple unit test for TransactionScope not working!

  • Thread starter Thread starter hellosticky
  • Start date Start date
H

hellosticky

I am very confused why this test is not working. I create a fake table,
then insert a row without completing the scope, and just in case,
throwing an exception, but when the test finishes, there is still a row
in the database (beyond the Assert, I can go to the database table and
see the row)!

[Test]
public void SimpleTransaction()
{
string tableName = "test" + new
Random(unchecked((int)DateTime.UtcNow.Ticks)).Next(1, 1000);

try
{
// Create a test database
using (IDbConnection conn = RDBMSLayer.DBConnect())
{
RDBMSLayer.ExecuteNonQuery(conn, "create table " +
tableName + " (id int not null primary key)");
}
using (IDbConnection conn2 = RDBMSLayer.DBConnect())
{
using (TransactionScope scope = new
TransactionScope())
{
int rowsInserted =
RDBMSLayer.ExecuteNonQuery(conn2, "insert into " + tableName + " (id)
values (1)");
Assert.AreEqual(1, rowsInserted);

// Now throw a random exception
if (rowsInserted == 1)
{
throw new Exception("Transaction
Exception");
}
}
}
}
catch (Exception)
{
using (IDbConnection conn = RDBMSLayer.DBConnect())
{
Assert.AreEqual(0, RDBMSLayer.GetRowCount(conn,
tableName));
}
}
}
 
Terribly interesting. I moved the database connection inside of the
TransactionScope and it worked! Anyone know why?

From:

using (IDbConnection conn2 = RDBMSLayer.DBConnect())
{
using (TransactionScope scope = new
TransactionScope())
{

To:

using (TransactionScope scope = new TransactionScope())
{
using (IDbConnection conn2 =
RDBMSLayer.DBConnect())
{
 
This makes perfect sense. A database connection object will only
auto-enlist in a transactionscope if the transactionscope exists at the time
the connection is opened. Since you started the transactionscope AFTER
opening the connection, the connection had no way of automatically detecting
this and you should've called the connection's Enlist() method to enlist it.



Terribly interesting. I moved the database connection inside of the
TransactionScope and it worked! Anyone know why?

From:

using (IDbConnection conn2 = RDBMSLayer.DBConnect())
{
using (TransactionScope scope = new
TransactionScope())
{

To:

using (TransactionScope scope = new TransactionScope())
{
using (IDbConnection conn2 =
RDBMSLayer.DBConnect())
{


I am very confused why this test is not working. I create a fake table,
then insert a row without completing the scope, and just in case,
throwing an exception, but when the test finishes, there is still a row
in the database (beyond the Assert, I can go to the database table and
see the row)!

[Test]
public void SimpleTransaction()
{
string tableName = "test" + new
Random(unchecked((int)DateTime.UtcNow.Ticks)).Next(1, 1000);

try
{
// Create a test database
using (IDbConnection conn = RDBMSLayer.DBConnect())
{
RDBMSLayer.ExecuteNonQuery(conn, "create table " +
tableName + " (id int not null primary key)");
}
using (IDbConnection conn2 = RDBMSLayer.DBConnect())
{
using (TransactionScope scope = new
TransactionScope())
{
int rowsInserted =
RDBMSLayer.ExecuteNonQuery(conn2, "insert into " + tableName + " (id)
values (1)");
Assert.AreEqual(1, rowsInserted);

// Now throw a random exception
if (rowsInserted == 1)
{
throw new Exception("Transaction
Exception");
}
}
}
}
catch (Exception)
{
using (IDbConnection conn = RDBMSLayer.DBConnect())
{
Assert.AreEqual(0, RDBMSLayer.GetRowCount(conn,
tableName));
}
}
}
Click to expand...
Click to expand...
 
I see. Would there be a way to not auto-enlist a connection inside a
TransactionScope (for efficiency's sake, if a low level piece of code
does something outside the scope of any "logical" transaction, such as
logging)?

Robert said:
This makes perfect sense. A database connection object will only
auto-enlist in a transactionscope if the transactionscope exists at the time
the connection is opened. Since you started the transactionscope AFTER
opening the connection, the connection had no way of automatically detecting
this and you should've called the connection's Enlist() method to enlist it.



Terribly interesting. I moved the database connection inside of the
TransactionScope and it worked! Anyone know why?

From:

using (IDbConnection conn2 = RDBMSLayer.DBConnect())
{
using (TransactionScope scope = new
TransactionScope())
{

To:

using (TransactionScope scope = new TransactionScope())
{
using (IDbConnection conn2 =
RDBMSLayer.DBConnect())
{


I am very confused why this test is not working. I create a fake table,
then insert a row without completing the scope, and just in case,
throwing an exception, but when the test finishes, there is still a row
in the database (beyond the Assert, I can go to the database table and
see the row)!

[Test]
public void SimpleTransaction()
{
string tableName = "test" + new
Random(unchecked((int)DateTime.UtcNow.Ticks)).Next(1, 1000);

try
{
// Create a test database
using (IDbConnection conn = RDBMSLayer.DBConnect())
{
RDBMSLayer.ExecuteNonQuery(conn, "create table " +
tableName + " (id int not null primary key)");
}
using (IDbConnection conn2 = RDBMSLayer.DBConnect())
{
using (TransactionScope scope = new
TransactionScope())
{
int rowsInserted =
RDBMSLayer.ExecuteNonQuery(conn2, "insert into " + tableName + " (id)
values (1)");
Assert.AreEqual(1, rowsInserted);

// Now throw a random exception
if (rowsInserted == 1)
{
throw new Exception("Transaction
Exception");
}
}
}
}
catch (Exception)
{
using (IDbConnection conn = RDBMSLayer.DBConnect())
{
Assert.AreEqual(0, RDBMSLayer.GetRowCount(conn,
tableName));
}
}
}
Click to expand...
Click to expand...
Click to expand...
 
You bet. Add "Enlist=false" to the connection string and it won't
auto-enlist.

I see. Would there be a way to not auto-enlist a connection inside a
TransactionScope (for efficiency's sake, if a low level piece of code
does something outside the scope of any "logical" transaction, such as
logging)?

Robert said:
This makes perfect sense. A database connection object will only
auto-enlist in a transactionscope if the transactionscope exists at the
time
the connection is opened. Since you started the transactionscope AFTER
opening the connection, the connection had no way of automatically
detecting
this and you should've called the connection's Enlist() method to enlist
it.



Terribly interesting. I moved the database connection inside of the
TransactionScope and it worked! Anyone know why?

From:

using (IDbConnection conn2 = RDBMSLayer.DBConnect())
{
using (TransactionScope scope = new
TransactionScope())
{

To:

using (TransactionScope scope = new TransactionScope())
{
using (IDbConnection conn2 =
RDBMSLayer.DBConnect())
{


(e-mail address removed) wrote:
I am very confused why this test is not working. I create a fake
table,
then insert a row without completing the scope, and just in case,
throwing an exception, but when the test finishes, there is still a
row
in the database (beyond the Assert, I can go to the database table and
see the row)!

[Test]
public void SimpleTransaction()
{
string tableName = "test" + new
Random(unchecked((int)DateTime.UtcNow.Ticks)).Next(1, 1000);

try
{
// Create a test database
using (IDbConnection conn = RDBMSLayer.DBConnect())
{
RDBMSLayer.ExecuteNonQuery(conn, "create table " +
tableName + " (id int not null primary key)");
}
using (IDbConnection conn2 = RDBMSLayer.DBConnect())
{
using (TransactionScope scope = new
TransactionScope())
{
int rowsInserted =
RDBMSLayer.ExecuteNonQuery(conn2, "insert into " + tableName + " (id)
values (1)");
Assert.AreEqual(1, rowsInserted);

// Now throw a random exception
if (rowsInserted == 1)
{
throw new Exception("Transaction
Exception");
}
}
}
}
catch (Exception)
{
using (IDbConnection conn = RDBMSLayer.DBConnect())
{
Assert.AreEqual(0, RDBMSLayer.GetRowCount(conn,
tableName));
}
}
}
Click to expand...
Click to expand...
Click to expand...
 
So is that purely a SQL server feature or every type of connection
(e.g. MySQL) needs to support auto-enlisting?

Thanks for your help

Robert said:
You bet. Add "Enlist=false" to the connection string and it won't
auto-enlist.

I see. Would there be a way to not auto-enlist a connection inside a
TransactionScope (for efficiency's sake, if a low level piece of code
does something outside the scope of any "logical" transaction, such as
logging)?

Robert said:
This makes perfect sense. A database connection object will only
auto-enlist in a transactionscope if the transactionscope exists at the
time
the connection is opened. Since you started the transactionscope AFTER
opening the connection, the connection had no way of automatically
detecting
this and you should've called the connection's Enlist() method to enlist
it.



Terribly interesting. I moved the database connection inside of the
TransactionScope and it worked! Anyone know why?

From:

using (IDbConnection conn2 = RDBMSLayer.DBConnect())
{
using (TransactionScope scope = new
TransactionScope())
{

To:

using (TransactionScope scope = new TransactionScope())
{
using (IDbConnection conn2 =
RDBMSLayer.DBConnect())
{


(e-mail address removed) wrote:
I am very confused why this test is not working. I create a fake
table,
then insert a row without completing the scope, and just in case,
throwing an exception, but when the test finishes, there is still a
row
in the database (beyond the Assert, I can go to the database table and
see the row)!

[Test]
public void SimpleTransaction()
{
string tableName = "test" + new
Random(unchecked((int)DateTime.UtcNow.Ticks)).Next(1, 1000);

try
{
// Create a test database
using (IDbConnection conn = RDBMSLayer.DBConnect())
{
RDBMSLayer.ExecuteNonQuery(conn, "create table " +
tableName + " (id int not null primary key)");
}
using (IDbConnection conn2 = RDBMSLayer.DBConnect())
{
using (TransactionScope scope = new
TransactionScope())
{
int rowsInserted =
RDBMSLayer.ExecuteNonQuery(conn2, "insert into " + tableName + " (id)
values (1)");
Assert.AreEqual(1, rowsInserted);

// Now throw a random exception
if (rowsInserted == 1)
{
throw new Exception("Transaction
Exception");
}
}
}
}
catch (Exception)
{
using (IDbConnection conn = RDBMSLayer.DBConnect())
{
Assert.AreEqual(0, RDBMSLayer.GetRowCount(conn,
tableName));
}
}
}
Click to expand...
Click to expand...
Click to expand...
 
Back
Top