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