ppiotrowicz ppiotrowicz - 3 months ago 150
C# Question

How do I perform an insert and return inserted identity with Dapper?

How do I perform an insert to database and return inserted identity with Dapper?

I've tried something like this:

string sql = "DECLARE @ID int; " +
"INSERT INTO [MyTable] ([Stuff]) VALUES (@Stuff); " +
"SELECT @ID = SCOPE_IDENTITY()";

var id = connection.Query<int>(sql, new { Stuff = mystuff}).First();


But it did't work.

@Marc Gravell thanks, for reply.
I've tried your solution but, still same exception trace is below

System.InvalidCastException: Specified cast is not valid

at Dapper.SqlMapper.<QueryInternal>d__a`1.MoveNext() in (snip)\Dapper\SqlMapper.cs:line 610
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType) in (snip)\Dapper\SqlMapper.cs:line 538
at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param) in (snip)\Dapper\SqlMapper.cs:line 456

Answer

It does support input/output parameters (including RETURN value) if you use DynamicParameters, but in this case the simpler option is simply:

string sql = @"
INSERT INTO [MyTable] ([Stuff]) VALUES (@Stuff);
SELECT CAST(SCOPE_IDENTITY() as int)";

var id = connection.Query<int>(sql, new { Stuff = mystuff}).Single();