c# - SqlBulkCopy - 哪个数据库导致异常？

Question

我正在使用 SqlBulkCopy 将一批记录从 MySQL 复制到 SQL Server。

正好 30 秒后，我明白了

System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
The statement has been terminated.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

异常内有一个“Error”对象，其中包含以下详细信息:

Message: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
LineNumber: 0
Source: .Net SqlClient Data Provider
Procedure: 
Index #1
Message: The statement has been terminated.
LineNumber: 1
Source: .Net SqlClient Data Provider
Procedure: 

这是代码

using (MySqlConnection sourceConnection = new MySqlConnection(AccManConnectionString)) {
sourceConnection.Open();
MySqlCommand commandSourceData = new MySqlCommand(string.Format(sql, VersionNum.ToString()), sourceConnection);
for (int i = 0; i < ParamNames.Length; i++)
{
    commandSourceData.Parameters.AddWithValue(ParamNames[i], SetIDList[i]);
}
MySqlDataReader reader = commandSourceData.ExecuteReader();
using (SqlConnection destinationConnection = new SqlConnection(TimetableConnectionString))
{
    try
    {
    destinationConnection.Open();   
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
    {
        bulkCopy.DestinationTableName = "NetworkID";

        // Configure the batch sizes and timeouts (cofig code omitted)
        bulkCopy.BatchSize = batchSize;
            bulkCopy.BulkCopyTimeout = timeout;

        try
        {
        bulkCopy.WriteToServer(reader);
        SqlCommand update = new SqlCommand(string.Format("UPDATE p SET p.Username = n.Username FROM NetworkID n INNER JOIN Person p ON n.PersonID = p.PersonID and n.VersionID = {0} where p.VersionID = {0}", VersionNum), destinationConnection);
        update.ExecuteNonQuery();
        }
        catch (SqlException ex)
        {
        log.Error("Exception caught", ex);
        }
        finally
        {
        reader.Close();
        }
    }
    }
    catch (Exception e)
    {
    log.Error("Exception caught", e);
    }
 }
}

我知道我可以(并且已经)尝试过很多超时/批量大小参数。但我的问题是，从编码的角度来看，有没有办法确定哪个数据库服务器给我带来了问题？

谢谢

Answer 1

您遇到的超时可能受到设置 SqlBulkCopy.BulkCopyTimeOut 的影响默认值为 30 秒。

要确定问题出在哪里，最好的选择是捕获 SqlException查看它是否包含更多详细信息，但在您的实例中，我相信这将是您的代码(客户端)超时。

关于 SqlException 的文档有一个很好的例子说明如何枚举异常中包含的错误。

更新1

我可以看到您正在使用 MySqlCommand，我猜这是 Devart，如果是这样，您还没有为此命令设置超时，对于这个命令，您需要使用语法 CommandTimeout .

MySqlCommand commandSourceData = new MySqlCommand(string.Format(sql, VersionNum.ToString()), sourceConnection);
commandSourceData.CommandTimeout = timeout;

您还应该在 SqlCommand 上放一个.

SqlCommand update = new SqlCommand(string.Format("UPDATE p SET p.Username = n.Username FROM NetworkID n INNER JOIN Person p ON n.PersonID = p.PersonID and n.VersionID = {0} where p.VersionID = {0}", VersionNum), destinationConnection);
update.CommandTimeout = timeout;

更新2

刚刚阅读 SqlBulkCopy 上的文档并注意到以下几点:

If multiple active result sets (MARS) is disabled, WriteToServer makes the connection busy. If MARS is enabled, you can interleave calls to WriteToServer with other commands in the same connection.

我不确定您是否使用 MARS，但是，上面的代码正在调用 SQL 以在 WriteToServer 方法之后、BulkCopy 关闭之前(通过使用)进行更新。您是否可以尝试通过在 Update 之前调用 close 来显式关闭 SQLBulkCopy，或者将 Update 移到 BulkCopy 的 using 语句之外。