Announcing SqlKata an Elegant Sql Query Builder for C#

SqlKata the dotnet sql query builder

sqlkata/querybuilder

Photo by chuttersnap on Unsplash

SqlKata is an elegant Sql Query Builder for C#, it helps you to talk with your database engine with a higher order of freedom, it allows you to write complex queries in an Object Oriented Manner, helpful when you need:

Complex Reports using subqueries, join, union, CTE and nested conditions

Database Abstraction dealing with multiple database vendors, write your query once, and SqlKata will compile it to the needed Database

Currently it support SqlServer, MySql and Postgres, however other database can still be used like Sqlite, but not tested officially by the team

Dynamic Queries building your queries on runtime is a must when you have an interactive dashboard or dynamic reports.

Building dynamic SQL query

Speed is a matter Unlike Entity Framework, SqlKata doesn’t know about your entities, and doesn’t keep track of them, so no additional memory consumption, just fetch and send, it uses Dapper for the Query Execution so performance here is unbeatable

Check it out https://github.com/sqlkata/querybuilder

Installation

$ dotnet add package SqlKata

or if you are using the Nuget Package Manager

PM> Install-Package SqlKata

Fetching data

The simplest way to get data is to pass the table name in the Query function

var books = db.Query("Books").Get();

After that you can add more Where filters

var englishBooks = db.Query("Books").Where("Lang", "en").Get();

WhereIn

var langs = new [] { "en", "fr" };
var ltrBooks = db.Query("Books").WhereIn("Lang", langs).Get();

Search By Id

var book = db.Query("Books").Where("Id", 100).First();

Recent Books

var books = db.Query("Books").OrderByDesc("PublishDate").Limit(10).Get();

Update example

var affected = db.Query("Books").Where("Score", ">", 1000).Update(new {
  Score = 1001
});

Delete example

var affected = db.Query("Books").WhereNull("AuthorId").Delete();

Insert examples

db.Query("Books").Insert(new {
  Title = "Intro to C#",
  AuthorId = 1,
});