Transactions in Ado.net and sql server

Posted by Navalemanoj0405 under Sql Server on 3/22/2011 | Points: 10 | Views : 1922 | Status : [Member] | Replies : 2
Hello All

I am new comer working on asp.net c#, sql server. I want to ask you question regarding Transactions .

we set transaction for any operation in .net by using Ado.net transaction and sql server transaction (in stored procedure).

so which is the better one. Or both should use ?

Please reply....

Thanks




Responses

Posted by: SheoNarayan on: 3/22/2011 [Administrator] HonoraryPlatinum | Points: 25

Up
0
Down
Hi Navalemanoj0405,

Welcome. Its not about which one is better but its more to do with your scenario. You can use either of them.

Scenario 1: You have to insert a record into Table1 and its log into a logTable. You can write INSERT statements for both the tables into the Stored Procedure. In this case to make sure that both operations are being performed or neither of them are being performed, you can wrap both INSERT statements into Transactions (in the stored procedure).

Scenario 2: Stored Procedures are already written and you do not have control over them. You need to perform the same above operation from your code (typically data access layer), then you can open the connection, attach the SqlTransaction and call the 1st Stored procedure to insert the record into Table1 and call the 2nd stored procedure to insert the record into logTable.

For more on Transactions, you may read this article http://www.dotnetfunda.com/articles/article184.aspx

Thanks



Regards,
Sheo Narayan
http://www.dotnetfunda.com

Navalemanoj0405, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Navalemanoj0405 on: 3/22/2011 [Member] Starter | Points: 25

Up
0
Down
Thank you very much SheoNarayan
I got it

Navalemanoj0405, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response