SmartSql Documentation

SmartSql is a .NET ORM library inspired by MyBatis that treats SQL as a first-class citizen. Unlike EF Core (which uses LINQ) or Dapper (which uses inline C# strings), SmartSql manages SQL statements in external XML files called SmartSqlMaps. This separation allows for easier DBA review, dynamic SQL construction via XML tags, and reusable SQL maps.

It is compatible with netstandard2.0 and C# 7.3, supporting both .NET Framework 4.6.1+ and .NET Core/.NET 5+.

SmartSql is a high-performance data access framework that combines the ideas of MyBatis with modern .NET capabilities. It uses XML to manage SQL, allowing developers to maintain absolute control over SQL performance while eliminating complex conditional logic in C# code via XML filter tags.

Key features include:

• SQL Management: XML-based SQL with conditional tags.
• Caching: Support for LRU, FIFO, and Redis.
• R/W Splitting: Easy configuration for read/write separation.
• Dynamic Repository: Automatic implementation of repository interfaces.
• Diagnostics & Monitoring: Built-in support for observability.
• Advanced Data Handling: Type handlers (e.g., JSON), ID generators (Snowflake, DB Sequence), and Bulk Insert support.

The SmartSql.DyRepository extension allows you to define C# interfaces that act as data access repositories. At runtime, SmartSql generates a fully-functional implementation using IL emit. This implementation automatically maps interface methods to SQL statements defined in your XML configuration based on naming conventions or explicit annotations.

// Define your interface
public interface IUserRepository
{
    // Maps to XML statement with Id="GetUser"
    [Statement(Id = "GetUser")]
    User GetUser(long id);
}

// Request the instance from the factory
var userRepository = repositoryFactory.CreateInstance(typeof(IUserRepository), sqlMapper) as IUserRepository;

// Use it
var user = userRepository.GetUser(123);

SmartSql provides several built-in capabilities to simplify complex data requirements:

• Dynamic Queries: Automatically adjusts queries based on provided optional filters.
• Built-in Caching: Supports memory and Redis to improve load times.
• Read/Write Splitting: Automatically distributes load by routing reads to replicas and writes to the primary database.
• Bulk Operations: High-performance bulk insert support for loading large datasets.
• Event Streaming: Built-in integration with Kafka and RabbitMQ for real-time data synchronization.
• Dynamic Repository: Automatically generates data access code from simple interface definitions.

SmartSql is a .NET ORM inspired by MyBatis that utilizes XML-managed SQL and a middleware-based execution pipeline. The architecture is divided into three primary layers:

1. Application Layer: Where your application code and Dynamic Repositories reside.
2. API Layer: The entry point for developers, consisting of ISqlMapper (the primary interface), SqlMapper (the implementation), and SmartSqlBuilder (the configuration tool).
3. Middleware Pipeline: A linked-list of IMiddleware components that intercept, transform, and observe every SQL invocation.
4. Data Access Layer: Handles the actual database interaction via IDbSession and ICommandExecuter.
5. Storage Layer: Includes the physical databases (Read/Write) and the Cache Provider.

This layered approach allows you to intercept and modify SQL execution at any stage without changing your core business logic.

SmartSql supports two configuration methods that both result in the same SmartSqlConfig object at runtime:

1. XML Configuration: The primary and most common method using a SmartSqlMapConfig.xml file.
2. Programmatic Configuration: Using the SmartSqlBuilder fluent API.

The XML approach is preferred for its structure and ease of management in large projects.

<?xml version="1.0" encoding="utf-8" ?>
<SmartSqlMapConfig xmlns="http://SmartSql.net/schemas/SmartSqlMapConfig.xsd">
  <Settings />
  <Properties />
  <Database />
  <TypeHandlers />
  <TagBuilders />
  <IdGenerators />
  <SmartSqlMaps />
</SmartSqlMapConfig>

SmartSql provides extensions across several functional areas to enhance data access and system management:

• Data Access Extensions: Specialized tools for interacting with data stores.
• Configuration & DI Extensions: Helpers for integrating with Dependency Injection and managing settings.
• Caching & Synchronization Extensions: Built upon core cache abstractions to manage data consistency (e.g., Redis, Cache Sync).
• Data Synchronization Extensions: Tools for keeping data in sync across different layers or systems.
• Dynamic SQL Extensions: Enhancements for generating and managing dynamic SQL queries.

The SmartSql.Bulk package provides a database-agnostic interface for high-performance bulk inserts. It uses native database mechanisms to achieve maximum throughput instead of row-by-row insertion.

Supported database providers include:

• SQL Server / MsSqlServer: Uses SqlBulkCopy.
• MySQL / MySqlConnector: Uses MySqlBulkLoader via temporary CSV files.
• PostgreSQL: Uses COPY BINARY via NpgsqlConnection.BeginBinaryImport().

All implementations implement the IBulkInsert interface, which supports both synchronous Insert() and asynchronous InsertAsync() methods.