YeSQL.NET is a powerful class library designed to enhance the management of SQL statements within C# applications. By allowing SQL to be loaded from external files, YeSQL.NET promotes cleaner coding and better organization. In this article, we’ll guide you through the installation, usage, and some troubleshooting tips to ensure a smooth experience.

Advantages of Using YeSQL.NET

• Better Editor Support: Edit your SQL files with ease, as they are recognized as standalone SQL documents.
• Query Reuse: Share SQL files across multiple projects effortlessly.
• Team Interoperability: Collaborate effectively; your DBAs can work directly with SQL code.
• Separation of Concerns: Modify SQL files independently of C# code, enhancing maintainability.
• Independent Development: SQL can be developed without waiting on C# code changes.

Installation

To get started with YeSQL.NET, you need to install it via NuGet. Follow these steps:

• For Visual Studio, open the Package Manager Console and run:

Install-Package YeSql.Net

• Using the dotnet CLI, run:

dotnet add package YeSql.Net

Overview

Once installed, import the required namespace at the beginning of your class file:

using YeSql.Net;

YeSQL.NET provides three main components:

• YeSqlLoader: Used for loading SQL statements.
• YeSqlParser: A parser that processes the SQL statements.
• ISqlCollection: An interface for storing the loaded SQL statements.

Creating a .sql File

Create a file with a .sql extension and write your SQL statements inside it. Each statement should have a tag. Here’s an example of what your users.sql might look like:

-- 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);

Loading SQL Statements

YeSQL.NET allows you to load SQL statements in various ways:

Load from a Default Directory

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

This method searches in the default directory where your application runs and can be configured using CopySqlFilesToOutputDirectory.

Accessing SQL Statements

You can access a specific SQL statement by its tag as shown:

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

Load from a Set of Directories or Files

Load SQL files from specified directories or files:

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

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

Parsing SQL Files

YeSQL also supports parsing SQL statements directly:

var source = "-- name: GetUsers
-- Gets user information.
SELECT id, username, email FROM users;";
YeSqlValidationResult validationResult;
ISqlCollection sqlStatements = new YeSqlParser().Parse(source, out validationResult);

Integration with ASP.NET Core

To integrate SQL loading into your ASP.NET Core application, add the following in your Program.cs file:

var builder = WebApplication.CreateBuilder(args);
ISqlCollection sqlStatements = new YeSqlLoader().LoadFromDefaultDirectory();
builder.Services.AddSingleton(sqlStatements);
builder.Services.AddControllers();
var app = builder.Build();
app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();
app.Run();

Copying SQL Files to Publish Directory

To ensure SQL files are included in the published output, use the CopySqlFilesToOutputDirectory package. Use the following command when publishing:

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

Samples

For examples of how to use YeSQL.NET, check out:

• Example.ConsoleApp
• Example.AspNetCore
• Example.Parsing
• Example.QueryReuse

Troubleshooting

If you encounter issues during setup or usage, consider the following:

• Ensure SQL files have the correct .sql extension and are accessible to the application.
• Check for any syntax errors in your SQL statements.
• Ensure you’re referring to tag names with the correct casing.
• If you’re still stuck, feel free to reach out for additional support.

For more insights, updates, or to collaborate on AI development projects, stay connected with fxis.ai.

Conclusion

YeSQL.NET stands as an easy-to-use solution for managing SQL statements in C#. By following this guide, you can streamline your SQL code management, keeping it clean and separated from application logic.

At fxis.ai, we believe that such advancements are crucial for the future of AI, as they enable more comprehensive and effective solutions. Our team is continually exploring new methodologies to push the envelope in artificial intelligence, ensuring that our clients benefit from the latest technological innovations.