To insert data in Dapper, you need to write your INSERT SQL statement and call the Execute method. Make sure to use parameters when passing your values to avoid SQL Injection.
- Inserting single row
- Inserting single row asynchronously
- Inserting multiple rows
- Bulk inserting rows
Dapper Insert
To insert in Dapper, you need to use the Execute method with an INSERT statement and provide your query parameter values.
In this example:
- We will create a connection
- We will create an
INSERTSQL statement - Call the
Executemethod
- 3a. The first time, we will pass parameter values with an anonymous type
- 3b. The second time, we will pass parameter values by providing the customer entity
// 1. We will create a connection
using (var connection = new SqlConnection(connectionString))
{
// 2. We will create an `INSERT` SQL statement
var sql = "INSERT INTO Customers (Name, Email) VALUES (@Name, @Email)";
// 3. Call the `Execute` method
{
// 3a. The first time, we will pass parameter values with an anonymous type
var anonymousCustomer = new { Name = "ZZZ Projects", Email = "[email protected]" };
var rowsAffected = connection.Execute(sql, anonymousCustomer);
Console.WriteLine($"{rowsAffected} row(s) inserted.");
}
{
// 3b. The second time, we will pass parameter values by providing the customer entity
var customer = new Customer() { Name = "Learn Dapper", Email = "[email protected]" };
var rowsAffected = connection.Execute(sql, customer);
Console.WriteLine($"{rowsAffected} row(s) inserted.");
}
var insertedCustomers = connection.Query<Customer>("SELECT * FROM Customers").ToList();
}
Dapper Insert Async
To insert in Dapper asynchronously, you need to use the ExecuteAsync method. Unfortunately, Dapper doesn't support cancellation token.
In this example, we will use the same logic as the previous example with a few differences:
- Replace the
Executemethod with theExecuteAsyncmethod - Use the
awaitkeyword - The
ConfigureAwait(false)part is optional (depending on your application type)
// 1. We will create a connection
using (var connection = new SqlConnection(connectionString))
{
// 2. We will create an `INSERT` SQL statement
var sql = "INSERT INTO Customers (Name, Email) VALUES (@Name, @Email)";
// 3. Call the `ExecuteAsync` method
{
// 3a. The first time, we will pass parameter values with an anonymous type
var anonymousCustomer = new { Name = "ZZZ Projects", Email = "[email protected]" };
var rowsAffected = await connection.ExecuteAsync(sql, anonymousCustomer);
// or var rowsAffected = await connection.ExecuteAsync(sql, anonymousCustomer).ConfigureAwait(false);
Console.WriteLine($"{rowsAffected} row(s) inserted.");
}
{
// 3b. The second time, we will pass parameter values by providing the entity
var customer = new Customer() { Name = "Learn Dapper", Email = "[email protected]" };
var rowsAffected = await connection.ExecuteAsync(sql, customer);
// or var rowsAffected = await connection.ExecuteAsync(sql, customer).ConfigureAwait(false);
Console.WriteLine($"{rowsAffected} row(s) inserted.");
}
var insertedCustomers = connection.Query<Customer>("SELECT * FROM Customers").ToList();
}
Dapper Insert Multiple Rows
For inserting multiple rows in Dapper, you have to provide a list to the Execute or ExecuteAsync method instead of a single anonymous object or an entity.
NOTE: Passing a list doesn't insert rows in bulk. Every item will be inserted by using a single insert statement (similar to looping on your item list to call the Execute method)
In this example:
- We will create a connection
- We will create an
INSERTSQL statement - Call the
Executemethod 3a. The first time, we will pass parameter values with a list of anonymous objects 3b. The second time, we will pass parameter values by providing a list of customers
// 1. We will create a connection
using (var connection = new SqlConnection(connectionString))
{
// 2. We will create an `INSERT` SQL statement
var sql = "INSERT INTO Customers (Name, Email) VALUES (@Name, @Email)";
// 3. Call the `Execute` method
{
// 3a. The first time, we will pass parameter values with a list of anonymous objects
var anonymousCustomers = new List<object>() {
new { Name = "ZZZ Projects", Email = "[email protected]" },
new { Name = "Anonymous Object 2", Email = "[email protected]" },
new { Name = "Anonymous Object 3", Email = "[email protected]" },
};
var rowsAffected = connection.Execute(sql, anonymousCustomers);
Console.WriteLine($"{rowsAffected} row(s) inserted.");
}
{
// 3b. The second time, we will pass parameter values by providing a list of customers
var customers = new List<Customer>()
{
new Customer() { Name = "Learn Dapper", Email = "[email protected]" },
new Customer() { Name = "Entity 2", Email = "[email protected]" },
new Customer() { Name = "Entity 3", Email = "[email protected]" }
};
var rowsAffected = connection.Execute(sql, customers);
Console.WriteLine($"{rowsAffected} row(s) inserted.");
}
var insertedCustomers = connection.Query<Customer>("SELECT * FROM Customers").ToList();
Dapper BulkInsert
For bulk inserting data in Dapper, the third-party library named Dapper Plus will be required.
You can read our tutorial on Bulk Inserting Data with Dapper
In this example using Dapper Plus:
- We will create a connection
- Call the
BulkInsertmethod with a list of customers
// 1. We will create a connection
using (var connection = new SqlConnection(connectionString))
{
var customers = new List<Customer>()
{
new Customer() { Name = "Learn Dapper", Email = "[email protected]" },
new Customer() { Name = "Entity 2", Email = "[email protected]" },
new Customer() { Name = "Entity 3", Email = "[email protected]" }
};
// 2. Call the `BulkInsert` method with a list of customers
connection.BulkInsert(customers);
var insertedCustomers = connection.Query<Customer>("SELECT * FROM Customers").ToList();
}
Conclusion
In conclusion, Dapper provides a simple and easy-to-use method for inserting data with the Execute and ExecuteAsync methods. You write your INSERT SQL statement and provide either a single item or a list.