ArdenHide.Utils.QuickSQL
1.3.1
See the version list below for details.
dotnet add package ArdenHide.Utils.QuickSQL --version 1.3.1
NuGet\Install-Package ArdenHide.Utils.QuickSQL -Version 1.3.1
<PackageReference Include="ArdenHide.Utils.QuickSQL" Version="1.3.1" />
paket add ArdenHide.Utils.QuickSQL --version 1.3.1
#r "nuget: ArdenHide.Utils.QuickSQL, 1.3.1"
// Install ArdenHide.Utils.QuickSQL as a Cake Addin #addin nuget:?package=ArdenHide.Utils.QuickSQL&version=1.3.1 // Install ArdenHide.Utils.QuickSQL as a Cake Tool #tool nuget:?package=ArdenHide.Utils.QuickSQL&version=1.3.1
QuickSQL
By default, Entity Framework does not support the ability to dynamically select by passing a string with the table name to get a DbSet.
This is how this library came about. This library allows you to perform a SELECT query by passing Request
object.
Install
Package Manager
Install-Package ArdenHide.Utils.QuickSQL
.NET CLI
dotnet add package ArdenHide.Utils.QuickSQL
You also need to install a package with your provider or implement your provider. Example for Microsoft Sql Server provider:
Package Manager
Install-Package ArdenHide.Utils.QuickSQL.MicrosoftSqlServer
.NET CLI
dotnet add package ArdenHide.Utils.QuickSQL.MicrosoftSqlServer
Example usage:
Providers currently supported: SupportedProviders
Also you can add your provider, see below.
The first step is to create the desired request.
using QuickSQL;
Request tokenBalances = new Request(
"TokenBalances",
new Collection<string>
{
{ "Token" }, { "Owner" }, { "Amount" }
},
new Collection<Condition>
{
new Condition { ParamName = "Id", Operator = OperatorName.Equals, ParamValue = "1" }
});
Request fields
TableName
- This is a required parameter. Pass a table name from which to take data.SelectedColumns
- This is a required parameter. Pass columns from which to take data.WhereConditions
- Not required parameter. Enter condition for search tables. If it is a string parameter, you need to pass the condition parameter in single quotes, likeParamValue = "'Alex'
.
The second step, invoke request.
using QuickSQL;
using QuickSQL.MicrosoftSqlServer;
Request tokenBalances = new Request(
"TokenBalances",
new Collection<string>
{
{ "Token" }, { "Owner" }, { "Amount" }
},
new Collection<Condition>
{
new Condition { ParamName = "Id", Operator = OperatorName.Equals, ParamValue = "1" },
new Condition { ParamName = "Name", Operator = OperatorName.Equals, ParamValue = "'Alex'" }
});
string result = QuickSql.InvokeRequest(
tokenBalances,
connectionString,
new SqlDataReader(),
new SqlQueryCreator()
);
Security
This library does not have SQL injection checks. Best security practice is to create a read-only user. It's also a good idea to limit the user's visibility to tables that they shouldn't see.
I didn't find my provider. Instructions for adding your provider
The first step is to create a DataReader for your SQL provider.
It is easier than it might seem, to implement your DateReader inherit the abstract class BaseDataReader
.
This abstract class have core logic for read SQL data.
You need to define CreateConnection()
and CreateReader()
for your provider.
Example for MySql provider
using QuickSQL.Datareader;
public class MySqlDataReader : BaseDataReader
{
public override DbConnection CreateConnection(string connectionString)
=> new MySqlConnection(connectionString);
public override DbDataReader CreateReader(string commandQuery, DbConnection connection)
=> new MySqlCommand(commandQuery, (MySqlConnection)connection).ExecuteReader();
}
Example for MicrosoftSqlServer provider
using QuickSQL.Datareader;
public class SqlDataReader : BaseDataReader
{
public override DbConnection CreateConnection(string connectionString)
=> new SqlConnection(connectionString);
public override DbDataReader CreateReader(string commandQuery, DbConnection connection)
=> new SqlCommand(commandQuery, (SqlConnection)connection).ExecuteReader();
}
The second step is to create a QueryCreator for your SQL provider. You need to define OnCreateCommandQuery()
for your provider. This function should create a SQL query string returning data in JSON format. You can use the CreateWhereCondition()
internal function to create the condition string.
Example for MicrosoftSqlServer provider
using QuickSQL.QueryCreator;
public class SqlQueryCreator : BaseQueryCreator
{
protected override string OnCreateCommandQuery(Request request)
{
string selectedColumns = string.Join(", ", request.SelectedColumns);
string commandQuery = $"SELECT {selectedColumns} FROM {request.TableName}";
if (request.WhereConditions != null)
{
commandQuery += $" {CreateWhereCondition(request.WhereConditions)}";
}
commandQuery += " FOR JSON PATH";
return commandQuery;
}
}
Product | Versions Compatible and additional computed target framework versions. |
---|---|
.NET | net6.0 is compatible. 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. |
-
net6.0
- Newtonsoft.Json (>= 13.0.1)
NuGet packages (2)
Showing the top 2 NuGet packages that depend on ArdenHide.Utils.QuickSQL:
Package | Downloads |
---|---|
ArdenHide.Utils.QuickSQL.MicrosoftSqlServer
Adding Microsoft Sql Server provider to QuickSQL. |
|
ArdenHide.Utils.QuickSQL.MySql
Adding MySql provider to QuickSQL. |
GitHub repositories
This package is not used by any popular GitHub repositories.
- added constructor by default to Request model.