Sometimes the amount of data processed by the program is relatively small, well-organized, all safe and sound, but the amount of data, the original latent problem is exposed.
The code for the original Access database is:
1SqlConnection conn = new SqlConnection (strconn);
2conn. Open ();
3SqlTransaction trans = conn. BeginTransaction ();
4try
5{
6 cengine.executenonquery (Trans,commandtype.text,sql);
7 Trans.commit ();
8}
9catch (SqlException ex)
10{
One by one trans. Rollback ();
ErrorCode = ex. number;
info = "Data operation failed:" Ex. message;
14}
15finally
16{
Trans. Dispose ();
Conn. Close ();
19}
20
21st
22
At run time, the system prompts for an error when the amount of data is too large or the processing time is longer. The error message is "SqlTransaction has been used up; it is no longer available." ”
At first, I suspected it was about memory. Because the system needs to be ready for transaction rollback, every execution of a inserted or modified SQL, must have a certain cost, data volume a large, I am afraid it will be too much. But I checked the SQL Server data, and I don't see any memory issues.
It was later thought that there was a time problem with the database connection sqltransaction. The default is 15 seconds. When the volume of data is large, this time is probably not enough. Then read:
1SqlConnection conn = new SqlConnection (strconn);
2conn. Open ();
3SqlTransaction trans = conn. BeginTransaction ();
4try
5{
6 SqlCommand cmd = new SqlCommand ();
7 Cmd.commandtype = CommandType.Text;
8//Connection time changed to 300 seconds
9 cmd.commandtimeout = 300;
Ten cmd.commandtext = SQL;
One by one cmd. Connection = conn;
CMD. Transaction = trans;
CMD. ExecuteNonQuery ();
Trans.commit ();
15}
16catch (SqlException ex)
17{
Trans. Rollback ();
ErrorCode = ex. number;
info = "Data operation failed:" Ex. message;
21}
22finally
039
Trans. Dispose ();
Conn. Close ();
26}
After the modification in the test, the problem solved:)
