C# Windows服务中SQL Server连接超时设置不生效问题求助

问题根源 #

你设置的Connect Timeout仅作用于SQL Server登录验证阶段的超时，但如果目标机器不在网络中，TCP层的连接超时会优先触发——Windows默认TCP超时约2分钟，这时候Connect Timeout根本没机会生效。

不用async/await的解决方案 #

方案1：提前测试TCP端口连通性 #

先手动建立TCP连接到SQL Server的默认端口（1433，若修改过端口则用对应值），设置TCP连接的超时时间，确认连通后再打开SQL连接。

代码示例：

public struct E3Data
{
    public DateTime dt;
    public float value;
}

public E3Data GetSQLData()
{
    E3Data e3 = new E3Data();
    string queryString = "SELECT TOP 1 DATEADD(HOUR,DATEDIFF(HOUR,CAST(E3TimeStamp AS DATE),E3TimeStamp),CAST(CAST(E3TimeStamp AS DATE) AS DATETIME)) As DateFormatted, [Nível Montante TDA (m)] AS Nivel FROM " + strtable + "_SASE ORDER BY E3TimeStamp DESC";
    string connectionString = "Server=192.168.80.4;Database=COR;User Id=user;Password=pass;Connect Timeout=5";
    string sqlServerIp = "192.168.80.4";
    int sqlServerPort = 1433; // 默认端口，按需修改
    int tcpTimeoutMs = 5000; // 5秒超时

    // 先验证TCP连通性
    using (var tcpClient = new TcpClient())
    {
        var connectTask = tcpClient.ConnectAsync(sqlServerIp, sqlServerPort);
        if (!connectTask.Wait(tcpTimeoutMs))
        {
            log("TCP连接超时，目标机器不可达");
            return e3;
        }
    }

    using (SqlConnection sqlconn = new SqlConnection(connectionString))
    {
        SqlCommand sqlcmd = new SqlCommand(queryString, sqlconn);
        
        try
        {
            sqlconn.Open(); // TCP已连通，此时Connect Timeout会生效
            using (SqlDataReader reader = sqlcmd.ExecuteReader())
            {
                if (reader.Read())
                {
                    e3.value = Convert.ToSingle(reader["Nivel"]); // 对应SQL中的别名Nivel
                    e3.dt = Convert.ToDateTime(reader["DateFormatted"]); // 对应SQL中的别名DateFormatted
                }
            }
        }
        catch (Exception ex)
        {
            log($"DB操作错误：{ex.Message}");
        }
        return e3;
    }
}

方案2：用Task.Wait包装SqlConnection.Open() #

把同步的Open操作包装成Task，设置Wait超时，无需修改方法为async：

public struct E3Data
{
    public DateTime dt;
    public float value;
}

public E3Data GetSQLData()
{
    E3Data e3 = new E3Data();
    string queryString = "SELECT TOP 1 DATEADD(HOUR,DATEDIFF(HOUR,CAST(E3TimeStamp AS DATE),E3TimeStamp),CAST(CAST(E3TimeStamp AS DATE) AS DATETIME)) As DateFormatted, [Nível Montante TDA (m)] AS Nivel FROM " + strtable + "_SASE ORDER BY E3TimeStamp DESC";
    string connectionString = "Server=192.168.80.4;Database=COR;User Id=user;Password=pass;Connect Timeout=5";
    int totalTimeoutMs = 5000; // 5秒总超时

    using (SqlConnection sqlconn = new SqlConnection(connectionString))
    {
        SqlCommand sqlcmd = new SqlCommand(queryString, sqlconn);
        
        try
        {
            // 包装Open操作并设置超时
            var openTask = sqlconn.OpenAsync();
            if (!openTask.Wait(totalTimeoutMs))
            {
                log("连接超时，目标机器不可达");
                return e3;
            }

            using (SqlDataReader reader = sqlcmd.ExecuteReader())
            {
                if (reader.Read())
                {
                    e3.value = Convert.ToSingle(reader["Nivel"]);
                    e3.dt = Convert.ToDateTime(reader["DateFormatted"]);
                }
            }
        }
        catch (Exception ex)
        {
            log($"DB操作错误：{ex.Message}");
        }
        return e3;
    }
}

注意事项 #

• 方案1的TCP测试可能被防火墙拦截（比如目标机器禁用了1433端口入站），这种情况下方案2更可靠。
• 原代码存在几处小问题：reader.read()需改为reader.Read()，字段名要与SQL别名严格对应，语句末尾需加分号，SqlDataReader建议用using自动释放资源。

内容的提问来源于stack exchange，提问作者FelipeFonsecabh