469,627 Members | 936 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,627 developers. It's quick & easy.

Transaction Sql Server?

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?
Thanks.

Nov 17 '05 #1
2 1968
In message <11*********************@g14g2000cwa.googlegroups. com>, Iwan
Petrow <xx****@abv.bg> writes
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#.

--
Steve Walker
Nov 17 '05 #2
In message <11*********************@g14g2000cwa.googlegroups. com>, Iwan
Petrow <xx****@abv.bg> writes
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#.

--
Steve Walker
Nov 17 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

10 posts views Thread by TZoner | last post: by
1 post views Thread by Avanish Pandey | last post: by
4 posts views Thread by yashgt | last post: by
1 post views Thread by Matik | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.