Visor.SqlServer 1.2.0

Visor

High-performance, Source-Generated ORM for .NET 10+. Treats your Database Stored Procedures as a strictly typed API. Visor is not about generating SQL queries; it's about efficiently consuming Database Endpoints.

Visor is designed to solve the "Enterprise Gap" in .NET data access:

• Dapper is fast but type-unsafe and requires boilerplate.
• EF Core is convenient but heavy and slow for bulk operations.
• Visor uses Source Generators to write zero-allocation ADO.NET code for you at compile time. Visor automates TVP boilerplate that you usually write manually.

🧠 Philosophy: Database as an API

Visor was not born solely for performance. It is an architectural statement: The Database should be treated as a strict external API.

1. Stored Procedures are Endpoints. Just like REST controllers, your Stored Procedures are the entry points.
2. Strict Contracts. You don't "query" the database; you consume it. The [Endpoint] attribute binds your code to the DB schema at compile time.
3. No SQL in C#. Visor is not a query builder. It is a high-performance execution engine for your data contracts.

🚀 Benchmarks: The "10k Insert" Challenge

We compared inserting 10,000 records into MS SQL Server using a Transactional Stored Procedure with Table-Valued Parameters (TVP).

Why is Visor 10x faster than EF and 800x faster than loops?

• Zero Allocation Streaming: Visor maps List<T> directly to IEnumerable<SqlDataRecord> (MSSQL) or Arrays (Postgres) using yield return. No intermediate DataTable or memory copying.
• No Runtime Reflection: All mapping code is generated at compile-time.
• Strict Mapping: If your DB schema changes, Visor fails fast with clear exceptions, not silent data corruption.

⚡ Quick Install

Install the provider for your database and the generator package:

# 1. Add the Source Generators (The Engine)
dotnet add package Visor.Generators

# 2. Add your Database Provider
dotnet add package Visor.SqlServer
# OR
dotnet add package Visor.PostgreSql

📦 Ecosystem

Visor is modular. You typically only need to install a Provider and the Generators.

Package	Description	Version
Visor.SqlServer	Main Provider. Includes TVP streaming & async logic.
Visor.PostgreSql	Postgres Provider. Supports Arrays & Composite Types.
Visor.Generators	Required. Roslyn Source Generators (Compile-time magic).
Visor.Abstractions	Attributes & Interfaces only. Keep your Domain clean.
Visor.Core	Shared runtime infrastructure (Internal).

⚡ Quick Start (MSSQL)

1. Define your Data Contract

Describe your Stored Procedure as a C# interface.

using Visor.Abstractions;

[Visor(VisorProvider.SqlServer)]
public interface IUserRepository
{
    // 1. Simple Execute (Scalar)
    [Endpoint("sp_GetUserCount")]
    Task<int> GetCountAsync();

    // 2. Read Data (DTO Mapping)
    [Endpoint("sp_GetUserById")]
    Task<UserDto> GetUserAsync(int id);

    // 3. High-Performance Bulk Insert (TVP)
    [Endpoint("sp_ImportUsers")]
    Task ImportUsersAsync(List<UserItemDto> users);
}

2. Define your DTOs

Use [VisorColumn] with the universal VisorDbType enum. It automatically maps to SqlDbType.Int in MSSQL context.

[VisorTable("dbo.UserListType")] // Matches SQL User-Defined Type
public class UserItemDto
{
    [VisorColumn(0, VisorDbType.Int32)]
    public int Id { get; set; }

    [VisorColumn(1, VisorDbType.String, Size = 100)]
    public string Name { get; set; }
}

3. Register & Use

Visor generates the implementation class UserRepository automatically.

// In Program.cs
services.AddScoped<IVisorConnectionFactory>(sp => 
    new SqlServerConnectionFactory("Server=..."));
services.AddScoped<IUserRepository, UserRepository>();

// In your Service
public class MyService(IUserRepository repo)
{
    public async Task SyncUsers(List<UserItemDto> users)
    {
        // This executes with Zero Allocation!
        await repo.ImportUsersAsync(users);
    }
}

🐘 Quick Start (PostgreSQL)

Visor fully supports PostgreSQL via Npgsql. It maps List<T> parameters to PostgreSQL Arrays/Composite Types automatically.

1. Define Interface

Specify the provider in the attribute.

[Visor(VisorProvider.PostgreSql)] // <--- Switch to Postgres
public interface IPgUserRepo
{
    [Endpoint("sp_import_users")]
    Task ImportUsersAsync(List<PgUserItem> users);
}

