-1

I'm currently trying to implement some End to End Tests (E2E) using TranactionScope and local file Database (mdf). funny is that the query is not being rolled back, so all my update/inserts are persisted. I dont understand what is done wrong

        using (new TransactionScope())
        {
            var newItem1 = new SomeEntity { Id = 4, Remark = "Test 2" };
            var newItem2 = new SomeEntity { Id = 5, Remark = "Test 2" };

            var x = new List<SomeEntity> { newItem1, newItem2 };
            _testTvp.SaveSomeEntities(x);

            var result = _test.GetSomeEntity(4);
            Assert.AreEqual(newItem1.Remark, result.Remark);
            result = _test.GetSomeEntity(5);
            Assert.AreEqual(newItem2.Remark, result.Remark);
        }

My connection string is:

for more code, see here: enter link description here

Roman Kern
  • 11
  • 2

5 Answers5

0

Nothing is wrong. This is how TransactionScope works.

From MSDN

If no exception occurs within the transaction scope (that is, between the initialization of the TransactionScope object and the calling of its Dispose method), then the transaction in which the scope participates is allowed to proceed. If an exception does occur within the transaction scope, the transaction in which it participates will be rolled back.

It rollbacks the transaction only if an exception occurs

Community
  • 1
  • 1
giammin
  • 18,620
  • 8
  • 71
  • 89
  • but without complete transaction will not be committed... Rollback never failed befire, if I just do not execute complete. I've tried to throw an exception, but it seems that their is no affect on the Transaction – Roman Kern Jul 04 '16 at 11:09
  • @RomanKern then `_testTvp.SaveSomeEntities(x);` is committing the transaction – giammin Jul 04 '16 at 13:16
  • _testTvp is of Type: ExecutionTest : IRepository. ExecutionTest is an Interface without any implementation. I'm using Proxy to generate execution stake for this method. The Interceptor Class name is GenericDalInterceptor. There is no transaction that commit something. this behaviour is really strange. Maybe it depend on Database engine. For this Test I do use Local Db. I can move the database to the real server and try again what happen. – Roman Kern Jul 04 '16 at 13:38
  • @RomanKern can you use sqlprofiler and debug your code to see the generated sql? you should debug your code line by line and see when the transaction is committed and by which line of code. Maybe it is not opening any transaction... – giammin Jul 04 '16 at 15:24
  • Maybe I just misunderstood the technique of the TransactionScope, but as long as I can remeber TransactionScope never trigger begin transaction and commit. my sql code ist always Stored Procedure call. – Roman Kern Jul 05 '16 at 10:16
  • @RomanKern how can it rollback a transaction if it does not put your sql code inside a begin/commit transaction – giammin Jul 05 '16 at 10:27
0

Why don't you try this with 

    using (SqlConnection sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();
                    using (SqlTransaction sqlTrans = sqlConnection.BeginTransaction())
                    {
                       //put your code here
                    }
                 }
Sagar
  • 454
  • 10
  • 22
  • I have to bin transaction to a specific DbCommand. This make code more complexe. If I could access Transaction from Connection without create new one, it would be fine. You can take a look into Execution Stack of IRepository in the project. Maybe it will be more clear, why there is not BeginTransaction at all – Roman Kern Jul 04 '16 at 11:15
0

Use something like as depending on your stack you might have ambient transactions:

string connStr = "...; Enlist = false";
using (TransactionScope ts = new TransactionScope())
{
    using (SqlConnection conn1 = new SqlConnection(connStr))
    {
        conn1.Open();
        conn1.EnlistTransaction(Transaction.Current);
    }
}

Under what circumstances is an SqlConnection automatically enlisted in an ambient TransactionScope Transaction?

Community
  • 1
  • 1
MeTitus
  • 3,390
  • 2
  • 25
  • 49
  • I'm using IDbConnection. It guarantee a maximum flexibility over multiple databases. EnlistTransaction explicit defined in SqlDbConnection. So I cannot use it. EnlistTransaction works for me, if I convert IDbConnection to SqlDbConnection. Connection String enlist = false/true have no effect on transaction scope – Roman Kern Jul 04 '16 at 12:59
  • I wasn't sure how were connection was defined. Your not calling the Complete method is that the reason why you are not sure why the transaction is being persisted? – MeTitus Jul 04 '16 at 13:06
  • I dont want to persist data... the test should just add some data temporary with uncommitted state. Sure I can just delete the data from table, but rollback is my first choice – Roman Kern Jul 04 '16 at 13:10
  • 1
    Yes they should not more so cause you're not calling Complete but without knowing what the SaveSomeEntities method is doing it is complicated to extend further. – MeTitus Jul 04 '16 at 13:12
  • Save Some Entity is a lazy DB Call... generated by the class: https://github.com/RomanKernSW/GenericDataAccessLayer/blob/master/GenericDAL/LazyDal/StoredProcedure/GenericDalInterceptor.cs Currently its a quick and dirty implementation – Roman Kern Jul 04 '16 at 13:40
  • Well I cant see all your code but you seem to be creating a connection before creating the Transaction when it should be the other way round. – MeTitus Jul 04 '16 at 13:48
0

Try this:

    using (new scope = new TransactionScope())
    {
        var newItem1 = new SomeEntity { Id = 4, Remark = "Test 2" };
        var newItem2 = new SomeEntity { Id = 5, Remark = "Test 2" };

        var x = new List<SomeEntity> { newItem1, newItem2 };
        _testTvp.SaveSomeEntities(x);

        var result = _test.GetSomeEntity(4);
        Assert.AreEqual(newItem1.Remark, result.Remark);
        result = _test.GetSomeEntity(5);
        Assert.AreEqual(newItem2.Remark, result.Remark);

        //either of the two following:
        Transaction.Current.Rollback();
        scope.Dispose();

    }
Steve Ford
  • 7,433
  • 19
  • 40
0

Well, I couldnt figure out what exactly is wrong. The possible solution to this is to delete inserted data. Not the best way, but much better one using DbTransaction.I will try to create some test with real sql server and see what is difference.

Roman Kern
  • 11
  • 2