Western Mass Hosting's mysql Articles

Building a High-Performance, Generic Data Access Layer in C# .NET: A Deep Dive

Introduction: Evolution of a Codebase

Several years ago, I shared a data access layer born from a transition from Visual Basic to the “sexy” world of C#. That original code served a crucial purpose: providing a fast, reusable, and strongly-typed bridge between SQL Server and C# objects. It featured a custom ORM mapper, synchronous and asynchronous execution paths, and caching support.

The .NET ecosystem has evolved significantly since then. With the mainstream adoption of async/await, dependency injection, and new performance APIs, it’s time to revisit this codebase. This article presents a comprehensive refactor of the original data access layer, addressing its weaknesses, enhancing its performance, and aligning it with modern .NET best practices—all without relying on any external libraries.

Critical Analysis of the Original Code

Before we dive into the new implementation, let’s acknowledge the strengths and weaknesses of the original design.

Strengths:

• Strong Typing: The generic GetResults<T> method provides excellent compile-time safety.
• Flexibility: Supports stored procedures and ad-hoc queries with parameters.
• Custom ORM: The Converter<T> class using Expression Trees is a powerful and fast mapping solution.
• Dual Paths: Offers both synchronous and asynchronous APIs.

Weaknesses to Address:

1. Async Anti-Patterns: The WrapperAsync class dangerously uses .Result, potentially causing deadlocks.
2. Resource Management: Connection and reader lifecycle could be more robust.
3. Parameter Handling: The manual SqlDbType mapping is complex and error-prone.
4. Tight Coupling: Relies on static settings, making testing and DI difficult.
5. Modern C# Features: Doesn’t leverage newer language features like record types, nullable contexts, or IAsyncEnumerable.

The Modernized Solution: Key Improvements

Our refactored code addresses these issues while maintaining the original architecture’s core benefits.

1. True Async/Await Implementation

The most critical fix is eliminating the dangerous Result and Wait() calls in the async path. We ensure all async operations are properly awaited throughout the call chain.

Before (Problematic in WrapperAsync):

using (DbDataReader r = db.GetResults().Result) // ❌ BLOCKING!
{
    _Query = Map(r).Result; // ❌ BLOCKING!
}

After (Proper Async in Refactored Code):

await using (var db = new DataAccessAsync(_connectionString))
{
    await using (var r = await db.GetResultsAsync()) // ✅ Proper await
    {
        _Query = await MapAsync(r); // ✅ Map is now truly async
    }
}

2. Simplified and Safer Parameter Handling

We replace the complex switch statement for SqlDbType with a more maintainable approach using SqlParameter directly, while still avoiding the performance pitfalls of AddWithValue for certain data types.

Refactored PrepareParams method:

protected void PrepareParameters(SqlCommand command)
{
    if (_parameterNames == null || _parameterValues == null) 
        return;

    for (int i = 0; i < _parameterNames.Length; i++)
    {
        var parameter = new SqlParameter(_parameterNames[i], _parameterValues[i]);

        // Only set explicit type if provided, otherwise let ADO.NET infer
        if (_parameterDataTypes != null && i < _parameterDataTypes.Length)
        {
            parameter.SqlDbType = _parameterDataTypes[i];
        }

        command.Parameters.Add(parameter);
    }
}

3. Modern Resource Management with IAsyncDisposable

We implement IAsyncDisposable alongside IDisposable for proper asynchronous resource cleanup, particularly important for the async data reader.

public class DataAccessAsync : IDisposable, IAsyncDisposable
{
    private SqlDataReader _reader;
    private SqlConnection _connection;
    private SqlCommand _command;
    private bool _disposed;

    public async ValueTask DisposeAsync()
    {
        await DisposeAsyncCore().ConfigureAwait(false);
        GC.SuppressFinalize(this);
    }

    protected virtual async ValueTask DisposeAsyncCore()
    {
        if (!_disposed)
        {
            if (_reader != null)
            {
                await _reader.DisposeAsync().ConfigureAwait(false);
                _reader = null;
            }

            if (_command != null)
            {
                _command.Dispose();
                _command = null;
            }

            if (_connection != null)
            {
                await _connection.DisposeAsync().ConfigureAwait(false);
                _connection = null;
            }

            _disposed = true;
        }
    }

    // IDisposable implementation remains for sync code
    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }
}

4. Dependency Injection Ready

We refactor the classes to accept dependencies (like connection strings) through constructors, making them testable and DI-friendly.

public class DataWrapper
{
    private readonly string _connectionString;
    private readonly ICacheProvider _cacheProvider;

    // Constructor injection enables proper testing and DI
    public DataWrapper(string connectionString, ICacheProvider cacheProvider = null)
    {
        _connectionString = connectionString ?? throw new ArgumentNullException(nameof(connectionString));
        _cacheProvider = cacheProvider;
    }

