Queryoont 0.1.4
See the version list below for details.
dotnet add package Queryoont --version 0.1.4
NuGet\Install-Package Queryoont -Version 0.1.4
<PackageReference Include="Queryoont" Version="0.1.4" />
paket add Queryoont --version 0.1.4
#r "nuget: Queryoont, 0.1.4"
// Install Queryoont as a Cake Addin
#addin nuget:?package=Queryoont&version=0.1.4
// Install Queryoont as a Cake Tool
#tool nuget:?package=Queryoont&version=0.1.4
Queryoont
The Queryoont is a minimal framework to use SQL with ASP.NET Core API in a dynamic way, you can issue statement from the client using a POST request, specifying the SELECT and WHERE.
Use the QueryoontAttribute
to decorate your controller action method to obtain the base SqlKata Query, next the Queryoont apply the body request filter to the query.
DISCLAIMER
This tool does not allow to specify joins and INSERT / UPDATE / DELETE statements for security reasons.
Actually, you can ONLY specify the WHERE
and the fields on SELECT
.
Setup the Queries
Decorate the controller action method with HttpPost
and QueryoontFilter
[HttpPost]
[QueryoontFilter]
public Query GetCustomer()
{
return new Query("Customers");
}
Startup
On the startup configure the QueryBuilder
class of SqlKata below is a configuration for the SqlServer.
// Configure SQL Server with SqlKata
var connectionString = Configuration.GetConnectionString("ConnectionStringName");
services.AddTransient<IDbConnection>((s) => new SqlConnection(connectionString));
services.AddTransient<QueryFactory>((s) =>
{
var compiler = new SqlServerCompiler();
return new QueryFactory(s.GetService<IDbConnection>(), compiler);
});
// Add Queryoont services
services.AddQueryoont();
Request Body on POST
This is a map of all possible conditions.
{
"version": 1.0,
// If you have more that one table use the prefix
"select": [
"table1.id",
"table2.name",
"table1.{title,note}" // SqlKata syntax
],
"filter": [
{
"type": "WhereCondition", // This condition is translated to (Where prefix.Id > 2 OR prefix.Id < 5) between brakets
"condition": [
{
"type": "Where",
"field": "prefix.Id",
"oper": ">",
"value": "2"
},
{
"type": "OrWhere",
"field": "prefix.Id",
"oper": "<",
"value": "5"
}
]
},
{
"type": "Where", // For AND condition repeat the Where
"field": "prefix.Id",
"oper": "<",
"value": "2"
},
{
"type": "OrWhere",
"field": "prefix.Id",
"oper": "<",
"value": "2"
},
{
"type": "WhereNot",
"field": "prefix.Id",
"oper": "<",
"value": "2"
},
{
"type": "OrWhereNot",
"field": "prefix.Id",
"oper": "<",
"value": "2"
},
{
"type": "WhereNull",
"field": "prefix.Id"
},
{
"type": "OrWhereNull",
"field": "prefix.Id"
},
{
"type": "WhereTrue",
"field": "prefix.Id"
},
{
"type": "WhereFalse",
"field": "prefix.Id"
},
{
"type": "WhereIn",
"field": "prefix.Id",
"values": [
1,
2,
3,
4
]
},
{
"type": "WhereNotIn",
"field": "prefix.Id",
"values": [
1,
2,
3,
4
]
}
]
}
Example
A simple example
CREATE TABLE Table1
(
[ID] INT NOT NULL PRIMARY KEY,
[NAME] VARCHAR(50),
[EMAIL] VARCHAR(100)
)
[HttpPost]
[QueryoontFilter]
public Query GetCustomer()
{
return new Query("table1");
}
{
"version": 1.0,
"select": [
"table1.Name"
],
"filter": [
{
"type": "Where",
"field": "table.Id",
"oper": "<",
"value": "20"
}
]
}
This is translated into this SQL
SELECT
table1.Name
FROM
table1
WHERE
table.Id < 20
And the corresponding json
[
{
"name": "name 1"
},
{
"name": "name 2"
}
]
Json Serialization
Adding services.AddQueryoont()
configure the framework to use Json.NET to take care of Serialization and Deserialization. If you want to customize the process of Serialization/Deserialization you could implement the inserface IJsonSerializer
of the framework and add it to the serviceCollection using the overload of services.AddQueryoont()
or by remove the call and adding it to the serviceCollections.
Know issues with System.Text.Json
There are some issues when using System.Text.Json
Writing
If you use the System.Text.Json
the default is to serialize on PascalCase the Dictionary
keys (the result of SqlKata is a DapperRow
that is a dynamic
object that is a Dictionary<string,object>
), if you want the camelCase with System.Text.Json
you should configure the JsonOptions like this
services.AddControllers()
.AddJsonOptions(o =>
o.JsonSerializerOptions.DictionaryKeyPolicy = System.Text.Json.JsonNamingPolicy.CamelCase
);
Reading
When deserialize object properties the System.Text.Json
adds ValueKind to value property, you must take care of it See this GiHub issue
Product | Versions Compatible and additional computed target framework versions. |
---|---|
.NET | net5.0 is compatible. net5.0-windows was computed. net6.0 was computed. net6.0-android was computed. net6.0-ios was computed. net6.0-maccatalyst was computed. net6.0-macos was computed. net6.0-tvos was computed. net6.0-windows was computed. net7.0 was computed. net7.0-android was computed. net7.0-ios was computed. net7.0-maccatalyst was computed. net7.0-macos was computed. net7.0-tvos was computed. net7.0-windows was computed. net8.0 was computed. net8.0-android was computed. net8.0-browser was computed. net8.0-ios was computed. net8.0-maccatalyst was computed. net8.0-macos was computed. net8.0-tvos was computed. net8.0-windows was computed. |
.NET Core | netcoreapp3.1 is compatible. |
-
.NETCoreApp 3.1
- Dawn.Guard (>= 1.12.0)
- Newtonsoft.Json (>= 13.0.1)
- SqlKata (>= 2.3.7)
- SqlKata.Execution (>= 2.3.7)
-
net5.0
- Dawn.Guard (>= 1.12.0)
- Newtonsoft.Json (>= 13.0.1)
- SqlKata (>= 2.3.7)
- SqlKata.Execution (>= 2.3.7)
NuGet packages
This package is not used by any NuGet packages.
GitHub repositories
This package is not used by any popular GitHub repositories.
Initial release