How to Perform an Insert and Return Inserted Identity with Dapper

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

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});

Dapper for NET Core: Insert into a table and return id of inserted row

You can run a query which has 2 parts, first is your INSERT part and second is a SELECT part. In the SELECT part, you can return(select) whatever column value you want.

For example, If your group table has a primary key column called GroupId and you have set that column for Identity value generation(automatic value generation), you can call the SCOPE_IDENTITY() to get the generated value.

We will use the QueryAsync method.

public async Task<int> AddNewGroup()
{
   using(_connection)
   {
      _connection.Open();
      var q = @"INSERT INTO Groups(Name,Description) VALUES
                 (@name, @desc); SELECT CAST(SCOPE_IDENTITY() as int)"
      var result = await _connection.QueryAsync<int>(q, 
                                          new { @name="some name", @desc="some desc"});
      return result.Single();
   }
}

Return id of record when insert or update or delete record

Well, in the case of UPDATE or DELETE, you already must have the id, otherwise you couldn't be calling any of these methods - correct?

In the case of an INSERT, if your table is in SQL Server and has an INT IDENTITY column, you can use something like this:

INSERT INTO dbo.YourTable (list-of-columns)
OUTPUT Inserted.Id   -- or whatever your IDENTITY column is called
VALUES (list-of-values)

and then from your C# code, use cmd.ExecuteScalar() (or ExecuteScalarAsync, if your prefer) to run this and get back a single, atomic value (the newly created id value):

var result = cmd.ExecuteScalar(....);

if (result != null)
{
    int newId = Convert.ToInt32(result);
}

How do I get back the key for a newly inserted record using Dapper.Contrib?

UPDATE: 02-May-2022

From source code, it appears that this feature is now implemented. I have not tested it though.

It appears that Insert<T> method will return (it will NOT map to entity property) newly generated numeric ID (return type of method is long) if only single entity is being inserted. If list of entities are being inserted, it will return number of rows inserted.

I am not sure how will this work with GUID IDs.

Following is the code-comment:

/// Inserts an entity into table "Ts" and returns identity id or number of inserted rows if inserting a list.  
...  
...  
/// <returns>Identity of inserted entity, or number of inserted rows if inserting a list</returns>

Also, observe the code that inserts single entity and should return newly generated ID as per code-comment above:

if (!isList)    //single entity
{
    returnVal = adapter.Insert(connection, transaction, commandTimeout, name, sbColumnList.ToString(),
    sbParameterList.ToString(), keyProperties, entityToInsert);
}
else
{
    //insert list of entities
    var cmd = $"insert into {name} ({sbColumnList}) values ({sbParameterList})";
    returnVal = connection.Execute(cmd, entityToInsert, transaction, commandTimeout);
}

---

Original Answer:

The Insert method of Dapper Contrib does not return the newly generated ID. This is because Contrib is built over Dapper which neither map the newly generated ID to the entity by default nor does it returns it. But there are ways to return the newly generated ID using Dapper. Those are discussed here, here and here.

_{Dapper Extensions (other query generator for Dapper) support this by default. Please refer to this answer for more details.}

One way is to bypass the Contrib and use Dapper; use any of the way explained in links to get the newly generated ID (OUTPUT parameter or @@IDENTITY etc.).

Other way to do this with Contrib is to get the newly generated ID by separately calling Dapper (as mentioned in links; @@IDENTITY) immediately after a call to Insert.

Third way is to explicitly assign the ID instead of automatically generating it. You can use [ExplicitKey] attribute to achieve this. You also need to change your database accordingly.

[Key] should be used for database-generated keys (e.g. autoincrement columns), while [ExplicitKey] should be used for explicit keys generated in code.

More aggressive solution is to modify the Contrib code to make Insert return newly generated ID.

By the way, I found an issue similar to your problem; see if that helps you.

Dapper multi insert returning inserted objects

to insert or update List of object with Dapper.Net you can't use Query

 connection.Query<Object>("your_query",your_list) 
 //connection.Query<Object>: use to select IEnumrable<object> from db
 //connection.QueryMultiple: use to execut multiple query at once then read result one by one 

var sql = 
@"
select * from Customers where CustomerId = @id
select * from Orders where CustomerId = @id
select * from Returns where CustomerId = @id";

