A Developer’s Repetition and the need for ORMs
Software development is sometimes a repetitive process. Developers are often asked to develop applications (or APIs) which are, initially, efficient enough for releasing an MVP (Minimum Viable Product) or for demonstrating a POC (Proof of Concepts) with strict release deadlines. Once these initial stages are passed and as the application evolves further, the focus is then shifted on optimizing performance, resulting in rewriting or redesigning the application layers and their interactions.
When developing applications under such conditions, its natural to look for frameworks and libraries which help in faster integration with underlying data sources such as a database without having to spend more time on them. Object Relational Mappers or ORMs are such frameworks which help developers focus on building business logic in their applications, while the interaction with the underlying databases are handled by these frameworks.
In the space of ASP.NET Core, developers generally look onto using Entity Framework Core, an ORM that helps developers integrate interactions with underlying databases without having to worry about writing these interactions in a language understood by these databases (eg. SQL).
Developers work with the entity objects in one language (eg. C#) while the EF Core takes up the heavy-lifting of converting these interactions into their equivalent syntaxes.
However this convenience does come up with some performance cost, as there can be a significant performance overhead when not properly optimized according to the requirement.
For example, EF Core comes with tracking feature where any set of data queried from the database is tracked continuously for changes and any changes made are written back to the database upon calling the SaveChanges() call.
While this may be convenient for CRUD operations, this might not be required in cases where the requirement is just to query the database and nothing much.
Also, configuring EF Core is not a straight-forward affair – EF Core relies on Entities-To-Tables mappings based on migrations, which can be a developer nightmare when something in database schema changes without it being available on the application side.
But that doesn’t mean writing interactions with the databases directly using native libraries such as ADO.NET or ODBC is easier. They are a bit tedious (but faster) and require a little more effort on mapping the results of a query or a command into equivalent business objects that the application understands.
Sometimes all we require is a middle-ground between a full fledged ORM (like EF Core, for example) and the native way of SQL integrations (such as SQL ODBC connections).
This paves way to micro-ORMs, which work as an object mapper over the database queries or commands that the application runs and returns results in the form of class objects that can be easily integrated with the higher-level functionalities.
In this article, let’s talk about Dapper – one of the most popular micro-ORM. Dapper is an open-source micro-ORM designed to work with dotnet technologies that promises a higher performance than the traditional ORMs.
Dapper works on the native SQL drivers for respective databases and maps the query results with the passed class types.
Getting Started with Dapper – Installation and Integration
To get started, we’d just install dapper in our application as below:
> dotnet add package Dapper
As mentioned before, Dapper works on the native SQL driver based on the underlying database. So we need to ensure that the respective SQL library is installed for the database. This is similar to the story in the good-old ADO.NET days.
In this example, we shall use an SQLite database. To create an SQLite database, just install sqlite3 for your OS from the official website and then run the below command. This creates a database file app.db, where you can store all your tables and other database stuff. The application interaction doesn’t change much – except that the underlying connection that we’re using to connect. The GetConnection() method in the below code explains the same – given are examples to connect to SQL Server and MySql as well.
> sqlite3 app.db
Interacting via Dapper
As mentioned before, Dapper is a micro-ORM and doesn’t come with any heavylifting features. All the interactions with the database are to be written in SQL and Dapper helps in mapping the input/output between SQL and C# types. Let’s start by creating an interface that templates the interactions of the Dapper.
public interface IDapperr
{
IEnumerable<T> Query<T>(string sql, DynamicParameters dp, CommandType commandType = CommandType.Text);
int Execute(string sql, DynamicParameters dp, CommandType commandType = CommandType.Text);
}
Here we define two operations, that covers majority of the interactions – Queries and Commands. Queries are for retrieving data that result in datasets and Commands are for modifying datasets inside the database which results in a summary of the operation.
The implementation of this class is what makes Dapper a bit different from the traditional ORM.
public class Dapperr : IDapperr
{
private IDbConnection GetConnection()
{
// for SQLite connections
// install Microsoft.Data.Sqlite nuget package
return new SqliteConnection("Data Source=app.db");
// for MySql connections
// install MySql.Data nuget package
// return new MySqlConnection("Server=localhost;Port=3306;Uid=admin;Password=Abcd$1234;Database=mydb");
// for SQL Server connections
// install Microsoft.Data.SqlClient nuget package
// return new SqlConnection("Server=localhost;User name=admin;Password=Abcd$1234;Initial Catalog=mydb")
}
public int Execute(string sql, DynamicParameters dp, CommandType commandType = CommandType.Text)
{
int result;
// get connection
using IDbConnection connection = GetConnection();
if (connection.State == ConnectionState.Closed)
connection.Open();
try
{
// start transaction
using var transaction = connection.BeginTransaction();
try
{
// execute command within transaction
result = connection.Execute(sql, dp, transaction);
// commit changes
transaction.Commit();
}
catch (Exception)
{
// rollback if exception
transaction.Rollback();
throw;
}
}
catch (Exception)
{
throw;
}
finally
{
// finally close connection
if (connection.State == ConnectionState.Open)
connection.Close();
}
return result;
}
public IEnumerable<T> Query<T>(string sql, DynamicParameters dp, CommandType commandType = CommandType.Text)
{
using IDbConnection db = GetConnection();
if (db.State == ConnectionState.Closed)
db.Open();
try
{
return db.Query<T>(sql, dp, commandType: commandType);
}
catch (Exception)
{
throw;
}
finally
{
if (db.State == ConnectionState.Open)
db.Close();
}
}
}
In both the functionalities, we follow a standard four step procedure:
- Create a Connection
- Open the Connection
- Run the Query/Command
- Close the Connection
Dapper provides with two extension methods on the IDbConnection type, that is returned by the GetConnection() method as shown above. This GetConnection() method returns an instance of SqliteConnection since the db involved is SQLite.
The Query() method takes in an SQL statement that is expected to return dataset and maps the output dataset into the target entity type that is passed as type parameters.
The parameters that are to be substituted in the query are passed in the form of DynamicParameters object, which is a dictionary containing the parameter keys and values.
The Execute() method takes in similar parameters and returns an integer that is the number of rows afffected by the SQL statement passed.
Generally we wrap the Execute() method in a transaction so that in case of any exception, the transaction can be rolled back.
We can also wrap the Query method in a transaction, because the SQL statement can be a combination of an INSERT and a SELECT statement (sometimes, who knows.)
public IEnumerable<T> Query<T>(string sql, DynamicParameters dp, CommandType commandType = CommandType.Text)
{
IEnumerable<T> result;
using IDbConnection db = GetConnection();
if (db.State == ConnectionState.Closed)
db.Open();
try
{
// start transaction
using var transaction = db.BeginTransaction();
try
{
// execute query within transaction
result = db.Query<T>(sql, dp, commandType: commandType, transaction: transaction);
// commit changes
transaction.Commit();
}
catch (Exception)
{
// rollback if exception
transaction.Rollback();
throw;
}
}
catch (Exception)
{
throw;
}
finally
{
if (db.State == ConnectionState.Open)
db.Close();
}
return result;
}
Building the Interaction
Let’s assume we’re building a Heroes API that works on the table Heroes in a database (in our case, inside the app.db).
We’re assumed to build interactions on the database via the API on CRUD (Create, Update, Retrieve and Delete). Assume we’re following a Repository pattern and have designed our IHeroesRepository as below:
public interface IRepository<T>
{
IEnumerable<T> All(int page, int maxRecords);
T Create(T t);
void Delete(long id);
IEnumerable<T> SearchByName(string tName);
T Single(long id);
T Update(long id, T model);
}
public interface IHeroesRepository : IRepository<Hero> { }
Just to compare, an implementation of IHeroesRepository that interacts via EF Core looks like below. The implementation looks so simple and readable that anyone can understand what’s happening here.
That’s something made possible because of the capabilities of the LINQ and LINQ-to-SQL capabilities of the EF Core. But does it translate to a better performance is a different question.
namespace DapperHeroes.Core.Repositories
{
public class HeroesRepository : IHeroesRepository
{
private readonly HeroesContext _context;
public HeroesRepository(HeroesContext context)
{
_context = context;
}
public IEnumerable<Hero> All(int page, int maxRecords)
{
return _context.Heroes.AsNoTracking()
.OrderByDescending(x => x.Created)
.Skip((page - 1) * maxRecords).Take(maxRecords);
}
public Hero Create(Hero hero)
{
hero.Created = DateTime.Now;
_context.Heroes.Add(hero);
_context.SaveChanges();
return hero;
}
public void Delete(long id)
{
Hero index = _context.Heroes.Find(id);
if (index != null)
{
_context.Heroes.Remove(index);
_context.SaveChanges();
}
}
public IEnumerable<Hero> SearchByName(string heroName)
{
return _context.Heroes.AsNoTracking()
.Where(x =>
x.Name.Contains(heroName) || heroName.Contains(x.Name));
}
public Hero Single(long id)
{
return _context.Heroes
.AsNoTracking().Single(x => x.Id == id);
}
public Hero Update(long id, Hero model)
{
model.Id = id;
_context.Heroes.Attach(model);
_context.SaveChanges();
return model;
}
}
}
The Dapper implementation of the same IHeroesRepository looks like below. In the case of Dapper, We’re skipping the LINQ-to-SQL entirely and passing the actual statement in its SQL form along with the substituted parameter values to the underlying database via the Dapper library and Dapper takes care of mapping the result data against types.
namespace DapperHeroes.Core.Dapper.Repositories
{
public class DapperHeroesRepository : IHeroesRepository
{
private readonly IDapperr _dapperr;
public DapperHeroesRepository(IDapperr dapperr)
{
_dapperr = dapperr;
}
public IEnumerable<Hero> All(int page, int maxRecords)
{
var sql = @"
SELECT h.Id, h.Category, h.Created, h.HasCape, h.IsAlive, h.Name, h.Powers
FROM Heroes AS h
ORDER BY h.Created DESC
LIMIT @maxRecords OFFSET @page";
var dp = new DynamicParameters(new { @maxRecords = maxRecords, @page = page });
return _dapperr.Query<Hero>(sql, dp);
}
public Hero Create(Hero hero)
{
var sql = @"
INSERT INTO Heroes (
Name,
Category,
HasCape,
IsAlive,
Powers,
Created)
VALUES
(
@name, @category, @hasCape, @isAlive, @powers, @created
);
SELECT h.* FROM Heroes h WHERE h.Id = last_insert_rowid();
";
var dp = new DynamicParameters(new
{
@name = hero.Name,
@category = hero.Category,
@hasCape = hero.HasCape,
@isAlive = hero.IsAlive,
@powers = hero.Powers,
@created = DateTime.Now
});
return _dapperr.Query<Hero>(sql, dp).FirstOrDefault();
}
public void Delete(long id)
{
var sql = @"DELETE FROM Heroes WHERE Id = @id";
_dapperr.Execute(sql, new DynamicParameters(new { id }));
}
public IEnumerable<Hero> SearchByName(string heroName)
{
var sql = @"SELECT h.* FROM Heroes h WHERE h.NAME LIKE %@heroName%";
var dp = new DynamicParameters(new { heroName });
return _dapperr.Query<Hero>(sql, dp);
}
public Hero Single(long id)
{
var sql = @"SELECT h.* FROM Heroes h WHERE h.Id == @id";
var dp = new DynamicParameters(new { id });
return _dapperr.Query<Hero>(sql, dp).FirstOrDefault();
}
public Hero Update(long id, Hero hero)
{
var sql = @"
UPDATE Heroes
SET Name = @name,
Category = @category,
HasCape = @hasCape,
IsAlive = @isAlive,
Powers = @powers;
SELECT h.* FROM Heroes h WHERE h.Id = @id";
var dp = new DynamicParameters(new
{
@id = id,
@name = hero.Name,
@category = hero.Category,
@hasCape = hero.HasCape,
@isAlive = hero.IsAlive,
@powers = hero.Powers
});
return _dapperr.Query<Hero>(sql, dp).FirstOrDefault();
}
}
}
Finally, we just need to register the Dapperr and HeroesRepository implementations on the container. Dapper doesn’t require a DbContext and its registration, like as in EFCore.
// either EFCore implementation
// or Dapper implementation shall work
/*
services.AddDbContext<HeroesContext>(options => {
options.UseSqlite("Data Source=app.db;");
});
services.AddScoped<IHeroesRepository, HeroesRepository>();
*/
services.AddScoped<IDapperr, Dapperr>();
services.AddScoped<IHeroesRepository, DapperHeroesRepository>();
Conclusion
Why should we consider Dapper?
The concept of ORM was created to facilitate using the class objects to interact with the underlying database without having to worry about the translation.
This was a lot comfortable than working with ADO.NET, which meant working with Data Readers and Adapters – where the translation into business objects was a bit painful.
On the other hand, fully featured ORMs couple databases with the application so much that any scope for database optimization or query optimization has to be done on the application side rather than on the database (although using Stored Procedures instead of LINQ-to-SQL can solve this to an extent).
Dapper’s approach to this is that developers focus on the business logic in their application, while Dapper takes care of mapping database objects to class objects, while still decoupling the database from the application.
Also, Dapper is lightweight when compared to the other ORMs and adds a layer of extensibility over the native SQL libraries, which in a sense is both efficient and simple.
This results in scalable applications and databases where the database side is decoupled so that it can be managed and optimized without worrying about impact on the applications.
Code: https://github.com/referbruv/aspnetcore-dapper-heroes-example
Found this article helpful? Please consider supporting!
I’m curious, what is the purpose of your transactions here? Transactions should affect the entire method in order to be effective