Dapper Using Lists

Last week’s post on ASP.NET Core with Dapper covered the very basics of using Dapper in an ASP.NET Core project. This week we will be changing up the sample code from last week to show using Dapper to deal with lists instead of just a single object at a time.

This post is going to be pretty short, but I thought it was important to point out that Dapper can execute a single query multiple times by passing it an IEnumerable.

The Sample

The following sample connects to the database, deletes all the existing contacts, seeds the contact table with 3 contacts (this is our list), and then selects all the contacts back out (this comes out as an IEnumerable).

using (var connection = 
           new SqlConnection(_configuration
                             .GetConnectionString("DefaultConnection")))
{
    connection.Open();

    connection.Execute("DELETE Contacts");

    var seedContacts = new List<Contact>
                       {
                           new Contact
                           {
                               Name = "Charlie Plumber",
                               Address = "123 Main St",
                               City = "Nashville",
                               Subregion = "TN",
                               Email = "cplumber@fake.com"
                           },
                           new Contact
                           {
                               Name = "Teddy Pierce",
                               Address = "6708 1st St",
                               City = "Nashville",
                               Subregion = "TN",
                               Email = "tpierce@fake.com"
                           },
                           new Contact
                           {
                               Name = "Kate Pierce",
                               Address = "6708 1st St",
                               City = "Nashville",
                               Subregion = "TN",
                               Email = "kpierce@fake.com"
                           }
                       };

    connection.Execute(@"INSERT INTO Contacts (Name, Address, City, 
                                               Subregion, Email) 
                         VALUES (@Name, @Address, @City, 
                                 @Subregion, @Email)",
                       seedContacts);
    
    var allContacts = connection
                      .Query<Contact>(@"SELECT Id, Name, Address, City, 
                                               Subregion, Email 
                                        FROM Contacts");
}

As expected the end result contains three contact records.

Wrapping Up

Hopefully, this quick little post helps fill in one of the gaps of last week’s post. Make sure and check out the Dapper GitHub page in addition to features I have covered in these two posts it also supports a lot of other functionality such as stored procedures, multiple result sets, etc. The code in its final state can be found here.

Originally published at Eric L. Anderson.