SQLTransaction or Table Valued Parameters for inserts across multiple tables?

0
votes

I have 2 tables, "TableA" for Item Details, "Table B" for File Attachments.

For the insertion of new item details records ( 1 new row in TableA, and possibly multiple rows in TableB in the database), which is a better option:

1) create 2 insert stored procedures (one for each table), and handle the transaction in the application layer For example:

try

{

Connection.Open();

SqlTransaction trans transaction= Connection.BeginTransaction(IsolationLevel.Serializable);

//insert item record here (inserts into TableA in the database)

//insert file attachment records here (inserts into TableB in the database)

transaction.Commit();

}

catch (Exception Ex)

{

trans.Rollback();

}

2) create table valued parameter TVPFileAttachments and pass everything into 1 single stored procedure in the database in one operation (transaction handled by stored procedure)

Which is the better standard practice when it comes to updating multiple tables this way?

1
.netsqlsql-serverdatabasetransactions

1 Answers

0
votes

Create 2 inserts inside transaction.

BEGIN TRY
BEGIN TRAN
INSERT INTO ...
VALUES....
INSERT INTO ....
VALUES....
COMMIT TRAN
END TRY 
BEGIN CATCH
ROLLBACK TRAN
END CATCH