Solve the problem of SqlTransaction exhausted (SQL processing timeout)_PHP tutorial

Sometimes the amount of data processed by the program is relatively small, everything is stable, and everything is safe and sound, but when the amount of data is large, the original hidden problems are exposed.
The original code to access the 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{
11 trans.Rollback();
12 ErrorCode = ex.Number;
13 Info = "Data operation failed:" ex.Message;
14}
15finally
16{
17 trans.Dispose();
18 conn.Close();
19}
20
21
22
When running, once the amount of data is too large or the processing time is long, long, the system will prompt an error. The error message is "SqlTransaction has been used up; it can no longer be used."
At first, I suspected it was related to memory. Because the system needs to be prepared for transaction rollback, every time a SQL statement is inserted or modified, there will be a certain amount of overhead. If the amount of data is large, it may be too much. However, I checked the SQL SERVER information and found no mention of memory issues.
Later I thought that there is a timing problem with database connection SqlTransaction. The default is 15 seconds. When the amount of data is large, this time may not be enough. So it was changed to:
1SqlConnection conn = new SqlConnection(strConn);
2conn.Open();
3SqlTransaction trans = conn.BeginTransaction();
4try
5{
6 SqlCommand cmd = new SqlCommand();
7 cmd.CommandType = CommandType.Text;
8 //The connection time limit is changed to 300 seconds
9 cmd.CommandTimeout = 300;
10 cmd.CommandText = sql;
11 cmd.Connection = conn;
12 cmd.Transaction = trans;
13 cmd.ExecuteNonQuery();
14 trans.Commit();
15}
16catch(SqlException ex)
17{
18 trans.Rollback();
19 ErrorCode = ex.Number;
20 Info = "Data operation failed:" ex.Message;
21 }
22finally
23{
24 trans.Dispose();
25 conn.Close();
26}
After modification, the problem is solved:)