SMART.Data.Infrastructure 1.0.9

🚀 SMART.Data.Infrastructure

A lightweight, high-performance data access layer built on Dapper with built-in retry policies, connection management, and audit capabilities.

<span class="badge badge-blue">.NET 6.0+</span> <span class="badge badge-green">Dapper 2.0.151+</span> <span class="badge badge-orange">Polly 7.2.3+</span>

📦 Installation

dotnet add package SMART.Data.Infrastructure

Or via Package Manager Console:

Install-Package SMART.Data.Infrastructure

✨ Features

• ⚡ Dapper-based - High-performance micro-ORM for .NET
• 🔌 Connection Management - Automatic connection handling with proper disposal
• 🔄 Retry Policies - Built-in Polly retry policies for transient failures
• 📝 Audit Trail - Built-in audit logging for all database operations
• 🔗 Transaction Support - Seamless transaction handling across multiple queries
• ⏱️ Async First - Full async/await support for better scalability
• 📄 Pagination Support - Built-in pagination for large datasets
• 🎯 Command Pattern - Structured query execution with metadata

📋 Prerequisites

• .NET 6.0 or higher
• SQL Server 2016 or higher
• Dapper 2.0.151 or higher

⚙️ Configuration

Program.cs Setup

using SMART.Data.Infrastructure.Abstractions;
using SMART.Data.Infrastructure.Core;
using SMART.Data.Infrastructure.Options;

var builder = WebApplication.CreateBuilder(args);

// Configure database options
builder.Services.Configure<DatabaseOptions>(builder.Configuration.GetSection("Database"));

// Register services
builder.Services.AddSingleton<IDbConnectionFactory, DbConnectionFactory>();
builder.Services.AddScoped<IDbExecutor, DbExecutor>();

appsettings.json

{
  "Database": {
    "ConnectionString": "Server=localhost;Database=YourDB;Trusted_Connection=true;MultipleActiveResultSets=true",
    "DefaultCommandTimeout": 30,
    "EnableRetryOnFailure": true,
    "MaxRetryCount": 3,
    "RetryDelayInSeconds": 2
  }
}

📚 API Reference

IDbExecutor Methods

<table>

<thead>

<tr>

<th>Method</th>

<th>Description</th>

<th>Return Type</th>

</tr>

</thead>

<tbody>

<tr>

<td>QueryAsync<T></td>

<td>Executes a query and returns multiple results</td>

<td>Task<IEnumerable<T>></td>

</tr>

<tr>

<td>QueryFirstOrDefaultAsync<T></td>

<td>Executes a query and returns first result or default</td>

<td>Task<T?></td>

</tr>

<tr>

<td>QuerySingleAsync<T></td>

<td>Executes a query and returns a single result</td>

<td>Task<T></td>

</tr>

<tr>

<td>ExecuteCommandAsync<T></td>

<td>Executes a command and returns a result</td>

<td>Task<CommandResult<T>></td>

</tr>

<tr>

<td>ExecuteCommandAsync</td>

<td>Executes a command with no return value</td>

<td>Task<CommandResult></td>

</tr>

<tr>

<td>GetPagedAsync<T></td>

<td>Executes a paginated query</td>

<td>Task<PagedResult<T>></td>

</tr>

<tr>

<td>QueryMultipleAsync</td>

<td>Executes query with multiple result sets</td>

<td>Task<SqlMapper.GridReader></td>

</tr>

</tbody>

</table>

CommandResult

public class CommandResult<T>
{
    public int Status { get; set; }      // 200 = Success, 400 = Failure
    public int RowsAffected { get; set; } // Number of rows affected
    public T? Data { get; set; }          // Returned data (e.g., new ID)
    public string Message { get; set; }   // Success or error message
    public bool IsSuccess => Status == 200;
}

💻 Usage Examples

1. Create Query Class

public static class GetUserByIdQuery
{
    public static IDbQuery Create(int userId) =>
        new DbQuery(
            Sql: "SELECT * FROM Users WHERE UserId = @UserId",
            Parameters: new { UserId = userId },
            Metadata: new QueryMetadata
            {
                Name = "User.GetById",
                Type = QueryType.Select,
                TablesAffected = new List<string> { "Users" }
            }
        );
}

2. Create Repository

public class UserRepository
{
    private readonly IDbExecutor _dbExecutor;

    public UserRepository(IDbExecutor dbExecutor)
    {
        _dbExecutor = dbExecutor;
    }

    public async Task<User?> GetByIdAsync(int userId)
    {
        var sql = "SELECT * FROM Users WHERE UserId = @UserId";
        return await _dbExecutor.QueryFirstOrDefaultAsync<User>(sql, new { UserId = userId });
    }

    public async Task<CommandResult<int>> CreateAsync(User user)
    {
        var sql = @"
            INSERT INTO Users (Name, Email, CreatedAt) 
            VALUES (@Name, @Email, @CreatedAt);
            SELECT SCOPE_IDENTITY();";

        return await _dbExecutor.ExecuteCommandAsync<int>(sql, user);
    }
}

3. Multiple Result Sets

