dapper.net multiple delete issue

Question

I'm trying to delete from two tables with two different vairables within a c# class, but I get the following error message:

When using the multi-mapping APIs ensure you set the splitOn param if you have keys other than Id Parameter name: splitOn

The sql statement executes fine when capturing command via SQL Profiler, so I'm stumped.

The dapper code is:

 public void DeleteListCode(string listCodeId)
    {
       using (var block = new TransactionBlock())
       {
           // Get the code first
           const string sql = "SELECT ListCode from ListCodes WHERE id =@listCodeId";
           var code = TransactionBlock.Connection.Query<string>(sql, new {listCodeId}, TransactionBlock.Transaction)
              .FirstOrDefault();

           if (string.IsNullOrEmpty(code)) return;

           const string sql2 = "delete from Lists WHERE ListCode = @code " +
                               "delete from ListCodes where Id = @listCodeId";

            TransactionBlock.Connection.Query(sql2, new {listCodeId, code}, TransactionBlock.Transaction);
           block.Commit();
       }
    }

I've successfully managed to use a multi select statement, but this is slightly different in the sense that I use two annonomous parameters.

As you aren't returning anything on the delete I think you should be using Execute rather than Query. — petelids
after the first Select where you are using sql variable once you have executed that query have you thought about executing a RollBack command since on the select you're only returning data and not changing data..? then execute the second statement just as you are doing..? or split the sql2 string into 2 different transactions.. also I think that petelids has a good point try doing something like this TransactionBlock.Connection.Execute instead — MethodMan

Marc Gravell Marc Gravell · Accepted Answer · 2014-08-13T17:10:09

The second operation should use Execute, not Query. It isn't a query, basically. That should be all you need.

dapper.net multiple delete issue

1 Answers