C# Dapper - Return inserted identity

This post describes two ways to get the generated identity of a newly inserted row. We will use the following table simple table with an Id column and a simple Text column:

CREATE TABLE [dbo].[MyEntities](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Text] [nvarchar](max) NOT NULL,
   CONSTRAINT [PK_MyEntities] PRIMARY KEY CLUSTERED 
   (
	  [Id] ASC
   )
)

We will use the following matching class for the table:

[Table("MyEntities")]
public class MyEntity
{
    public int Id { get; set; }
    public string Text { get; set; }
}

A simple insert statement for this table using dapper could look like:

using (var sqlConnection = new SqlConnection(@"Data Source=localhost;Initial Catalog=MyDb;Integrated Security=True;"))
{
    var identity = sqlConnection.QuerySingle<int>("INSERT INTO [dbo].[MyEntities] (Text) VALUES (@Text);"
        , new MyEntity { Text = "this is a text" });
}

Using standard SQL we can add "output inserted.Id" to our statement which "Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement":

using (var sqlConnection = new SqlConnection(@"Data Source=localhost;Initial Catalog=MyDb;Integrated Security=True;"))
{
    var identity = sqlConnection.QuerySingle<int>("INSERT INTO [dbo].[MyEntities] (Text) output inserted.Id VALUES (@Text);"
        , new MyEntity { Text = "this is a text" });
}

The above will give us the newly created identity of the row within that scope. The example expects an int as the identity, you will have to change this if you use a different identity type for your table. If you use Execute instead of QuerySingle you get the standard "number of rows affected" and not the identity.

Using Dapper.Contrib

Alternatively to using standard Dapper you can use the Dapper.Contrib nuget package, this enables you to simplify your insert statements in the following way:

using (var sqlConnection = new SqlConnection(@"Data Source=localhost;Initial Catalog=MyDb;Integrated Security=True;"))
{
    var identity = sqlConnection.Insert(new MyEntity { Text = "this is a text" });
}

With Dapper Contrib you do not have to write any SQL, it will create this on its own. Getting the identity is also built into the Insert method, so you will not have to write your own output for this.

That is all

I hope you found what you were searching for, let me know in the comments down below!