Insert records with Dapper in C#

C# Insert Records with Dapper

Here’s an example where we insert a single product record into a database:

using Dapper;

const string INSERT_SQL =
@"INSERT INTO [Products]
    ([ProductName], 
    [Category], 
    [Price], 
    [StockQuantity])	   
VALUES
    (@ProductName,
    @Category,
    @Price,
    @StockQuantity)";

void Insert(Product product)
{
    using (var con = new SqlConnection(connectionString))
    {
        con.Execute(INSERT_SQL, param: product);
    }
}

In this code, the Execute() method from Dapper is used to execute an INSERT SQL query. The product object is passed as a parameter, and Dapper automatically maps its properties to the corresponding parameters in the SQL query. This is convenient as Dapper handles the parameter mapping behind the scenes.

If you prefer not to use a model class, you can manually specify parameters instead, like so:

con.Execute(INSERT_SQL, new { ProductName = "Laptop", Category = "Electronics", Price = 799.99, StockQuantity = 150 });

Inserting records can sometimes feel cumbersome due to the verbosity of INSERT statements.

To streamline the process, I often generate the INSERT statement using SQL Server Management Studio (SSMS) or metadata and copy/paste it into the code, as shown above.

There are also third-party libraries that help generate SQL statements for you, but personally, I prefer manually writing the queries. This ensures that I know exactly what SQL is being executed, which is the primary advantage of using Dapper it gives you control over the exact SQL statements that are run.

Inserting Multiple Records

If you want to insert multiple records at once, you can pass a list of objects to the param argument, and Dapper will execute an INSERT statement for each object. Here’s an example that inserts multiple product records:

using Dapper;

const string INSERT_SQL =
@"INSERT INTO [dbo].[Products]
    ([ProductName], 
    [Category], 
    [Price], 
    [StockQuantity])	   
VALUES
    (@ProductName,
    @Category,
    @Price,
    @StockQuantity)";

void InsertMultiple(List<Product> products)
{
    using (var con = new SqlConnection(connectionString))
    {
        con.Execute(INSERT_SQL, param: products);
    }
}

When you pass a list of products, Dapper will iterate through the list and execute an INSERT for each product. While this is convenient, inserting many records sequentially like this can affect performance, especially when working with large datasets.

To improve performance for bulk inserts, consider using a BULK INSERT operation or other methods designed for bulk data handling. It's crucial to perform performance testing if you anticipate frequent large-scale inserts.