ArdenHide.Utils.QuickSQL 1.3.1

There is a newer version of this package available.
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                
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="ArdenHide.Utils.QuickSQL" Version="1.3.1" />                
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add ArdenHide.Utils.QuickSQL --version 1.3.1                
#r "nuget: ArdenHide.Utils.QuickSQL, 1.3.1"                
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
// 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

Build Status codecov CodeFactor

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, like ParamValue = "'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 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. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

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.

Version Downloads Last updated
1.3.3 752 6/14/2022
1.3.2 451 6/3/2022
1.3.1 419 6/3/2022
1.3.0 805 6/3/2022
1.2.1 597 5/24/2022
1.2.0 713 5/23/2022
1.1.0 399 5/20/2022
1.0.0 1,294 5/10/2022

- added constructor by default to Request model.