dyarosh
asked on
New transaction is not allowed because there are other threads running in the session Error
I receive the following error when I try and initiate an SQL Transaction. From looking at various posts, the problem seems to be because I have a Reader open when I try and initiate the transaction. Given the following code, can someone suggest a workaround to the problem? I am using VS2010, C#, SQL Server 2005.
The code performs the following:
Select CVLIDs from the master table that are less than the archive date.
For each CVLID
Retrieve the CVLID Data (comes from 3 different tables)
Insert related fields into the 3 Stats tables
Delete the CVL Data from the main tables (this has not been implemented yet in the code)
I want to use transaction processing so if an insert fails in one table, the data will be rolled back and the data will not be deleted from the main tables. I want the transaction to be on each individual CVL so that if one fails it won't prevent the archiving of the others. This will be run on a daily basis and expect 400 - 500 records to be archived daily. When it first runs there will be over 1500 records to be archived.
Any help is greatly appreciated!
The code performs the following:
Select CVLIDs from the master table that are less than the archive date.
For each CVLID
Retrieve the CVLID Data (comes from 3 different tables)
Insert related fields into the 3 Stats tables
Delete the CVL Data from the main tables (this has not been implemented yet in the code)
I want to use transaction processing so if an insert fails in one table, the data will be rolled back and the data will not be deleted from the main tables. I want the transaction to be on each individual CVL so that if one fails it won't prevent the archiving of the others. This will be run on a daily basis and expect 400 - 500 records to be archived daily. When it first runs there will be over 1500 records to be archived.
Any help is greatly appreciated!
public Int32 ArchiveCVLs(DateTime archivedate)
{
// This function archives the CVLs into the CVL Stats tables.
SqlDataReader reader;
Int32 cvlid, retVal;
bool errFlg = false;
string tmpErrorMsg = "";
Int32 cnt = 0;
try
{
// Define connection object
SqlConnection connection = null;
// Open Connection to Database
connection = OpenDatabase();
if (connection == null)
{
return 0;
}
// Create Command object
SqlCommand command = connection.CreateCommand();
SqlCommand command2 = connection.CreateCommand();
command.Connection = connection;
command2.Connection = connection;
// Retrieve CVLs to be archived
command.CommandText = "SELECT CVL_Policy.CVLID " +
"FROM CVL_Policy " +
"JOIN CVL_Submitter ON CVL_Policy.CVLID = CVL_Submitter.CVLID " +
"WHERE IsClosed = @IsClosed AND DateTimeSubmitted < @reportdate";
command.Parameters.Clear();
command.Parameters.Add("@IsClosed", SqlDbType.Bit);
command.Parameters["@IsClosed"].Value = true;
command.Parameters.Add("@reportdate", SqlDbType.DateTime);
command.Parameters["@reportdate"].Value = archivedate;
reader = command.ExecuteReader();
// For each CVL, retrieve the CVL Data, update the Stats tables and then delete the CVL
SqlTransaction transaction;
while (reader.Read())
{
// Start a local transaction
transaction = connection.BeginTransaction("ArchiveCVL");
command2.Transaction = transaction;
cvlid = Convert.ToInt32(reader["CVLID"]);
try
{
if (RetrieveCVLData(cvlid))
{
cnt++;
// CVL Data found, insert into Stats Tables
command2.Parameters.Clear();
command2.CommandText = "INSERT INTO CVL_Stats_Transmit " +
"(CVLID, FaxNumber, FaxTransmitAttempts, FaxIsTransmitted, faxtransmitdate, " +
"EmailAddr, EmailTransmitAttempts, EmailIsTransmitted, EmailTransmitDate) " +
"VALUES(@cvlid, @faxnumber, @faxtransmitattempts, @faxistransmitted, @faxtransmitdate, " +
"@emailaddr, @emailtransmitattempts, @emailistransmitted, @emailtransmitdate);";
command2.Parameters.Add("@cvlid", SqlDbType.Int);
command2.Parameters["@cvlid"].Value = cvlid;
command2.Parameters.Add("@faxnumber", SqlDbType.NChar, 255);
command2.Parameters["@faxnumber"].Value = FaxNumber;
command2.Parameters.Add("@faxtransmitattempts", SqlDbType.Int);
command2.Parameters["@faxtransmitattempts"].Value = FaxTransmitAttempts;
command2.Parameters.Add("@faxistransmitted", SqlDbType.Bit);
command2.Parameters["@faxistransmitted"].Value = FaxIsTransmitted;
command2.Parameters.Add("@faxtransmitdate", SqlDbType.DateTime);
if (String.IsNullOrEmpty(FaxTransmitDate))
command2.Parameters["@faxtransmitdate"].Value = DBNull.Value;
else
command2.Parameters["@faxtransmitdate"].Value = FaxTransmitDate;
command2.Parameters.Add("@emailaddr", SqlDbType.NChar, 255);
command2.Parameters["@emailaddr"].Value = EmailAddr;
command2.Parameters.Add("@emailtransmitattempts", SqlDbType.Int);
command2.Parameters["@emailtransmitattempts"].Value = EmailTransmitAttempts;
command2.Parameters.Add("@emailistransmitted", SqlDbType.Bit);
command2.Parameters["@emailistransmitted"].Value = EmailIsTransmitted;
command2.Parameters.Add("@emailtransmitdate", SqlDbType.DateTime);
if (EmailTransmitDate == null)
command2.Parameters["@emailtransmitdate"].Value = DBNull.Value;
else
command2.Parameters["@emailtransmitdate"].Value = EmailTransmitDate;
try
{
retVal = command2.ExecuteNonQuery();
}
catch (Exception ex1)
{
retVal = 0;
tmpErrorMsg = tmpErrorMsg + "Error Inserting into CVL_Stats_Transmit CVLID (" + cvlid.ToString() + "). " + ex1.Message + "<br />";
cnt--;
if (cnt < 0)
cnt = 0;
cvlid = 0;
errFlg = true;
}
if (retVal == 1)
{
// Insert successful
command2.Parameters.Clear();
command2.CommandText = "INSERT INTO CVL_Stats_Submitter " +
"(CVLID, SubmitterEmail, DateTimeSubmitted, SubmitterName) " +
"VALUES(@cvlid, @submitteremail, @datetimesubmitted, @submittername);";
command2.Parameters.Add("@cvlid", SqlDbType.Int);
command2.Parameters["@cvlid"].Value = cvlid;
command2.Parameters.Add("@submitteremail", SqlDbType.NChar, 50);
command2.Parameters["@submitteremail"].Value = SubmitterEmail;
command2.Parameters.Add("@datetimesubmitted", SqlDbType.DateTime);
if (String.IsNullOrEmpty(DateTimeSubmitted))
command2.Parameters["@datetimesubmitted"].Value = DBNull.Value;
else
command2.Parameters["@datetimesubmitted"].Value = DateTimeSubmitted;
command2.Parameters.Add("@submittername", SqlDbType.NChar, 50);
command2.Parameters["@submittername"].Value = SubmitterName;
try
{
retVal = command2.ExecuteNonQuery();
}
catch (Exception ex2)
{
retVal = 0;
tmpErrorMsg = tmpErrorMsg + "Error inserting into CVL_Stats_Submitter CVLID (" + cvlid.ToString() + "). " + ex2.Message + "<br />";
cvlid = 0;
errFlg = true;
}
if (retVal == 1)
{
// Insert successful
command2.Parameters.Clear();
command2.CommandText = "INSERT INTO CVL_Stats_Policy " +
"(CVLID, StateName, PolicyNumber, AssignedAgent, " +
"DateTimeAssigned, IsClosed, DateTimeClosed) " +
"VALUES(@cvlid, @statename, @policynumber, @assignedagent, " +
"@datetimeassigned, @isclosed, @datetimeclosed);";
command2.Parameters.Add("@cvlid", SqlDbType.Int);
command2.Parameters["@cvlid"].Value = cvlid;
command2.Parameters.Add("@statename", SqlDbType.NChar, 2);
command2.Parameters["@statename"].Value = StateName;
command2.Parameters.Add("@policynumber", SqlDbType.NChar, 255);
command2.Parameters["@policynumber"].Value = PolicyNumber;
command2.Parameters.Add("@assignedagent", SqlDbType.Int);
command2.Parameters["@assignedagent"].Value = AssignedAgent;
command2.Parameters.Add("@datetimeassigned", SqlDbType.DateTime);
if (String.IsNullOrEmpty(DateTimeAssigned))
command2.Parameters["@datetimeassigned"].Value = DBNull.Value;
else
command2.Parameters["@datetimeassigned"].Value = DateTimeAssigned;
command2.Parameters.Add("@isclosed", SqlDbType.Bit);
command2.Parameters["@isclosed"].Value = IsClosed;
command2.Parameters.Add("@datetimeclosed", SqlDbType.DateTime);
if (String.IsNullOrEmpty(DateTimeClosed))
command2.Parameters["@datetimeclosed"].Value = DBNull.Value;
else
command2.Parameters["@datetimeclosed"].Value = DateTimeClosed;
try
{
retVal = command2.ExecuteNonQuery();
}
catch (Exception ex3)
{
retVal = 0;
tmpErrorMsg = tmpErrorMsg + "Error inserting into CVL_Stats_Policy CVLID (" + cvlid.ToString() + "). " + ex3.Message + "<br />";
cvlid = 0;
errFlg = true;
}
if (retVal == 0)
{
errFlg = true;
tmpErrorMsg = tmpErrorMsg + "Error Insert data into CVL_Stats_Policy (CVLID=" + cvlid.ToString() + ").<br />";
}
}
else
{
errFlg = true;
tmpErrorMsg = tmpErrorMsg + "Error Insert data into CVL_Stats_Submitter (CVLID=" + cvlid.ToString() + ").<br />";
}
}
else
{
errFlg = true;
tmpErrorMsg = tmpErrorMsg + "Error Insert data into CVL_Stats_Transmit (CVLID=" + cvlid.ToString() + ").<br />";
}
}
}
catch (Exception ex)
{
tmpErrorMsg = tmpErrorMsg + "There was a problem archiving CVLID (" + cvlid.ToString() + "). " + ex.Message + "<br />";
cnt--;
if (cnt < 0)
cnt = 0;
cvlid = 0;
errFlg = true;
}
// Attempt to commit the transaction
if (errFlg == false)
transaction.Commit();
else
transaction.Rollback();
}
if (tmpErrorMsg != "")
ErrorMessage = tmpErrorMsg + " Please contact Intranet Team to report the problem.";
else
ErrorMessage = "";
reader.Close();
connection.Close();
connection.Dispose();
command.Dispose();
reader.Dispose();
return cnt;
}
catch (Exception e)
{
ErrorMessage = "There was a problem archiving CVLs. " + e.Message + " Please contact Intranet Team to report the problem.";
return 0;
}
}ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
EugeneZ -
I had reviewed those links before posting my question but did not find them helpful because I am not using EF.
I had reviewed those links before posting my question but did not find them helpful because I am not using EF.
ASKER
I endued up using the DataTable solution. Thanks for the help.
1. Read all the data and close the reader (perhaps use a DataTable) and then iterate on the disconnected collection.
2. Use a different SqlConnection for the transaction. Then the first connection for the collection is not interfering with the second transaction connection.
Gary Davis