Dapper 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 db = new SqlConnection(ConnectionString))
    {
        Author author = new Author()
        {
            FirstName = "William",
            LastName = "Shakespeare"
        };

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

        int rowsAffected = db.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 InsertMultipleAuthors()
{
    using (IDbConnection db = new SqlConnection(ConnectionString))
    {
        string sqlQuery = "INSERT INTO Authors (FirstName, LastName) VALUES(@FirstName, @LastName)";

        int rowsAffected = db.Execute(sqlQuery,
            new[]
            {
                new {FirstName = "Emily", LastName = "Dickinson"},
                new {FirstName = "Leo", LastName = "Tolstoy"},
                new {FirstName = "Rabindranath", LastName = "Tagore"}
            }
        );
    }
}

If you retrieve all the authors from the database, you will see that a new record is already added at the end.

private static void GetAllAuthors()
{
    using (IDbConnection db = new SqlConnection(ConnectionString))
    {
        List<Author> authors = db.Query<Author>("SELECT * FROM Authors").ToList();

        foreach (var author in authors)
        {
            Console.WriteLine(author.FirstName + " " + author.LastName);
        }
    }
}

Let's execute the above code, and you will see the following output.

Cardinal Tom B. Erichsen
Meredith Alonso
Robert T. Kiyosaki
William Shakespeare
Emily Dickinson
Leo Tolstoy
Rabindranath Tagore