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 = "[email protected]"
                           },
                           new Contact
                           {
                               Name = "Teddy Pierce",
                               Address = "6708 1st St",
                               City = "Nashville",
                               Subregion = "TN",
                               Email = "t[email protected]"
                           },
                           new Contact
                           {
                               Name = "Kate Pierce",
                               Address = "6708 1st St",
                               City = "Nashville",
                               Subregion = "TN",
                               Email = "[email protected]"
                           }
                       };

    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.

ASP.NET Core with Dapper

Entity Framework Core has been a the heart of a lot of the post I have done recently. I thought it would be a good change of pace to try out one of the alternatives to Entity Framework Core and give Dapper a try.

I tried to find a good example of the difference between micro ORMs like Dapper and full ORMs like Entity Framework Core, but all the ones I found ended up pushing one option over the other.  I’m not looking to say one is better than the other, but to add a new tool to my toolbelt.

Basically, the differences seem to come down to micro ORMs provide significantly fewer features than a full ORM but come with a higher level of performance. Which is the right choice is going to vary by project.

Sample Project

We will be using a new Razor Pages application as the starting point for the sample application. It currently doesn’t have any database access setup. The code for the starting point of the sample can be found here.

I’m going to be using an existing database created for the SQL Server example in my Entity Framework sample project. Below is a Contacts table generated from that sample project in case you want to manually generate the table.

CREATE TABLE [dbo].[Contacts] (
    [Id]         INT            IDENTITY (1, 1) NOT NULL,
    [Name]       NVARCHAR (MAX) NULL,
    [Address]    NVARCHAR (MAX) NULL,
    [City]       NVARCHAR (MAX) NULL,
    [Subregion]  NVARCHAR (MAX) NULL,
    [PostalCode] NVARCHAR (MAX) NULL,
    [Phone]      NVARCHAR (MAX) NULL,
    [Email]      NVARCHAR (MAX) NULL,
    [Timestamp]  ROWVERSION     NULL
);

Add Dapper Nuget Package

Using your favorite way to add a NuGet package add a reference to the Dapper package. I’m going to do it using Visual Studio by right-clicking on the project and selecting Manage NuGet Packages.

On the next screen, from the Browse tab use the Search Box to search for Dapper. Select the Dapper package and click Install.

Add Connection String Configuration

We are going to store the database connection string in the appsettings.json file. Open the file and add a ConnectionString section with a DefaultConnection that contains the database connection string. This exact setup isn’t required I’m just following the way configuration is set up for Entity Framework base projects. The following is my full configuration file with the new section at the top.

{
  "ConnectionStrings": {
    "DefaultConnection": "Your connection string"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "AllowedHosts": "*"
}

Contact Model

Add a Contact class in a Models folder which will be used to represent our data. The following is the full model class I will be using in this example.

public class Contact
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
    public string Subregion { get; set; }
    public string PostalCode { get; set; }
    public string Phone { get; set; }
    public string Email { get; set; }
}

Test Setup

To keep things simple I will not be dealing with any UI other than as a way to run the Dapper related code. As such we will be using the Page Modle of the Index page to run the sample code. This is in no way an example of the best practice setup and is only done to demo Dapper in the simplest setup possible.

The following is the full page model with the configuration for the application being injected via the constructor. All the changes passed this point will be in the OnGet function.

public class IndexModel : PageModel
{
    private readonly IConfiguration _configuration;

    public IndexModel(IConfiguration configuration)
    {
        _configuration = configuration;
    }

    public void OnGet()
    {

    }
}

Using Dapper

Dapper provides a number of extension methods off of the IDbConnection interface. For this example, we will use these extensions to check to see if a specific contact exists, if it doesn’t then insert it, and finally select the contact back out of the database.

The first step is to open a connection. In this example, we are using SQL Server, but Dapper doesn’t really care and will work with any ADO.NET provider. The following code opens a SQL connection using the connection string from appsettings.json.

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

Next, we are going to query the Contacts table for the ID of a specific contact.

if (connection.QueryFirstOrDefault<int?>(@"SELECT Id 
                                           FROM Contacts 
                                           WHERE Name = @Name",
                                         new {Name = "Charlie Plumber"}) == null)
{
}

As you can see from the above you there is nothing special about the SQL being written. For this query, we are using an anonymous type to pass the query a name filter which will be translated to a SqlParameter which ensures the query won’t all things like SQL injection. Note that the parameter name in the SQL string must match the name of the corresponding property on the object being passed to the query.

This next example is the contact insert if the contact wasn’t found in the previous query.

connection.Execute(@"INSERT INTO Contacts (Name, Address, City, Subregion, Email)
                     VALUES (@Name, @Address, @City, @Subregion, @Email)",
                   new Contact
                   {
                       Name = "Charlie Plumber",
                       Address = "123 Main St",
                       City = "Nashville",
                       Subregion = "TN",
                       Email = "[email protected]"
                   });

Again the SQL is exactly what you would expect, which is one of the great parts about Dapper if you are familiar with SQL.

