Using TransactionScope in Service Layer for UnitOfWork operations

Question

Is my approach right to bundle all 3 dataprovider.GetXXX methods in a TransactionScope in the service layer as UnitOfWork?

Would you do something different?

From where does the TransactionScpe ts know the concrete ConnectionString?

Should I get the Transaction object from my connection and pass this Transaction objekt to the constructor of the TransactionScope ?

Service Layer like AdministrationService.cs

private List<Schoolclass> GetAdministrationData()
{

   List<Schoolclass> schoolclasses = null
   using (TransactionScope ts = new TransactionScope())
                    {
                        schoolclasses = _adminDataProvider.GetSchoolclasses();
                        foreach (var s in schoolclasses)
                        {
                           List<Pupil> pupils = _adminDataProvider.GetPupils(s.Id);
                           s.Pupils = pupils;

                           foreach (var p in pupils)
                           {
                               List<Document> documents = _documentDataProvider.GetDocuments(p.Id);
                               p.Documents = documents;
                           }
                        } 

                        ts.Complete();
                    }


   return schoolclasses;
}

Sample how any of those 3 methods in the DataProvider could look like:

public List<Schoolclass> GetSchoolclassList()
        {
            // used that formerly without TransactionSCOPE => using (var trans = DataAccess.ConnectionManager.BeginTransaction())
            using (var com = new SQLiteCommand(DataAccess.ConnectionManager))
            {
                com.CommandText = "SELECT * FROM SCHOOLCLASS";

                var schoolclasses = new List<Schoolclass>();

                using (var reader = com.ExecuteReader())
                {
                    Schoolclass schoolclass = null;
                    while (reader.Read())
                    {
                        schoolclass = new Schoolclass();
                        schoolclass.SchoolclassId = Convert.ToInt32(reader["schoolclassId"]);
                        schoolclass.SchoolclassCode = reader["schoolclasscode"].ToString();
                        schoolclasses.Add(schoolclass);
                    }
                }
                // Used that formerly without TransactionSCOPE => trans.Commit();
                return schoolclasses;
            }
        }

Answer 1

This looks fine - that's what TransactionScope is there for, to provide transaction control in your code (and this is a common pattern for UoW).

From where does the TransactionScpe ts know the concrete ConnectionString?

It doesn't. That depends on your data access layer and doesn't really mean much to TransactionScope. What TransactionScope does is create a transaction (which will by default be a light-weight one) - if your data access spans several databases, the transaction will automatically be escalated to a distributed transaction. It uses MSDTC under the hood.

Should I get the Transaction object from my connection and pass this Transaction objekt to the constructor of the TransactionScope ?

No, no, no. See the above. Just do what you are doing now. There is no harm in nesting TransactionScopes.