AutoIncrement Int PK doesn't Increment through DapperLite?

[RefractedPaladin]

Any ideas? I have the table set right and if I use straight SQL it works correctly but once I use DapperLite, like below, it only gives Id a value of 0. this.CurrentPunch = new Punches(){ PunchTime = DateTime.Now, Method = "PIN"}; db.Insert(CurrentPunch);

[RefractedPaladin]

In SqlMapper.cs I tried adding the following to the AddParams() as a HACK way of getting around this but to no avail as I simply get an exception stating "Insufficient number of parameters supplied" so I'm guessing I need to find where I'm getting the values or columns. Also, now that I'm typing all this out it occurs to me that if I did this it would break 'Update' as it uses that method as well?

private static void AddParams(IDbCommand cmd, object data) { if (cmd == null || data == null) return;

IEnumerable<string> propertyNames = GetParamNames(data); foreach (string propertyName in propertyNames) { // I added this if() if (propertyName == "Id") continue; .......

[RefractedPaladin]

Well, I'm a little ashamed to admit this but I got it 'working' doing the following.
In SqlMapperInsertUpdate.cs, the Insert() I added the following two lines right before the Execute()

sql = sql.Replace("@Id,", string.Empty); sql = sql.Replace("Id,", string.Empty);

[ryankirkman]

@RefractedPaladin One other solution is having a Punches class you use for inserts that doesn't have an Id property. That way they query builder won't pick up the field and try to insert it.

[ryankirkman]

The other thing you could try is making Id nullable in the model: http://stackoverflow.com/questions/11771166/how-do-i-get-dapper-rainbow-to-insert-to-a-table-with-autoincrement-on-sqlite

This should be a much better solution for you.

[RefractedPaladin]

Ah, thanks. Tried the Id nullable thing and it seems to be working.

[RefractedPaladin]

Follow up to this, or maybe it's really seperate, but is there a way to get the auto ID back from the Insert? Otherwise, I seem to be stuck writing code like so: db.Insert(newEmp); newEmp.Id = db.Get<Employee>("PIN", newEmp.PIN).Id;

Where PIN is like a FK that is manually assigned though not guaranteed to be unique.

[ryankirkman]

Great question! It should be possible. We'll see what we can do

On Tuesday, February 18, 2014, RefractedPaladin notifications@github.com wrote:

Follow up to this, or maybe it's really seperate, but is there a way to get the auto ID back from the Insert? Otherwise, I seem to be stuck writing code like so: db.Insert(newEmp); newEmp.Id = db.Get("PIN", newEmp.PIN).Id;

Where PIN is like a FK that is manually assigned though not guaranteed to be unique.

Reply to this email directly or view it on GitHubhttps://github.com/ryankirkman/DapperLite/issues/4#issuecomment-35460937 .

[RefractedPaladin]

Clearly this won't work for you but I took a look at Dapper.Rainbow and what they were doing which was basically a SELECT scope_identity() so I tried to model after that. I didn't have a ton of time and what I have works but is specific to having "Id" as the PK. What can I say but "Works For Me!" :)

I'd be interested in writing/seeing a better, more generic solution though. Mines below just in case you were curious.

var id = connection.Query<Int64?>("SELECT MAX(Id) AS Id FROM " + tableName); return id.FirstOrDefault();

and then of course changing the signature of both Insert() from void to Int64? though I suppose I could also use long?

[ryankirkman]

Looks like this could help you out: http://stackoverflow.com/questions/1822135/how-can-i-access-the-last-inserted-row-id-within-a-sql-script

SELECT last_insert_rowid();