Getting started with Dapper

Have you heard about ORM? And what about Micro-ORM?

We have a lot of software that needs to store data and the “traditional”, and even natural choice, is adopt some Relational Database. In order to simplify the communication with the database and to “glue” the world of Object Oriented (OO) programming languages with the structural nature of the relational data, developers like to use some Object-relational mapping (ORM) framework.These frameworks makes magic: Creating data schema, making queries with joins, protecting data injection, mapping tables to objects and so on…

All of it is amazing and really works. But it has a cost: performance!

Let calm us down. That performance loose is not a big deal. Usually we can not even feel that. So in almost all cases is a good choice have a good ORM framework doing the hard work for you. And your users will be happy to see your productivity and fast delivery.

In the other hand, there are cases that we need to spend a special attention to the performance factor. It is a quite common see in big systems, using ORM, some parts with specific SQL queries hard coded. It works like a compiled queries and transfer the data using DTO (Data Transport Objects).

Now, let´s try to find a balance between the worlds?

When we think in the buzzword of the moment, microservices, it will be easy see how to separate a critical portion of the software in a independent service. And if we try an approach using a “lite” ORM framework, to improve our coding productivity without sacrifice the performance!?

Dapper shows us that:

It is so fast that looses just to the native way to handle with the data, but honestly, that is quite a draw!

OK, I worked a lot with Entity Framework (EF) and NHibernate, they are amazing ORM frameworks, very helpful and quite power.

But Dapper goes straightforward: just mapping, making queries and executing simple commands in the database. And it has his secrets, like I heard from one of the Dapper developers, part of the code is wrote using IL, and there is a cache system.

OK, you don’t need to concern about that, lets see how it could help us. And I promise you, it will be easy!

First of all let get the data model from another post:

I’ll use a SQL Server database and run the follow script to create my tables:

CREATE TABLE [dbo].[BlogPosts] (
    [Id]          INT            IDENTITY (1, 1) NOT NULL,
    [Title]       NVARCHAR (MAX) NULL,
    [Description] NVARCHAR (MAX) NULL,
    CONSTRAINT [PK_dbo.BlogPosts] PRIMARY KEY CLUSTERED ([Id] ASC)
);

GO
CREATE TABLE [dbo].[Comments] (
    [Id]          INT            IDENTITY (1, 1) NOT NULL,
    [Message]     NVARCHAR (MAX) NULL,
    [BlogPost_Id] INT            NULL,
    CONSTRAINT [PK_dbo.Comments] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_dbo.Comments_dbo.BlogPosts_BlogPost_Id] FOREIGN KEY ([BlogPost_Id]) REFERENCES [dbo].[BlogPosts] ([Id])
);


GO
CREATE NONCLUSTERED INDEX [IX_BlogPost_Id]
    ON [dbo].[Comments]([BlogPost_Id] ASC);

Using Visual Studio, C# and our favorite friend to PoCs, a Console Application Project, I have to create the classes to represent the model.

using System.Collections.Generic;

namespace BlogPoc
{
    class Program
    {
        static void Main(string[] args)
        {
        }
    }

    public class Comment
    {
        public int Id { get; set; }
        public string Message { get; set; }
        public BlogPost BlogPost { get; set; }
    }

    public class BlogPost
    {
        public int Id { get; set; }
        public string Title { get; set; }
        public string Description { get; set; }
        public List<Comment> Comments { get; set; }
    }
}

What about some data for tests?

insert into BlogPosts (Title, Description) values ('First Post', 'This is the first Post');
insert into BlogPosts (Title, Description) values ('Second Post', 'This is the second Post');

insert into Comments (Message, BlogPost_Id) values ('Hello Word', 1);
insert into Comments (Message, BlogPost_Id) values ('Hello again', 1);
insert into Comments (Message, BlogPost_Id) values ('Just another comment', 2);

Now we can install Dapper, using the nugget command:

Install-Package Dapper

So, lets try to list the blog posts:

… using this code:

