How do I perform an insert and return inserted identity with Dapper?
C#Sql ServerDapperC# Problem Overview
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
C# Solutions
Solution 1 - C#
It does support input/output parameters (including RETURN
value) if you use DynamicParameters
, but in this case the simpler option is simply:
var id = connection.QuerySingle<int>( @"
INSERT INTO [MyTable] ([Stuff]) VALUES (@Stuff);
SELECT CAST(SCOPE_IDENTITY() as int)", new { Stuff = mystuff});
Note that on more recent versions of SQL Server (2005+) you can use the OUTPUT
clause:
var id = connection.QuerySingle<int>( @"
INSERT INTO [MyTable] ([Stuff])
OUTPUT INSERTED.Id
VALUES (@Stuff);", new { Stuff = mystuff});
Solution 2 - C#
KB:2019779,"You may receive incorrect values when using SCOPE_IDENTITY() and @@IDENTITY", The OUTPUT clause is the safest mechanism:
string sql = @"
DECLARE @InsertedRows AS TABLE (Id int);
INSERT INTO [MyTable] ([Stuff]) OUTPUT Inserted.Id INTO @InsertedRows
VALUES (@Stuff);
SELECT Id FROM @InsertedRows";
var id = connection.Query<int>(sql, new { Stuff = mystuff}).Single();
Solution 3 - C#
A late answer, but here is an alternative to the SCOPE_IDENTITY()
answers that we ended up using: OUTPUT INSERTED
Return only ID of inserted object:
It allows you to get all or some attributes of the inserted row:
string insertUserSql = @"INSERT INTO dbo.[User](Username, Phone, Email)
OUTPUT INSERTED.[Id]
VALUES(@Username, @Phone, @Email);";
int newUserId = conn.QuerySingle<int>(
insertUserSql,
new
{
Username = "lorem ipsum",
Phone = "555-123",
Email = "lorem ipsum"
},
tran);
Return inserted object with ID:
If you wanted you could get Phone
and Email
or even the whole inserted row:
string insertUserSql = @"INSERT INTO dbo.[User](Username, Phone, Email)
OUTPUT INSERTED.*
VALUES(@Username, @Phone, @Email);";
User newUser = conn.QuerySingle<User>(
insertUserSql,
new
{
Username = "lorem ipsum",
Phone = "555-123",
Email = "lorem ipsum"
},
tran);
Also, with this you can return data of deleted or updated rows. Just be careful if you are using triggers because (from link mentioned before):
> Columns returned from OUTPUT reflect the data as it is after the > INSERT, UPDATE, or DELETE statement has completed but before triggers > are executed. > > For INSTEAD OF triggers, the returned results are generated as if the > INSERT, UPDATE, or DELETE had actually occurred, even if no > modifications take place as the result of the trigger operation. If a > statement that includes an OUTPUT clause is used inside the body of a > trigger, table aliases must be used to reference the trigger inserted > and deleted tables to avoid duplicating column references with the > INSERTED and DELETED tables associated with OUTPUT.
More on it in the docs: link
Solution 4 - C#
The InvalidCastException you are getting is due to SCOPE_IDENTITY being a Decimal(38,0).
You can return it as an int by casting it as follows:
string sql = @"
INSERT INTO [MyTable] ([Stuff]) VALUES (@Stuff);
SELECT CAST(SCOPE_IDENTITY() AS INT)";
int id = connection.Query<int>(sql, new { Stuff = mystuff}).Single();
Solution 5 - C#
Not sure if it was because I'm working against SQL 2000 or not but I had to do this to get it to work.
string sql = "DECLARE @ID int; " +
"INSERT INTO [MyTable] ([Stuff]) VALUES (@Stuff); " +
"SET @ID = SCOPE_IDENTITY(); " +
"SELECT @ID";
var id = connection.Query<int>(sql, new { Stuff = mystuff}).Single();
Solution 6 - C#
There is a great library to make your life easier Dapper.Contrib.Extensions. After including this you can just write:
public int Add(Transaction transaction)
{
using (IDbConnection db = Connection)
{
return (int)db.Insert(transaction);
}
}
Solution 7 - C#
I see answer for sql server, well here it is for MySql using a transaction
Dim sql As String = "INSERT INTO Empleado (nombres, apepaterno, apematerno, direccion, colonia, cp, municipio, estado, tel, cel, correo, idrol, relojchecadorid, relojchecadorid2, activo,extras,rfc,nss,curp,imagen,sueldoXHra, IMSSCotiza, thumb) VALUES (@nombres, @apepaterno, @apematerno, @direccion, @colonia, @cp, @municipio, @estado, @tel, @cel, @correo, @idrol, @relojchecadorid, @relojchecadorid2, @activo, @extras, @rfc, @nss, @curp, @imagen,@sueldoXHra,@IMSSCotiza, @thumb)"
Using connection As IDbConnection = New MySqlConnection(getConnectionString())
connection.Open()
Using transaction = connection.BeginTransaction
Dim res = connection.Execute(sql, New With {reg.nombres, reg.apepaterno, reg.apematerno, reg.direccion, reg.colonia, reg.cp, reg.municipio, reg.estado, reg.tel, reg.cel, reg.correo, reg.idrol, reg.relojchecadorid, reg.relojchecadorid2, reg.activo, reg.extras, reg.rfc, reg.nss, reg.curp, reg.imagen, reg.thumb, reg.sueldoXHra, reg.IMSSCotiza}, commandTimeout:=180, transaction:=transaction)
lastInsertedId = connection.ExecuteScalar("SELECT LAST_INSERT_ID();", transaction:=transaction)
If res > 0 Then
transaction.Commit()
return true
end if
End Using
End Using
Solution 8 - C#
If you're using Dapper.SimpleSave:
//no safety checks
public static int Create<T>(object param)
{
using (SqlConnection conn = new SqlConnection(GetConnectionString()))
{
conn.Open();
conn.Create<T>((T)param);
return (int) (((T)param).GetType().GetProperties().Where(
x => x.CustomAttributes.Where(
y=>y.AttributeType.GetType() == typeof(Dapper.SimpleSave.PrimaryKeyAttribute).GetType()).Count()==1).First().GetValue(param));
}
}
Solution 9 - C#
I was using .net core 3.1 with postgres 12.3. Building on the answer from Tadija Bagarić I ended up with:
using (var connection = new NpgsqlConnection(AppConfig.CommentFilesConnection))
{
string insertUserSql = @"INSERT INTO mytable(comment_id,filename,content)
VALUES( @commentId, @filename, @content) returning id;";
int newUserId = connection.QuerySingle<int>(
insertUserSql,
new
{
commentId = 1,
filename = "foobar!",
content = "content"
}
);
}
where AppConfig is my own class which simply gets a string set for my connection details. This is set within the Startup.cs ConfigureServices method.