Can I use TransactionScope in business layer instead of using SQLiteTransaction in data provider?

Question

I would like to use the TransactionScope class in my business layer:

In AdminService class:

public void DeleteSchoolclass(Schoolclass schoolclass)
{
    using (var trans = new TransactionScope())
    {
        adminProvider.DeleteSchoolclass(schoolclass.SchoolclassId);
        timetableProvider.DeleteTimeTableEntry(schoolclass.SchoolclassCode);

        trans.Complete();
    }             
}

When I debug both Delete methods abouve the Transaction property of each Delete command object inside the dataprovider is NULL so it seems there is no transaction... How can I find out wether a Transaction is used or not?

Wen I debug the transactionscope object I dont find any Connection...

TransactionScope seems like magic...

Thats a method in my DataAccess class:

public static SQLiteConnection ConnectionManager
{
    get
    {
        if (_con == null)
        {     
            _con = new SQLiteConnection(@"Data Source=C:\TBM\Database\dev.db");
            _con.Open();

            SQLiteCommand command = new SQLiteCommand(sqlString, _con);
            command.ExecuteNonQuery();
        }

        return _con;
    } 
 }

my connection is always open as it would make no sense closing it for every sql command having a single threaded application.

UPDATE:

public void AddPupil(int schoolclassCodeID, Pupil pupil)
        {
            using (SQLiteTransaction trans = DataAccess.ConnectionManager.BeginTransaction())
            {               
                using (SQLiteCommand com = new SQLiteCommand(DataAccess.ConnectionManager))
                {
                    com.Parameters.Add(new SQLiteParameter("@FirstName", pupil.FirstName));
                    com.Parameters.Add(new SQLiteParameter("@LastName", pupil.LastName));
                    com.Parameters.Add(new SQLiteParameter("@Gender", pupil.Gender));
                    com.Parameters.Add(new SQLiteParameter("@Street", pupil.Street));
                    com.Parameters.Add(new SQLiteParameter("@City", pupil.City));
                    com.Parameters.Add(new SQLiteParameter("@Postal", pupil.Postal));
                    com.Parameters.Add(new SQLiteParameter("@Phone", pupil.Phone));
                    com.Parameters.Add(new SQLiteParameter("@SchoolclassId", schoolclassCodeID));

                    com.CommandText = "INSERT INTO PUPIL (Firstname,LastName,Gender,Street,City,Postal,Phone,SchoolclassId_FK)" +
                                        " VALUES(@FirstName,@LastName,@Gender,@Street,@City,@Postal,@Phone,@SchoolclassId)";
                    com.ExecuteNonQuery();

                    com.CommandText = "SELECT last_insert_rowid() as lastID";
                    pupil.Id = Convert.ToInt32(com.ExecuteScalar());
                }
                trans.Commit();               
            }
        }

score 1 · Answer 1 · answered Jun 25 '11 at 19:17

1

Transaction.Current will tell you whether there is an active transaction.

Only once a connection is opened within a transaction scope will it be included in the transaction.

HTH

answered Jun 25 '11 at 19:17

Eben Roux

12,983
2
27
48

