In this article, we will learn how to insert a record in an SQL database and return the inserted identity using Dapper.
Let’s begin!
Why We Need to Return the Inserted Identity
In the world of databases, there are many times when we need to retrieve the identity of a new record we add. Once we store data, we may need to perform other actions on it, like update, delete, or reference it in other parts of our application. We often need it for auditing purposes to record what we insert each time.
The tool we will use for this purpose is Dapper, an Object-Relational Mapping (ORM) library for .NET that simplifies database interactions with minimal overhead. We will see how Dapper can help us retrieve the identity of our inserted records, using two different methods.
How to Set Up Our Application
Let’s begin by building a console app to demonstrate our solutions, with the command:
dotnet new console -n ReturnInsertedIdentityApp
Let’s proceed with the installation of the required NuGet packages:
dotnet add package Microsoft.Data.SqlClient dotnet add package Dapper
Next, let’s create a settings file in the solution named appsettings.json and store the connection string:
{
"ConnectionStrings": {
"Connection": "Server=server;Database=db;User ID=user;Password=password;TrustServerCertificate=True;"
}
}
This file is useful to keep track of various settings for our application, including database connection details. This connection string usually contains things like the server’s name, the database’s name, and our username and password.
Now, let’s retrieve the connection string with the following code in the Program.cs file:
var connectionString = new ConfigurationBuilder()
.SetBasePath(AppContext.BaseDirectory)
.AddJsonFile("appsettings.json")
.Build().GetConnectionString("Connection");
Here, we specify the name of the JSON file and the name of the connection string.
To begin our sample scenario, let’s create a new SQL table named Students:
public void CreateTable(string connectionString)
{
using var connection = new SqlConnection(connectionString);
connection.Open();
var createTableSql = """
CREATE TABLE Students (Id INT IDENTITY(1,1) PRIMARY KEY,
Surname VARCHAR(255),
Firstname VARCHAR(255))
""";
connection.Execute(createTableSql);
connection.Close();
}
We implement a method called CreateTable() where we create a new database table called Students with three columns (Id, Surname, and Firstname) in our SQL database.
First, we establish a database connection using the provided connectionString, ensuring that it’s disposed of correctly when we’re done. Then, we open the connection and define an SQL command to create the Students table with the specified column names and data types. Using the Dapper library, we execute this SQL command on the connected database.
Once we create the table, we close the database connection to free up resources.
Lastly, let’s declare a class that corresponds to the records in the sample Table:
public class Student
{
public string Surname { get; set; }
public string Firstname { get; set; }
}
Now we are ready to begin developing our solutions.
Return Inserted Identity With the Use of SCOPE_IDENTITY
In the first solution, we will use the SCOPE_IDENTITY() function in Transact-SQL syntax. We use it to get the last identity value inserted into an identity column from the same scope. A scope is like a module, which can be a stored procedure, trigger, function, or batch. This means that two statements are considered to be in the same scope when they are inside the same stored procedure, function, or batch.
Let’s develop our method:
public static int UseOfScopeIdentity(string connectionString, Student newStudent)
{
using var dbConnection = new SqlConnection(connectionString);
dbConnection.Open();
var lastInsertedId = dbConnection.QuerySingle<int>(
"""
INSERT INTO Students (Firstname, Surname)
VALUES (@Firstname, @Surname)
SELECT CAST(SCOPE_IDENTITY() AS INT);
""",
newStudent
);
dbConnection.Close();
return lastInsertedId;
}
We implement a method called UseOfScopeIdentity() that takes the connectionString and the Student object that we want to insert. Then, we start by establishing a database connection using the provided connection string and open it, ensuring it’s ready for executing SQL commands.
We execute an SQL command that inserts the record in the table and retrieves the auto-generated identity value of the newly added record using the SCOPE_IDENTITY() function inside the SQL query. For this purpose, we use the QuerySingle<int>() extension from the Dapper library. We use this when we expect the SQL query to return exactly one row of data, and we want to map that result to a strongly typed object or a simple data type, like an integer or a string. The QuerySingle<int>() method expects the SQL query and the parameter as arguments.
We cast this identity value to an integer, store it in the lastInsertedId variable, and close the database connection.
Finally, we return lastInsertedId, which represents the unique ID of the recently added student.
Return Inserted Identity With the Use of OUTPUT INSERTED
Next, let’s change the SQL query to use the OUTPUT clause of Transact-SQL:
public static int UseOfOutputInserted(string connectionString, Student newStudent)
{
using var dbConnection = new SqlConnection(connectionString);
dbConnection.Open();
var lastInsertedId = dbConnection.QuerySingle<int>(
"""
INSERT INTO Students (Firstname, Surname)
OUTPUT INSERTED.Id
VALUES (@Firstname, @Surname);
""",
newStudent
);
dbConnection.Close();
return lastInsertedId;
}
The alteration from the previous solution is in the query. Here, we use the OUTPUT clause along with INSERTED.Id.
The OUTPUT is a clause in SQL that we often use in combination with INSERT, UPDATE, and DELETE statements. It allows us to retrieve the values of the rows that were affected by the operation and return them as a result set.
Which to Use and Why?
The choice of which method we should use among the two options depends on the specific needs of our project.
The OUTPUT clause is available in SQL Server 2005 version and later, while the SCOPE_IDENTITY() is available from SQL Server 2000. Generally, the OUTPUT is more versatile, allowing us to retrieve the identity values for multiple rows affected by an INSERT operation.
Also, there are some cases where the SCOPE_IDENTITY() might not behave as we expect. Let’s say for example that an insert operation fires a table trigger and that trigger performs additional inserts. The identity value we retrieve could pertain to the trigger’s insert rather than the original insert that fired the trigger.
We consider the OUTPUT clause to be a better and safer option when it comes to retrieving the identity of a newly inserted object.
Conclusion
In this article, we explore two different ways of returning the inserted identity to a database, using the Dapper ORM library.
