transactions

[Iain Mcleod]

I wish to do a series of inserts on a sql server database in the context of
a transaction. The inserts will be done as a series of stored procedure
calls. I wish to be able to rollback any inserts should one fail.

A quick google on ".net transactions" and the following page comes up:
http://samples.gotdotnet.com/quickstart/aspplus/doc/mtstransactions.aspx

It says basically that the class System.EnterpriseServices.ContextUtil is
the chap for the job. However on looking at the EnterpriseServices
namespace, it seems to be full of classes related to COM components. Is
this definately the correct class to use for the job?
Also, the above page describes using the <%@transaction=required%> page
directive in asp.net to set the transaction scope and level. What would
happen if a non asp.net program tried to use transactions? Surely there
must be a programmatic way of doing this too?

Am I barking up the wrong tree with this?

Many thanks
Iain A. Mcleod

 

[Sahil Malik]

Iain,

If it is a purely database transaction that you wish to control, I would
recommend ado.net transactions or even transactions within SQL Server to
handle it.

System.EnterpriseServices is what used to be COM+. Actually it is a little
bit more than that - it is in .NET now, and has a few .NET like things in
it. Plus a lot of it is now managed code. One of the things enterprise
services lets you do is to enlist any task (even non - database) into an MTS
transaction. The ASP.NET or even Classic ASP page which has a directive
<%@transaction=required%> specified; is another example of a worker process
that must instantiate a new transaction before it runs. As you might have
already guessed by now, any worker process can enlist itself in a new or
existing transaction.

EnterpriseServices gives you a *lot* more than transactions only. It gives
you instance pooling/creation/queued components/LCEs/Messaging . quite a
long list actually.

What I am driving at is, COM+ and enterprise services are topics that entire
books have been written about. They are a lot more than meets the eye and
yes they are a very powerful concept and should not be ignored - but for
your requirement - check and see if you can satisfy that with ADO.NET
transactions or even SQL Server transactions; before you look at
msdtc/mtc/enterprise services. What's more, there could be many times that
deploying applications that use EnterpriseServices could be a pain the
booty. This might not be an issue for a server, but for desktop client apps
it becomes an unreal hell. I must be clear though - EnterpriseServices rock;
but killing a mosquito with a hand grenade is not the wisest thing to do.

Should you desire, you can find more information on using ADO.NET with
EnterpriseServices in my book - Chapter #12. Another really good book I like
on Enterprise Services is written by Juwal Lowy.

Regards,

- Sahil Malik
You can reach me thru my blog http://www.dotnetjunkies.com/weblog/sahilmalik

 

[Iain Mcleod]

Thank you Sahil. I thought the com+/mts thingy was a bit of an overkill.
I googled on your suggestion of ado.net transactions and got the following
code:
SqlTransaction objTransaction = objConnection.BeginTransaction();

Perfect: It was indeed the SqlTransaction class I was looking for .

I take it that I can use this with stored procedures (anti sqlinjection) as
well as with ad-hoc sql as in the following pseudocode?

try
begin transaction
executeNonQuery (storedproc1, params)
executeNonQuery (storedproc2, params)
commit transaction
catch ex as exception
roll back transaction
end try

Apologies if this seems obvious, I'm a Java programmer learning .net

Regards
Iain

 

[Sahil Malik]

Yes you can use it with stored procs.

- Sahil Malik
You can reach me thru my blog http://www.dotnetjunkies.com/weblog/sahilmalik