c# - Solution for INSERT or UPDATE and fetching newly created id on SQL server -

is possible perform insert or update following constraints :

1. dapper used in project
2. the primary key auto-incrementing positive
3. the newly inserted data may not have pk value (or has value of 0)
4. the data needs have pk value on newly inserted row.
5. the query being generated procedurally, , generalizing preferable

something :

int newid = db.queryvalue<int>( <<insert or update>>, somedata ); 

i have read different solutions, , best solution seems this one :

merge tablename target using (values ('new value', 'different value'))     source (field1, field2)     on target.idfield = 7 when matched     update     set field1 = source.field1,         field2 = source.field2,         ... when not matched     insert ( idfield, field1, field2, ... )     values ( 7,  source.field1, source.field2, ... ) 

but

1. it seems fail on third constraint ,
2. it not guarantee return newly generated id.

because of 5th constraint (or preferance), stored procedure seems overly complicated.

what possible solutions? thanks!

if table has auto-increment field, can't assign value field when inserting record. ok can, it's bad idea :)

using t-sql merge statement can put of values source table, including default invalid identity value, write insert clause as:

when not matched     insert (field1, field2, ...)     values (source.field1, source.field2, ...) 

: , use output clause inserted identity value:

output inserted.idfield 

---

that said, think might complicating sql code generation little, tables lot of fields. better generate distinct update , insert queries... if you've got way of tracking changes object can update changed fields.

assuming you're working on ms sql, can use scope_identity() function after insert statement value of identity field record in composite statement:

insert tablename(field1, field2, ...) values('field1value', 'field2value', ...); select cast(scope_identity() int) ident; 

when execute sql statement you'll resultset inserted identity in single column. db.queryvalue<int> call return value you're after.

for standard integer auto-increment fields above fine. other field types, or more general case, try casting scope_identity() result varchar(max) , parse resultant string value whichever type identity column expects - guid, etc.

in general case, try in db class:

public string insertwithid(string insertquery, params object[] parms) {     string query = insertquery + "\nselect cast(scope_identity() varchar(max)) ident;\n";     return this.queryvalue<string>(insertquery, parms); } 

and/or:

public int insertwithintid(string insertquery, params object[] parms) {     string query = insertquery + "\nselect cast(scope_identity() int) ident;\n";     return this.queryvalue<int>(query, parms); } 

that way can prepare insert query , call appropriate insertwithid method resultant identity value. should satisfy 5th constraint luck :)