DotNet Core vs. NHibernate vs. Dapper Smackdown!

The Contenders

Dapper

Dapper is a hybrid ORM.  This is a great ORM for those who have a lot of ADO legacy code to convert.  Dapper uses SQL queries and parameters can be used just like ADO, but the parameters to a query can be simplified into POCOs.  Select queries in Dapper can also be translated into POCOs.  Converting legacy code can be accomplished in steps because the initial pass of conversion from ADO is to add Dapper, followed by a step to add POCOs, then to change queries into LINQ (if desired).  The speed difference in my tests show that Dapper is better than my implementation of ADO for select queries, but slower for inserts and updates.  I would expect ADO to perform the best, but there is probably a performance penalty for using the data set adapter instead of the straight sqlCommand method.

If you’re interested in Dapper you can find information here: Stack Exchange/Dapper.   Dapper has a NuGet package, which is the method I used for my sample program.

ADO

I rarely use ADO these days, with the exception of legacy code maintenance or if I need to perform some sort of bulk insert operation for a back-end system.  Most of my projects are done in Entity Framework, using the .Net Core or the .Net version.  This comparison doesn’t feel complete without including ADO, even though my smackdown series is about ORM comparisons.  So I assembled a .Net console application with some ADO objects and ran a speed test with the same data as all the ORM tests.

NHibernate

NHiberate is the .Net version of Hibernate.  This is an ORM that I used at a previous company that I worked for.  At the time it was faster than Entity Framework 6 by a large amount.  The .Net Core version of Entity Framework has fixed the performance issues of EF and it no longer makes sense to use NHibernate.  I am providing the numbers in this test just for comparison purposes.  NHibernate is still faster than ADO and Dapper for everything except the select.  Both EF-7 and NHibernate are so close in performance that I would have to conclude that they are the same.  The version of NHibernate used for this test is the latest version as of this post (version 4.1.1 with fluent 2.0.3).

Entity Framework 7 for .Net Core

I have updated the NuGet packages for .Net Core for this project and re-tested the code to make sure the performance has not changed over time.  The last time I did a smackdown with EF .Net Core I was using .Net Core version 1.0.0, now I’m using .Net Core 1.1.1.  There were not measurable changes in performance for EF .Net Core.

The Results

Here are the results side-by-side with the .ToList() method helper and without:

Test for Yourself!

First, you can download the .Net Core version by going to my GitHub account here and downloading the source.  There is a SQL script file in the source that you can run against your local MS SQL server to setup a blank database with the correct tables.  The NHibernate speed test code can also be downloaded from my GitHub account by clicking here. The ADO version is here.  Finally, the Dapper code is here.  You’ll want to open the code and change the database server name.

 

Bulk Data Inserts

Summary

In this blog post I’m going to demonstrate how to do a bulk data insert using ADO.  I’ll be adding code to my helper set of tools so that you can use the ADO database code at my GitHub account to write clean ADO database methods.

Bulk Inserts

The C# SqlClient library contains a method called SqlBulkCopy, which is described in this blog post: Bulk Insert into SQL Server using SqlBulkCopy in C# 

The first thing I’m going to do is add a static helper object to contain all of this code:

DataColumn dateColumn = new DataColumn();
dateColumn.DataType = Type.GetType(“System.DateTime“);
dateColumn.ColumnName = “SaleDate”;
prodSalesData.Columns.Add(dateColumn);

I wanted to contain this in one helper method in order to avoid a lot of repetition.  So I created this object/method to handle it:

public static class DataSetHelpers
{
    public static void AddDataColumn(this DataTable dataTable, 

       string fieldName, string dataType)
    {
        DataColumn dataColumn = new DataColumn();
        dataColumn.DataType = Type.GetType(dataType);
        dataColumn.ColumnName = fieldName;
        dataTable.Columns.Add(dataColumn);
    }
}

Inside my ADODatabaseContext object, I added this method:

public void BulkInsert(DataTable dataTable)
{
    using (SqlBulkCopy s = new SqlBulkCopy(_db))
    {
        s.DestinationTableName = dataTable.TableName;
        s.BulkCopyTimeout = CommandTimeOut;

        foreach (var column in dataTable.Columns)
        {
            s.ColumnMappings.Add(column.ToString(), 
               column.ToString());
        }

        s.WriteToServer(dataTable);
    }
}

Now the bulk insert consists of two parts: The DataTable definition and the DataSet populate.  You can create a custom method in your code to populate each row with one method call.  This would be specific to the table you are populating, I’m going to leave that part expanded.  Here’s the object I used to bulk insert into a table named “Store”:

public class CreateStoreRecords
{
    private DataTable StoreData;


    private void SetupDataTable()
    {
        StoreData = new DataTable(“Store“);

        StoreData.AddDataColumn(“Name“, “System.String“);
        StoreData.AddDataColumn(“Address“, “System.String“);
        StoreData.AddDataColumn(“City“, “System.String“);
        StoreData.AddDataColumn(“State“, “System.String“);
        StoreData.AddDataColumn(“Zip“, “System.String“);
    }

    public void InsertRecords()
    {
        SetupDataTable();

        using (var db = new ADODatabaseContext(“Server=sqlserver;
          Integrated Security=True“))
        {
            DataRow StoreList = StoreData.NewRow();
            StoreList[“Name“] = “Toys R Us“;
            StoreList[“Address“] = “123 Main St“;
            StoreList[“City“] = “Chicago“;
            StoreList[“State“] = “IL“;
            StoreList[“Zip“] = “12345“;
            StoreData.Rows.Add(StoreList);

            StoreList = StoreData.NewRow();
            StoreList[“Name“] = “Target“;
            StoreList[“Address“] = “5th Ave“;
            StoreList[“City“] = “New York“;
            StoreList[“State“] = “NY“;
            StoreList[“Zip“] = “33333“;
            StoreData.Rows.Add(StoreList);

            db.BulkInsert(StoreData);
        }
    }
}

If you want to create the store table in MS SQL Server to match my sample code, then you can use this code in a query window to generate it:

CREATE TABLE [dbo].[Store](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NULL,
    [Address] [varchar](50) NULL,
    [City] [varchar](50) NULL,
    [State] [varchar](50) NULL,
    [Zip] [varchar](50) NULL,
 CONSTRAINT [PK_Store] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON
[PRIMARY]
) ON [PRIMARY]

You’ll have to replace the connection string to match your server and access rights (assuming you’re not using integrated security).

Where to Get The Code

You can download the sample code from above at my GitHub account by clicking here.