Dapper .NET C#创建临时表失败：返回-1且插入报错

问题描述 #

在.NET C#中使用Dapper创建本地临时表时遇到异常：执行CREATE TABLE语句无报错、数据库连接状态显示已打开，但ExecuteAsync返回-1，临时表未实际创建；后续向该表插入数据时抛出**“无效的对象名称 '#IngredientSubstitutionUpload'”**错误。

原始代码 #

public abstract class DapperBaseRepository : IDisposable
{
    private readonly IDbConnection _dbConnection;

    protected DapperBaseRepository()
    {
        _dbConnection = new SqlConnection(configuration.GetConnectionString("DefaultConnection"));
    }

    public async Task<int> CreateTempTable()
    {
        string mappingTable = "[dbo].[#IngredientSubstitutionUpload]";
        
        var query = @$"
            CREATE TABLE {mappingTable}(
                Row int NOT NULL,
                OriginalIngredient nvarchar(255) NOT NULL,
                OriginalSupplierCode nvarchar(255) NOT NULL,
                ReplacementIngredient nvarchar(255) NOT NULL,
                ReplacementSupplierCode nvarchar(255) NOT NULL)
        ";
    
        await _dbConnection.ExecuteAsync(query); // 返回-1
        
        // 插入数据时抛出错误：无效的对象名称 '#IngredientSubstitutionUpload'
        await _dbConnection.ExecuteAsync(
            $"INSERT INTO {mappingTable} VALUES (@row, @originalIngredient, @originalSupplierCode, @replacementIngredient, @replacementSupplierCode)",
            substitutions.Select((x, idx) => new
            {
                row = idx,
                originalIngredient = x.OriginalIngredient,
                originalSupplierCode = x.OriginalSupplierCode,
                replacementIngredient = x.ReplacementIngredient,
                replacementSupplierCode = x.ReplacementSupplierCode
            }));

    }
    
    public void Dispose()
    {
        _dbConnection.Dispose();
    }
}

问题原因分析 #

1. 临时表架构前缀错误：本地临时表（#开头）不属于任何用户架构，无需指定[dbo]前缀，错误的架构声明会导致SQL Server无法正确识别临时表。
2. 连接未保持打开：虽然SqlConnection在执行命令时会自动打开，但命令执行后可能被连接池自动回收关闭。本地临时表的生命周期与创建它的连接绑定，连接关闭后临时表会被SQL Server自动销毁，后续插入操作使用新连接时无法访问该表。
3. 异步操作的连接状态不稳定：两次ExecuteAsync调用可能触发连接的打开/关闭循环，导致第二次操作使用的连接与创建临时表的连接不是同一个会话，无法访问会话级的临时表。

解决方案 #

1. 修正临时表命名 #

移除不必要的架构前缀，将[dbo].[#IngredientSubstitutionUpload]改为#IngredientSubstitutionUpload。

2. 显式管理连接生命周期 #

在执行所有操作前显式打开连接，操作完成后再关闭，确保整个流程使用同一个数据库会话。

修正后的代码 #

public abstract class DapperBaseRepository : IDisposable
{
    private readonly IDbConnection _dbConnection;
    private readonly IConfiguration _configuration;

    protected DapperBaseRepository(IConfiguration configuration)
    {
        _configuration = configuration;
        _dbConnection = new SqlConnection(_configuration.GetConnectionString("DefaultConnection"));
    }

    public async Task<int> CreateTempTableAndInsert(IEnumerable<SubstitutionModel> substitutions)
    {
        string mappingTable = "#IngredientSubstitutionUpload";
        
        var createQuery = $@"
            CREATE TABLE {mappingTable}(
                Row int NOT NULL,
                OriginalIngredient nvarchar(255) NOT NULL,
                OriginalSupplierCode nvarchar(255) NOT NULL,
                ReplacementIngredient nvarchar(255) NOT NULL,
                ReplacementSupplierCode nvarchar(255) NOT NULL)
        ";

        // 显式打开连接，确保会话持续
        await _dbConnection.OpenAsync();
        
        try
        {
            // 创建临时表（CREATE TABLE返回-1是SQL Server正常行为，不代表执行失败）
            await _dbConnection.ExecuteAsync(createQuery);
            
            // 同一连接下执行插入操作
            var affectedRows = await _dbConnection.ExecuteAsync(
                $"INSERT INTO {mappingTable} VALUES (@row, @originalIngredient, @originalSupplierCode, @replacementIngredient, @replacementSupplierCode)",
                substitutions.Select((x, idx) => new
                {
                    row = idx,
                    originalIngredient = x.OriginalIngredient,
                    originalSupplierCode = x.OriginalSupplierCode,
                    replacementIngredient = x.ReplacementIngredient,
                    replacementSupplierCode = x.ReplacementSupplierCode
                }));
                
            return affectedRows;
        }
        finally
        {
            // 确保连接关闭，避免资源泄漏
            await _dbConnection.CloseAsync();
        }
    }
    
    public void Dispose()
    {
        _dbConnection.Dispose();
    }
}

额外注意事项 #

• 本地临时表（#table）仅在当前数据库会话可见，跨连接无法访问；若需跨连接共享，可使用全局临时表（##table），但需注意并发冲突问题。
• 可通过查询tempdb.sys.tables验证临时表是否创建成功，例如执行SELECT * FROM tempdb.sys.tables WHERE name LIKE '%IngredientSubstitutionUpload%'。
• 异步操作中需严格管理连接状态，避免因连接提前释放导致临时表丢失。

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