Iwan said:
Hi,
I have three tables T1,T2,T3 with reletions between them. I want to
fill a row as follows:
1. fill T1
2. fill T2
3. fill T3
If any of the operations fail - all fail.
Tables have 6,8 and 12 attributes. How could I realize the transaction
(or anything else if there is)? Do I create a store procedure with many
parameters (26 in this case) for each attribute? Or is it better to
manage the transaction from C# code?
It depends. You could manage the transaction manually in C# using a
SQLTransaction, or automatically using the COM+ transaction support in
EnterpriseServices, or you could do as you suggest and handle it in the
stored procedure.
The main reason for doing it in the sp is the principle of keeping
transactions as close to the database as possible. DBAs, in my
experience, can get very sniffy about transactions outside of the
database, and start ranting about locks and performance. In practice,
unless you do something really daft like running a slow select query in
transaction with some inserts, it doesn't make an awful lot of
difference.
The reasons for handling it in C# are that for anything non-trivial, C#
is a much nicer language than TSQL and that it's easier to get reuse
from small, simple stored procedures than from large complex ones. You
can, of course, always break your big SP into smaller ones and call them
from TSQL.
What you're doing looks pretty simple; three insert statements. If I
knew that the operation would never change, and that there was no scope
for reuse of the individual inserts, I'd be tempted to hold my nose and
pass the 26 parameters. If what you are doing is a special case of a set
of operations you might otherwise carry out individually, or if you
think that in future you might need to update more tables in the
transaction, I think I'd probably use a transaction from C#.
