Best of Open Source Software Awards

How to work with Dapper in C#

Dapper

Dapper

Dapper is an open source, lightweight micro-ORM that simplifies data access while ensuring that your application is high-performant

RELATED TOPICS

Object relational mappers have been in use for a long time to eliminate the impedance mismatch that exists between the object and data models in an application. Dapper is an open source, lightweight ORM developed by the Stack Overflow team that's very fast compared to other ORMs primarily because of its light weight.

Dapper was built keeping performance and ease of use in mind. It provides support for both static and dynamic object binding, using transactions, using stored procedures and also bulk inserts of data.

Getting started

To get started using Dapper, follow these steps:

  1. Open Visual Studio 2015 IDE
  2. Click on File -> New -> Project
  3. Select "Web -> ASP.Net Web Application" from the "New Project" dialog
  4. Specify a name for the web project
  5. Select the empty project template for ASP.Net
  6. Click OK to save the project

This creates an empty ASP.Net Web application project.

If you have NuGet installed, you can install Dapper using NuGet -- just select the project in the Solution Explorer window, right click on "Manage NuGet Packages..." and then find Dapper. Once found, click on Install to start installation of Dapper. Once Dapper has been successfully installed, you are good to go.

Using Dapper

Let's now write some code using Dapper to perform CRUD operations against a database. Consider a database named IDG that contains a table called Author with the following fields.

  • ID
  • FirstName
  • LastName

You should create an entity class (POCO class) for this database table for simplicity when working with Dapper. Here's the entity class named Author that corresponds to the Author table in the IDG database.

public class Author

    {

        public int Id { get; set; }

        public string FirstName { get; set; }

        public string LastName { get; set; }

    }

The Query() extension method in the Dapper framework enables you to retrieve data from the database and populate data in your object model. The following method retrieves all the records from the Author table, stores them in memory and returns the collection.

public List<Author> ReadAll()

        {

            using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorks"].ConnectionString))

            {

                return db.Query<Author>

                ("Select * From Author").ToList();

            }

        }

Note that you should include the Dapper namespace in your program to leverage the Dapper framework.

The following method illustrates how you can search a particular record from the Author table.

public Author Find(int id)    

{

 using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings

["AdventureWorks"].ConnectionString))

   {

      return db.Query<Author>("Select * From Author " +

      WHERE Id = @Id", new { id }).SingleOrDefault();

   }

}

The Execute() method of the Dapper framework can be used to insert, update, or delete data into a database. This method returns an integer value that implies the number of rows that have been affected on execution of the query.

The following method illustrates how you can update a record using the Dapper framework.

public int Update(Author author)

{

  using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings

["AdventureWorks"].ConnectionString))

     {

    string sqlQuery = "UPDATE Author SET FirstName = @FirstName, " +

         " LastName = @LastName " + "WHERE Id = @Id";

         int rowsAffected = db.Execute(sqlQuery, author);

         return rowsAffected;

     }       

}

As you can see in the above code snippet, the Update() method returns the number of rows that have been affected, i.e., the number of records that have been updated. In this example, just one record has been updated and hence the method would return 1 on success.

To work with stored procedures using the Dapper framework, you should mention the command type explicitly when calling the Query or the Execute methods. Here's an example that shows how you can use stored procedures with Dapper.

 public List<Author> Read()

        {

            using (IDbConnection db = new SqlConnection

              (ConfigurationManager.ConnectionStrings

              ["AdventureWorks"].ConnectionString))

               {

                string readSp = "GetAllAuthors";

                return db.Query<Author>(readSp,

                commandType: CommandType.StoredProcedure).ToList();

               }

        }

The Dapper framework also supports transactions i.e., you can use transactional operations if needed. To do this, you can take advantage of the BeginTransaction() and EndTransaction() methods as you usually do when working with transactions in ADO.Net. You would then need to write your transactional statements inside the BeginTransaction and EndTransaction method calls.

This article is published as part of the IDG Contributor Network. Want to Join?

RELATED TOPICS
From CIO: 8 Free Online Courses to Grow Your Tech Skills
View Comments
Join the discussion
Be the first to comment on this article. Our Commenting Policies