C# – Insert records with Dapper

by

You can insert records with Dapper by using Execute() on an INSERT statement and passing in a model object. Here’s an example of inserting a single movie record:

using Dapper;

const string INSERT_SQL =
@"INSERT INTO [Movies]
	([Name]
	,[YearOfRelease]
	,[Description]
	,[Director]
	,[BoxOfficeRevenue])	   
VALUES
	(@Name,
	@YearOfRelease,
	@Description,
	@Director,
	@BoxOfficeRevenue)";

void Insert(Movie movie)
{
	using (var con = new SqlConnection(connectionString))
	{
		con.Execute(INSERT_SQL, param: movie);
	}
}
Code language: C# (cs)

Notice that you can simply pass in an object (param: movie) and Dapper handles the tedious parameter mapping for you. It tries to map properties from the object to the query parameters in the INSERT statement. If you don’t have/want a model class, you can explicitly specify all the parameters (i.e. param: new { name, year }).

Insertions are a little tedious to code because INSERT statements are so verbose compared to other SQL statements. To reduce the amount of typing, I typically generate the INSERT with SSMS (Script Table as > INSERT INTO) or from metadata and then copy/paste into the code as a string (as shown in the code above).

Note: There are extension libraries for Dapper that generate SQL statements (such as INSERT) for you. Personally, I don’t use those, because I like to know exactly what SQL statements are being executed. That’s the primary benefit of using Dapper – it executes the exact SQL statement you pass in (and handles the tedious mapping), so there are no surprises to deal with.

Insert multiple records

When you pass a list of objects for the param argument, Dapper will execute the INSERT for every object in the list. Here’s an example of inserting multiple movie records:

using Dapper;

const string INSERT_SQL =
@"INSERT INTO [dbo].[Movies]
	([Name]
	,[YearOfRelease]
	,[Description]
	,[Director]
	,[BoxOfficeRevenue])	   
VALUES
	(@Name,
	@YearOfRelease,
	@Description,
	@Director,
	@BoxOfficeRevenue)";

void InsertMultiple(List<Movie> movies)
{
	using (var con = new SqlConnection(connectionString))
	{
		con.Execute(INSERT_SQL, param: movies);
	}
}
Code language: C# (cs)

Behind the scenes, Dapper executes multiple INSERT statements (one for each object in the list). Executing lots of INSERT statements sequentially can really degrade performance. Be sure to performance test your code if you’re expecting to insert lots of data like this frequently. Consider doing a BULK INSERT instead to improve performance.

Leave a Comment