2. Configure Bootstrapper (Important!)

PostgreSQL requires composite types to be registered at startup. Visor generates a helper method for this.

// In Program.cs
var dataSourceBuilder = new NpgsqlDataSourceBuilder("Host=...");

// This method is AUTO-GENERATED by Visor! 
// It registers all types marked with [VisorTable].
dataSourceBuilder.UseVisor(); 

var dataSource = dataSourceBuilder.Build();

services.AddScoped<IVisorConnectionFactory>(sp => 
    new PostgreSqlConnectionFactory(dataSource));

3. Define DTO

Use [VisorColumn] with Name property to map C# PascalCase to Postgres snake_case. Types are inferred automatically or mapped from VisorDbType.

[VisorTable("user_list_type")]
public class PgUserItem
{
    // Use 'Name' to map to lowercase Postgres columns
    [VisorColumn(0, Name = "id")] 
    public int Id { get; set; }

    // VisorDbType.String maps to 'text' in Postgres
    [VisorColumn(1, VisorDbType.String, Name = "user_name")]
    public string UserName { get; set; }
}

🎛️ Advanced: Output Parameters & Return Values

Real-world Enterprise Stored Procedures often return more than just data — they return pagination metadata, status codes, and calculated values via OUTPUT parameters.

Visor handles this elegantly using the Response Wrapper pattern.

1. Define a Wrapper Class

Create a class that describes everything the procedure returns. Use [VisorResultSet] for the data list, and [VisorOutput] for parameters.

public class SearchResult
{
    // The main data (SELECT * FROM ...)
    [VisorResultSet]
    public List<UserDto> Users { get; set; }

    // Maps to @TotalCount OUTPUT parameter
    [VisorOutput("TotalCount")]
    public int TotalCount { get; set; }

    // Maps to the RETURN statement (e.g., RETURN 0)
    [VisorReturnValue]
    public int ReturnCode { get; set; }
}

2. Update Interface

Return the wrapper class instead of a List.

[Visor(VisorProvider.SqlServer)]
public interface IAdvancedRepo
{
    [Endpoint("sp_SearchUsers")]
    Task<SearchResult> SearchAsync(string filter);
}

3. Usage

Visor automatically handles the ADO.NET complexity: executing the reader, mapping the list, closing the reader, and then populating the output parameters.

var result = await repo.SearchAsync("Alice");

Console.WriteLine($"Found {result.Users.Count} out of {result.TotalCount}.");
if (result.ReturnCode != 0) { /* Handle error */ }

🛡️ Transaction Support

Visor supports explicit transactions via the VisorDbLease pattern (Unit of Work).

public async Task CreateOrderFlow(OrderDto order)
{
    // Start a transaction scope (scoped per request)
    await _factory.BeginTransactionAsync();

    try 
    {
        // These repositories will automatically share the active transaction
        await _orders.CreateAsync(order);
        await _inventory.DecreaseStockAsync(order.ProductId, order.Quantity);

        await _factory.CommitTransactionAsync();
    }
    catch
    {
        await _factory.RollbackTransactionAsync();
        throw;
    }
}

🧠 The "White Box" Approach

Most ORMs are "Black Boxes" — they do magic at runtime that you can't see or debug easily. Visor is a "White Box".

• It generates readable C# code in your obj/Generated folder.
• You can step through the generated code with a debugger.
• You can see exactly how SqlDataReader or NpgsqlDataReader is being read.
• Strict by Default: If a column is missing in the result set, Visor throws a VisorMappingException immediately, preventing "silent zero" bugs in production.

🗺️ Roadmap

We are actively bridging the gap between raw speed and developer experience.

• Core Features: MSSQL & PostgreSQL Support, TVP Streaming, Transactions.
• Complex Mapping: Output Parameters, Return Values, Composite Types.
• NuGet Packaging: automated CI/CD flow.
• Railway Oriented Programming: Native support for Result<T> and functional flows.
• Resiliency: Retry policies & Circuit Breaker integration.
• Command Config: Per-command Timeouts & Behaviors.
• Observability: Advanced OpenTelemetry Tracing, Metrics & Logging.
• More Providers: Oracle, MySQL, CosmosDB, MongoDB.
• Configuration: Json-based Database configuration support.
• CLI Tool: Database-First scaffolding. (See README-CLI.md)

License

Distributed under the MIT License. See LICENSE for more information.