Sometimes when a program processes a small amount of data, everything is smooth and fine, but with a large amount of data, the underlying problem is exposed.

In a project I did, I was responsible for the attendance check of a factory. There are more than 1000 employees in the factory. If each person clocked in four times a day for 30 days a month, the number of attendance records produced is 1000 * 4 * 30 = 120,000. To process these records, I create SQL statements and execute them:

   SqlConnection conn = new SqlConnection(strConn);
   conn.Open();
   SqlTransaction trans = conn.BeginTransaction();
   try  
   {
      CEngine.ExecuteNonQuery(trans, CommandType.Text, sql);
      trans.Commit();
   }
   catch(SqlException ex)
   {
      trans.Rollback();
      ErrorCode = ex.Number;
      Info = "Data operation failed:" + ex.Message;
   }
   finally
   {
      trans.Dispose();
      conn.Close();
   }
Copy the code

But it’s running in error. The error message “SqlTransaction has run out; It can no longer be used.”

At first, I suspected it was memory related. Because the system needs to be ready for transaction rollback, each execution of an insert or modify SQL, there is a certain amount of overhead, a large amount of data, I am afraid will not be able to bear. However, I checked the DATA of SQL SERVER and did not see any mention of memory problems.

Then IT occurred to me that there was a time limit for database connection to SqlConnection. The default is 15 seconds. When there’s a lot of data, it’s probably not enough time. So instead:

      SqlConnection conn = new SqlConnection(strConn);
      conn.Open();
      SqlTransaction trans = conn.BeginTransaction();
      try
      {
           SqlCommand cmd = new SqlCommand();
           cmd.CommandType = CommandType.Text;
           // Change the connection time to 300 seconds
           cmd.CommandTimeout = 300;
           cmd.CommandText = sql;
           cmd.Connection = conn;
           cmd.Transaction = trans;
           cmd.ExecuteNonQuery();
           trans.Commit();
        }
        catch(SqlException ex)
        {
           trans.Rollback();
           ErrorCode = ex.Number;
           Info = "Data operation failed:" + ex.Message;
        }
        finally
        {
           trans.Dispose();
           conn.Close();
        }
Copy the code

Problem solved.