Single sql transaction on multiple method

Question

Modular programing is right approach but it sometimes lead to problem which needs extra efforts and research. I have three database insert function like InsertName(),InsertAddress(),InsertPhoneNo() take it as example. All these functions must execute if exception occurs in any of function no change will be made to database .

What I can do is merge all three in one and use sqltransaction.

InsertDetails()
{
using (SqlTransaction sqlTransaction = cn.BeginTransaction()) 
    {
      using (SqlCommand cm = new SqlCommand()) 
      {
        cm.Transaction = sqlTransaction;
        InsertName();//Code to insert name   
        Insertaddress();//code to insert address
        InsertPhoneNo();//code to insert phone no
      }
      sqlTransaction.Commit();
    }
}

but the solution above is against my modular approach. Is it possible to bind multiple functions to one sql transaction without merging them , If not which is the best approach possible to achieve this.

score 5 · Answer 1 · answered May 11 '13 at 05:08

5

Just pass in cn and sqlTransaction in to the functions then have the functions use the passed in parameters instead of opening up their own connections.

answered May 11 '13 at 05:08

Scott Chamberlain

124,994
33
282
431

score 3 · Answer 2 · answered May 11 '13 at 11:18

Depends on how your code is written within the methods, but you might want to have a look at the TransactionScope object, so your code would look like:

InsertDetails()
{
    using (TransactionScope ts = new TransactionScope()) 
    {    
        InsertName();//Code to insert name   
        Insertaddress();//code to insert address
        InsertPhoneNo();//code to insert phone no

        ts.Complete();    
    }    
}

More information on MSDN here:

http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx

As a side note - you need to look in the code in your methods to see if this is suitable, and to ensure you keep the transaction in the database and not have it escalated to a distributed transaction! — Paddy, May 11 '13 at 11:19

score 1 · Answer 3 · edited May 23 '17 at 11:52

In a well designed distributed architecture(simplified and database centeric), you will have (at lest) there layers and a domain entity tier (Other layers will have dependency to domin entyty tier)

User interface
service
Data Access
Common(Domain Entity)

Dependency between them is top down, so
user interface has reference to service, service to Data Access and all layers are dependent to Domain Entity

Per domain entity we will have a separate service, for example if we have a Customer Table and an Order table we will have:
CustomerEntity and OrderEntity (Domain entity objects)
CustomerService and OrderService (Service layer objects)
CustomerDA and CustomerDA (Data Access layer objects)

As a rule any DataAccess object will do Inset, Update, Delete for its Domain entity and will not interfere to other entities CRUD operation
Second role will be that any service object has to call its own enteritis's data access object.

The key point that will answer your question is here:
Service layer objects can call other services, so a sample service called UpdateCustomerNameAndUpdateAllHisOrders(CustomerEntity customer) // a very long name
could be implemented in CustomerService class, conceptually like this:

UpdateCustomerNameAndUpdateAllHisOrders(CustomerEntity customer)
{
  //StartTransaction();
  CustomerDa.Update(customer);
  var orders = OrderService.GetAllOrders(customer);
  //Modify Orders logic
  OrderService.UpdateOrders(orders);
  //CommiteTransaction();
}

By the way for integrity of data, the transaction is managed from service layer
Answer of @Scott Chamberlain will do like this in its own way

For service layer transaction management you may see here
Spring.Net Framework is a good sample of doing this as an aspect.
Hope be helpful.

Single sql transaction on multiple method

3 Answers3