SqlKata Query Builder

Jan 24 2023

How to setup and use SqlKata in a console application.

Overview

SqlKata is a fluent Sql query builder for C# according to their website (https://sqlkata.com/). It allows you to maintain tight control on the form of your Sql statements while still abstracting you away from the base database system (MS Sql Server, Postgres, etc.) Today we’ll setup a new console appplication that uses SqlKata to see it in action.

Create the Console Application

Make a directory for the application

mkdir sqlkata && cd sqlkata

Generate the new console application using the dotnet cli

dotnet new console --use-program-main

Open the new project in VS Code

code . -r

Add the necessary nuget packages

dotnet add package System.Data.SqlClient 
dotnet add package SqlKata.Net6.Execution
dotnet add package Newtonsoft.Json

Create a new sql server instance with docker (optional, you can use an existing instance if you wish)

sudo docker run --name mssql-server -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=YourSuperSecretPassword1!" -e "MSSQL_PID=Developer" -p 1433:1433 -d mcr.microsoft.com/mssql/server

Create a new database named “youtube” and add a new table

CREATE TABLE [dbo].[sample-table](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [varchar](50) NULL,
	[createdAt] [datetime] NULL,
 CONSTRAINT [PK_sample-table] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END

Add a record to the new table so that there is something to retrieve with the application

INSERT [dbo].[sample-table] ([name], [createdAt]) VALUES (N'sqlkata', CAST(N'2023-01-23T00:00:00.000' AS DateTime))
GO

Modify the main method in the Program.cs file to look like below (be sure and change the connection string to match your setup)

    static void Main(string[] args)
    {
        // create the db connection
        using var connection =
            new SqlConnection("data source=ubuntuserver,14333;initial Catalog=youtube;User ID=sa;password=YourSuperSecretPassword1!;");

        // create the sqlkata compiler for ms sql server and context
        var compiler = new SqlServerCompiler();
        var db = new QueryFactory(connection, compiler);

        // Create a new record in the database
        int affect = db.Query("sample-table").Insert(new {
            name = "Another Record",
            createdAt = DateTime.UtcNow
        });

        // better example of fully dynamic columns and values
        var cols = new [] {"name", "createdAt"};
        var data = new [] {
            new object[] { "A", DateTime.UtcNow },
            new object[] { "B", DateTime.UtcNow },
            new object[] { "C", DateTime.UtcNow },
        };
        db.Query("sample-table").Insert(cols, data);

        // build and execute the query
        var idFilter = 1;
        var records = db.Query("sample-table"). // .Where("id", idFilter) <-- if you want to add a where clause
            Limit(100).Get<SampleObject>().ToList();
            
        // loop through the results writing to the console
        foreach (var record in records)
        {
         var json = JsonConvert.SerializeObject(record, Newtonsoft.Json.Formatting.Indented);
         Console.WriteLine(json);
        }
    }

Add the following using statements to your Program.cs

using System.Data.SqlClient;
using Newtonsoft.Json;
using SqlKata.Net6.Compilers;
using SqlKata.Net6.Execution;

Run the application

dotnet run

Dalton Jones