.NET MVC 3.1使用Snowflake .NET连接器遇会话过期，求替代方案

使用Snowflake .NET连接器（Snowflake.Data）在.NET MVC 3.1应用中拉取Snowflake数据时，当应用超过4小时未发起调用，会触发报错：

"Session no longer exists. New login required to access the service. SqlState: , VendorCode: 390111"

已知设置CLIENT_SESSION_KEEP_ALIVE=True可解决会话超时问题，但该配置暂不被.NET连接器支持，求可行替代方案。

当前代码如下：

public async Task<List<PO>> FetchPO(string plant)
{
    using IDbConnection dbConnection = new SnowflakeDbConnection();
            dbConnection.ConnectionString = _configuration.GetConnectionString(Constants.ConnectionStrings.SnowflakeConnectionString);

    List<PO> po= (await dbConnection.QueryAsync<PO>($"select * from {Constants.Snowflake.Views.PO} ")).ToList();

    return po;
}

1. 捕获会话过期异常并重试 #

针对VendorCode: 390111的会话过期错误，添加异常捕获逻辑，遇到该错误时重新创建连接执行查询。这种方式适配当前每次新建连接的代码结构，无需复杂改造。

修改后的代码示例：

public async Task<List<PO>> FetchPO(string plant)
{
    int retryCount = 2; // 设置重试次数
    for (int i = 0; i < retryCount; i++)
    {
        try
        {
            using IDbConnection dbConnection = new SnowflakeDbConnection();
            dbConnection.ConnectionString = _configuration.GetConnectionString(Constants.ConnectionStrings.SnowflakeConnectionString);

            return (await dbConnection.QueryAsync<PO>($"select * from {Constants.Snowflake.Views.PO} ")).ToList();
        }
        catch (SnowflakeDbException ex)
        {
            // 判断是否为会话过期错误
            if (ex.VendorCode == 390111 && i < retryCount - 1)
            {
                // 可选：短暂延迟后重试
                await Task.Delay(1000);
                continue;
            }
            throw; // 非目标错误或重试耗尽，抛出原异常
        }
    }
    return new List<PO>(); // 兜底返回
}

2. 维护连接池并定期发送心跳请求 #

如果希望保持长会话避免频繁重连，可以实现一个简单的连接池，定时执行轻量查询（如SELECT 1）来维持会话活跃。注意要处理线程安全问题，确保连接状态有效。

核心代码示例：

public class SnowflakeConnectionManager
{
    private readonly IConfiguration _configuration;
    private SnowflakeDbConnection _connection;
    private readonly Timer _heartbeatTimer;
    private readonly object _lockObj = new object();

    public SnowflakeConnectionManager(IConfiguration configuration)
    {
        _configuration = configuration;
        // 每3.5小时执行一次心跳，提前于4小时超时时间
        _heartbeatTimer = new Timer(ExecuteHeartbeat, null, TimeSpan.Zero, TimeSpan.FromHours(3.5));
    }

    public async Task<IDbConnection> GetValidConnection()
    {
        lock (_lockObj)
        {
            if (_connection == null || _connection.State != ConnectionState.Open)
            {
                _connection?.Dispose();
                _connection = new SnowflakeDbConnection();
                _connection.ConnectionString = _configuration.GetConnectionString(Constants.ConnectionStrings.SnowflakeConnectionString);
                _connection.Open();
            }
            return _connection;
        }
    }

    private async void ExecuteHeartbeat(object state)
    {
        try
        {
            var conn = await GetValidConnection();
            using var cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT 1";
            await cmd.ExecuteScalarAsync();
        }
        catch (Exception)
        {
            // 心跳失败，标记连接无效，下次获取时重建
            lock (_lockObj)
            {
                _connection?.Dispose();
                _connection = null;
            }
        }
    }
}

使用时在依赖注入中注册为单例，再在业务方法中调用：

private readonly SnowflakeConnectionManager _connManager;

// 构造函数注入连接管理器
public YourService(SnowflakeConnectionManager connManager)
{
    _connManager = connManager;
}

public async Task<List<PO>> FetchPO(string plant)
{
    using var dbConnection = await _connManager.GetValidConnection();
    return (await dbConnection.QueryAsync<PO>($"select * from {Constants.Snowflake.Views.PO} ")).ToList();
}

3. 调整Snowflake会话超时参数 #

联系Snowflake管理员，修改账户级别或用户级别的SESSION_TIMEOUT参数，延长会话的默认有效期（默认4小时）。该参数单位为分钟，例如设置为300分钟（5小时）：

-- 修改账户级别会话超时
ALTER ACCOUNT SET SESSION_TIMEOUT = 300;

-- 或修改指定用户的会话超时
ALTER USER YOUR_USER_NAME SET SESSION_TIMEOUT = 300;

注意：此操作需要对应的管理员权限，且会影响该账户/用户下的所有会话。

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