Learn Dapper
Powered by Dapper Plus - Learn how to improve your performance in Dapper
30% OFF - 9th Anniversary discount on Dapper Plus until December 15 with code: ZZZANNIVERSARY9

Inserting Data With Dapper

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.

In this article, we will talk about the 4 most common scenarios for inserting:

Dapper Insert

To insert in dapper, you need to use the Execute method with an INSERT statement and provide your query parameters values.

In this example:

  1. We will create a connection
  2. We will create an INSERT sql statement
  3. Call the Execute method 3a. The first time, we will pass parameters values with an anonymous type 3b. The second time, we will pass parameters values by providing the customer entity
language-csharp
| 
// 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 parameters values with an anonymous type
		var anonymousCustomer = new { Name = "ZZZ Projects", Email = "zzzprojects@example.com" };

		var rowsAffected = connection.Execute(sql, anonymousCustomer);
		Console.WriteLine($"{rowsAffected} row(s) inserted.");
	}

	{
		// 3b. The second time, we will pass parameters values by providing the customer entity
		var customer = new Customer() { Name = "Learn Dapper", Email = "learndapper@example.com" };

		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 Execute method with the ExecuteAsync method
  • Use the await keyword
  • The ConfigureAwait(false) part is optional (depending on your application type)
language-csharp
| 
// 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 parameters values with an anonymous type
		var anonymousCustomer = new { Name = "ZZZ Projects", Email = "zzzprojects@example.com" };

		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 parameters values by providing the entity
		var customer = new Customer() { Name = "Learn Dapper", Email = "learndapper@example.com" };

		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 entity.

NOTE: Passing a list doesn't insert rows in bulk. Every item will be inserted by performing a single insert statement (similar to looping on your item list to call the Execute method)

In this example:

  1. We will create a connection
  2. We will create an INSERT sql statement
  3. Call the Execute method 3a. The first time, we will pass parameters values with a list of anonymous objects 3b. The second time, we will pass parameters values by providing a list of customers
language-csharp
| 
// 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 parameters values with a list of anonymous objects
		var anonymousCustomers = new List<object>() {
			new { Name = "ZZZ Projects", Email = "zzzprojects@example.com" },
			new { Name = "Anonymous Object 2", Email = "anonymousobject2@example.com" },
			new { Name = "Anonymous Object 3", Email = "anonymousobject2@example.com" },
		};

		var rowsAffected = connection.Execute(sql, anonymousCustomers);
		Console.WriteLine($"{rowsAffected} row(s) inserted.");
	}

	{
		// 3b. The second time, we will pass parameters values by providing a list of customers
		var customers = new List<Customer>()
		{
			new Customer() { Name = "Learn Dapper", Email = "learndapper@example.com" },
			new Customer() { Name = "Entity 2", Email = "entity2@example.com" },
			new Customer() { Name = "Entity 3", Email = "entity3@example.com" }
		};

		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:

  1. We will create a connection
  2. Call the BulkInsert method with a list of customers
language-csharp
| 
// 1. We will create a connection
using (var connection = new SqlConnection(connectionString))
{
	var customers = new List<Customer>()
		{
			new Customer() { Name = "Learn Dapper", Email = "learndapper@example.com" },
			new Customer() { Name = "Entity 2", Email = "entity2@example.com" },
			new Customer() { Name = "Entity 3", Email = "entity3@example.com" }
		};

	// 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.

FAQ

How to insert data in bulk in Dapper?

To insert data in bulk, you need to use Dapper Plus. The BulkInsert extension methods allow you to insert a list of entities using Bulk Operations.

Learn more about Bulk Inserting data with Dapper

How to insert data and return the id in Dapper?

To insert data and return the id, you have 2 choices:

  1. Use the Execute method and select the identity inserted with SCOPE_IDENTITY()
  2. Use Dapper Plus with the property mapped as identity or output

Solution 1

using (var connection = new SqlConnection(connectionString))
{
	var sql = @"
INSERT INTO Customers (Name, Email) VALUES (@Name, @Email)
SELECT SCOPE_IDENTITY()
";

	var customer = new Customer() { Name = "Learn Dapper", Email = "learndapper@example.com" };

	var customerID = connection.Execute(sql, customer);
	customer.CustomerID = customerID;

	Console.WriteLine(customer.CustomerID);
}

Solution 2

// global context mapping: https://dapper-plus.net/getting-started-mapping#global-context-mapping
DapperPlusManager.Entity<Customer>().Identity(x => x.CustomerID);

using (var connection = new SqlConnection(connectionString))
{
	var customer = new Customer() { Name = "Learn Dapper", Email = "learndapper@example.com" };

	connection.BulkInsert(customer);

	Console.WriteLine(customer.CustomerID);
}
How to insert data including the identity value in Dapper?

To insert data, including the identity value, you have 2 choices:

  1. Use the Execute method and turn on the identity insert
  2. Use Dapper Plus with the InsertKeepIdentity option

Solution 1

using (var connection = new SqlConnection(connectionString))
{
	var sql = @"
SET IDENTITY_INSERT Customers ON  

INSERT INTO Customers (CustomerID, Name, Email) VALUES (@CustomerID, @Name, @Email)
";

	var customer = new Customer() { CustomerID = 13, Name = "Learn Dapper", Email = "learndapper@example.com" };

	connection.Execute(sql, customer);

	Console.WriteLine(customer.CustomerID);
}

Solution 2

// global context mapping: https://dapper-plus.net/getting-started-mapping#global-context-mapping
DapperPlusManager.Entity<Customer>().Identity(x => x.CustomerID);

using (var connection = new SqlConnection(connectionString))
{
	var customer = new Customer() { CustomerID = 13, Name = "Learn Dapper", Email = "learndapper@example.com" };

	connection.UseBulkOptions(x => x.InsertKeepIdentity = true).BulkInsert(customer);

	Console.WriteLine(customer.CustomerID);
}
How to insert data using a list in Dapper?

To insert data using a list, you need to pass the list in a parameter instead of a single object to the Execute or ExecuteAsync method. However, inserting by providing a list doesn't perform a bulk insert. Instead, it simply iterates over every item to perform a single insert. See the Inserting multiple rows for an example.

See the Bulk Inserting documentation if you want to insert the list in bulk.

How to insert data using a stored procedure in Dapper?

To insert data using a stored procedure, you need to call the Execute method with the commandType: CommandType.StoredProcedure parameter

Learn more about Executing stored procedures with Dapper

How to insert data using a transaction in Dapper?

To insert data using a transaction, you have 3 choices:

  1. Begin a transaction from your connection with connection.BeginTransaction()
  2. Create a TransactionScope
  3. Use Dapper.Transaction (recommended solution)

Learn more about Transaction with Dapper

How to insert data using if not exists in Dapper?

To insert data using if not exists, you have 2 choices:

  1. Use the Execute method and INSERT with a WHERE NOT EXISTS sql statement
  2. Use Dapper Plus with the InsertIfNotExists option

Solution 1

using (var connection = new SqlConnection(My.connectionString))
{
	var sql = @"
INSERT INTO Customers (Name, Email)
SELECT @Name, @Email
WHERE NOT EXISTS (SELECT 1 FROM Customers AS X
	  WHERE X.CustomerID = @CustomerID)
";

	var customer = new Customer() { CustomerID = 13, Name = "Learn Dapper", Email = "learndapper@example.com" };

	connection.Execute(sql, customer);

	var insertedCustomers = connection.Query<Customer>("SELECT * FROM Customers").ToList();
}

Solution 2

using (var connection = new SqlConnection(connectionString))
{
	var customer = new Customer() { CustomerID = 13, Name = "Learn Dapper", Email = "learndapper@example.com" };

	connection.UseBulkOptions(x => x.InsertIfNotExists = true).BulkInsert(customer);
	
	var insertedCustomers = connection.Query<Customer>("SELECT * FROM Customers").ToList();
}
How to insert data using C# in Dapper?

Here is an example of inserting data using C# syntax:

using (var connection = new SqlConnection(connectionString))
{
	var sql = "INSERT INTO Customers (Name, Email) VALUES (@Name, @Email)";

	var customer = new Customer() { Name = "Learn Dapper", Email = "learndapper@example.com" };

	connection.Execute(sql, customer);
}
How to insert data using VB.NET in Dapper?

Here is an example of inserting data using VB.NET syntax:

Using connection As New SqlConnection(connectionString)
    Dim sql = "INSERT INTO Customers (Name, Email) VALUES (@Name, @Email)"
    
    Dim customer = New Customer() With { .Name = "Learn Dapper", .Email = "learndapper@example.com" }
    
    connection.Execute(sql, customer)
End Using
Date Modified: 2023-08-01
Author:

Edit this page in GitHub

Got any Dapper Question?