    public async Task> GetResultsAsync(string query, CommandType queryType, 
        string[] paramNames = null, object[] paramValues = null, 
        SqlDbType[] paramDataTypes = null, bool shouldCache = false, string cacheId = "") 
        where T : new()
    {
        // Implementation using injected dependencies
    }
}

The Complete Refactored Codebase

Here are the key components of our modernized data access layer:

1. DataAccess Class (Base)

using System;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;

namespace ModernDataAccess.Core
{
    public class DataAccess : IDisposable, IAsyncDisposable
    {
        private readonly string _connectionString;
        private SqlConnection _connection;
        private SqlCommand _command;
        private SqlDataReader _reader;
        private bool _disposed;

        // DI-friendly constructor
        public DataAccess(string connectionString)
        {
            _connectionString = connectionString ?? throw new ArgumentNullException(nameof(connectionString));
            InitializeComponents();
        }

        private void InitializeComponents()
        {
            _connection = new SqlConnection(_connectionString);
            _command = new SqlCommand { Connection = _connection };
        }

        public void SetCommand(string query, CommandType commandType, 
            string[] paramNames = null, object[] paramValues = null, 
            SqlDbType[] paramDataTypes = null)
        {
            _command.CommandText = query;
            _command.CommandType = commandType;
            _command.Parameters.Clear();

            if (paramNames != null && paramValues != null)
            {
                PrepareParameters(paramNames, paramValues, paramDataTypes);
            }
        }

        private void PrepareParameters(string[] paramNames, object[] paramValues, SqlDbType[] paramDataTypes)
        {
            for (int i = 0; i < paramNames.Length; i++)
            {
                var param = new SqlParameter(paramNames[i], paramValues[i]);

                if (paramDataTypes != null && i < paramDataTypes.Length)
                {
                    param.SqlDbType = paramDataTypes[i];
                }

                _command.Parameters.Add(param);
            }
        }

        public async Task ExecuteReaderAsync()
        {
            if (_connection.State != ConnectionState.Open)
            {
                await _connection.OpenAsync().ConfigureAwait(false);
            }

            _reader = await _command.ExecuteReaderAsync(CommandBehavior.CloseConnection)
                .ConfigureAwait(false);

            return _reader;
        }

        public async ValueTask DisposeAsync()
        {
            await DisposeAsyncCore().ConfigureAwait(false);
            GC.SuppressFinalize(this);
        }

        protected virtual async ValueTask DisposeAsyncCore()
        {
            if (!_disposed)
            {
                if (_reader != null)
                {
                    await _reader.DisposeAsync().ConfigureAwait(false);
                }

                _command?.Dispose();

                if (_connection != null)
                {
                    await _connection.DisposeAsync().ConfigureAwait(false);
                }

                _disposed = true;
            }
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!_disposed)
            {
                if (disposing)
                {
                    _reader?.Dispose();
                    _command?.Dispose();
                    _connection?.Dispose();
                }

                _disposed = true;
            }
        }
    }
}

2. Enhanced Object Mapper

using System;
using System.Collections.Concurrent;
using System.Data;
using System.Linq.Expressions;
using System.Reflection;

namespace ModernDataAccess.Core.Mapping
{
    public class ObjectMapper where T : new()
    {
        private static readonly ConcurrentDictionary> _cache = 
            new ConcurrentDictionary>();

        private readonly IDataRecord _record;
        private readonly Func _mapper;

        public ObjectMapper(IDataRecord record)
        {
            _record = record;
            var key = GenerateCacheKey(record);
            _mapper = _cache.GetOrAdd(key, k => CompileMapper(record));
        }

        private string GenerateCacheKey(IDataRecord record)
        {
            var fieldCount = record.FieldCount;
            var fieldNames = new string[fieldCount];

            for (int i = 0; i < fieldCount; i++)
            {
                fieldNames[i] = record.GetName(i);
            }

            return string.Join("|", fieldNames.OrderBy(n => n));
        }

        private Func CompileMapper(IDataRecord record)
        {
            var parameter = Expression.Parameter(typeof(IDataRecord), "record");
            var bindings = new List();
            var fieldCount = record.FieldCount;

            for (int i = 0; i < fieldCount; i++)
            {
                var fieldName = record.GetName(i);
                var property = typeof(T).GetProperty(fieldName, 
                    BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase);

                if (property == null || !property.CanWrite) 
                    continue;

                var fieldIndex = Expression.Constant(i);
                var valueExpression = Expression.Call(
                    parameter,
                    typeof(IDataRecord).GetMethod("GetValue", new[] { typeof(int) }),
                    fieldIndex);

                // Handle DBNull conversion
                var condition = Expression.Condition(
                    Expression.Equal(valueExpression, Expression.Constant(DBNull.Value)),
                    Expression.Default(property.PropertyType),
                    Expression.Convert(valueExpression, property.PropertyType));

                bindings.Add(Expression.Bind(property, condition));
            }

            var newExpression = Expression.New(typeof(T));
            var memberInit = Expression.MemberInit(newExpression, bindings);
            var lambda = Expression.Lambda>(memberInit, parameter);

            return lambda.Compile();
        }

