This library allows for TypeSafe SQL building, enables independent definition and reuse of subqueries and CTEs, and supports unit testing without the need for tables.
$ dotnet add package Carbunql.TypeSafeThis library is experimental.
This library allows for TypeSafe SQL building, enables independent definition and reuse of subqueries and CTEs, and supports unit testing without the need for tables.
This is a demo of turning SQL into a function and reusing it.
First, define a table like the one below as a model class.
using Carbunql;
using Carbunql.TypeSafe;
public record Sale : IDataRow
{
public long sale_id { get; set; }
public string product_name { get; set; } = null!;
public int unit_price { get; set; }
public int quantity { get; set; }
public double tax_rate { get; set; }
public IDataSet DataSet { get; set; } = null!;
}
A model class must satisfy the following conditions:
It must have a no-argument constructor.
It must implement the IDataRow interface.
Implementing the IDataRow interface simply involves defining the DataSet property. No special logic is required.
Now let's write a select query in TypeSafe.
A query that selects all rows and all columns can be written like this:
var s = Sql.DefineDataSet<Sale>();
// select query debug print
Console.WriteLine(s.ToText());
Running this code will generate the following SQL statement:
SELECT
*
FROM
sale AS s
Now let's turn this select query into a function so that we can reuse it.
private static FluentSelectQuery<Sale> SelectSale()
{
var s = Sql.DefineDataSet<Sale>();
return Sql.From(() => s);
}
To call this function, you would write it like this:
Console.WriteLine(SelectSale().ToText());
Let's get the select query that has been made into a function and add a Where clause to it. The generated query will be published as a function named "SelectFilteredSale".
private static FluentSelectQuery<Sale> SelectFilterdSale()
{
// Give the query a name
var all_sale = SelectSale;
// Define the query as a dataset and give it an alias name
var sl = Sql.DefineDataSet(() => all_sale());
return Sql.From(() => sl)
.Where(() => sl.sale_id == 1);
}
The generated SQL looks like this:
SELECT
*
FROM
(
SELECT
*
FROM
sale AS s
) AS sl
WHERE
sl.sale_id = 1You can see that the query obtained by the function "SelectSale" is reused as a subquery.
Finally, let's inject some test data. First, define the test data as follows:
private static FluentSelectQuery<Sale> SelectSaleTestDataQuery()
{
return new FluentSelectQuery<Sale>([
new Sale{sale_id = 1, product_name = "apple", unit_price = 105, quantity = 5, tax_rate = 0.07},
new Sale{sale_id = 2, product_name = "orange", unit_price = 203, quantity = 3, tax_rate = 0.07},
]).Comment("test data");
} This code is a select query using the Values clause and has the following meaning:
/* test data */
SELECT
v.sale_id,
v.product_name,
v.unit_price,
v.quantity,
v.tax_rate
FROM
(
VALUES
(1, CAST('apple' AS text), 105, 5, CAST(0.07 AS double precision)),
(2, CAST('orange' AS text), 203, 3, CAST(0.07 AS double precision))
) AS v (
sale_id, product_name, unit_price, quantity, tax_rate
)This is very easy because we can use the model class as is.
Now let's inject it.
private static FluentSelectQuery<Sale> SelectFilterdSaleWithTestData()
{
var query = SelectFilterdSale();
// Important: Give your test data the same name as your table name
var sale = SelectSaleTestDataQuery();
// Add test data to the WITH clause
query.With(() => sale);
return query;
}The above code will generate the following query:
WITH
sale AS (
/* test data */
SELECT
v.sale_id,
v.product_name,
v.unit_price,
v.quantity,
v.tax_rate
FROM
(
VALUES
(1, CAST('apple' AS text), 105, 5, CAST(0.07 AS double precision)),
(2, CAST('orange' AS text), 203, 3, CAST(0.07 AS double precision))
) AS v (
sale_id, product_name, unit_price, quantity, tax_rate
)
)
SELECT
*
FROM
(
SELECT
*
FROM
sale AS s
) AS sl
WHERE
sl.sale_id = 1For the full demo code and a more complex example, see the source code below.
PM> Install-Package Carbunql.TypeSafe