September 11, 2007 at 11:54 pm
Hi,
I need to updates two tables in a single transaction.
First table contains one row to update, but Second table contains multiple rows.
I am using C#2005 and ADO.NET 2.0, my question is how to update/insert these multiple rows from multiple table in a single transaction?, means if any row transaction fails the whole transactions need to get rollback.
Can anybody help me regarding this?..
Regards,
Nelson.
September 12, 2007 at 5:25 am
High level overview of how to do this (we do this a lot in our application).
Use a sproc that takes an XML parameter.
Wrap the parent and child updates into XML
Have the sproc do the insert(s)/update(s) within a transaction.
Avoid a client side transaction, if you can.
Can post more if you'd like, but it varies a lot depending on your specific situation.
October 2, 2007 at 6:41 am
Cube,
Greg is right. Managing transactions can become a pain via clientside, unless you have already built a data layer to do so.
For a stored procedure to use transaction, it will look something like this:
create procedure dbo.up_UpdateTwoTables
(
@TableAID int
, @ValueA1 int
, @ValueA2 int
, @TableBID int
, @ValueB1 int
, @ValueB2 int
, @Error INT = NULL OUTPUT
)
AS
BEGIN
BEGIN TRAN
UPDATE dbo.TableA
SET
ValueA1 = @ValueA1
, ValueA2 = @ValueA2
WHERE
PKTableAID = @TableAID
SET @Error = @@Error
UPDATE dbo.TableB
SET
ValueB1 = @ValueB1
, ValueB2 = @ValueB2
WHERE
PKTableBID = @TableBID
SET @Error = @Error + @@Error
IF @Error>0
BEGIN
Rollback Tran
END
ELSE
BEGIN
Commit Tran
END
END
However, I believe you have mention that you will update multiple rows for TableB, you will need to modify the code above, with “comma separated values” for the parameter associated with TableB. You can do using a tally/number tables to parse the values and update the affected rows in TableB.
This can become complex and a bit tricky, if you do not have a strong TSQL development background. If so and if your deadline is coming soon, I would suggest using a transaction on the client side. (Via web app or app)
Regards,
Wameng Vang
MCTS
October 2, 2007 at 8:42 am
Thanks for the reply!..
If you give a code example or a related example link, it will be helpful.
Regards,
Cube.
October 3, 2007 at 2:24 am
October 4, 2007 at 1:33 am
Hi Guys ,
There is one another method to update several rows with a single transaction.
use SQlbulkCopy class for that...........Follow this link for code and explanation..........
http://davidhayden.com/blog/dave/archive/2006/03/08/2877.aspx
Thanks,
Avinish Awasthi
October 5, 2007 at 6:10 am
You can update multiple rows in single table with UPDATE using JOINS, also you can work with BEGIN TRAN and COMMIT TRAN (with SET XACT_ABORT ON; along with TRY CATCH in T-SQL 2005) to make sure that when one update fails all fail.
Since you have not posted your requirement specifically, I'll show how to update multiple rows in single table using JOIN and then then UPDATE other table in the JOIN (all by using sql server TRANSACTIONS).
Assuming that 2 tables by name Table1 and Table2 exist, each with 3 columns with names Table1Column1, Table1Column2 and Table1Column3 for Table1, and Table2Column1, Table2Column2 and Table2Column3 for Table2
BEGIN TRAN
BEGIN TRY
UPDATE SampleAlias1 SET Table1Column1 = 'value1', Table1Column2 = 'value2'
FROM Table1 AS SampleAlias1
JOIN Table2 AS SampleAlias2
ON SampleAlias2.Table2Column3 = SampleAlias1.Table1Column3
UPDATE SampleAlias2 SET Table2Column1 = 'value3', Table2Column2 = 'value4'
FROM Table1 AS SampleAlias1
JOIN Table2 AS SampleAlias2
ON SampleAlias2.Table2Column3 = SampleAlias1.Table1Column3
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
IF @@TRANCOUNT >= 1
BEGIN
COMMIT TRAN
END
This script will update both Table1 and Table2 and on an error will ROLLBACK the entire change.
Please get back if you need specific solution or more clarification.
-Ashik
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply