Skip to main content

Dapper On Steroids: SqlKata

Dapper is a lightweight, high-performance ORM for .NET. It is widely used because of its simplicity and speed. Dapper in its essence provides simple mapping to POCO and works with raw queries minimizing the overhead. On the other hand it lacks of flexibility in terms of query writing, because we need to write queries like static strings. One simple example is shown in the code snippet below.

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

class Program
{
    static void Main(string[] args)
    {
        using (var connection = new SqlConnection("connection_string"))
        {
            connection.Open();

            string sql = "SELECT * FROM Companies";
            var companies = connection.Query<Company>(sql);
        }
    }
}


With the lack of flexibility in query construction, we would be in temptation to go for Entity Framework or some other heavy weight ORM, but there is also a simple library (SqlKata) which provides a decent flexibility in query construction and also keeps a full control over the generated query and we don't need to trade of the execution speed. 

The library itself is mostly based on Builder Design Pattern that is used for constructing queries. In the code snipet below is a simple example how to use the library.

var connection = new SqlConnection("connection string example");
var compiler = new SqlServerCompiler();
var db = new QueryFactory(connection, compiler);
var query = db.Query("Companies")
    .Select("Id", "Name")
    .Where("IsActive", true)
    .OrderBy("Name")
    .Limit(10);


The library also supports all other SQL comamnds, so it is worth to check it if you want to keep your query generation lighweight, simple and in your full control, what we cannot say for Entity Framework. Of course, this cannot be a full alternative to Entity Framework, but it can replace it in most cases.
Labels:

Comments

Post a Comment

Popular posts from this blog

Design Patterns: Singleton

Tyipically the first design pattern most people learn, often wrongly ☺ To give an introduction, we can say that singleton is one of the creational design patterns which ensures only one class instance with single point of access thru entire application.  Because it is relatively simple to implement, the Singleton pattern is sometimes misapplied in situations where it is not the most suitable choice. When to use it? Here are the few examples of corrent usage of singleton: Configuration Management  Centralized configuration settings for consistent use thru entire application Caching Maintaning  Single istance of cached objects for easy and fast acces Logging  Ensure unified mechanism to avoid duplication of log files, formats, etc Global State Management  Centralized management of the state which is needed to be shared accross the application Resource sharing  Thread pools, database connection, I/O operations When not to use it? On the other hand, here are fe...
Post a Comment
Read more

Design Patterns: Builder

This is also, like a Singleton , one of the creational design patterns. It provides the way of creating complex objects step by step by simple chaining and every particular step is independent of other steps. Let us dive into the real example of usage. For showing purpose we have created an example in C# which creates simple SQL queries using described pattern.  using System; using System.Text; namespace BuilderPatternExample { public interface ISqlQueryBuilder { ISqlQueryBuilder Select(string columns); ISqlQueryBuilder From(string table); ISqlQueryBuilder Where(string condition); ISqlQueryBuilder OrderBy(string columns); string Build(); } public class SelectQueryBuilder : ISqlQueryBuilder { private readonly StringBuilder _queryBuilder; public SelectQueryBuilder() { _queryBuilder = new StringBuilder(); } public ISqlQueryBuilder Select(string columns) { ...
Post a Comment
Read more

Why Do Employers Lie In Interviews?

This is a very common subject that many of us have already experienced. But when you realize that half of what has been said at interviews is actually a lie, you are already at least six months in the company, you have already started some project and it wouldn’t be appropriate to leave the company at that moment. Why is this happening? First of all, let us see how the usual interview process looks like in software development companies. First round interview in most of these companies is an interview with HR. This is the first insight about the company. A person who works in HR is usually someone who, in most cases, doesn’t understand what the software is and how the software development process goes. Big respect to those companies where HR knows these things. This phase usually contains some standard questions about your personality, what do you like about the company, how this company is something that you are actually looking for, where you see yourself in five/ten  years etc… ...
Post a Comment
Read more