Correct method of deleting over 2100 rows (by ID) with Dapper

To get this code working, I went dark side.

Since Dapper makes my list into parameters. And SQL Server can't handle a lot of parameters. (I have never needed even double digit parameters before). I had to go with Dynamic SQL.

So here was my solution:

string listOfIdsJoined = "("+String.Join(",", listOfIds.ToArray())+")";
connection.Execute("delete from myTable where Id in " + listOfIdsJoined);

Before everyone grabs the their torches and pitchforks, let me explain.

• This code runs on a server whose only input is a data feed from a Mainframe system.
• The list I am dynamically creating is a list of longs/bigints.
• The longs/bigints are from an Identity column.

I know constructing dynamic SQL is bad juju, but in this case, I just can't see how it leads to a security risk.

Dapper request the List of object having parameter as a property so in above case a list of object having Id as property will work.

connection.Execute("delete from myTable where Id in (@Id)", listOfIds.AsEnumerable().Select(i=> new { Id = i }).ToList());

This will work.

One option is to create a temp table on the server and then use the bulk load facility to upload all the IDs into that table at once. Then use a join, EXISTS or IN clause to delete only the records that you uploaded into your temp table.

Bulk loads are a well-optimized path in SQL Server and it should be very fast.

For example:

1. Execute the statement CREATE TABLE #RowsToDelete(ID INT PRIMARY KEY)
2. Use a bulk load to insert keys into #RowsToDelete
3. Execute DELETE FROM myTable where Id IN (SELECT ID FROM #RowsToDelete)
4. Execute DROP TABLE #RowsToDelte (the table will also be automatically dropped if you close the session)

(Assuming Dapper) code example:

conn.Open();

var columnName = "ID";

conn.Execute(string.Format("CREATE TABLE #{0}s({0} INT PRIMARY KEY)", columnName));

using (var bulkCopy = new SqlBulkCopy(conn))
{
    bulkCopy.BatchSize = ids.Count;
    bulkCopy.DestinationTableName = string.Format("#{0}s", columnName);

    var table = new DataTable();                    
    table.Columns.Add(columnName, typeof (int));
    bulkCopy.ColumnMappings.Add(columnName, columnName);

    foreach (var id in ids)
    {
        table.Rows.Add(id);
    }

    bulkCopy.WriteToServer(table);
}

//or do other things with your table instead of deleting here
conn.Execute(string.Format(@"DELETE FROM myTable where Id IN 
                                   (SELECT {0} FROM #{0}s", columnName));

conn.Execute(string.Format("DROP TABLE #{0}s", columnName));