Hello World in Dapper

Hello World in Dapper

When it comes to use an ORM tool for a .NET platform, there are various options. Most of you will think EF as a first option, some of you will think NHibernate as an alternative.
Both of them are mature products with a top-notch functionality. But sometimes learning curve of these products can be overwhelmed.

In this article I would like to present you an alternative to above-mentioned biggest players on the market. Dapper ORM is the product. It is positioned as a Micro ORM – it doesn’t have all the functionality from its bigger brothers, but it is fast and stable with by far less learning curve and you can still produce high quality solutions. I find it quite useful for a small projects when you need to code something quickly but still have ORM tool in your solution. I find it also very useful for .NET Core concept.

In this article I’ll show you how to create all 4 basic CRUD operations in Dapper. I created a really simple solution witch just one project (C#, Console Project), so no division into appropriate layers (Data layer, Business Layer, UI Layer, etc..) exists. Because I love reading books, so let’s create a solution that stores information about books.

Tutorial is divided into several steps:

Database side steps

On SQL Server Instance, create a database called LibraryDb (all with default settings just for simplicity), create a table Book and insert some data:

CREATE DATABASE LibraryDb

CREATE TABLE dbo.Book
(
 BookId int IDENTITY(1,1),
 Name varchar(250),
 AuthorFirstName varchar(100),
 AuthorLastName varchar(100),
 YearOfPublication date
);


INSERT INTO dbo.Book 
(Name, AuthorFirstName, AuthorLastName, YearOfPublication) 
VALUES
('The Lord of The Rings', 'John', 'Tolkien', '19540729'),
('Dune', 'Frank', 'Herbert', '19650801'),
('Hyperion', 'Dan', 'Simmons', '19890526')

Application side steps, part 1 – set the stage

For the simplicity (after all, it is just a “Hello World” example), solution consists only one project of type Console Application. I named my solution Library.

1. Class Book

We need to create a class that represents our books. Create a class named Book:

class Book
{
  public int BookId { get; set; }
  public string Name { get; set; }
  public string AuthorFirstName { get; set; }
  public string AuthorLastName { get; set; }
  public DateTime YearOfPublication { get; set; }
}

 

Next, we need to create the whole logic of ORM manipulation. This can be done by using a concept of Repository. A repository is a kind of an interface between our Objects (O) and Relational data (R). We Map (M) them together, so then we have an ORM concept.

In order to create a Repository we need to have two main things:

  • an Interface with all defined and allowed manipulations on the particular Repository (point no. 2)
  • a Repository that implements all manipulations from its Interface (point no. 3)

2. Interface IBookRepository

Create a new interface named IBookRepository:

interface IBookRepository
{

    List<Book> GetBooks();
    Book GetSingleBook(int bookId);
    bool InsertBook(Book insertedBook);
    bool DeleteBook(int bookId);
    bool UpdateBook(Book updatedBook);

}

These are the methods that are used to manipulate with the data:

  • GetBooks() – returns a list with all books (that is why the returned value is a collection of Books – List<Books>)
  • GetSingleBook(int bookId) – returns only a single book with particular bookId (this is why a returned value is Book)
  • InsertBook(Book insertedBook) – inserts a new book. When insertion is succeeded it returns true, otherwise – false
  • DeleteBook(int bookId) – deletes an existing book with particular bookId. When deletion is succeeded it returns true, otherwise – false
  • UpdateBook(Book updatedBook) – updates a book. When update is succeeded it returns true, otherwise – false

What is worth mentioning is having these methods we are able to implement all CRUD database methods, because:

Method name CRUD operation
GetBooks() Retrieve
GetSingleBook(int bookId) Retrieve
InsertBook(Book insertedBook) Create
DeleteBook(int bookId) Delete
UpdateBook(Book updatedBook) Update

3. Create a Repository BookRepository

In this step we create a Repository, that implements all methods from it’s interface.
Create a new class called BookRepository and implement the interface IBookRepository on this class.
So, the initial result is like this:

class BookRepository : IBookRepository
{
    public bool DeleteBook(int bookId)
    {
        throw new NotImplementedException();
    }

    public List<Book> GetBooks()
    {
        throw new NotImplementedException();
    }

    public Book GetSingleBook(int bookId)
    {
        throw new NotImplementedException();
    }

    public bool InsertBook(Book insertedBook)
    {
        throw new NotImplementedException();
    }

    public bool UpdateBook(Book updatedBook)
    {
        throw new NotImplementedException();
    }
}

The first part is done, all the relevant files (2 classes and 1 interface) were created:

File name Functionality
Book.cs Class that represents our book
IBookRepository.cs Interface with all allowed methods for BookRepository repository
BookRepository.cs Class that implements ORM logic

The structure of the solution looks like this:

Solution structure after adding all necessary files
Solution structure after adding all necessary files

 

Application side steps, part 2 – Install Dapper

As we are going to use Dapper, we must add this library to our project. Dapper is delivered as NuGet package. So, let’s use NuGet Package Manager Console and just add it to our project. In Visual Studio navigate to: Tools -> NuGet Package Manager -> Package Manager Console

In the console type:

Install-Package Dapper

Here is the process of installation:

How to install Dapper package by using NuGet
How to install Dapper package by using NuGet

You can verify If the package has been added by using Get-Package command (or also by looking If it is available in References section in Solution Explorer):

How to confirm If Dapper package was installed
How to confirm If Dapper package was installed

 

Application side steps, part 3 – Implement CRUD operations

In this part we are going to implement all CRUD operations in our BookRepository.

1. Add references to libraries:

using System.Data;
using System.Data.SqlClient;
using Dapper;

2. We must start with creating a way to connect to our database. Add this code to BookRepository.cs:

private static IDbConnection _db = null;

public BookRepository(string connectionString)
{
    _db = new SqlConnection(connectionString);
}

Here we have a constructor that has one parameter – a string that represents a connections string to our database. We use SqlConnection method to initiate a connection to our database.So our BookRepository class looks like:

class BookRepository : IBookRepository
{

    private static IDbConnection _db = null;

    public BookRepository(string connectionString)
    {
        _db = new SqlConnection(connectionString);
    }

3. Implement all CRUD methods

Here all CRUD methods are implemented.

GetBooks()

Modify GetBooks method by writing this piece of code:

public List<Book> GetBooks()
{

  string _sql = 
    @"
    SELECT BookId, Name, AuthorFirstName, AuthorLastName, YearOfPublication 
    FROM dbo.Book
    ";

  // return list of all Books
  return _db.Query(_sql).ToList();

}

GetSingleBook(int bookId)

Modify GetSingleBook method by writing this piece of code:

public Book GetSingleBook(int bookId)
{

  int _bookId = bookId;
  string _sql =
    @"
    SELECT BookId, Name, AuthorFirstName, AuthorLastName, YearOfPublication 
    FROM dbo.Book
    WHERE BookId = @BookId
    ";

  // return particular Book with BookId = bookId
  return _db.Query<Book>(_sql, new { bookId = _bookId }).FirstOrDefault();

}

InsertBook(Book insertedBook)

Modify InsertBook method by writing this piece of code:

public bool InsertBook(Book insertedBook)
{

  string _sql =
    @"
    INSERT INTO dbo.Book
    (Name, AuthorFirstName, AuthorLastName, YearOfPublication)
    VALUES 
    (@Name, @AuthorFirstName, @AuthorLastName, @YearOfPublication)
    ";

  try
  {

    // insert Book insertedBook
    _db.Query(_sql, insertedBook).SingleOrDefault();

  }
  catch(Exception e)
  { 
    return false; 
  }
  
  return true;
}

DeleteBook(int bookId)

Modify DeleteBook method by writing this piece of code:

public bool DeleteBook(int bookId)
{

  int _bookId = bookId;
  string _sql = 
    @"
    DELETE FROM dbo.Book 
    WHERE BookId = @BookId
    ";

  // delete Book with BookId = bookId and return number of deleted rows
  int deletedRows = _db.Execute(_sql, new { bookId = +bookId });

  return deletedRows > 0;

}

UpdateBook(Book updatedBook)

Modify UpdateBook method by writing this piece of code:

public bool UpdateBook(Book updatedBook)
{

  string _sql =
    @"
    UPDATE dbo.Book 
    SET 
      Name = @Name,
      AuthorFirstName = @AuthorFirstName,
      AuthorLastName = @AuthorLastName,
      YearOfPublication = @YearOfPublication
    WHERE BookId = @BookId
    ";

  // update Book with BookId = bookId and return number of updated rows
  int updatedRows = _db.Execute(_sql, updatedBook);

  return updatedRows > 0;

}

The whole C# code for each of files looks like:

Book.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Library
{
    class Book
    {
        public int BookId { get; set; }
        public string Name { get; set; }
        public string AuthorFirstName { get; set; }
        public string AuthorLastName { get; set; }
        public DateTime YearOfPublication { get; set; }
    }

}

IBookRepository.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Library
{
    interface IBookRepository
    {
        List<Book> GetBooks();
        Book GetSingleBook(int bookId);
        bool InsertBook(Book insertedBook);
        bool DeleteBook(int bookId);
        bool UpdateBook(Book updatedBook);
    }
}

BookRepository.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Dapper;

namespace Library
{
    class BookRepository : IBookRepository
    {

        private static IDbConnection _db = null;

        public BookRepository(string connectionString)
        {
            _db = new SqlConnection(connectionString);
        }

        public bool DeleteBook(int bookId)
        {
            int _bookId = bookId;
            string _sql = 
                @"
                DELETE FROM dbo.Book 
                WHERE BookId = @BookId
                ";

            // delete Book with BookId = bookId and return number of deleted rows
            int deletedRows = _db.Execute(_sql, new { bookId = +bookId });

            return deletedRows > 0;

        }

        public List<Book> GetBooks()
        {
            
            string _sql = 
                @"
                SELECT BookId, Name, AuthorFirstName, AuthorLastName, YearOfPublication 
                FROM dbo.Book
                ";

            // return list of all Books
            return _db.Query<Book>(_sql).ToList();

        }

        public Book GetSingleBook(int bookId)
        {
            int _bookId = bookId;
            string _sql =
                @"
                SELECT BookId, Name, AuthorFirstName, AuthorLastName, YearOfPublication 
                FROM dbo.Book
                WHERE BookId = @BookId
                ";

            // return particular Book with BookId = bookId
            return _db.Query<Book>(_sql, new { bookId = _bookId }).FirstOrDefault();

        }

        public bool InsertBook(Book insertedBook)
        {

            string _sql =
                @"
                INSERT INTO dbo.Book
                (Name, AuthorFirstName, AuthorLastName, YearOfPublication)
                VALUES 
                (@Name, @AuthorFirstName, @AuthorLastName, @YearOfPublication)
                ";
            
                try
                {

                    // insert Book insertedBook
                    _db.Query(_sql, insertedBook).SingleOrDefault();

                }
                catch(Exception e)
                { 
                    return false; 
                }

                return true;
        } 

        public bool UpdateBook(Book updatedBook)
        {

            string _sql =
                @"
                UPDATE dbo.Book 
                SET 
                    Name = @Name,
                    AuthorFirstName = @AuthorFirstName,
                    AuthorLastName = @AuthorLastName,
                    YearOfPublication = @YearOfPublication
                WHERE BookId = @BookId
                ";

            // update Book with BookId = bookId and return number of updated rows
            int updatedRows = _db.Execute(_sql, updatedBook);
            
            return updatedRows > 0;

            }
        }
}

Client side steps

All of the ORM CRUD operations were implemented in the above section. Now there is the last part – show that our code (with a big help from Dapper’s side) works. To do this, we just simple get information about books, insert a book, then update and delete it. Everything in Console Application for the simplicity.

1. Add these two “helper” methods to Program.cs file, just after Main method definition. These methods are purely for improving the readability of the code.

static void ShowBook(Book book)
{
    Console.WriteLine(String.Format("Book Id: {0} \nName: {1} \nAuthor: {2}, {3} \nDate of publication (YYYY-MM-DD): {4:yyyy-MM-dd} \n\n",
                            book.BookId,
                            book.Name,
                            book.AuthorLastName,
                            book.AuthorFirstName,
                            book.YearOfPublication));
}
    
static void ShowBooks(List<Book> books)
{
    foreach (Book book in books)
        Console.WriteLine(String.Format("Book Id: {0} \nName: {1} \nAuthor: {2}, {3} \nDate of publication (YYYY-MM-DD): {4:yyyy-MM-dd} \n\n",
                                            book.BookId,
                                            book.Name,
                                            book.AuthorLastName,
                                            book.AuthorFirstName,
                                            book.YearOfPublication));
}

2. Here you can find the whole content of a Program.cs file, so you can see how I implemented checking of all of these ORM CRUD operations:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

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

            // connection string to my SQL Server
            string connectionString = @"Server=MIDGARD;Database=LibraryDb;Trusted_Connection=True;";

            // create an instance of BookRepository
            BookRepository bookRepository = new BookRepository(connectionString);


            // 1. Get all books
            Console.WriteLine("----- SELECT all books -----");
            List<Book> books = bookRepository.GetBooks();
            ShowBooks(books);



            // 2. Get only book where BookId = 2
            Console.WriteLine("----- SELECT only a particular book -----");
            Book book = bookRepository.GetSingleBook(2);
            ShowBook(book);



            //3. Insert a new Book
            Console.WriteLine("----- INSERT a book -----");

            // Create a new instance of Book
            Book newBook = new Book
            {
                Name = "Solaris",
                AuthorFirstName = "Stanislaw",
                AuthorLastName = "Lem",
                YearOfPublication = new DateTime(1961, 5, 22)
            };

            bookRepository.InsertBook(newBook); // insert the book

            // and check If the book was inserted
            books = bookRepository.GetBooks();
            ShowBooks(books);



            // 4. Update a Book
            Console.WriteLine("----- UPDATE a book -----");

            // Create an instance of Book that to be updated
            Book updatedBook = new Book
            {
                BookId = 4, // we are updating Book with BookId = 4
                Name = "Solaris",
                AuthorFirstName = "Stanislaw",
                AuthorLastName = "Lem",
                // and we are changing YearOfPublication
                YearOfPublication = new DateTime(1977, 5, 22)
            };

            bookRepository.UpdateBook(updatedBook); // update the book

            // and check If the book was updated
            books = bookRepository.GetBooks();
            ShowBooks(books);



            // 5. Delete a Book
            Console.WriteLine("----- DELETE a book -----");

            bookRepository.DeleteBook(4); // delete the book

            // and check If the book was deleted
            books = bookRepository.GetBooks();
            ShowBooks(books);




            Console.WriteLine("After connection");
            Console.ReadKey();

        }

        

        // just a helper method, showing the details of the book in a more readable way
        static void ShowBook(Book book)
        {
            Console.WriteLine(String.Format("Book Id: {0} \nName: {1} \nAuthor: {2}, {3} \nDate of publication (YYYY-MM-DD): {4:yyyy-MM-dd} \n\n",
                                    book.BookId,
                                    book.Name,
                                    book.AuthorLastName,
                                    book.AuthorFirstName,
                                    book.YearOfPublication));
        }

        // just a helper method, showing the details of the book in a more readable way
        static void ShowBooks(List<Book> books)
        {
            foreach (Book book in books)
                Console.WriteLine(String.Format("Book Id: {0} \nName: {1} \nAuthor: {2}, {3} \nDate of publication (YYYY-MM-DD): {4:yyyy-MM-dd} \n\n",
                                                    book.BookId,
                                                    book.Name,
                                                    book.AuthorLastName,
                                                    book.AuthorFirstName,
                                                    book.YearOfPublication));


        }

    }

}

Bottom line

In the article I presented Dapper – an ORM, that is positioned as a Micro ORM. Is fast (you can check comparison on an official site), stable and can be used when you need to do something quick, maybe on a smaller project where all the functionality of mature products are not necessary.

Of course I know that the C# code should be with better quality (try/catch sections, etc…), but I didn’t want to add additional lines of code because it would only increase the complexity of the code and the real content (show how Dapper works) could be obfuscated.

 

 

 

Leave a Reply

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