3

I am using dapper.rainbow for inserting a record into MSSQL db. Following is my code

int? id  = db.RoomTypes.Insert(roomType)

When i run my app, i am getting the below exception.

Cannot insert explicit value for identity column in table 'RoomTypes' 
when IDENTITY_INSERT is set to OFF.

I think that dapper.rainbow is using the value (default value for int) for my Identity Column during the insert. This is what causing this exception. My identity column for the table RoomTypes is auto incremented and it is also the primary key of my table.

Now, how do i stop the dapper.rainbow from using the ID column during insert.

Greg Smith
  • 2,449
  • 2
  • 24
  • 37
krishnan
  • 782
  • 1
  • 9
  • 20

1 Answers1

3

Looking at the Dapper.Rainbow.Insert method, the method is "dumb" to the type passed in as the data parameter:

public virtual int? Insert(dynamic data)
{
    var o = (object)data;
    List<string> paramNames = GetParamNames(o);

    string cols = string.Join(",", paramNames);
    string cols_params = string.Join(",", paramNames.Select(p => "@" + p));
    var sql = "set nocount on insert " + TableName + " (" + cols + ") values (" + cols_params + ") select cast(scope_identity() as int)";

    return database.Query<int?>(sql, o).Single();
}

In other words, if you include the ID as a property in the parameter, then Dapper will try to insert that value into the database. And as you've seen, if you have IDENTITY_INSERT set to Off, then the insert will fail.

This means you'll need to create a new type that doesn't include your ID property to pass into the method. A few of ideas come to mind:

  1. Create a base class without the ID for RoomTypes and then just pass in the base class to the Insert method.
  2. Create a dynamic parameter and pass that to the Insert method with only the properties you want to insert into the database.
  3. Override the Insert method and remove the ID parameter before sending to the Dapper
Metro Smurf
  • 37,266
  • 20
  • 108
  • 140
  • thanks.. i used the dynamic parameter, by this way i can exclude created and modified fields too. – krishnan Dec 28 '12 at 12:43
  • I don't get this. Looking at the same source now, I see `paramNames.Remove("Id");`, and it says 'last change 5 Dec 2012' - when that line was added. (But the version I get from Nuget hasn't got that line...) – Benjol Oct 07 '13 at 06:24
  • Not sure what's going on. When I answered this question, the Google Code repository did not have `paramNames.Remove("Id");`. Very odd. – Metro Smurf Oct 07 '13 at 17:13
  • if you are on FW 4.5 this is possible dynamic objHitEntry = new ExpandoObject(); objHitEntry.CompanyId = DismantlerId; objHitEntry.NumHits = 1; objHitEntry.PostKod = PostCode; db.Hits.Insert(objHitEntry); – Vishnoo Rath Dec 07 '15 at 21:51