Simple unit test for TransactionScope not working!

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));
}
}
}
 
H

hellosticky

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())
{
 
R

Robert Simpson

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...
 
H

hellosticky

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...
 
R

Robert Simpson

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...
 
K

kevgrig

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Transaction Help 2
how to get the id of the inserted datarow? 3
VB 2005 trying to use TransactionScope with DB2 backend 1
Multithreaded Database access with C# on an Sql2005 and TransactionScope class (Bug or did I some mi 3
TransactionScope in framework 2.0 6
corruption updating a NCLOB field with ODP.Net 2
About TransactionScope 3
SqlDataReader.Read() with image data type size 65,535 2

Top