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

Question

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 lt int gt  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  lt QueryInternal gt 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

User · Answer

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 lt int gt  sql  new   Stuff   mystuff   Single

User · Answer

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 lt int gt  sql  new   Stuff   mystuff   Single

User · Answer

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

User · Answer

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

User · Answer

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 lt int gt  sql  new   Stuff   mystuff   Single

User · Answer

If you re using Dapper SimpleSave      no safety checks  public static int Create lt T gt  object param                using  SqlConnection conn   new SqlConnection GetConnectionString                           conn Open                conn Create lt T gt   T param               return  int     T param  GetType   GetProperties   Where                      x   gt  x CustomAttributes Where                          y  gt y AttributeType GetType      typeof Dapper SimpleSave PrimaryKeyAttribute  GetType    Count    1  First   GetValue param

User · Answer

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 lt int gt      INSERT INTO  MyTable    Stuff   VALUES   Stuff   SELECT CAST SCOPE IDENTITY   as int    new   Stuff   mystuff      Note that on more recent versions of SQL Server you can use the OUTPUT clause   var id   connection QuerySingle lt int gt      INSERT INTO  MyTable    Stuff   OUTPUT INSERTED Id VALUES   Stuff     new   Stuff   mystuff

User · Answer

I was using  net core 3 1 with postgres 12 3   Building on the answer from Tadija Bagaric I ended up with  using  var connection   new NpgsqlConnection AppConfig CommentFilesConnection                          string insertUserSql     quot INSERT INTO mytable comment id filename content                      VALUES   commentId   filename   content  returning id  quot                int newUserId   connection QuerySingle lt int gt                                               insertUserSql                                              new                                                                                               commentId   1                                                  filename    quot foobar  quot                                                   content    quot content quot                                                                                                                        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

User · Answer

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     quot INSERT INTO dbo  User  Username  Phone  Email                          OUTPUT INSERTED  Id                          VALUES  Username   Phone   Email   quot    int newUserId   conn QuerySingle lt int gt                                   insertUserSql                                  new                                                                       Username    quot lorem ipsum quot                                       Phone    quot 555-123 quot                                       Email    quot lorem ipsum quot                                                                     tran    Return inserted object with ID  If you wanted you could get Phone and Email or even the whole inserted row  string insertUserSql     quot INSERT INTO dbo  User  Username  Phone  Email                          OUTPUT INSERTED                           VALUES  Username   Phone   Email   quot    User newUser   conn QuerySingle lt User gt                                   insertUserSql                                  new                                                                       Username    quot lorem ipsum quot                                       Phone    quot 555-123 quot                                       Email    quot lorem ipsum quot                                                                     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

[c#] How do I perform an insert and return inserted identity with Dapper?

Examples related to c#

Examples related to sql-server

Examples related to dapper