Last post Feb 28, 2017 09:37 PM by deepalgorithm
Feb 28, 2017 03:00 PM|aspfun|LINK
I learn transaction for asp.net here.
This demo only for one submission.
In my project, there is a [Submit] button in which will execute four sub.
Each sub will insert data into one table.
How to apply code for transaction?
That is, if any one sub failed to insert all data should be rolled back.
Feb 28, 2017 04:48 PM|jimmy69|LINK
what do you means by one submission for the link you provide ?
in the link, i see 4 insert if one fail all data will be roll back.
If you have many datas to insert , update and so on, for me you need just init your transaxtion , assign the transaction to all your sqlcommand and so on ...
SqlConnection db = new SqlConnection( ...);
transaction = db.BeginTransaction();
// ALL YOUR INSERT / UPDATE AND SO ON
// roll back
have a nice day
Feb 28, 2017 05:20 PM|raju dasa|LINK
if any one sub failed to insert all data should be rolled back.
Transaction can't be kept open waiting for all 4 data submissions.
Each Request/connection should commit/rollback opened transaction.
AFAIK, your requirement is not possible.
A better approach would be saving Ids of inserted data (from DB) into Session and then
check for submissions status (error) and DELETE all of inserted data based on Ids from Session.
Feb 28, 2017 06:13 PM|deepalgorithm|LINK
You have several options. You can create another routine that wraps those four methods in a transaction which your button click event will call. From there you can use TransactionScope.
Or you can you can use SqlTransaction to execute multiple queries in one thread.
public void RootMethod()
using(var scope = new TransactionScope())
/* Perform transactional work here */
catch (TransactionAbortedException ex)
var connection = new SqlConnection(connectionString);
trans = connection.BeginTransaction();
foreach (var commandString in sqlCommandList)
var command = new SqlCommand(commandString, connection,trans);
catch (Exception ex) //error occurred
Feb 28, 2017 09:09 PM|aspfun|LINK
Thank all of you.
Here is my code structure. Where to add transaction and roll back code?
Protected Sub btnSubmit_Click(sender As Object, e As System.EventArgs) Handles btnSubmit.Click
Protected sub1(byval ...20 parameters)
Dim myDSN As String = ConfigurationManager.AppSettings("ConnectionString")
Dim myConn As New SqlConnection(myDSN)
Dim strSQL As String
strSQL = " insert into table1"
Dim cmd As New SqlCommand(strSQL, myConn)
cmd.CommandType = CommandType.Text
Protected sub2(byval ...20 parameters)
... the same way to insert table2
Protected sub3(byval ...20 parameters)
... the same way to insert table3
Protected sub4(byval ...20 parameters)
... the same way to insert table4
Feb 28, 2017 09:37 PM|deepalgorithm|LINK
My answer above is for c# (which I know), but the concept in VB.NET should be the same.
Seems like it would be easiest to implement this using TransactionScope given how your code is structured, refer to the following:
Sample not tested
Using scope As New TransactionScope()
'call other methods here
Catch ex As TransactionAbortedException
Catch ex As ApplicationException
VB.NET documentation on TransactionScope usage: