Urgent problem: Any help greatly appreciated

[Simon Harvey]

Hi everyone,

I'm having a problem that I don't know how to sort.

I am trying to execute a number of SQL stored procedures in a single
transaction. However it always throughs an exception saying that the "Thread
was being aborted"
I really need to be able to execute these procedures in a single transaction
so that the data doesnt become corrupt.

If anyone could help me I would be very greatful.

Sincerest thanks and kindest regards

Simon

public static bool executeBatchTransaction(ArrayList cmds){
SqlConnection con = new SqlConnection(connectionString);
IEnumerator cmdEnumerator;
SqlCommand currentCmd;
SqlTransaction trans;
DataSet dataset = new DataSet();

// Get this command seperately so we can start the transaction
currentCmd = (SqlCommand)cmds[0];

// We can't put this in a try block because if con.open fails, trans wont
be assigned to and we'll
// get an unassigned variable. Wont compile
// Start the transaction
currentCmd.Connection = con;
currentCmd.Connection.Open();
trans = currentCmd.Connection.BeginTransaction();
currentCmd.Transaction = trans;


// Execute the first command seperately
try{
if(!executeNonQuery(currentCmd)){
trans.Rollback();
return false;
}
}
catch(Exception e){
trans.Rollback();
ExceptionManager.Publish(new Exception("Exception detected whilst
executing DataAccessProvider.executeBatchTransaction(ArrayList cmds", e ));
MiscLogic.redirectOnError("/errors/defaultErrorPage.aspx");
}

cmdEnumerator = cmds.GetEnumerator();
// Skip the first command
cmdEnumerator.MoveNext();

while(cmdEnumerator.MoveNext()){
currentCmd = (SqlCommand)cmdEnumerator.Current;
try{
if(executeNonQuery(currentCmd)){
continue;
}
else{
trans.Rollback();
return false;
}
}
catch(Exception e){
trans.Rollback();
ExceptionManager.Publish(new Exception("Exception detected whilst
executing DataAccessProvider.executeBatchTransaction(ArrayList cmds", e ));
MiscLogic.redirectOnError("/errors/defaultErrorPage.aspx");
}
}


// If we get to here all the commands executed successfully. Commit and
return
trans.Commit();
return true;

}

 

[chanmmn]

Can you combine those store procedures to derive a new one in your SQL
server?

chanmm

 

[David Browne]

Hi everyone,

I'm having a problem that I don't know how to sort.

I am trying to execute a number of SQL stored procedures in a single
transaction. However it always throughs an exception saying that the "Thread
was being aborted"
I really need to be able to execute these procedures in a single transaction
so that the data doesnt become corrupt.

If anyone could help me I would be very greatful.

Sincerest thanks and kindest regards

OK, where to start.

First, don't return bool. Just use a void function. If anything goes wrong
throw an exception.

Second don't do a ASP.NET redirect in this function. It belongs in the
catch block of the code which invokes this function. Not only is is "tier
mixing", the redirect is implemented with a ThreadAbortException, and it can
be confusing to follow the thread of execution.

Third, you need to guaratee that the connection gets closed.

Forth, simplify the program flow.

Try this instead

public static SqlConnection connect()
{
SqlConnection con = new SqlConnection(connectionString);
con.Open();
return con;
}
public static void executeBatchTransaction(ArrayList cmds)
{
using (SqlConnection con = connect())
{
SqlTransaction trans = con.BeginTransaction();;
for (int i = 0; i < cmds.Count; i++)
{
SqlCommand cmd = (SqlCommand)cmds;
cmd.Transaction = trans;
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
trans.Rollback();
throw;
}
}
trans.Commit();
}
}


David

 

[David Browne]

OK, where to start.

First, don't return bool. Just use a void function. If anything goes wrong
throw an exception.

Second don't do a ASP.NET redirect in this function. It belongs in the
catch block of the code which invokes this function. Not only is is "tier
mixing", the redirect is implemented with a ThreadAbortException, and it can
be confusing to follow the thread of execution.

Third, you need to guaratee that the connection gets closed.

Forth, simplify the program flow.

Try this instead

public static SqlConnection connect()
{
SqlConnection con = new SqlConnection(connectionString);
con.Open();
return con;
}

You can shorten this even further, at the risk of being a tad cryptic, to

public static void executeBatchTransaction(ArrayList cmds)
{
using (SqlConnection con = connect())
using (SqlTransaction trans = con.BeginTransaction())
{
for (int i = 0; i < cmds.Count; i++)
{
SqlCommand cmd = (SqlCommand)cmds;
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
}
trans.Commit();
}
}

Since SqlTransaction.Dispose will rollback an uncommited transaction.

David