Dapper calls sp_executesql when I have parameters, is there a way around that?

dapper

Question

When I call

connection.Execute(sql);

Dapper executes and everything is fine. When I call

connection.Execute(sql, new { UserId = _userId });

it executes with sp_executesql.

The issue is when it uses sp_executesql it's in its own scope. If it creates a temporary table, it's not accessible to subsequent queries that use the same connection. I could get around it by using global temporary tables, but I don't want to risk having two processes interfere with each other.

Does anybody know a way around that?

Update: I have the same problem when I use SqlCommand objects without Dapper. I wrote a unit test that illustrates the problem I'm having. WorksWithParameters fails with System.Data.SqlClient.SqlException : Invalid object name '#TEMP_OBJECTLIST'.

[TestFixture]
public class DapperTest
{
    private const string TestObjectType = "S";
    private const string ConnectionString = "XXXXXXXXX";

    private static void CreateTempTableWithoutParameters(SqlConnection connection)
    {
        const string sql = "SELECT TOP 10 * INTO #TEMP_OBJECTLIST FROM sys.objects WHERE TYPE = 'S'";
        connection.Execute(sql);
    }

    private static void UseTempTableWithoutParameters(SqlConnection connection)
    {
        const int expectedCount = 10;

        const string sql = "SELECT COUNT(*) FROM #TEMP_OBJECTLIST WHERE TYPE = 'S'";
        var count = connection.Query<int>(sql).First();

        Assert.AreEqual(expectedCount, count);
    }

    private static void CreateTempTableWithParameters(SqlConnection connection)
    {
        const string sql = "SELECT TOP 10 * INTO #TEMP_OBJECTLIST FROM sys.objects WHERE TYPE = @OBJECT_TYPE";
        connection.Execute(sql, new {OBJECT_TYPE = TestObjectType});
    }

    private static void UseTempTableWithParameters(SqlConnection connection)
    {
        const int expectedCount = 10;

        const string sql = "SELECT COUNT(*) FROM #TEMP_OBJECTLIST WHERE TYPE = @OBJECT_TYPE";
        var param = new {OBJECT_TYPE = TestObjectType};

        var count = connection.Query<int>(sql, param).First();

        Assert.AreEqual(expectedCount, count);
    }

    [Test]
    public void WorksWithParameters()
    {
        using (var connection = new SqlConnection(ConnectionString))
        {
            connection.Open();

            CreateTempTableWithParameters(connection);
            UseTempTableWithParameters(connection);
        }
    }

    [Test]
    public void WorksWithoutParameters()
    {
        using (var connection = new SqlConnection(ConnectionString))
        {
            connection.Open();

            CreateTempTableWithoutParameters(connection);
            UseTempTableWithoutParameters(connection);
        }
    }
}

Popular Answer

One way around the temp table scope problem is to create the temp table with one dummy column in the outer scope, then use alter table statements to add all the desired columns and use it.

Additionally, How to share data between procedures by Erland Sommarskog may be useful to you or another person looking for different options for sharing data.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why