CODEWITHSUNDEEP
c#sql-servert-sqldapper
PajLe
PajLe

Reputation: 924

Insert large array into a SQL Server table variable with Dapper

I have an integer list and I'm trying to insert those values into a table variable, declared with DECLARE statement, using Dapper. I've tried several combinations, but ultimately it leads to Incorrect syntax near ',' error.

Can Dapper even differentiate between a local variable and a Dapper query param, both being with @ prefix?

Fiddle

List<int> output = null;
        
List<int> input = new List<int> 
{
    1, 2, 3
};

var sql = @"DECLARE @tempTable TABLE (Id INT)
            INSERT INTO @tempTable VALUES (@Ids); 
                    
            SELECT * FROM @tempTable;";

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()))
{
    output = connection.Query<int>(sql, new { Ids = input }).ToList();
}

Note that the input list can be bigger than 1000.

Upvotes: 1

Views: 2235

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89091

Dapper will transform the list into seperate parameters, suitable for inclusion in an IN predicate, but not an INSERT ... VALUES query. Instead pass the values as JSON, which is also much, much cheaper for large lists than using separate parameters. EG

List<int> output = null;

List<int> input = new List<int>
    {
        1, 2, 3
    };

var sql = @"
DECLARE @tempTable TABLE (Id INT)
INSERT INTO @tempTable(id) select value from openjson( @Ids ); 
        
SELECT * FROM @tempTable;";

var inputJson = System.Text.Json.JsonSerializer.Serialize(input);

using (var con = new SqlConnection("server=localhost;database=tempdb;integrated security=true;trust server certificate=true")) 
{
    output = con.Query<int>(sql, new { Ids = inputJson }).ToList();
}

Upvotes: 5

Related Questions