YeSql.Net 1.0.1

YeSQL.NET

YeSQL.NET is a class library for loading SQL statements from .sql files instead of writing SQL code in your C# source files.

• This library was inspired by krisajenkins/yesql.
• YepSQL library has been used as a reference for the creation of the parser.

See the API documentation for more information on this project.

Index

• Advantages
• Installation
• Overview
  • Creating .sql file
  • Load from a default directory
  • Accessing SQL statements
  • Load from a set of directories
  • Load from a set of files
  • Parsing .sql files
  • Integration with ASP.NET Core
  • Copy .sql files to the publish directory
• Samples
• Contribution

Advantages

By keeping the SQL and C# separate you get:

• Better editor support. Your editor probably already has great SQL support. By keeping the SQL as SQL, you get to use it.
• Query reuse. Drop the same SQL files into other projects, because they're simply SQL. Share them as a module.
  • See this sample on how to distribute SQL files in a nuget package.
• Team interoperability. Your DBAs can read and write the SQL you use in your .NET project.
• Separation of concerns. Since your SQL statements are not embedded (hard-coded) directly in the application code, you can make minor changes to the SQL file without having to open the C# source file.
  • Any changes you make to the SQL file should not affect the C# source file unless it is a major change (e.g., renaming a column).
• Independent development. A developer can create the SQL statements without waiting for another developer to create the C# source file with the proposed feature.

Installation

If you're want to install the package from Visual Studio, you must open the project/solution in Visual Studio, and open the console using the Tools > NuGet Package Manager > Package Manager Console command and run the install command:

Install-Package YeSql.Net

If you are making use of the dotnet CLI, then run the following in your terminal:

dotnet add package YeSql.Net

Overview

You must import the namespace types at the beginning of your class file:

using YeSql.Net;

This library provides three main types:

• YeSqlLoader
• YeSqlParser
• ISqlCollection

See the API documentation for more information on these types.

Creating .sql file

Create a file with .sql extension containing the SQL statements.

It is recommended that the extension be lowercase so that there are no problems when loading the SQL file on a Linux distro.

Example: users.sql

-- name: GetUsers
-- Gets all users
SELECT 
id, 
email 
FROM users;

-- name: GetUserById
-- Gets user information
SELECT 
id, 
username, 
email 
FROM users
WHERE id = @id;

-- name: InsertUser
-- Create user record
INSERT INTO users (id, username, email)
VALUES (@id, @username, @email);

Each SQL statement must be associated with a tag using the name: prefix and must begin with a comment. This is necessary so that the parser can uniquely identify each SQL statement by its tag.

For example, in this case the tag is GetUsers and the SQL statement would be SELECT id, email FROM users;.

You should also note that comments that do not use the name: prefix will be ignored by the parser.

Load from a default directory

You can load SQL statements from a default directory called yesql.

ISqlCollection sqlStatements = new YeSqlLoader().LoadFromDefaultDirectory();

This method starts searching from the current directory where the application is running (e.g. bin/Debug/net8.0).

It is recommended to install the nuget package called CopySqlFilesToOutputDirectory to copy the .sql files from the project folder to the output directory (e.g. bin/Debug/net8.0). This will create a folder called yesql in the output directory where all the .sql files will be. From there, the LoadFromDefaultDirectory method will start loading the SQL files.

You can install the package from the terminal:

dotnet add package CopySqlFilesToOutputDirectory

Accessing SQL statements

You can access SQL statements using the ISqlCollection interface.

string tagName = "GetUsers";
string sqlCode = sqlStatements[tagName];

But you must specify the tag name to access the SQL statement. Remember that each SQL code is identified with its respective tag.

The tag name is case-sensitive. GetUsers is not the same as getUsers.

Load from a set of directories

You can load SQL statements from all the SQL files in the specified directories.

var directories = new[]
{
  "./sql/reports",
  "/home/admin/MyApp2/reports"
};
ISqlCollection sqlStatements = new YeSqlLoader().LoadFromDirectories(directories);

You can attach the absolute or relative path to the directory name. If the path is relative, then the method will start searching from the current directory where the application is running (e.g. bin/Debug/net8.0).

Load from a set of files

You can load SQL statements from the specified files.

var sqlFiles = new[]
{
  "./reports/users.sql",
  "/home/admin/MyApp2/products.sql"
};
ISqlCollection sqlStatements = new YeSqlLoader().LoadFromFiles(sqlFiles);

You can attach the absolute or relative path to the file name. If the path is relative, then the method will start searching from the current directory where the application is running (e.g. bin/Debug/net8.0).

Parsing .sql files

You can use the parser to extract SQL statements from any data source.

var source =
"""
-- name: GetUsers
-- Gets user information.
SELECT 
id, 
username, 
email 
FROM users;
""";
YeSqlValidationResult validationResult;
ISqlCollection sqlStatements = new YeSqlParser().Parse(source, out validationResult);
if(validationResult.HasError())
{
  // Some code to handle the error.
}

If you do not want to handle the error, you can use the ParseAndThrow method.

ISqlCollection sqlStatements = new YeSqlParser().ParseAndThrow(source);

This method throws an exception of type YeSqlParserException when the parser encounters an error.

Integration with ASP.NET Core

In your Program.cs file, load the SQL files and add the ISqlCollection type as a singleton service.

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.

// Loads the SQL files from a default directory called yesql.
ISqlCollection sqlStatements = new YeSqlLoader().LoadFromDefaultDirectory();
// Adds the returned instance as a singleton service.
builder.Services.AddSingleton<ISqlCollection>(sqlStatements);
builder.Services.AddControllers();

var app = builder.Build();
app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();
app.Run();

You can then use the added service in your own classes using the DI pattern.

Example:

public class GetUsersQuery
{
    private readonly ISqlCollection _sqlCollection;
    public GetUsersQuery(ISqlCollection sqlCollection)
    {
        _sqlCollection = sqlCollection;
    }

    public IEnumerable<User> Execute()
    {
        string tagName = "GetUsers";
        string sql = _sqlCollection[tagName];
        // Here you can add the code to execute the query.
        // You can use the MySQL client API or any other.
    }
}

Copy .sql files to the publish directory

CopySqlFilesToOutputDirectory package is also used to copy the .sql files from the project folder to the publish directory when using the dotnet publish command. This will create a folder called yesql in the publish directory where all the .sql files will be.

It is recommended to publish the application in a directory other than the project folder (where the project file is located).

Example:

dotnet publish -o /home/admin/out/PublishedApp -c Release

For more information, see this issue: Recommendations for publishing the application.

Samples

You can find a complete and functional example in these projects:

• Example.ConsoleApp
• Example.AspNetCore
• Example.Parsing
• Example.QueryReuse
• Example.PluginApp
• Example.NetFramework
• Example.AspNetFramework

Contribution

Any contribution is welcome! Remember that you can contribute not only in the code, but also in the documentation or even improve the tests.

Follow the steps below:

• Fork it
• Create your feature branch (git checkout -b my-new-change)
• Commit your changes (git commit -am 'Add some change')
• Push to the branch (git push origin my-new-change)
• Create new Pull Request