Creating temporary table in Dapper ..net c#

Question

I'm trying to create a #temporary table using Dapper without much success. I'm not receiving any SQL exceptions or errors, the connection is opened but the endresult is "-1" and no temporary table is created. Anyone know where I am going wrong here?

Thanks

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); // return -1
        
        
        // When attempting to insert here error is thrown: - Invalid object name '#IngredientSubstitutionUpload'.
        
        // Insert into temporary table 
        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();
    }
}

Answer 1

You need to explicitly open the connection before calling the ExecuteAsyc statements. Otherwise Dapper will auto-close the connection after each ExecuteAsync giving each call a separate session (which the temp table will not survive).

Sample taken from https://www.learndapper.com/misc/temporary-table:

using (var connection = new SqlConnection(connectionString))
{
    await conn.Open();
    
    connection.Execute(@"CREATE TABLE #tmpOrder(orderId int);");
    connection.Execute(@"INSERT INTO #tmpOrder(orderId) VALUES (1);");
    
    return connection.Query<int>(@"SELECT * FROM #tmpOrder;");
}