This last example is a combination of all the previous examples with a select to get out the contact details of the contact in question.

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

    if (connection
           .QueryFirstOrDefault<int?>(@"SELECT Id 
                                        FROM Contacts 
                                        WHERE Name = @Name",
                                      new {Name = "Charlie Plumber"}) == null)
    {
        connection.Execute(@"INSERT INTO Contacts (Name, Address, City, 
                                                   Subregion, Email) 
                             VALUES (@Name, @Address, @City, 
                                     @Subregion, @Email)",
                           new Contact
                           {
                               Name = "Charlie Plumber",
                               Address = "123 Main St",
                               City = "Nashville",
                               Subregion = "TN",
                               Email = "[email protected]"
                           });
    }

    var charile = 
      connection.QueryFirstOrDefault<Contact>(@"SELECT Id, Name, Address, 
                                                       City, Subregion, Email 
                                                FROM Contacts 
                                                WHERE Name = @Name",
                                              new {Name = "Charlie Plumber"});
}

Wrapping Up

This first round of playing with Dapper was fun. I write a lot of SQL in my day job so it was kind of nice seeing the explicit SQL vs the magic of a full ORM. I can tell you from doing this sample I would miss Entity Framework’s database creation and migration capabilities.

The code in its final state can be found here.

Getting a Sample SQL Server Database

As tends to happen this isn’t the post I set out to write today. We hit an issue at work the other day that I want to write about, but to do so I need a sample database (I can’t use the data from work). I wanted something that is more fleshed out that my normal single table contact database. I google for AdventureWorks, since that is the sample database I have always seen in examples.

Options

Turns out that AdventureWorks isn’t the only SQL Server sample database option in town these days. Microsoft has a SQL Server Samples repo that has three different options depending on your needs. The following is a description and links to all there options as they stand right now.

wide-world-importers

The new sample database for SQL Server 2016 and Azure SQL Database. It illustrates the core capabilities of SQL Server 2016 and Azure SQL Database, for transaction processing (OLTP), data warehousing and analytics (OLAP) workloads, as well as hybrid transaction and analytics processing (HTAP) workloads.

contoso-data-warehouse

Sample data warehouse that illustrates loading data into Azure SQL Data Warehouse.

AdventureWorks

Sample databases and Analysis Services models for use with SQL Server.

Since I’m not dealing with a data warehouse the middle option is out. AdventureWorks is still a valid option, but I just can’t pass up WideWorldImporters which is the newest sample used to show off SQL Server 2016.

Getting Started

Make sure you have both SQL Server and SQL Server Management Studio installed. For SQL Server we will be using the on-premises version. I recommend grabbing the developer edition as it provides the full set of features for free as long as it isn’t used in production. Once you have both of the above installed it is time to get download the database related files.

As of this writing, this GitHub release page contains the latest bits to get started with, and yes it is from June of 2016. There are a lot of options listed, but the file we are interested in is WideWorldImporters-Full.bak. If you are looking to explore some of the new features of SQL Server 2016 you should also grab sample-scripts.zip to play with, but this isn’t going to be covered in this post.

Restoring the Database

Now that we have the backup of the database we are going to restore it is time to open up SQL Server Management Studio. In the connection dialog, connect to the server you want the database to end up on. In this case, I’m connecting to a SQL Server instance running on my local PC. When you are all set hit the Connect button.

Once connected you should see the selected server in the Object Explorer window.

Right-click on the Databases folder (or node if you prefer) and click Restore Database.

This will launch the Restore Database dialog. This dialog is full of stuff, but we are going to focus on the minimum needed to restore a database from a backup file. First, select the Device option and then click the  button.

This will show the Select backup devices dialog. We want to use the File type and then click the Add button.

On the next screen, you need to enter the path to your backup file. I’m not sure why, but this isn’t the easiest dialog for finding a file. I found it easier to use Windows Explorer to find the directory the backup file is in and copy it to the Backup File Location. Once you have the right directory select the backup file and click OK.

Back on the Select backup devices dialog click the OK button to continue. This will land you back on the Restore Database dialog which will now display information from the backup that was selected. Click the OK button to start the restore process.

After a minute or so the restore process should complete. If you expand the Databases node back in the Object Explorer window you should see the restored database listed.

Data Generation

The WideWorldImporters database comes with a stored procedure that will generate current data for you to work with. This isn’t a fast process so be prepared to wait if you decide to run this. To start open a new query window for the WideWorldImporters database by right-clicking and selecting New Query.

If you run the following query it will start the data generation process.

EXECUTE DataLoadSimulation.PopulateDataToCurrentDate
    @AverageNumberOfCustomerOrdersPerDay = 60,
    @SaturdayPercentageOfNormalWorkDay = 50,
    @SundayPercentageOfNormalWorkDay = 0,
    @IsSilentMode = 1,
    @AreDatesPrinted = 1;

The official docs have more details on data generation. From the docs, it says that data generation will take about 10 minutes per year and starts off from 2016 so you are looking at a 30-minute minimum runtime (and based on my test that 10 minutes per year number is much too low). Do note that the back up comes with data so this process is only needed if you want recent (date wise) data.

Wrapping Up

The above process wasn’t hard, but if you are like me and haven’t worked much with database backup hopefully you found this post helpful. I’m sure you will find the sample database helpful for trying out some of the newer features that SQL Server or for a source of data that is OK to use publically.

Make sure you check out the official docs pages for Wide World Importers. It has a lot more information as well as instructions for other workload use cases such as data warehousing.