Need help with querybuilder?
Click the “chat” button below for chat support from the developer who created it, or find similar developers for support.

About the developer

sqlkata
1.8K Stars 321 Forks MIT License 542 Commits 95 Opened issues

Description

SQL query builder, written in c#, helps you build complex queries easily, supports SqlServer, MySql, PostgreSql, Oracle, Sqlite and Firebird

Services available

!
?

Need anything else?

Contributors list

SqlKata Query Builder

Twitter

WE ARE NOT ACCEPTING NEW COMPILERS, if you want to add your own compiler, we recommend to create a separate repo like SqlKata-Oracle

Follow Twitter for the latest updates about SqlKata.

Quick Demo

SqlKata Query Builder is a powerful Sql Query Builder written in C#.

It's secure and framework agnostic. Inspired by the top Query Builders available, like Laravel Query Builder, and Knex.

SqlKata has an expressive API. it follows a clean naming convention, which is very similar to the SQL syntax.

By providing a level of abstraction over the supported database engines, that allows you to work with multiple databases with the same unified API.

SqlKata supports complex queries, such as nested conditions, selection from SubQuery, filtering over SubQueries, Conditional Statements and others. Currently it has built-in compilers for SqlServer, MySql, PostgreSql and Firebird.

The SqlKata.Execution package provides the ability to submit the queries to the database, using Dapper under the covers.

Checkout the full documentation on https://sqlkata.com

Installation

using dotnet cli

sh
$ dotnet add package SqlKata

using Nuget Package Manager

sh
PM> Install-Package SqlKata

Quick Examples

Setup Connection

var connection = new SqlConnection("...");
var compiler = new SqlCompiler();

var db = new QueryFactory(connection, compiler)

QueryFactory
is provided by the SqlKata.Execution package.

Retrieve all records

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

Retrieve published books only

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

Retrieve one book

var introToSql = db.Query("Books").Where("Id", 145).Where("Lang", "en").First();

Retrieve recent books: last 10

var recent = db.Query("Books").OrderByDesc("PublishedAt").Limit(10).Get();

Include Author information

var books = db.Query("Books")
    .Include(db.Query("Authors")) // Assumes that the Books table have a `AuthorId` column
    .Get();

This will include the property "Author" on each "Book"

json
[{
    "Id": 1,
    "PublishedAt": "2019-01-01",
    "AuthorId": 2
    "Author": { // 

Join with authors table

var books = db.Query("Books")
    .Join("Authors", "Authors.Id", "Books.AuthorId")
    .Select("Books.*", "Authors.Name as AuthorName")
    .Get();

foreach(var book in books) { Console.WriteLine($"{book.Title}: {book.AuthorName}"); }

Conditional queries

var isFriday = DateTime.Today.DayOfWeek == DayOfWeek.Friday;

var books = db.Query("Books") .When(isFriday, q => q.WhereIn("Category", new [] {"OpenSource", "MachineLearning"})) .Get();

Pagination

var page1 = db.Query("Books").Paginate(10);

foreach(var book in page1.List) { Console.WriteLine(book.Name); }

...

var page2 = page1.Next();

Insert

int affected = db.Query("Users").Insert(new {
    Name = "Jane",
    CountryId = 1
});

Update

int affected = db.Query("Users").Where("Id", 1).Update(new {
    Name = "Jane",
    CountryId = 1
});

Delete

int affected = db.Query("Users").Where("Id", 1).Delete();

We use cookies. If you continue to browse the site, you agree to the use of cookies. For more information on our use of cookies please see our Privacy Policy.