When I look at the current Transaction and the TransactionInformation I see the CreateTime of the transaction 2 hours ago before I just run my app in visual studio. It seems my TransactionScope is not working because then there would be written another time. I am having a STA app here with sqlite and a singleton connection which only closes when the app closes. What could cause that Transaction to have such a unrealistic time? – msfanboy Jun 25 '11 at 19:31
In this scenario a TransactionScope will not work. You *have* to open the connection within the scope; else it doesn't know about it. There really is nothing wrong with opening and then disposing a connection for each batch. You could use another method to create the connection and use the created connection within your 'providers'. As the link abatishchev provided mentions: if you create another connection within the scope the transaction *may* be escalated to a distributed transaction --- it depends on how SQLite works (I don't know it at all). Good luck :) – Eben Roux Jun 25 '11 at 20:33
@Eben Then explain this => Well when I do the first Delete method in my dataprovider the data is not deleted until trans.commit() is called. When the 2nd delete method throws a provokated exception by me the first delete statement is not executed either. So I guess the transaction must/should work. What do you think? – msfanboy Jun 25 '11 at 20:37
That's weird. Are you not using explicit transactions in your '*Providers? Either that or your singleton connection is created in the first call --- in which case it will be part of the transaction scope transaction. – Eben Roux Jun 26 '11 at 07:16
@Eben I removed all explicit SQLiteTransaction from my DataProvider classes. Now my Service classes use everywhere a using Transactionscope for create,delete,update methods. What I found out now is this:.......... 1.) When I firstly call using (var trans = new TransactionScope()) and within that Transactionscope I do 2 delete methods in the dataprovider and each one is doing DataAccess.ConnectionManager for getting the connection THEN the Transaction.Current is NOT null. – msfanboy Jun 26 '11 at 08:09
...........2.) When I secondly do a Delete method not on a schoolclass but a pupil see code above I use Transactionscope in the DataProvider then the connection is not created anymore (as it was done before using a singleton) and the Transaction.Current within the TransactionScope is NULL. Can you do something with that info? – msfanboy Jun 26 '11 at 08:13
@Eben forget about point 2.) I did NOT use TransactionScope I used SQLiteTransaction accidently, my failure... will have to do some more tests. – msfanboy Jun 26 '11 at 08:23
Just to make clear the Transaction.Current is not away of a SQLiteTransaction created! AND everytime I do a TransactionScope in my service and Within that TransactionScope I create a new SQLiteCommand the command`s DBTransaction object is always NULL. Well but what is nagging me, that every com.ExecuteNonQuery() I do in the dataprovider within a TransactionScope in the service, the query is ONLY executed then when the transactionscope.Complete is called. For me it seems TransactionScope is always wrapping a dataprovider method and only executes it when the transactionscope.complete is called. – msfanboy Jun 26 '11 at 08:43
Actually that shouldnt be...sqlite .net forum is down for days I guess I have to ask there. – msfanboy Jun 26 '11 at 08:43
@Eben gave you a point for your answer which is ok in general with sql server but not with sqlite. I think I should have asked that question on sqlite .net forum which is down for now. – msfanboy Jun 26 '11 at 09:13
It isn't that the query is only executed when `scope.complete` is called. It is executed immediately but within a transaction. So any other select will not see that data and in some cases will be blocked (unless you do a dirty read --- however that is done using sqlite). And remember that the transaction is attached to a connection so creating a command will not do the trick. – Eben Roux Jun 26 '11 at 13:04
@Eben not sure what you want to advise me with your last comment, but yes the Connection has a Transaction... Yes with scope.Complete the transaction is commited. – msfanboy Jun 26 '11 at 18:23

score 0 · Answer 2 · edited May 23 '17 at 12:19

0

IIRC, TransactionScope is tightly bound with Distributes Transaction Coordinator (MSDTC). So be aware, especially if you're using SQLite.

All so see a number of other questions on SO, e.g. this.

edited May 23 '17 at 12:19

Community

1
1

answered Jun 25 '11 at 19:28

abatishchev

98,240
88
296
433

You throw at me a link which doesnt help me at all as I do Sqlite... a STA app as I wrote. – msfanboy Jun 25 '11 at 19:55
TransactionScope will only escalate to Distributed if a second connection is needed (SQL2005) or if a second connection on a different DB / resource is needed (SQL 2008) – StuartLC Aug 20 '11 at 10:35
@nonnb: Do you have any links to confirm? Very interesting. – abatishchev Aug 20 '11 at 17:38
Your link (http://stackoverflow.com/questions/1690892/transactionscope-automatically-escalating-to-msdtc-on-some-machines) confirms the unnecessary 2005 escalations. DTC is needed as soon as more than one resource manager (e.g. SQL and MSMQ) is involved in an ACID transaction - e.g. here - http://geekswithblogs.net/dotnetrodent/archive/2008/04/16/121279.aspx. – StuartLC Aug 21 '11 at 15:26

Can I use TransactionScope in business layer instead of using SQLiteTransaction in data provider?

2 Answers2