如何重用重新打开连接的代码

本文关键字：代码 连接 新打开 何重用 | 更新日期: 2023-09-27 18:22:23

我们的生产服务器会杀死非活动连接，因此我们的API需要在需要时恢复它们。以下代码有效，但非常重复：

   private const int MaxRetryCount = 3;
    public static SqlDataReader RestoreConnectionAndExecuteReader(SqlCommand command)
    {
        int retryCount = 0;
        while (retryCount++ < MaxRetryCount)
        {
            try
            {
                if (command.Connection.State == ConnectionState.Closed)
                    command.Connection.Open();
                return command.ExecuteReader();
            }
            catch(Exception e)
            {
                if(!e.Message.ToLower().Contains("transport-level error has occurred"))
                {
                    throw;
                }
            }
        }
        throw new Exception("Failed to restore connection for command:"+command.CommandText);
    }
    public static void RestoreConnectionAndExecuteNonQuery(SqlCommand command)
    {
        var retryCount = 0;
        while(retryCount++ < MaxRetryCount)
        {
            try
            {
                if (command.Connection.State == ConnectionState.Closed)
                    command.Connection.Open();
                command.ExecuteNonQuery();
                return;
            }
            catch(Exception e)
            {
                if (!e.Message.ToLower().Contains("transport-level error has occurred"))
                {
                    throw;
                }
            }
        }
        throw new Exception("Failed to restore connection for command:" + command.CommandText);
    }

如何重构代码并消除重复？我需要保留这些方法的签名，因为它们在整个系统中都在使用。

如何重用重新打开连接的代码

private const int MaxRetryCount = 3;
public static T RestoreConnectionAndExecute<T>(SqlCommand command, Func<SqlCommand, T> func)
{
    int retryCount = 0;
    while (retryCount++ < MaxRetryCount)
    {
        try
        {
            if (command.Connection.State == ConnectionState.Closed)
                command.Connection.Open();
            return func(command);
        }
        catch(Exception e)
        {
            if(!e.Message.ToLower().Contains("transport-level error has occurred"))
            {
                throw;
            }
        }
    }
    throw new Exception("Failed to restore connection for command:"+command.CommandText);
} 
public static SqlDataReader RestoreConnectionAndExecuteReader(SqlCommand command)
{
    return RestoreConnectionAndExecute(command, c => c.ExecuteReader());
}
public static int RestoreConnectionAndExecuteNonQuery(SqlCommand command)
{
    return RestoreConnectionAndExecute(command, c => c.ExecuteNonQuery());
}

private const int MaxRetryCount = 3;
        public static SqlDataReader RestoreConnectionAndExecuteReader(SqlCommand command)
        {
            return RestoreConnectionAndExecuteQueryHelper(command, true);
        }
        public static void RestoreConnectionAndExecuteNonQuery(SqlCommand command)
        {
            RestoreConnectionAndExecuteQueryHelper(command, false);
        }
        private static SqlDataReader RestoreConnectionAndExecuteQueryHelper(SqlCommand command, bool returnReader)
        {
            var retryCount = 0;
            while (retryCount++ < MaxRetryCount)
            {
                try
                {
                    if (command.Connection.State == ConnectionState.Closed)
                        command.Connection.Open();
                    if (returnReader)
                    {
                        return command.ExecuteReader();
                    }
                    else
                    {
                        command.ExecuteNonQuery();
                        return null;
                    }
                }
                catch (Exception e)
                {
                    if (!e.Message.ToLower().Contains("transport-level error has occurred"))
                    {
                        throw;
                    }
                }
            }
            throw new Exception("Failed to restore connection for command:" + command.CommandText);
        }

这些方法的常见部分是连接检索过程。您可以创建一个新的静态方法来负责调用的连接，例如，检索以命令为参数的连接，并在其他情况下建立连接时返回该连接。

代码示例中有一些东西我不喜欢。但是，要明确地回答您关于消除重复的问题，请将公共代码提取到接受委托的方法中。

private TReturn RestoreConnectionAndExecute<T>(SqlCommand command, Func<SqlCommand, TReturn> execute) 
{
   int retryCount = 0;
   while (retryCount++ < MaxRetryCount)
   {
       try
       {
          if (command.Connection.State == ConnectionState.Close) 
              command.Connection.Open();
          return execute(command);
       } 
       catch(Exception e)
       {
          ...
   }
}
public SqlDataReader RestoreConnectionAndExecuteReader(SqlCommand command) 
{
   return this.RestoreConnectionAndExecute(command, c => c.ExecuteReader());
}
public void RestoreConnectionAndExecuteNonQuery(SqlCommand command)
{
   // Ignore return
   this.RestoreConnectionAndExecute(command, c => c.ExecuteNonQuery());
}

不过，你真的应该重新思考一些事情。其中：

  • 捕获特定异常
  • 使用Exception.Number或ErrorCode代替消息（这将在本地化版本中发生变化，可能在更新的FX版本中也会发生变化）
  • 对IDisposable资源使用语句
  • 引发特定异常
相关文章：