Dapper – ORM

There are many ORM tools available. One of them is Dapper. Dapper is known as the king of ORM.

The following are the key features of Dapper:

• Speed and fast in performance.
• Fewer lines of code.
• Object Mapper.
• Static Object Binding.
• Dynamic Object Binding.
• Easy Handling of SQL Query.
• Easy Handling of Stored Procedure.
• Operating directly to the IDBConnection class that provides smoothness and running queries directly to the database instead of passing data using various objects as we do in EF and ADO.NET.
• Multiple Query Support.
• Support for Stored Procedure.
• Bulk Data insert functionality.
• Dapper also allows fetching multiple data based on multiple inputs.

Why Dapper

• Dapper is the second-fastest ORM.

Image reference: Dapper dotnet.

• Perform CRUD operations directly using the IDBConnection object.
• Provide querying static and dynamic data over the database.
• Get generic results for simple or complex data types.
• Dapper allows storing bulk data at once.

How to Install Dapper

There are two ways to install Dapper:

1. DapperMappingFileForC#4.5.cs.

Add this SqlMapperAsync.cs file to your project and get started with Dapper functionality.

You can SqlMapperAsync.cs depending on the .Net framework you use. 

• NuGet Package Manager.

In Visual Studio, create a new console project, and in Solution Explorer right-click References and select Manage NuGet Package Manager and search for Dapper and using the NuGet Package Manager Console command for the Nugget Package Manager “install-package dapper”, and this will install Dapper into your project.

How Dapper Works

“Dapper Majorly Include Three Steps”

Step 1

Create an IDBConnection object with Connection String.

Step 2

Write a query and store it in a normal string variable.

Step 3

Call db.execute() and pass the query and it’s done.

There are many other ways as well that we will explore in the following example.

For this example, I have one database named “ContactDB” and include one table called Contacts. Using Dapper let’s perform CRUD operations on this contacts table.

Create a Console application and install Dapper and include the namespace for Dapper.

Example 1

Let’s first start by getting all the data and printing it onto the console.

So:

Step 1

Create an object of an IDbConnection class and a new instance of SqlConnection.

1. static IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings[“SqlServerConnString”].ConnectionString);  

SqlServerConnString is a connection string name that you can write in the app.config of your console application.

1. <connectionStrings>  
2.    <add name=”SqlServerConnString” providerName=”System.Data.SqlClient” connectionString=”Data Source=Friyank\MSSQLSERVER2012;Initial Catalog=ContactDB;Integrated Security=True;MultipleActiveResultSets=True” />  
3. </connectionStrings>  

Step 2

Write a Query and store it into a string.

1. String query = “select * from contacts”;  

Step 3

Fire a query on db instance and typecast the generic return type into a list of contacts.

1. (List<Contact>)db.Query<Contact>(query);  

You have successfully retrieved all the data from the contact table just by using these three simple steps to populate this data wherever you need it.

Print the entire list onto Grid view of a Windows Forms form or WPF or pass the entire List of data to any external WCF.

For demo purposes, I will just print this data onto the console.

Example 2

Now let’s insert data into a Contact table. This also includes the same three steps.

Step 1

Create an object of the IDbConnection class and a new instance of SqlConnection.

1. static IDbConnection db. = new SqlConnection(ConfigurationManager.ConnectionStrings[“SqlServerConnString”].ConnectionString);  

Step 2

1. string query = @”Insert into contacts values (@FirstName, @LastName, @Email, @Company, @Title);  
2. Select Cast (Scope_Identity() as int)”;  

Here, we include two queries to get the last inserted id of the contact table.

Step 3

Now fire the query over the db instance and for the return type, we will get a single value and that will be an int. So, store it into an int variable and pass the contact object with the query as follows.

1. int id = db.Query<int>(query,contact).Single();  

And again in these three simple steps, the data will be inserted into the database.

Example 3

Now let’s get a single amount of data from the database contact table by passing an id in the where clause.

Step 1

Create an object of the IDbConnection class and a new instance of SqlConnection.

1. static IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings[“SqlServerConnString”].ConnectionString);  

Step 2

1. string query = “select * from contacts where id = @id”;  

Step 3

Now fire the query over the db instance and for the return type, we will get a single value and that will be a contact. So, store it into the contact object.

1. Contact cont = (Contact)db.Query<Contact>(query, new {id = id }).SingleOrDefault();  

Again in these three simple steps, data will be fetched from the database using id.

Note: In the same way we can update and delete records of the database.

We can even use db.Execute().

The main difference between db.query and db.execute is, in db.query we get a return value as we desired since it’s a generic method and db.execute is not a generic method so it always returns an int.

Working With Advanced Dapper 

• We may have a requirement to deal with multiple tables, for example querying more than two tables at once or fetching data based on the foreign key.
• We can access multiple tables at once in Dapper and that is also very smooth.
• Pass a list of objects and Dapper itself will identify the insertion as a bulk insert.
• Fetch data based on various parameters and Dapper will automatically convert the array into CSV and return all in a list of objects.

Example 4

Let’s say I have one more table called to address as in the following:

We will perform CRUD operations on both tables together using the same contact id as the primary key for the contact table and foreign key for the address table.

