I had something like this going on with one of our internal apps, until I
decided
to close and dispose the command and it's connection explicitly:
cmd.Connection.Close(); // Explicit
cmd.Connection.Dispose(); // Implicit close
cmd.Dispose(); // Explicit, .NET 1.0 is No-op, 1.1? 2.0?
This might be overkill but worked. It cannot be translated directly
to your case, but might give some ideas. For example,
in private void WriteInput(AssignmentContainer data, DataCon dcon, ..,
it might help to call cmd.Dispose() or use using(cmd).
Also, it might be useful to be more explicit with closing the data objects.
Instead of relying on Dispose(), do trans.Close() - trans.Dispose(), and
dcon.Close() - dcon.Dispose(). They do not incure much overhead,
but the code is a bit more readable.
You might check also
http://blogs.msdn.com/angelsb/archive/2004/07/15/184479.aspx
for fake MARS.
Laura
Jochen Berger said:
Okay, here are the two main methods involved in this drama.
<code>
public void CalculatePreproduction(int vpKey)
{
// a database helper which contains the SqlConnection
DataCon dcon = new DataCon(helper.ConnectionString);
StringBuilder selectAssignments = new StringBuilder();
selectAssignments.Append("select * from PPTB1003 where VP_LNR =
").Append(vpKey);
// helper.LoadTable() loads the data from the DB into a DataTable
that's returned by the SQL statement
DataTable allAssignments = helper.LoadTable("PPTB1003",
dcon.Connection, selectAssignments.ToString(), false);
SqlTransaction trans = dcon.Connection.BeginTransaction();
try
{
// any assignment for that id found?
if (allAssignments != null)
{
Invoice invoice = new Invoice(FetchSalaryTable(DateTime.Now),
allDiscounts);
foreach (DataRow row in allAssignments.Rows)
{
// DataInContainer puts all data from the row in an object.
Invoice.Calculate() then calculates the prices.
// WriteInput finally writes the results to 2 different tables
WriteInput(invoice.Calculate(DataInContainer(row)), dcon, trans);
}
trans.Commit();
}
}
catch(Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
trans.Dispose();
// this Dispoce() contains a call of the SqlConnection.Close() method
dcon.Dispose();
}
}
private void WriteInput(AssignmentContainer data, DataCon dcon,
SqlTransaction trans)
{
// has this assignment got a valid assignment ID?
if (data.AssignmentKey > 0)
{
StringBuilder update1003 = new StringBuilder();
update1003.Append("update PPTB1003 set");
// [...]
// several fields, that need to be updated
// [...]
update1003.Append(" where ASSIGNMENT_ID =
").Append(data.AssignmentKey);
SqlCommand cmd = dcon.Connection.CreateCommand();
cmd.CommandText = update1003.ToString();
cmd.CommandType = CommandType.Text;
cmd.Transaction = trans;
try
{
cmd.ExecuteNonQuery();
}
catch(Exception exc)
{
throw new Exception("Error while storing the data of assignment " +
data.AssignmentNo + ":\n" + exc.ToString(), exc);
}
StringBuilder update1004 = new StringBuilder();
update1004.Append("update PPTB1004 set SUPERVISOR_INST_SL =
").Append(data.SupervisorInstitute).Append(" where ASSIGNMENT_ID =
").Append(data.AssignmentKey);
cmd = dcon.Connection.CreateCommand();
cmd.CommandText = update1004.ToString();
cmd.CommandType = CommandType.Text;
cmd.Transaction = trans;
try
{
cmd.ExecuteNonQuery();
}
catch(Exception exc)
{
throw new Exception("Error while storing for consistency of appraisal
for assignment " + data.AssignmentNo + ":\n" + exc.ToString(), exc);
}
}
}
</code>
