Kvr.SqlBuilder 1.1.0

SqlBuilder

Copyright © 2024 Kvr.SqlBuilder. All rights reserved.

A lightweight, fluent SQL query builder for .NET that provides type-safe SQL query construction with support for table aliases, column selection, and complex joins. With Table and Column attributes annotations support.

Table of Contents

• Features
• Installation
• Quick Start Guide
  • Basic Usage
  • Advanced Queries
• Usage
  • Table Names
  • Column Selection
  • Joins
  • Where Clauses
  • Order By
• Best Practices
• Limitations
• Supported Frameworks
• Version History
• License
• Contributing
• Dependencies
• Support
• Build Status

Features

• Fluent API for building SQL queries
• Type-safe property and table selection
• Support for table aliases and prefixes
• Automatic handling of SQL Server and standard SQL syntax
• Support for complex JOIN operations
• Column selection with aliasing
• WHERE clause construction with multiple conditions
• ORDER BY clause support
• Minimal boilerplate code
• TableAttribute and ColumnAttribute annotations support

Installation

You can install the package via NuGet Package Manager:

dotnet add package Kvr.SqlBuilder

Quick Start Guide

Here's a quick example of how to use SqlBuilder to construct SQL queries:

using Kvr.SqlBuilder;

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
}

public class Order
{
    public int OrderId { get; set; }
    public int CustomerId { get; set; }
    public decimal Amount { get; set; }
}

// Basic query
var query = new SqlBuilder()
    .SelectAll<Customer>()
    .From<Customer>()
    .Build();
// Result: SELECT [Id], [Name], [Email] FROM [Customers]

// Join query with conditions
var joinQuery = new SqlBuilder()
    .SelectAll<Customer>(out var customerPrefix)
    .SelectAll<Order>(out var orderPrefix)
    .From<Customer>()
    .Join<Customer, Order, int>(
        customer => customer.Id,
        order => order.CustomerId)
    .Where<Customer>(c => c.Id, 1)
    .OrderBy<Order>(o => o.Amount, false)
    .Build();
// Result: SELECT kvr0.[Id], kvr0.[Name], kvr0.[Email], kvr1.[OrderId], kvr1.[CustomerId], kvr1.[Amount] 
//        FROM [Customers] kvr0 
//        JOIN [Orders] kvr1 ON kvr0.[Id] = kvr1.[CustomerId] 
//        WHERE kvr0.[Id] = 1 
//        ORDER BY kvr1.[Amount] DESC

Usage

Table Names

SqlBuilder supports both singular and plural table names, with customization options:

// Global setting for plural table names
SqlBuilder.UseGlobalPluralTableNames();

// Instance-specific setting
var builder = new SqlBuilder()
    .UsePluralTableNames()
    .SelectAll<Customer>()
    .From<Customer>();

// Custom table name mapping
SqlBuilder.MapGlobalTable<Customer>("tbl_customers");
// or instance-specific
builder.MapTable<Customer>("tbl_customers");

Column Selection

Multiple ways to select columns:

// Select all columns
builder.SelectAll<Customer>();

// Return c.Id as first column, it is important for Dapper's splitOn parameter
builder.SelectAll<Customer>(c => c.Id);

// Select specific columns
builder.Select<Customer>(c => new[] { c.Id, c.Name });

// Select with alias
builder.Select<Customer>(c => c.Name, "CustomerName");

// Exclude specific columns
builder.SelectAll<Customer>(excludeColumns: new[] { c => c.Email });

// Exclude specific column Email. Return c.Id as first column, it is important for Dapper's splitOn parameter
builder.SelectAll<Customer>(excludeColumns: new[] { c => c.Email }, c => c.Id);

Joins

Support for various JOIN types:

// INNER JOIN
builder.Join<Customer, Order, int>(
    customer => customer.Id,
    order => order.CustomerId);

// LEFT JOIN
builder.LeftJoin<Customer, Order, int>(
    customer => customer.Id,
    order => order.CustomerId);

// RIGHT JOIN
builder.RightJoin<Customer, Order, int>(
    customer => customer.Id,
    order => order.CustomerId);

// FULL JOIN
builder.FullJoin<Customer, Order, int>(
    customer => customer.Id,
    order => order.CustomerId);

Where Clauses

Building WHERE conditions:

builder
    .Where<Customer>(c => c.Id, "1")
    .And<Customer>(c => c.Name, "'John'")
    .Or<Customer>(c => c.Email, "'john@example.com'");

Best Practices

• Use strongly-typed expressions when possible
• Leverage table aliases for complex queries
• Keep SQL Server compatibility in mind
• Use appropriate column selection instead of SELECT *
• Consider query performance when building complex JOINs

Limitations

1. Limited support for complex subqueries (could use RawSql methods for this)
2. No direct support for GROUP BY and HAVING clauses
3. No NamingConvention support for table and column (e.g. SnakeCase, PascalCase for table and column names)

Supported Frameworks

• .NET Standard 2.0+
• .NET 5.0+
• .NET 6.0+
• .NET 7.0+

Version History

• 1.1.0

  • Add RawSql methods (Where, And, Or, OrderBy) and AppendRawSql method
  • Change parameter type from LambdaExpression to Expression<Func<T, object>> for strongly-typed expressions

• 1.0.0

  • Initial release
  • Basic SQL query building
  • Support for JOINs, WHERE clauses, and ORDER BY
  • Table name customization
  • SQL Server compatibility

License

This project is licensed under the Apache License 2.0 - see the LICENSE file for details.

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

1. Fork the Project
2. Create your Feature Branch (git checkout -b feature/AmazingFeature)
3. Commit your Changes (git commit -m 'Add some AmazingFeature')
4. Push to the Branch (git push origin feature/AmazingFeature)
5. Open a Pull Request

Dependencies

None. This is a standalone library.

Support

If you encounter any issues or have questions, please file an issue on the GitHub repository.

Build Status