Dapper Transaction Insert Data

Inserting data into the database is one of the CRUD operations that act on an individual row by inserting a row. There are various ways to insert new records into the database using Dapper ORM.

The Dapper.Transaction provides the IDbTransaction.Execute extension method that is used to insert a record.

You can easily insert a single new record by writing an INSERT statement with parameters for each column you want to set.

private static void InsertSingleAuthor()
{
    using (IDbConnection connection = new SqlConnection(ConnectionString))
    {
        connection.Open();

        using (var transaction = connection.BeginTransaction())
        {
            Author author = new Author()
            {
                FirstName = "Cokie",
                LastName = "Roberts"
            };

            string sqlQuery = "INSERT INTO Authors (FirstName, LastName) VALUES(@FirstName, @LastName)";

            int rowsAffected = transaction.Execute(sqlQuery, author);
        }
    }
}

It is a simple SQL insert statement on the Authors table. There are columns and their values corresponding to parameters.

You can also use the Execute method to insert multiple authors.

private static void InsertMultipleBooks()
{
    using (IDbConnection connection = new SqlConnection(ConnectionString))
    {
        connection.Open();

        using (var transaction = connection.BeginTransaction())
        {
            string sqlQuery = "INSERT INTO Books (Title, Category, AuthorId) VALUES(@Title, @Category, @AuthorId)";

            int rowsAffected = transaction.Execute(sqlQuery,
                new[]
                {
                    new Book {Title = "From This Day Forward", Category = "Humor & Entertainment", AuthorId = 4},
                    new Book {Title = "Founding Mothers: The Women Who Raised Our Nation", Category = "History", AuthorId = 4},
                    new Book {Title = "Records of Our National Life : The National Archives", Category = "History", AuthorId = 4}
                }
            );
        }
    }
}

Let's execute the above code, and if you retrieve all the authors and books from the database, you will see that the new records are added at the end.