public async Task<ApplicationDetailDto?> GetApplicationDetailAsync(int applicationId)
{
    var sql = @"
        SELECT * FROM Applications WHERE ApplicationId = @ApplicationId;
        SELECT * FROM Interviews WHERE ApplicationId = @ApplicationId;
        SELECT * FROM Documents WHERE ApplicationId = @ApplicationId;";

    using var multi = await _dbExecutor.QueryMultipleAsync(sql, new { ApplicationId = applicationId });

    var application = await multi.ReadSingleOrDefaultAsync<ApplicationDetailDto>();
    if (application == null) return null;

    var interviews = (await multi.ReadAsync<InterviewDto>()).ToList();
    var documents = (await multi.ReadAsync<DocumentDto>()).ToList();

    application.Interviews = interviews;
    application.Documents = documents;

    return application;
}

4. Paginated Query

public async Task<PagedResult<User>> GetUsersPagedAsync(int pageNumber, int pageSize)
{
    var sql = @"
        SELECT * FROM Users 
        ORDER BY CreatedAt DESC 
        OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

    var countSql = "SELECT COUNT(*) FROM Users";
    var parameters = new { Offset = (pageNumber - 1) * pageSize, PageSize = pageSize };

    return await _dbExecutor.GetPagedAsync<User>(sql, countSql, parameters, pageNumber, pageSize);
}

5. Transaction Support

public async Task<CommandResult> TransferMoneyAsync(int fromAccount, int toAccount, decimal amount)
{
    using var connection = await _dbExecutor.GetConnectionAsync();
    connection.Open();
    using var transaction = connection.BeginTransaction();

    try
    {
        var withdrawSql = "UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountId = @FromAccount";
        await _dbExecutor.ExecuteCommandAsync(withdrawSql, new { Amount = amount, FromAccount = fromAccount }, transaction);

        var depositSql = "UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountId = @ToAccount";
        await _dbExecutor.ExecuteCommandAsync(depositSql, new { Amount = amount, ToAccount = toAccount }, transaction);

        transaction.Commit();
        return CommandResult.Success(2);
    }
    catch
    {
        transaction.Rollback();
        throw;
    }
}

🛡️ Error Handling

try
{
    var result = await _dbExecutor.ExecuteCommandAsync<int>(sql, parameters);

    if (result.IsSuccess)
    {
        Console.WriteLine($"Success: {result.Message}, ID: {result.Data}");
    }
    else
    {
        Console.WriteLine($"Error: {result.Message}");
    }
}
catch (SqlException ex)
{
    Console.WriteLine($"Database error: {ex.Message}");
}
catch (Exception ex)
{
    Console.WriteLine($"General error: {ex.Message}");
}

⚡ Performance Tips

• Use QueryMultipleAsync for multiple result sets in one round trip
• Use parameterized queries to prevent SQL injection and enable plan caching
• Use appropriate command timeouts for long-running queries
• Dispose connections properly (automatic with using statements)
• Use pagination for large datasets
• Avoid N+1 queries by using joins or multiple result sets

🔐 Security Best Practices

• Always use parameterized queries (never concatenate strings)
• Use connection strings with minimal privileges
• Implement proper audit logging for sensitive operations
• Use encryption for sensitive data at rest and in transit
• Store connection strings in secure configuration (e.g., Azure Key Vault, User Secrets)

📊 Logging

public class UserRepository
{
    private readonly IDbExecutor _dbExecutor;
    private readonly ILogger<UserRepository> _logger;

    public UserRepository(IDbExecutor dbExecutor, ILogger<UserRepository> logger)
    {
        _dbExecutor = dbExecutor;
        _logger = logger;
    }

    public async Task<User?> GetByIdAsync(int userId)
    {
        _logger.LogInformation("Fetching user with ID: {UserId}", userId);

        try
        {
            var result = await _dbExecutor.QueryFirstOrDefaultAsync<User>(
                "SELECT * FROM Users WHERE UserId = @UserId", 
                new { UserId = userId });

            _logger.LogInformation("Successfully fetched user: {UserId}", userId);
            return result;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error fetching user: {UserId}", userId);
            throw;
        }
    }
}

🧪 Unit Testing

[TestClass]
public class UserRepositoryTests
{
    private Mock<IDbExecutor> _mockDbExecutor;
    private UserRepository _repository;

    [TestInitialize]
    public void Setup()
    {
        _mockDbExecutor = new Mock<IDbExecutor>();
        _repository = new UserRepository(_mockDbExecutor.Object);
    }

    [TestMethod]
    public async Task GetByIdAsync_ShouldReturnUser_WhenUserExists()
    {
        // Arrange
        var expectedUser = new User { UserId = 1, Name = "John Doe" };
        _mockDbExecutor.Setup(x => x.QueryFirstOrDefaultAsync<User>(It.IsAny<string>(), It.IsAny<object>()))
            .ReturnsAsync(expectedUser);

        // Act
        var result = await _repository.GetByIdAsync(1);

        // Assert
        Assert.IsNotNull(result);
        Assert.AreEqual(1, result.UserId);
    }
}

📦 Dependencies

• Dapper (>= 2.0.151)
• Polly (>= 7.2.3)
• Microsoft.Extensions.Options (>= 6.0.0)
• System.Data.SqlClient (>= 4.8.5)

📝 Version History

<table>

<thead>

<tr>

<th>Version</th>

<th>Date</th>

<th>Changes</th>

</tr>

</thead>

<tbody>

<tr>

<td>1.0.0</td>

<td>2024-01-15</td>

<td>Initial release with Dapper integration, retry policies, and audit capabilities</td>

</tr>

</tbody>

</table>

📄 License

This library is proprietary and confidential. Unauthorized copying, distribution, or use is strictly prohibited.

🤝 Support

For issues, questions, or support, please contact the SMART development team.

Built with ❤️ by the SMART Development Team