Inserting a list into temp table using Dapper

Question

I am trying to insert a List of integers into a temporary table using Dapper. I wrote my query based on the selected answer to this Stack Overflow question. However, I get a syntax error when running the query.

Code:

List<int> lst = new List<int> {1, 2, 3, 4};
string queryString="CREATE TABLE #Temp (ListInt int)" 
                + " INSERT INTO #Temp (ListInt) VALUES (@Lst);";
dbConnection.Open();

dbConnection.Query(queryString, new {Lst = lst});

Error:

System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near ','.

How can I use Dapper to insert this list?

What is 'lst' in your code? Provide a minimal and complete example — Jeroen Heier, Aug 10 '18 at 15:13

score 3 · Accepted Answer · answered Aug 10 '18 at 21:23

You can accomplish this via Dapper's Execute a Command multiple times feature.

The SQL query must contain only the command you want repeated, and the parameters (passed to Dapper) must be an IEnumerable of objects where each object has a property named after the parameter you're using. Finally, use Execute (not Query) when you're inserting (not retrieving) values:

List<int> lst = new List<int> {1, 2, 3, 4};
dbConnection.Open();
dbConnection.Execute("CREATE TABLE #Temp (ListInt int)");
dbConnection.Execute("INSERT INTO #Temp (ListInt) VALUES (@item);",
    lst.Select(x => new { item = x }));

Note that if you're inserting thousands of items, the INSERT statement will be executed once per item, which can be very slow. If this is the case, investigate a community extension to Dapper, such as BulkInsert that can insert 1000s of items in one INSERT statement.

Inserting a list into temp table using Dapper

1 Answers1