// special attention to the usings here!
using Dapper;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace BlogPoc
{
    class Program
    {
        // here I'm using a local MDF, setup it using your database config, so pay attention here!!!
        static string connectionString = @"Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\BlogTestDatabase.mdf;Initial Catalog=BlogTestDatabase;Integrated Security=True";

        static void Main(string[] args)
        {
            IEnumerable<BlogPost> posts;

            // normal/usual connection
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // the magic, a typed and simple query
                posts = connection.Query<BlogPost>("Select * From BlogPosts");
            }

            // showing the results
            foreach (var post in posts)
                Console.WriteLine($"{post.Title} - {post.Description}");

            Console.WriteLine();
            Console.WriteLine("end...");
            Console.ReadLine();
        }
    }

    // ... classes...
}

And below you can see a traditional ADO.NET code, just to compare:

    static void Main(string[] args)
    {
        List<BlogPost> posts = new List<BlogPost>();

        using (var connection = new SqlConnection(connectionString))
        using (var command = new SqlCommand("Select * From BlogPosts", connection))
        {
            connection.Open();
            var reader = command.ExecuteReader();

            while (reader.Read())
            {
                var post = new BlogPost();
                post.Title = reader.GetString(reader.GetOrdinal("Title"));
                post.Description = reader.GetString(reader.GetOrdinal("Description"));
                posts.Add(post);
            }
            
        }

        foreach (var post in posts)
            Console.WriteLine($"{post.Title} - {post.Description}");

        Console.WriteLine();
        Console.WriteLine("end...");
        Console.ReadLine();
    }
}

Honestly, ADO.NET is fast and useful, but brings a lot of details and complexity just for a simple query!

And Dapper can do interesting tricks, like a dynamic query:

static void Main(string[] args)
{
    // dynamic variable, that is not necessary have a typed query!
    dynamic posts;

    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();
        // with parameters
        posts = connection.Query("Select * From BlogPosts where Title like '%' + @title + '%'", new { title = "first" });
    }

    foreach (var post in posts)
        Console.WriteLine($"{post.Title} - {post.Description}");

    Console.WriteLine();
    Console.WriteLine("end...");
    Console.ReadLine();
}

If you take a look carefully, you will see that I used parameters in that one too!

What if we try to list comments and bring the post info as well (n to one relationship)?

static void Main(string[] args)
{
    IEnumerable<Comment> comments;

    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();

        // join comments with post
        comments = connection.Query<Comment, BlogPost, Comment > (
            "Select * From Comments c Join BlogPosts b on c.BlogPost_Id = b.Id", 
            (comment, post) => { comment.BlogPost = post; return comment; });
    }

    foreach (var comment in comments)
        Console.WriteLine($"{comment.Message}: {comment.BlogPost.Title} - {comment.BlogPost.Description}");

    Console.WriteLine();
    Console.WriteLine("end...");
    Console.ReadLine();
}

There is a whole new world of possibilities like query native types. You can query an int, for a select count something. But for these things, you can use the documentation, there is a lot of examples there.

Dapper can perform commands, like insert, in a very simple way.

static void Main(string[] args)
{
    var entity = new BlogPost {
        Title = "Post inserted by dapper",
        Description = "Bla bla bla..."
    };

    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();
        connection.Execute("insert BlogPosts (Title, Description) values (@Title, @Description)", entity);
    }
}

And that goes on and on, with the delete, update…

So, the idea here is just bring the curiosity to your mind and try to get you excited about a very simple way to do things with high performance.

I hope you enjoyed 😉

Spaki.

With more than 15 years of experience developing softwares and technologies, talking about startups, trends and innovation, today my work is focused to be CTO, Software Architect, Technical Speaker, Technical Consultant and Entrepreneur.

From Brazil, currently lives in Portugal working at https://www.farfetch.com as Software Architect, besides to keep projects in Brazil, like http://www.almocando.com.br/

Share

1 thought on “Getting started with Dapper”

Leave a Reply

Your email address will not be published. Required fields are marked *