Transactions

Topics: General Discussion Forum, July and December Releases Forum, Service Factory Modeling Edition Forum
Feb 25, 2008 at 4:47 PM
Can anybody please give me guidance on how to implement transactions with the December 06 version of the WSSF. I am using SQL Server 2000 and cannot use TransactionScope as using MSDTC is not an option. I would like to use SQLTransactions however I cannot work out how to do this with the way Repositories are created in the sample.

All of the examples I have found are similar to the following :-

Database db = DatabaseFactory.CreateDatabase();

DbCommand Query1Command = db.GetStoredProcCommand("Query1";);
DbCommand Query2Command = db.GetStoredProcCommand("Query2");

using (DbConnection connection = db.CreateConnection())
{
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
try
{
db.ExecuteNonQuery(Query1Command , transaction);
db.ExecuteNonQuery(Query2Command ,transaction);

transaction.Commit();
}
catch
{
transaction.Rollback();
}
}

However because I am using the WSSF example code I am unsure of how to create and share a transaction as each time you create a Repository the Repository base class creates a new Database object :-
public Repository( string databaseName )
{
this.databaseName = databaseName;
this.db = DatabaseFactory.CreateDatabase(databaseName);
}

And the DBConnection object is not available as it is tucked away inside the DAAB. Thanks for any help or guidance.
Feb 25, 2008 at 5:04 PM
Edited Feb 26, 2008 at 10:54 AM
Take look at these links:
http://www.codeplex.com/servicefactory/Thread/View.aspx?ThreadId=21612
Feb 26, 2008 at 11:30 AM
Hi Charly,

Thanks for your reply. I have studied these links but not of them help me with weaving SQLtransactions into the WSSF Repository style Database Access Layer. Each Repository is created to perform a single CRUD operation therefore I need to implement Transactions across repositories and it is this I am struggling to do.

I'm getting close to giving up on this one.....

Thanks again
Steve
Feb 26, 2008 at 2:37 PM
So assuming you can't use TransactionScope (for performance resons in SQL 2000) then perhaps you may need a small refactoring in our respository classes so you may have an action (BL) that will create the Tx and call the different respositories like this:

  1. Create a repository class that will call all your transactionable repositories
  2. Refactor your transactionalbe repositories adding a ctor that will receive a DbTransaction object and use that in your methods.
  3. In your nre rep class, have a code like this:

using (DbTransaction transaction = connection.BeginTransaction())
{
     SomeRepository repository1 = new SomeRepository(transaction);
     SomeOtherRepository repository2 = new SomeOtherRepository(transaction);
     repository1.DoOperation(criteria);
     repository2.DoOperation(criteria);
     transaction.Commit();
}

So you get the idea.
Feb 27, 2008 at 9:09 AM
Thankyou! This is exactly what I needed a new perspective.