using (var multi = connection.QueryMultiple(sql, new {id=selectedId}))
{
  var customer = multi.Read<Customer>().Single();
  var orders = multi.Read<Order>().ToList();
  var returns = multi.Read<Return>().ToList();
   ...
} 

you should use only Execute for multi insert or update

Execute("your_query",your_list, your_transaction);

so if you need to multi insert and return IDs for inserted records

// **using transaction depend on your needs**

//Example to multi insert and return full record

  string query = @"Insert Into _TableName ( _columns) 
                                  OUTPUT INSERTED.* 
                                values ( _parameters )"; //parameters should be same as object properties name to let dapper do correct mapping 

[OUTPUT INSERTED.*] will return full insert row with id and you are free to return any property by replace asterisk with propertyname [OUTPUT INSERTED.Id] will return only id

// will be good for small list

 for (int i = 0; i < youList.Count-1; i++)
                {
                    youList[i] = DbConnection.Query<object>(query, youList[i]).FirstOrDefault();
                } // for loop is better for preformance

//for big List you can use SqlBulkCopy review this link here

Get id from stored procedure insert with Dapper in .NET Core

Use ExecuteScalar for this.

int identity = await connection.ExecuteScalar<int>(SP_INSERT, parameters, commandType: CommandType.StoredProcedure);

Using Query or QueryFirst or any variation of Query will work, but isn't semantically correct. You're not querying a database; you're executing a stored procedure.

Dapper return inserted value using stored procedures

(Because your 'spAuthors_Insert' is SINGLE ROW based (and not set based), you can do the below).

Your stored procedure needs to perform a SELECT.
(as its very last instruction). A select using one of the three "identity" functions below.

ONE of the three below : (and probably in order of preference, see : What is the difference between Scope_Identity(), Identity(), @@Identity, and Ident_Current()? )

SELECT SCOPE_IDENTITY()

or

SELECT IDENT_CURRENT('tablename')

or

SELECT @@IDENTITY

(where your table name is 'Authors')

..

Then your ExecuteAsync

probably needs to be

https://learn.microsoft.com/en-us/dotnet/api/system.data.common.dbcommand.executescalarasync?view=net-6.0

ExecuteScalar(Async)

Because you need to get that single column/row value.

From the documentation:

An asynchronous version of ExecuteScalar(), which executes the command
and returns the first column of the first row in the first returned
result set. All other columns, rows and result sets are ignored.

Note the "first column of the first row". (and do not miss the "first returned result set" as well).

(Aka, make sure the ONLY "Select" in the stored procedure is one of the three IDENTITY functions above.

And to "dot the i" on the c# code.

Task SaveData<T>(

I would change to something like:

Task<long> SaveSingle<T>(

or maybe

Task<K> SaveSingle<T,K>(

(the above is the intention, I haven't tried out the generics definition on the method.

where K is "int" or "long" (INT,BIGINT in ms-sql-server)....and you return the new PrimaryKey.

And cast your result of .ExecuteScalarAsync as long or K.

Using Dapper Dot Net get last inserted id

Okay I am going to answer my own asked question I got answer after some other search in Dapper we have a Extension Method .Single() that will return the value of Selectd value newly added i.e.

var id = _db.Query<int>(query_x, new { stuff = @stuff }).Single();

in my case it will return the value of newly added primary key value. I just add the .Single() function to get newly added value.

C# Web API using Dapper to insert data using a stored procedure - unable to get inserted record Id

That's because you are getting the result of the stored procedure call, which tells you the number of rows inserted (which is 1).

You want to read the output parameter @refres (and add it to your DynamicParameters as an output parameter)

/* ... */
param.Add("@refres", dbType: DbType.Int32, direction: ParameterDirection.Output);
con.Execute("Sp_InsertTestData", param, sqltrans,0,CommandType.StoredProcedure);
var yourId = param.Get<int>("@refres");

Btw, on your stored procedure instead of:

select @refres=SCOPE_IDENTITY()

You might want to prefer this:

SET @refres = SCOPE_IDENTITY() AS INT

And I'm not sure what that last SELECT is there for

Or directly output the inserted ID (using the OUTPUT SQL clause on the INSERT) and then you could read the result, and not have an output parameter at all.

---

Related Topics