Overview
KnightMoves.SqlObjects is a .NET NuGet package library that implements an SQL builder based on objects. Other SQL builders rely on string manipulation, such as concatenation and interpolation, but the methodology in this library is different. Instead of printing SQL strings, this library wraps the SQL language syntax with C# objects so that the entirety of a SQL query is made up of objects. This allows for a much more powerful experience and set of capabilities that string manipulators cannot provide.
The full documentation can be found here, but for this article, we will examine its most basic yet most important feature, which is Syntax Matching.
Syntax Matching
Some ORMs and SQL builders use method names that are similar but different from the SQL language. This library matches the SQL syntax almost exactly, with some minor exceptions. The strategy for this library is that when you're using the SQL builder, you are able to think and code in SQL instead of trying to remember the new terminology of the SQL builder.
Let's dive in with some examples.
Examples
First, create a simple Console application in Visual Studio and add KnightMoves.SqlObjects NuGet package library from https://nuget.org.
Once you have a basic console application generated, you can add your code to the Main() method of the Program.cs file.
Start with importing the namespace.
using KnightMoves.SqlObjects;
Next, you add the code below to the Main() method.
The fluent SQL builder is available through the static TSQL class so you can begin there and code as much as though you’re coding in SQL.
var sql = TSQL
.SELECT()
.STAR()
.FROM("Products")
.Build()
;
Console.WriteLine(sql);
Run the application to see how the SQL is built. Here's the output:
SELECT
*
FROM [Products]
That used a basic SELECT * but there are various ways to specify the columns of the select list. The most basic way is to use the COLUMN() method for each column you specify.
var sql = TSQL
.SELECT()
.COLUMN("ProductID")
.COLUMN("ProductName")
.FROM("Products")
.Build()
;
Console.WriteLine(sql);
Here's the output:
SELECT
[ProductID],
[ProductName]
FROM [Products]
But we’re just getting started. You can provide a collection of column names and pass that to the COLUMNS() method (notice it is plural) and it will use those names to create the list of columns.
var columns = new List { "ProductID", "ProductName" };
var sql = TSQL
.SELECT()
.COLUMNS(columns)
.FROM("dbo", "Products", "p")
.Build()
;
Console.WriteLine(sql);
Output
SELECT
[ProductID],
[ProductName]
FROM [dbo].[Products] p
If you know SQL well, then you know that there are all manner of things you can do in the select list to make it a more robust query. This library handles them. Let’s start with a simple alias using .AS().
var sql = TSQL
.SELECT()
.COLUMN("ProductID").AS("Id")
.COLUMN("ProductName")
.FROM("Products")
.Build()
;
Output
SELECT
[ProductID] AS [Id],
[ProductName]
FROM [Products]
You can see it correctly produces the line [ProductID] AS [Id]
Do you need to specify the schema and a multipart identifier? Easy. Suppose you’re using dbo as the schema and p as an alias for the Products table. Then you can do so like this.
var sql = TSQL
.SELECT()
.COLUMN("p", "ProductID", "Id")
.COLUMN("p", "ProductName")
.FROM("dbo", "Products", "p")
.Build()
;
Console.WriteLine(sql);
Output
SELECT
[p].[ProductID] AS [Id],
[p].[ProductName]
FROM [dbo].[Products] p
You can also see an alternative to provide the alias. Instead of using .AS() you can provide the alias as a third parameter to the COLUMN() method.
It’s a pain to keep repeating the COLUMN() method call, and we know that we can use a collection of column names, but what if we need to prefix them with the table alias? Easy, we can do it like this.
var columns = new List { "ProductID", "ProductName" };
var sql = TSQL
.SELECT()
.COLUMNS("p", columns)
.FROM("dbo", "Products", "p")
.Build()
;
Console.WriteLine(sql);
Output
SELECT
[p].[ProductID],
[p].[ProductName]
FROM [dbo].[Products] p
The use of aliases becomes more important when you’re joining tables. So, let’s give that a try by joining Products and Categories.
var sql = TSQL
.SELECT()
.COLUMN("p", "ProductID")
.COLUMN("c", "CategoryName")
.FROM("dbo", "Products", "p")
.INNERJOIN("dbo", "Categories", "c").ON("c", "CategoryID").IsEqualTo("p", "CategoryID")
.Build()
;
Console.WriteLine(sql);
Output
SELECT
[p].[ProductID],
[c].[CategoryName]
FROM [dbo].[Products] p
INNER JOIN [dbo].[Categories] c ON [c].[CategoryID] = [p].[CategoryID]
If you need to join more tables, then all you have to do is slap another INNERJOIN() call exactly where you normally would if you’re coding in SQL with the schema and alias like so.
var sql = TSQL
.SELECT()
.COLUMN("p", "ProductID")
.COLUMN("p", "ProductName")
.COLUMN("c", "CategoryName")
.COLUMN("s", "CompanyName")
.FROM("dbo", "Products", "p")
.INNERJOIN("dbo", "Categories", "c").ON("c", "CategoryID").IsEqualTo("p", "CategoryID")
.INNERJOIN("dbo", "Suppliers", "s").ON("s", "SupplierID").IsEqualTo("p", "SupplierID")
.Build()
;
Console.WriteLine(sql);
Output
SELECT
[p].[ProductID],
[p].[ProductName],
[c].[CategoryName],
[s].[CompanyName]
FROM [dbo].[Products] p
INNER JOIN [dbo].[Categories] c ON [c].[CategoryID] = [p].[CategoryID]
INNER JOIN [dbo].[Suppliers] s ON [s].[SupplierID] = [p].[SupplierID]
Notice that throughout this demo, you can see that when you're using this library, you can think in SQL terms. Some things will deviate slightly, such as the use of COLUMN() instead of just literally typing in the column name where it belongs and later you’ll see that we use a fluent method call for operators such as IsEqualTo() instead of the = string character, but the thought process is the same. You're thinking in SQL even though you're coding in C#.
For further assistance, because the library is SQL in C# dressing, its methods, and signatures pop up in the intelicode features of the IDE, where you can search through the options to find what you're looking for easily.
![T-SQL Statement SELECT]()
We are barely scratching the surface here. The library implements all DML statements of Microsoft's T-SQL language, which is fully documented here: KnightMoves.SqlObject Documentation
Head on over there to get started and see what you can do with the basics. Stay tuned for other articles in this series, where we’ll cover more and more features of this robust library.
Thanks for reading this far. I sincerely hope you enjoy this library as much as I enjoyed making it.