        public T Map()
        {
            return _mapper(_record);
        }

        public static async IAsyncEnumerable MapAllAsync(IDataReader reader)
        {
            var mapper = new ObjectMapper(reader);

            while (await reader.ReadAsync().ConfigureAwait(false))
            {
                yield return mapper.Map();
            }
        }
    }
}

3. Modern Wrapper Class with Async Support

using ModernDataAccess.Core;
using ModernDataAccess.Core.Mapping;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;

namespace ModernDataAccess
{
    public class DataAccessWrapper : IDisposable, IAsyncDisposable
    {
        private readonly string _connectionString;
        private DataAccess _dataAccess;

        public DataAccessWrapper(string connectionString)
        {
            _connectionString = connectionString;
        }

        public async Task> GetResultsAsync(
            string query, 
            CommandType commandType = CommandType.Text,
            (string Name, object Value, SqlDbType? DataType)[] parameters = null,
            bool shouldCache = false, 
            string cacheKey = null) where T : new()
        {
            try
            {
                _dataAccess = new DataAccess(_connectionString);

                // Convert tuple parameters to arrays for backward compatibility
                var paramArrays = parameters != null ? 
                    ConvertParametersToArrays(parameters) : 
                    (null, null, null);

                _dataAccess.SetCommand(
                    query, 
                    commandType, 
                    paramArrays.Names, 
                    paramArrays.Values, 
                    paramArrays.DataTypes);

                await using var reader = await _dataAccess.ExecuteReaderAsync().ConfigureAwait(false);

                var results = new List();
                await foreach (var item in ObjectMapper.MapAllAsync(reader))
                {
                    results.Add(item);
                }

                return results;
            }
            finally
            {
                await DisposeAsync().ConfigureAwait(false);
            }
        }

        private (string[] Names, object[] Values, SqlDbType[] DataTypes) 
            ConvertParametersToArrays((string Name, object Value, SqlDbType? DataType)[] parameters)
        {
            var names = new string[parameters.Length];
            var values = new object[parameters.Length];
            var dataTypes = new SqlDbType[parameters.Length];

            for (int i = 0; i < parameters.Length; i++)
            {
                names[i] = parameters[i].Name;
                values[i] = parameters[i].Value;
                dataTypes[i] = parameters[i].DataType ?? SqlDbType.Variant;
            }

            return (names, values, dataTypes);
        }

        public async ValueTask DisposeAsync()
        {
            if (_dataAccess != null)
            {
                await _dataAccess.DisposeAsync().ConfigureAwait(false);
            }
        }

        public void Dispose()
        {
            _dataAccess?.Dispose();
        }
    }
}

Usage Example

using ModernDataAccess;
using System;
using System.Data;
using System.Threading.Tasks;

class Program
{
    static async Task Main(string[] args)
    {
        var connectionString = "Your_Connection_String_Here";

        using var wrapper = new DataAccessWrapper(connectionString);

        var parameters = new (string, object, SqlDbType?)[] 
        {
            ("@ZipCode", "01020", SqlDbType.VarChar),
            ("@RadiusMile", 100, SqlDbType.Float)
        };

        var results = await wrapper.GetResultsAsync(
            "List.ZipSearch",
            CommandType.StoredProcedure,
            parameters);

        Console.WriteLine($"Found {results.Count} locations");
    }
}

public class LocationInfo
{
    public long ZipID { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public double Latitude { get; set; }
    public double Longitude { get; set; }
}

Performance Considerations and Benchmarks

The refactored code maintains the performance characteristics of the original while adding safety:

1. Compiled Expression Caching: The ObjectMapper caches compiled delegates for each unique result set shape.
2. Async Overhead: Proper async/await has minimal overhead compared to the blocked threads in the original implementation.
3. Connection Pooling: ADO.NET's built-in connection pooling ensures efficient connection management.
4. Reduced Boxing: The improved parameter handling reduces boxing operations for value types.

Conclusion

This modernized data access layer preserves the original vision of a fast, flexible, and strongly-typed database access solution while addressing critical issues and aligning with contemporary .NET development practices. It demonstrates how to:

• Implement true async/await throughout the stack
• Manage resources safely with IAsyncDisposable
• Create DI-friendly components
• Maintain high performance with cached expression compilation
• Use modern C# features effectively

The result is a robust, maintainable, and performant data access solution that remains completely original and dependency-free, ready for use in modern .NET applications.