C＃ASP.Net：异常（有时只）长的数据库操作时 [英] C# ASP.Net: Exception (some times only) during long database operation

问题描述

我们有一个使用WCF连接到业务层的ASP.Net Web应用程序。有时候，我得到的异常时，它正在执行一个巨大的操作。有趣的是，当我跑了它的第一次成功。此后，它抛出异常。

We have a ASP.Net web application that connects to its business layer using WCF. Sometimes, I am getting exception when it is performing a huge operation. The interesting part is when I ran it for the first time it was successful. Afterwards it is throwing exception.

例外：套接字连接被中止。

Exception: The socket connection was aborted.

的操作是它上传拉链codeS进入从CSV文件数据库表。首先，我们从CSV文件中读取并进行压缩codeS的一个长度的字符串。这被传递到存储过程，并执行数据库操作。

The operation is that it uploads zipcodes into database tables from a csv file. First we read from csv file and make a single lengthy string of zipcodes. This is passed to the stored procedure and the database operations are performed.

注1 :)当我把断点和测试，很明显，字符串的创建（从CSV获取数据和附加后）是pretty快速（不到一分钟）。

Note 1:) When I placed breakpoint and tested, it is clear that the creation of the string (after taking data from csv and appending) is pretty fast (less than one minute).

注2 :)我删除了交易（从C＃code）和测试，即使在当时的例外是存在的。

Note 2:) I removed the transaction (from C# code) and tested, even then the exception is there.

注3 :)有一万卢比的CSV（十万）的记录。每行有四列（邮编code，市，县，州）。 CSV文件的大小为3MB。

Note 3:) There are one lakh (one hundred thousand) records in csv. Each row has four columns (ZipCode, City, County, State). Size of the csv file is 3MB.

我做了一个有关事务日志的大小怀疑。然后，我使用下面的命令收缩日志文件。
DBCC SHRINKFILE（'MyDB_log'，1）GO

I had a doubt about the transaction log size. Then I shrinked the log file using the following command. DBCC SHRINKFILE('MyDB_log', 1) GO

然后我使用SELECT [尺寸]检查日志大小，MAX_SIZE，Data_Space_Id，[FILE_ID]，Type_Desc，[名] FROM FRAMIS_R2075.sys.database_files WHERE data_space_id = 0

Then I checked the log size using SELECT [Size],Max_Size,Data_Space_Id,[File_Id],Type_Desc,[Name] FROM FRAMIS_R2075.sys.database_files WHERE data_space_id = 0

尺寸为128;最大尺寸是268435456; Type_Desc =LOG

The Size is 128; Max Size is 268435456; Type_Desc = "LOG"

即使异常收缩后仍然来了。

Even after shrinking the exception is still coming.

框架：.NET 3.0

Framework: .Net 3.0

DB：SQL Server 2005中

DB: SQL Server 2005

嗯，好像有在业务层的错误时抛出过，当我等了20多分钟。它说，无效的尝试调用阅读时，读者被称为。通过见状，我删除了DbDataReader和使用的SqlCommand更新数据库表。同样，出现了在业务层的异常，一些20分钟说超时异常之后。任何想法，为什么发生这种情况？

Well, it seems like there is an excpetion in the business layer too, when I waited for 20 more minutes. It said, "Invalid Attempt to call Read when the reader is called". By seeing this, I removed the DbDataReader and used SqlCommand to update the database tables. Again, there came an exception in business layer, after some 20 minutes saying "Timeout exception". Any idea why this is happening?

  private void ProcessDatabaseOperationsForZipCode(StringBuilder dataStringToProcess, int UserID)
    {
        int CountOfUnchangedZipCode = 0;
        string strRetiredZipCode = "";
        string strNewZipCode = "";
        dataStringToProcess.Remove(dataStringToProcess.Length - 1, 1);


        if (dataStringToProcess.Length > 0)
        {

            //TimeSpan.FromMinutes(0) - to make transaction scope as infinite.
            using (TransactionScope transaction = TransactionScopeFactory.GetTransactionScope(TimeSpan.FromMinutes(0)))
            {


                SqlConnection mySqlConnection = new SqlConnection("data source=myServer;initial catalog=myDB; Integrated Security=SSPI;");
                SqlCommand mySqlCommand = new SqlCommand("aspInsertUSAZipCode", mySqlConnection);
                mySqlCommand.CommandType = CommandType.StoredProcedure;
                mySqlCommand.Parameters.Add("@DataRows",dataStringToProcess.ToString());
                mySqlCommand.Parameters.Add("@currDate", DateTime.Now);
                mySqlCommand.Parameters.Add("@userID", UserID);
                mySqlCommand.Parameters.Add("@CountOfUnchangedZipCode", 1000);
                mySqlCommand.CommandTimeout = 0;
                mySqlConnection.Open();
                int numberOfRows = mySqlCommand.ExecuteNonQuery();

         //Database db = DatabaseFactory.CreateDatabase();
                //DbCommand cmd = db.GetStoredProcCommand("aspInsertUSAZipCode");
                //cmd.CommandTimeout = 0;
                //db.AddInParameter(cmd, "@DataRows", DbType.String, dataStringToProcess.ToString());
                //db.AddInParameter(cmd, "currDate", DbType.DateTime, DateTime.Now);
                //db.AddInParameter(cmd, "userID", DbType.Int32, UserID);
                //db.AddOutParameter(cmd, "CountOfUnchangedZipCode", DbType.String, 1000);


                //using (DbDataReader rdrUpgradeTypes = (DbDataReader)db.ExecuteReader(cmd))
                //{
                //    //while (rdrUpgradeTypes.Read())
                //    //{
                //    //    if (!String.IsNullOrEmpty(Utility.GetString(rdrUpgradeTypes, "NewZipCode")))
                //    //    {
                //    //        strNewZipCode = strNewZipCode + "," + Utility.GetString(rdrUpgradeTypes, "NewZipCode");
                //    //    }
                //    //}
                //}


                transaction.Complete();

            }
        }


    }

感谢

Lijo

推荐答案

问题很可能是到SQL Server的连接超时 - 这两个连接的默认超时和命令是30秒

The problem is likely that the connection to the SQL Server times out - the default timeout for both connections and commands is 30 seconds.

您既可以达到超时（在连接字符串和code）或分手的更新成块。

You can either up the timeout (on the connection string and in code) or break up the updates into chunks.

这篇关于C＃ASP.Net：异常（有时只）长的数据库操作时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！