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.

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 a simple SQL insert statement on the Authors table. There are the columns and their values corresponding to parameters.

The Execute extension method of Dapper is used to insert a record. 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.