Data Missing after a transaction is said "committed successfully" - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Data Missing after a transaction is said "committed successfully"

skchan12skchan12 Posts: 1Member
Hi,

My application requires several clients (can be at the same time) to write the data to the SQL Server 2008 through the WEB Service.

The Web Service is just a transaction and return "success" or "fail". I found that after a few hundreds or thousands of transactions, though the Web Service return "success", the data is missing after that transaction is said committed successfully.

The followings is the piece of code that the Web Service will call.

public static int ExecSQL(string[] SQL)
{
int I = 0;
int J = 0;
string S = "";
try {
CN = GenConnection();
CN.Open();
SqlConnection.ClearPool((SqlConnection)CN);
TR = CN.BeginTransaction();

CM = GenCommand();
CM.Connection = CN;
CM.Transaction = TR;
CM.CommandTimeout = 600;
for (J = SQL.GetLowerBound(0); J <= SQL.GetUpperBound(0); J++) {
S = SQL[J].Trim();
if (!string.IsNullOrEmpty(S)) {
CM.CommandText = S;
I = CM.ExecuteNonQuery();
}
}
TR.Commit();

CN.Close();
CN.Dispose();
GC.Collect();

return 0;
} catch (Exception E) {
try {
if ((TR != null)) {
if ((TR.Connection != null)) {
TR.Rollback();
}
}
} catch {
}
LogError(E, S);

CN.Close();
CN.Dispose();
GC.Collect();
return 255;

} finally {
}
}

I have log the data before calling this sub-routine and after this sub-routine. The data missing is found just after successfully committed. Please help.... it's been distributing me for several months.....

Best regards
Sign In or Register to comment.