Now let’s get multiple data from the database contact table and database address table passing an id.

Step 1

Create an object for the IDbConnection class and a new instance of SqlConnection.

1. static IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings[“SqlServerConnString”].ConnectionString);  

Step 2

Now create a string for multiple queries.

1. string query = “select * from contacts where id = @id ; select * from addresses where ContactId = @id;”;  

Step 3

Now fire a query on db instance as follows:

1. using (var multipleresult = db.QueryMultiple(query, new { id = id }))  
2. {  
3.    var contact = multipleresult.Read<Contact>().SingleOrDefault();  
4.    var Addresses = multipleresult.Read<Address>().ToList();  
5.    if (contact != null && Addresses != null)  
6.    {  
7.       contact.Addresses.AddRange(Addresses);  
8.    }  
9. }  

And it’s done, you have successfully retrieved multiple records from multiple tables just using three steps.

Example 5

Let’s use a Stored Procedure for data access from the database.

I have one Stored Procedure that excepts one parameter (ID) and returns contact data and address data based on that ID.

1. USE [ContactDB]  
2. GO  
3. /****** Object:  StoredProcedure [dbo].[sp_GetContact_Address]    Script Date: 3/3/2015 3:29:25 PM ******/  
4. SET ANSI_NULLS ON  
5. GO  
6. SET QUOTED_IDENTIFIER ON  
7. GO  
8. Create proc [dbo].[sp_GetContact_Address]  
9.  @id int   
10. as  
11. begin   
12. select * from contacts where id = @id ;   
13. select * from addresses where ContactId = @id;  
14.   
15. end  

To call a Stored Procedure using Dapper.

Step 1

Create an object for the IDbConnection class and a new instance of SqlConnection.

1. static IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings[“SqlServerConnString”].ConnectionString);  

Here we will not write any query and then pass it to the database.

Instead, we will call a Stored Procedure that is situated inside the database.

The changes we will do here is to pass a Stored Procedure name instead of query and will pass one additional parameter called command type.

Step 2

Now fire a query on db instance as follows:

1. using (var multipleresult = db.QueryMultiple(“sp_GetContact_Address”, new { id = id }, commandType: CommandType.StoredProcedure))  
2. {  
3.    var contact = multipleresult.Read<Contact>().SingleOrDefault();  
4.    var Addresses = multipleresult.Read<Address>().ToList();  
5.    if (contact != null && Addresses != null)  
6.    {  
7.       contact.Addresses.AddRange(Addresses);  
8.    }  
9. }  

And in just two simple steps, you have successfully made a call to a database Stored Procedure.

Example 6

We can also pass a dynamic object or values to a Stored Procedure when we need to deal with runtime objects.

The following is a sample Stored Procedure that adds a contact into the contact table:

1. create procedure [dbo].[SaveContact]  
2.     @Id         int output,  
3.     @FirstName  varchar(50),  
4.     @LastName   varchar(50),      
5.     @Company    varchar(50),  
6.     @Title      varchar(50),  
7.     @Email      varchar(50)  
8. AS  
9. BEGIN  
10. INSERT INTO [dbo].[Contacts]  
11.            ([FirstName],[LastName],[Company],[Title],[Email]) VALUES  
12.            (@FirstName, @LastName, @Company, @Title, @Email);  
13.         SET @Id = cast(scope_identity() as int)  
14. END;  

The following shows how to call a Stored Procedure using Dapper and pass dynamic values. 

Step 1

Create an object of the IDbConnection class and a new instance of SqlConnection.

1. static IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings[“SqlServerConnString”].ConnectionString);  

Step 2

Create a dynamic object and pass a value to that object.

1. var parameter = new DynamicParameters();  
2. parameter.Add(“@Id”,con.Id,dbType: DbType.Int32,direction:ParameterDirection.InputOutput);  
3. parameter.Add(“@FirstName”, con.FirstName);  
4. parameter.Add(“@LastName”, con.LastName);  
5. parameter.Add(“@Company”, con.Company);  
6. parameter.Add(“@Title”, con.Title);  
7. parameter.Add(“@Email”, con.Email);  

Step 3

Call a Stored Procedure using the db.execute method.

1. db.Execute(“SaveContact”,parameter,commandType:CommandType.StoredProcedure);  
2.   
3. //To get newly created ID back  
4. con.Id = parameter.Get<int>(“@Id”);  

Passing data to the Stored Procedure.

1. #region Insert Dynamic Object To Database  
2. dynamic c = new Contact();  
3. Program p = new Program();  
4. Console.WriteLine(“Enter First Name : “);  
5. c.FirstName = Console.ReadLine();  
6. Console.WriteLine(“Enter Last Name : “);  
7. c.LastName = Console.ReadLine();  
8. Console.WriteLine(“Enter Email Address : “);  
9. c.Email = Console.ReadLine();  
10. Console.WriteLine(“Enter Company Name: “);  
11. c.Company = Console.ReadLine();  
12. Console.WriteLine(“Enter Title : “);  
13. c.Title = Console.ReadLine();  
14. Console.WriteLine(“New Contact Created With ID {0} “, p.dynamicspcall(c).Id);  
15. #endregion  

And these are the ways to manipulate a database using C# dynamic objects.