I have two Inserts to do in one transaction.

When creating a new login I not only insert basic info into Accounts_Users, but specific info into AnotherModule_Users as well because my sites functionality is tied tightly to AnotherModule.

I could have a single sp_ that calls the two children sp_'s, but I am trying to get it to work without a parent sp_ by using the SqlCommand.BeginTransaction() method. I'm trying to figure out how I could change DBObject to support this functionality. The SqlCommand object needs to persist for both sp_ calls, so I was thinking about a static SqlCommand(but I can't even get the declaration to work) and another overload of RunProcedure. Anyone have any input?

Could you post any relevant code?

The DbObject abstract class is quite similar to the code in the Data Access Application Block from Microsoft. I think you can get it at www.asp.net.

This DbObject in ThePhile is quite limited, but it does serve the simple needs of this application. If you want to do some more serious work, it would be wise to use the real Data Access Application Block, instead of the scaled-down version that comes with this application.

Eric

Thanks a lot, I'll definitely look into DAAB, sounds like a great tool. It's amazing how much code is already written.
I will have to go with a short term solution for Transactions for right now, though. I'm trying to get a site live within a few weeks and this is but one task. Not enough time to rearchitect.

Basically, my 2nd module has one table that has the same relationship to Accounts_Users as Forums_Members does. MemberID is the Identity and Primary Key and UserID is the foreign key in my table, like Forums_Members. The transaction is to insert a row in both tables when they fill out the appname/webmodules/accounts/new.aspx.
I know there's 3 ways to do Trans. in T-SQL, ADO.NET(mentioned above) and the one I discovered since last posting is <@Page Transaction="RequiresNew" %>

I'm trying to get the 3rd technique to work. The calls(INSERTs) needed are:
Accounts.Data.User.Create()
MyModule.Data.Member.Create()

So I'm gonna work with this for awhile and try to make it work with the two sp_'s and some Try/Catch code.

No, please don't do #3. This only applies to COM+ (EnterpriseServices). This is a distributed COM+ transaction - a very advanced concept and DEFINITELY not what you want.

I would go with #1. I'd make a new SP that handles both updates in a transaction. But then also you have to modify the business layer to make it call the new SP. You should never call an SP directly from a code behind in a 3 tier application like this.

Eric

Geez, glad you told me that because I got it working and was gonna use it. I saw this in my ASP.NET Unleashed book, and it looked like a good solution. His example was pretty basic. Can you elaborate on why it's inappropriate here?

You should google these terms to learn more. This is a big subject and I can't easily cover it here:
"servicedcomponent" "enterpriseservices" "requiresnew" "distributed transaction"

However, there's a bigger reason not to do it this way. It cuts into the nice 3-tier model the authors developed. They are never doing any database access from any .aspx or code-behind file. It's always handled in the middle tier - the business object layer.

The question of whether you should do any database transactions from the ADO.NET components (instead of doing them in a stored procedure) is another matter to consider. It's called a client-side transaction with you do it in ADO.NET instead of within SQL Server. One of my big ADO.NET heros is Shawn Wildermuth: he says you should only do client-side tranactions if you absolutely must. The problem of doing it in ADO.NET is that you're extending the scope of the transaction to include another tier - it has to come to your web server, and it will therefore have a bigger footprint, and it will be slower because it has to leave the database, and it has more chances to fail. Keeping a transaction within the DB keeps it small and fast (minimizes the time that SQL Server has to keep locks active).

There's another issue of whether you should do any DB access from a webserver, but that discussion is not within the scope of this project. I'm seeing more and more people migrate to a Serive Oriented Architecture where no database access takes place from a webserver. But this model can't be used in low cost hosting sites, and it would require a complete redesign. This is a topic for a more advanced book (no, not by me - I'm too busy to write books now!). Do some google searches for "Service Oriented Arctitecture" to learn more about this. The idea is to call web services from your asp.net code, and the web services can exist on another server, which will be in a protected network zone behind a firewall. This limits problems in case your webserver is attacked by a hacker. You always want to protect your database at all costs. In some states now (notably California) there are new rules which cause a lot of trouble for a company if a hacker gets access to certain info from the DB (account numbers, names and addresses, etc). The company has to contact all of its customers and explain that there personal info may have been comprimised. Companies *HATE* this new rule, and they're redesigning their systems to better-protect this information.

Eric