Axiom.DynamicSqlToJsonConverter 1.0.1

DapperSqlToJsonConverter

A high-performance, memory-efficient C# library that converts SQL Server stored procedure and query results directly to JSON without requiring predefined model classes. Built on top of Dapper for optimal performance and flexibility.

Features

• Model-Free: No need to define classes for your SQL results
• High Performance: Built on Dapper with optimized JSON serialization
• Memory Efficient: Streaming support for large datasets
• Flexible Output: JSON strings, JsonDocument, or direct file writing
• Batch Processing: Size-based and row-based batching options
• Custom JSON Options: Full control over JSON serialization settings
• Comprehensive Error Handling: Specific exception types for different failure scenarios
• SQL Server Optimized: Designed specifically for SQL Server with proper type handling

Installation

Install-Package DapperSqlToJsonConverter

Quick Start

using DynamicSqlToJsonConverter;
using Microsoft.Data.SqlClient;

var connectionString = "Server=.;Database=TestDB;Integrated Security=true;";
using var connection = new SqlConnection(connectionString);
var converter = new DapperSqlToJsonConvertor(connection);

// Execute stored procedure
var json = await converter.ExecuteStoredProcedureToJsonAsync(
    "GetCustomersByRegion",
    new { Region = "North", MinOrderValue = 1000 }
);

Console.WriteLine(json);

Core Methods

Stored Procedure Execution

// Basic execution
var json = await converter.ExecuteStoredProcedureToJsonAsync(
    "sp_GetSalesData",
    new { StartDate = DateTime.Now.AddMonths(-1), EndDate = DateTime.Now }
);

// With JsonDocument for advanced processing
var jsonDoc = await converter.ExecuteStoredProcedureToJsonDocumentAsync(
    "sp_GetSalesData",
    new { StartDate = DateTime.Now.AddMonths(-1) }
);

SQL Query Execution

// Direct SQL execution
var json = await converter.ExecuteQueryToJsonAsync(
    "SELECT CustomerID, CompanyName FROM Customers WHERE Country = @country",
    new { country = "USA" }
);

// With custom timeout
var json = await converter.ExecuteQueryToJsonAsync(
    "SELECT * FROM LargeTable WHERE Status = @status",
    new { status = "Active" },
    commandTimeout: 300 // 5 minutes
);

Streaming for Large Datasets

Row-Based Batching

// Process large datasets in batches of 10,000 rows
await foreach (var batch in converter.ExecuteStoredProcedureToJsonStreamAsync(
    "sp_GetAllOrders",
    batchSize: 10000))
{
    // Process each batch
    await ProcessBatch(batch);
}

Size-Based Batching

// Process data in batches up to 50MB each
await foreach (var batch in converter.ExecuteStoredProcedureToJsonStreamBySizeAsync(
    "sp_GetLargeDataset",
    maxBatchSizeBytes: 52_428_800)) // 50MB
{
    // Send to API, save to file, etc.
    await SendToApi(batch);
}

Direct File Writing

For extremely large datasets, write directly to disk to minimize memory usage:

// Write results directly to file (memory efficient)
await converter.WriteStoredProcedureToFileAsync(
    "sp_GetAllOrderHistory",
    @"C:\exports\order_history.json",
    new { StartDate = DateTime.Now.AddYears(-2) },
    commandTimeout: 600, // 10 minutes
    bufferSize: 131072,  // 128KB buffer
    flushInterval: 5000  // Flush every 5000 rows
);

Custom JSON Configuration

var customOptions = new JsonSerializerOptions
{
    PropertyNamingPolicy = JsonNamingPolicy.SnakeCaseLower,
    WriteIndented = false, // Compact JSON
    PropertyNameCaseInsensitive = true,
    DefaultIgnoreCondition = JsonIgnoreCondition.WhenWritingNull
};

var converter = new DapperSqlToJsonConvertor(connection, customOptions);

Data Type Handling

The library automatically handles SQL Server data types with appropriate JSON conversions:

• DateTime/DateTimeOffset: Converted to ISO 8601 format ("2023-12-01T10:30:00.000Z")
• Guid: Converted to string representation
• Binary data (byte[]): Converted to Base64 strings
• Decimal/Numeric: Preserved as JSON numbers
• NULL/DBNull: Converted to JSON null
• NaN/Infinity values: Converted to JSON null for safety

Error Handling

The library provides specific exception types for better error handling:

try
{
    var json = await converter.ExecuteStoredProcedureToJsonAsync("sp_GetData");
}
catch (DatabaseException ex)
{
    // SQL Server specific errors
    logger.LogError($"Database error: {ex.Message}");
}
catch (JsonConversionException ex)
{
    // JSON serialization errors
    logger.LogError($"JSON conversion error: {ex.Message}");
}
catch (ConverterException ex)
{
    // General converter errors
    logger.LogError($"Converter error: {ex.Message}");
}

Advanced Usage Examples

Processing Multiple Result Sets

// For stored procedures that return multiple result sets
var jsonDoc = await converter.ExecuteStoredProcedureToJsonDocumentAsync("sp_GetDashboardData");

// Access as JsonDocument for complex processing
var root = jsonDoc.RootElement;
if (root.ValueKind == JsonValueKind.Array && root.GetArrayLength() > 0)
{
    foreach (var item in root.EnumerateArray())
    {
        // Process each row
        ProcessDataRow(item);
    }
}

Dynamic Parameter Building

// Using Dapper's DynamicParameters
var parameters = new DynamicParameters();
parameters.Add("@StartDate", DateTime.Now.AddMonths(-3));
parameters.Add("@EndDate", DateTime.Now);
parameters.Add("@CategoryId", 5);

var json = await converter.ExecuteStoredProcedureToJsonAsync(
    "sp_GetProductSales",
    parameters
);

Dependency Injection Setup

// In your DI container setup
services.AddScoped<IDbConnection>(provider => 
    new SqlConnection(connectionString));

services.AddScoped<DapperSqlToJsonConvertor>(provider =>
{
    var connection = provider.GetRequiredService<IDbConnection>();
    var jsonOptions = new JsonSerializerOptions
    {
        PropertyNamingPolicy = JsonNamingPolicy.CamelCase,
        WriteIndented = true
    };
    return new DapperSqlToJsonConvertor(connection, jsonOptions);
});

Performance Considerations

• Connection Management: The converter doesn't manage connection lifetime - ensure proper disposal
• Memory Usage: Use streaming methods for datasets larger than available memory
• JSON Options: WriteIndented = false reduces output size for network transfer
• Batch Sizes: Tune batch sizes based on your memory constraints and processing requirements
• File Writing: Direct file writing is most memory-efficient for very large datasets

Requirements

• .NET 6.0 or higher
• Microsoft.Data.SqlClient 5.0+
• Dapper 2.0+
• System.Text.Json (included in .NET 6+)

Thread Safety

The DapperSqlToJsonConvertor class is not thread-safe. Create separate instances for concurrent operations or implement appropriate synchronization.

License

MIT License - see LICENSE file for details.

Contributing

Contributions are welcome! Please feel free to submit issues, feature requests, or pull requests.

Support

For questions and support, please open an issue on the GitHub repository.