DAAB 4.0 transactions in business tier

Topics: Data Access Application Block
Jul 30, 2008 at 11:13 PM

I need to insert a person and many reservations for each person into the database. I want to do this in a transaction from the business tier but I am not sure how to do this and remain database neutral. On the presentation tier I will loop the people in a listbox and insert them and the dates for each reservation by calling the business tier.
I am trying to figure out how to put two calls to my data access tier in one transaction.

Here is my code for the data tier


internal
int InsertPerson(string lName, string fName, string phone, string email)

 {

 

DbCommand command = dbSQL.GetStoredProcCommand("dbo.usp_InsertPerson");

 

 

dbSQL.AddInParameter(command, "LName", DbType.String, lName);

 

 dbSQL.AddInParameter(command,

"FName", DbType.String, fName);

 

  dbSQL.AddInParameter(command,

"Phone", DbType.String, phone);

 

  dbSQL.AddInParameter(command,

"Email", DbType.String, email);

 

  dbSQL.AddOutParameter(command,

"PersonID", DbType.Int32, 4);

 

 

try

 

 

{

 

 dbSQL.ExecuteNonQuery(command);

 

int personID = Convert.ToInt32(dbSQL.GetParameterValue(command, "@PersonID"));

 

 

return personID;

 

 }

 

catch (Exception ex)

 

 {

 

ExceptionPolicy.HandleException(ex, "Data Access Policy");

 

 

throw ex;

 

 }

}

 

internal

void InsertReservation(int personID, int propertyID, DateTime reservDate)

 

  {

 

DbCommand command = dbSQL.GetStoredProcCommand("dbo.usp_InsertReservation");

 

  dbSQL.AddInParameter(command,

"PersonID", DbType.Int32, personID);

 

  dbSQL.AddInParameter(command,

"PropertyID", DbType.Int32, propertyID);

 

  dbSQL.AddInParameter(command,

"ReservDate", DbType.DateTime, reservDate);

 

 

try

 

 

{

 

 dbSQL.ExecuteNonQuery(command);

}

 

catch (Exception ex)

 

  {

 

ExceptionPolicy.HandleException(ex, "Data Access Policy");

 

 

throw ex;

 

  }

}

 I was thinking about creating a DbTransaction object in the business tier and using the same transaction to call each data tier method but I am not sure about how to get this done.
Can someone help please? 

 

Jul 31, 2008 at 12:10 AM

Hi,

You can use the TransactionScope class for this kind of operation. See http://msdn.microsoft.com/en-us/library/cc511672.aspx and http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx. If you use this feature with Oracle, you need to make sure you've installed MTS support with your Oracle Client (see http://support.microsoft.com/default.aspx?scid=kb;en-us;q193893).

Regards,
Fernando

Jul 31, 2008 at 8:46 PM

Hi fsimonazzi,

I think I got it. I really wanted to use one transaction scope accross two methods calls so that each method was in the same transaction.
I left my data access tier alone which looks like my original post and then used this code in my business tier to call the those methods.
I am not sure if I need to create the new scope in the second method though.

 

 

internal int InsertPersonTrans(string lName, string fName, string phone, string email)

 

{

 

using (TransactionScope scope = new TransactionScope())

 

{

 

using (DbConnection conn = db.CreateConnection())

 

{

conn.Open();

 

return dal.InsertPerson( lName, fName, phone, email);

 

}

}






 

 

internal void InsertReservationTrans(int personID, int propertyID, DateTime reservDate)

 

{

 

using (TransactionScope scope = new TransactionScope())

 

{

 

using (DbConnection conn2 = db.CreateConnection())

 

{

conn2.Open();

dal.InsertReservation(personID, propertyID, reservDate);

}

scope.Complete();

}

}

 

 

Jul 31, 2008 at 10:20 PM
I don't think this is working after all. I can't seen to make the second insert happen. The personID from the first insert is coming back with an autoincrement number from the table, but when the second insert tries to take place it fails because the first insert didn't complete yet so there is a foreign key violation.
Jul 31, 2008 at 10:35 PM

Hi,

You would keep the same TransactionScope instance for both database access operations so they share a transaction. If you call different methods for the different database operations, then the TransactionScope has to be declared on a method higher up in the stack. In can see that the examples in msdn aren't very clear about this; http://msdn.microsoft.com/en-us/library/ms172152.aspx shows some slightly better ones.

Fernando

Aug 11, 2008 at 3:07 PM
That would put the TransactionScope in my presentation tier which I was trying to avoid.
Aug 11, 2008 at 4:18 PM
Well, you need to share something (either explicitly or implicitly) to determine which operations belong to the same transaction, and you also need to determine transaction boundaries.

It looks like your presentation layer is taking the responsibility of coordinating fine-grained service calls, forcing it to handle transaction-related details.

Fernando
Aug 11, 2008 at 5:58 PM
OK, thanks for your help. I will try to move the loop logic to the business layer.