How with Dapper to insert in a temproary table?

.net c# dapper mysql sql

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?

1
1
8/10/2018 4:52:12 PM

Accepted Answer

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.

1
8/10/2018 9:23:57 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow