Using Dapper Micro ORM in ASP.NET Core
Dapper is an open-source Micro ORM that runs on .NET and .NET Core Framework. It is simple and lightweight compared to HNibernate and Entity Framework. Dapper’s simplicity means it’s architected to focus on the most important task and many feature that fully featured ORMs ship with are stripped out. It worries about the 95% scenario, and gives you the tools you need most of the time. It doesn’t attempt to solve every problem.
Dapper is a NuGet library that you can add in to your project. It is compliant with .NET Standard 2.0, which means you can use it in .NET applications that target the .NET Framework and .NET Core.
Since It only provides subset of features provided by full-blown ORMs ,it is also known as ‘’Micro ORM”. The library allows developers quickly and easily access data from databases without the need to write tedious code. It can work across all the database platforms such as Microsoft SQL, SQL Lite, Oracle, MySQL, and PostgreSQL etc. Dapper allows you to execute raw SQL queries, map the results to objects, and execute stored procedures, among other things.
Why Dapper?(Key Features)
- It is one of fastest Micro-ORM.
- Drastically reduces the database access code.
- Work with multiple database – SQL Server, Oracle, SQLite, MySQL, PostgreSQL etc.
- Easy Handling of SQL Query/Stored Procedure.
- Single and Multiple Query Support.
- Allows fetching multiple data based on multiple inputs.
When Should You Use Dapper?
- Dapper is a good choice in scenarios where read-only data changes frequently and is requested often. It is particularly good in stateless scenarios (e.g. the web) where there is no need to persist complex object graphs in memory for any duration.
- Dapper does not translate queries written in .NET languages to SQL like a full-blown ORM.
- So you need to be comfortable writing queries in SQL or have someone write them for you.
- Dapper has no real expectations about the schema of your database. It is not reliant on conventions in the same way as Entity Framework Core, so Dapper is also a good choice where the database structure isn’t particularly normalized.
- Dapper works with an ADO.NET IDbConnection object, which means that it will work with any database system for which there is an ADO.NET provider.
- You can use both an ORM and a micro ORM in the same project.
How to Add Dapper Library to an ASP.NET Core Project?
1. Using NuGet Package Manager in Visual Studio follow the below mentioned steps to add dapper in your project
- Open package installer
- Select browse tab
- Enter Dapper
- Select dapper and click on Install button
2. Using .NET CLI run the following command to install the Dapper NuGet package in the Package Manager Console
PM> Install-Package Dapper
Using Dapper with MySql
Dapper supports a variety of database providers, including the popular Microsoft SQL Server, Oracle, MySQL, PostgreSQL, SQLite, etc. For example, when working with MySQL in .NET Core, you must install MySql.Data NuGet package.
Let’s see some examples of working with dapper using stored procedure/queries in MySql Database
Example 1: Querying Single Row using Query
using (IDbConnection db = new MySqlConnection(“Connection String”))
{
var userQuery = “SELECT Id, Name FROM User WHERE Id=@Id”;
//Use SingleOrDefault() method to return default value
//if no record is found
var user = db.Query<User>( userQuery,
new { Id = id },
commandType: CommandType.Text)
.SingleOrDefault();
}
We will execute a stored procedure that takes an id parameter and returns the User with that particular id.
Example 2: Querying Single Row using Stored Procedure
using (IDbConnection db = new MySqlConnection(“Connection String”))
{
//Initialize DynamicParameters object to pass parameters to
//stored procedure
DynamicParameters parameters = new DynamicParameters();
parameters.Add(“Id”, Id);
//Use SingleOrDefault() method to return default value
//if no record is found
var user = db.Query<User>(“usp_UserById”,
parameters,
commandType: CommandType.StoredProcedure)
.SingleOrDefault();
}
Example 3: Querying Multiple Rows using Stored Procedure
using (IDbConnection db = new MySqlConnection(“Connection String”))
{
var users = db.Query<User>(“usp_Users”,
null,
commandType: CommandType.StoredProcedure)
.ToList();
}
Example 4: Querying Multiple Results using stored procedure
using (IDbConnection db = new MySqlConnection(“Connection String”))
{
DynamicParameters parameters = new DynamicParameters();
parameters.Add(“Id”, Id);
var result = db.QueryMultiple(“usp_UserDetailById”,
parameters,
commandType: CommandType.StoredProcedure);
//UserDetail entity having properties Profile and Address
UserDetail detail = new UserDetail();
detail.Profile = result.Read<UserProfile>().SingleOrDefault();
detail.Addresses = result.Read<Address>().ToList();
}
Example 5: Handling database column which returns JSON value
Suppose we have a column in user table for persisting user’s Skill which is a JSON column and contains data like
[
{
“name”: “java”,
”proficiency”: “learner”
},
{
“name”: ”.NET”,
”proficiency”: ”skilled”
}
]
By default, Dapper does not support auto mapping of JSON to Object, we can solve this problem by using Custom Handler.
The Dapper has a way to add custom user-defined types. We can achieve it via the SqlMapper.TypeHandler. TypeHandlers are an option in Dapper to serialize / deserialize objects during saving and querying the database.
Let’s look at the implementation: Let’s say we have User and Skill model as shown below
public class User
{
public int Id { get; set; }
public string Name { get; set; }
public List< Skill > Skills { get; set;}
}
public class Skill
{
public string Name { get; set; }
public string Proficiency { get; set; }
}
Create a Custom Handler class which will convert JSON into Object
public class SkillHandler : SqlMapper.ITypeHandler
{
public object Parse(Type skill, object value)
{
return JsonConvert.DeserializeObject(value as string, skill);
}
public void SetValue(IDbDataParameter parameter, object value)
{
throw new NotImplementedException();
}
}
SetValue method returns serialized object to JSON string, handling also the null value. Parse method returns deserialized object from the database column.
Register the Custom Handler in Program.cs
SqlMapper.AddTypeHandler(typeof(Skill), new SkillHandler());
Now that we have configured our custom handler, let’s use it in our code to get JSON data from database.
using (IDbConnection db = new MySqlConnection(“Connection String”))
{
DynamicParameters parameters = new DynamicParameters();
parameters.Add(“Id”, Id);
var user = db.Query<User>(“usp_UserById”,
parameters,
commandType: CommandType.StoredProcedure)
.SingleOrDefault();
}
Result:
{
id: 1,
name: “Anshu”,
skills: [
{
name: “java”,
proficiency: “learner”
},
{
name: “.NET”,
proficiency: “skilled”
}
]
}
Conclusion
We’ve learned how to integrate Dapper in the ASP.NET Core project, and how to use queries and stored procedures with Dapper. Dapper is a good choice for applications that require easy data access. It is a perfect option for developers who need fast data access without the overhead of more complex ORM solutions. Dapper is compatible with .NET Framework and .NET Core.
About the Author
Anshu Kumar
(Software Engineer)