Urgent problem: Any help greatly appreciated

  • Thread starter Thread starter Simon Harvey
  • Start date Start date
S

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;

}
 
Simon Harvey said:
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
Click to expand...
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 said:
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;
}
Click to expand...


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
 
Back
Top