Enterprise Application Datablocks transaction

Question

Having a bit of trouble with the application block, trying to figure out a logical way to execute 3 SP's in a transaction.

So far I have :

SqlDatabase db = new SqlDatabase(this.ConnectionString );
DbCommand insertMessageDetailCommand = db.GetStoredProcCommand("InsertMessageDetail");

Looking good ... from this point things get a bit confusing, obviously I need to add the SP parameters which I do through:

db.AddInParameter.......

Whats confusing me is - notice where you add the parameters - to the SqlDatabase object.

So... this is all fine and well even with the first item in the transaction:

DbTransaction transaction = connection.BeginTransaction();
                try
                {
                    //INSERT Message Info
                    int PK = Convert.ToInt32(db.ExecuteScalar(insertMessageDetailCommand, transaction));

Except now I need to call a new SP, with its own parameters. Do I need to somehow clear the parameters from the db object?

The only way I see this working is if I create yet another db object, but surely this wouldn't be compatible with the same transaction?

Please help

Answer 1

If you're wondering I think I figured it out, notice how the db object requires a command object parameter. It will delegate the param to the required operation. Pretty helpful if you think about it.

Answer 2

You could simply place all 3 calls within a transaction scope. see:

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