Oris.DataKit

A lightweight, enterprise-ready database helper library for .NET that eliminates boilerplate code for running stored procedures and functions. Built with async-first patterns, comprehensive error handling, and enterprise-grade features.

Features

• 🚀 Async-First: All operations are fully asynchronous
• 🔄 Transaction Support: Built-in transaction management with scope patterns
• 📊 Bulk Operations: High-performance bulk insert and update operations
• 🔍 Strongly Typed Queries: Automatic mapping to strongly typed objects
• 📄 Pagination: Built-in pagination support with total count
• 🔧 Dependency Injection: Full DI container integration
• 📝 Comprehensive Logging: Built-in logging with performance monitoring
• 🔄 Retry Policies: Configurable retry policies for transient failures
• 🏢 Enterprise Ready: Connection pooling, error handling, and monitoring

Installation

dotnet add package Oris.DataKit

Quick Start

1. Register Services

services.AddOrisDataKit(options =>
{
    options.ConnectionString = "YourConnectionString";
    options.CommandTimeout = 30;
    options.EnableLogging = true;
    options.EnablePerformanceMonitoring = true;
});

2. Basic Usage

public class UserService
{
    private readonly IDbHelper _dbHelper;

    public UserService(IDbHelper dbHelper)
    {
        _dbHelper = dbHelper;
    }

    public async Task<IEnumerable<User>> GetUsersAsync()
    {
        return await _dbHelper.QueryAsync<User>(
            "sp_GetUsers", 
            CommandType.StoredProcedure);
    }

    public async Task<User> GetUserByIdAsync(int id)
    {
        return await _dbHelper.QueryFirstAsync<User>(
            "sp_GetUserById", 
            CommandType.StoredProcedure,
            new SqlParameter("@Id", id));
    }

    public async Task<int> CreateUserAsync(string name, string email)
    {
        return await _dbHelper.ExecuteNonQueryAsync(
            "sp_CreateUser", 
            CommandType.StoredProcedure,
            new SqlParameter("@Name", name),
            new SqlParameter("@Email", email));
    }
}

Advanced Features

Transaction Management

await using (var scope = new DbTransactionScope(connectionString))
{
    await scope.DbHelper.ExecuteNonQueryAsync(
        "INSERT INTO Users(Name) VALUES(@Name)",
        CommandType.Text,
        new SqlParameter("@Name", "John"));

    await scope.DbHelper.ExecuteNonQueryAsync(
        "INSERT INTO UserProfiles(UserId, Bio) VALUES(@UserId, @Bio)",
        CommandType.Text,
        new SqlParameter("@UserId", userId),
        new SqlParameter("@Bio", "Software Developer"));

    scope.Commit(); // Both operations are committed together
}

Bulk Operations

var users = new List<User>
{
    new User { Name = "Alice", Email = "alice@example.com" },
    new User { Name = "Bob", Email = "bob@example.com" }
};

await _dbHelper.BulkInsertAsync(users, "Users");

Pagination

var (users, totalCount) = await _dbHelper.ExecutePaginatedAsync<User>(
    "SELECT Id, Name, Email FROM Users",
    pageNumber: 1,
    pageSize: 10,
    sortColumn: "Name",
    sortDescending: false);

Multiple Result Sets

var (users, departments) = await _dbHelper.QueryMultipleAsync<User, Department>(
    "SELECT * FROM Users; SELECT * FROM Departments",
    CommandType.Text);

Configuration Options

services.AddOrisDataKit(options =>
{
    options.ConnectionString = "YourConnectionString";
    options.CommandTimeout = 30;                    // Command timeout in seconds
    options.EnableLogging = true;                   // Enable operation logging
    options.EnablePerformanceMonitoring = true;     // Enable performance monitoring
    options.MaxRetryCount = 3;                      // Retry attempts for transient failures
    options.RetryBaseDelayMs = 1000;                // Base delay for retries
    options.RetryMaxDelayMs = 30000;                // Maximum delay for retries
    options.EnableConnectionPooling = true;         // Enable connection pooling
    options.MaxPoolSize = 100;                      // Maximum pool size
    options.MinPoolSize = 0;                        // Minimum pool size
});

API Reference

Core Methods

Method	Description
ExecuteNonQueryAsync	Executes a non-query command (INSERT, UPDATE, DELETE)
ExecuteScalarAsync<T>	Executes a command and returns a single scalar value
ExecuteReaderAsync	Executes a command and returns a forward-only data reader
ExecuteDataTableAsync	Executes a command and returns a DataTable
ExecuteDataSetAsync	Executes a command and returns a DataSet
QueryAsync<T>	Executes a query and maps results to strongly typed objects
QueryFirstAsync<T>	Executes a query and returns the first record
QuerySingleOrDefaultAsync<T>	Executes a query and returns a single record or null
QueryMultipleAsync<T1, T2>	Executes a query and returns multiple result sets
BulkInsertAsync<T>	Performs bulk insert operation
BulkUpdateAsync<T>	Performs bulk update operation
ExecutePaginatedAsync<T>	Executes a query with pagination support
ExecuteJsonAsync	Executes a query and returns JSON string
ExecuteDictionaryAsync<TKey, TValue>	Executes a query and returns a dictionary

Transaction Methods

Method	Description
BeginTransactionAsync	Begins a database transaction
CommitTransactionAsync	Commits the specified transaction
RollbackTransactionAsync	Rolls back the specified transaction

Error Handling

The library includes comprehensive error handling with:

• Automatic retry for transient SQL errors
• Detailed logging of errors and performance metrics
• Graceful connection management
• Transaction rollback on failures

Performance Monitoring

When enabled, the library automatically tracks:

• Query execution times
• Operation counts
• Error rates
• Connection pool statistics

Requirements

• .NET 6.0 or later
• SQL Server 2012 or later
• Microsoft.Data.SqlClient

License

MIT License - see LICENSE file for details.

Contributing

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

Support

For issues and questions, please create an issue on GitHub.