473,320 Members | 1,854 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 2089
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
by: TZoner | last post by:
1) Can one find the location of the 'Transaction Log' at: <Hard Disk>\Program Files\Microsoft SQL Server\MSSQL\Data\MyDb_Log.ldf? 2) Is it safe to delete it, as SQL will create a new Transaction...
5
by: Jay Chan | last post by:
The transaction log in a database in our SQLSERVER-2000 server has grown to 16GB. I cannot shrink the transaction log manually because it says that the entire 16GB log size is not free. This is...
0
by: DotNetJunkies User | last post by:
I am writing a distributed transaction code. My current scenario include a client database(Suppose client- having 4 main database) which can be installed anywhere which would connect to a public...
1
by: Avanish Pandey | last post by:
Hello All We have 3 differen services (in 3 different server) Service A,B,C . We want to implement distributed transaction when call methods of B and C from A. Is it possible? if yes then how? ...
0
by: Manoj Sharma | last post by:
I am performance testing some batch processing engines. These are written in ..NET and SQL Server and are typically concerned with generating XML files out of data retrieved from the database. I...
7
by: Abdul-Wahid Paterson | last post by:
Hi, I have had a site working for the last 2 years and have had no problems until at the weekend I replace my database server with a newer one. The database migration went like a dream and I had...
4
by: yashgt | last post by:
Hi, We have created a SQL server 2000 database. We observe that the transaction log keeps growing over time. We are now about to run out of space. We have been periodically shrinking the...
1
by: Matik | last post by:
Hi to all, Probably I'm just doing something stupid, but I would like you to tell me that (if it is so), and point the solution. There ist the thing: I' having a sp, where I call other sp...
2
by: rbg | last post by:
Hi, On My local SQL server I have added a linked server to another SQL server (remoteserver) in another Windows NT Domain. When I run this code select count(*) from...
5
by: Kruton | last post by:
Hello everyone, This is more of an architectural question about SQL Server. Can someone please explain why when I perform a query such as the one below that updates a table using begin and end...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.