Mapping Dynamic Stored Procedures with Repository Pattern Reflection

Dozens of stored procedures for CRUD activities, reports, imports, exports, and business logic are frequently encountered in large enterprise applications. In the past, developers would manually map parameters, read DataTables, and transform results into models in a distinct C# function for every process. However, this results in excessive connection between your C# layer and SQL, repetitive code, and difficult-to-maintain repositories.

Making your repository dynamic, which uses Reflection to automatically convert any stored procedure to a C# model, is a better strategy. This article will teach you how to use Reflection inside a Repository Pattern to construct a Dynamic Stored Procedure Mapper with complete control, robust typing, and clear architecture.

1. Why Reflection-Based Mapping?

Let’s understand why this approach is useful.

Problems with Traditional Repository Methods

• You manually create commands for each stored procedure.
• Each method repeats parameter setup and mapping.
• Any DB schema change requires code changes everywhere.
• Adding new stored procedures increases maintenance.

Goal

Create a single generic method like this:

var result = repository.ExecuteStoredProcedure<MyModel>("spGetEmployeeDetails", parameters);

…and it should automatically:

• Create a SQL Command
• Execute the stored procedure
• Read results
• Map columns to the model using property names

That’s the power of Reflection.

2. High-Level Architecture

Let’s visualize the flow of this approach.

Flowchart – Dynamic Stored Procedure Mapping

+---------------------------------------------------+
| Controller / Service Layer                        |
| Calls Repository.ExecuteStoredProcedure<T>()      |
+---------------------------+-----------------------+
                            |
                            v
+---------------------------------------------------+
| Repository Layer                                   |
| - Uses SqlHelper / ADO.NET                         |
| - Passes parameters dynamically                    |
| - Executes stored procedure                        |
| - Maps result set to model using Reflection        |
+---------------------------+-----------------------+
                            |
                            v
+---------------------------------------------------+
| SQL Server Stored Procedure                        |
| - Returns data matching C# model                   |
| - Executes business logic                          |
+---------------------------------------------------+

3. Step-by-Step Implementation

Let’s now build this dynamically.

Step 1: Create a Base Repository

using System.Data;
using System.Data.SqlClient;
using System.Reflection;

public class BaseRepository
{
    private readonly string _connectionString;

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

    public async Task<List<T>> ExecuteStoredProcedureAsync<T>(
        string storedProcedureName,
        Dictionary<string, object>? parameters = null)
        where T : new()
    {
        var resultList = new List<T>();

        using (var connection = new SqlConnection(_connectionString))
        {
            await connection.OpenAsync();
            using (var command = new SqlCommand(storedProcedureName, connection))
            {
                command.CommandType = CommandType.StoredProcedure;

                if (parameters != null)
                {
                    foreach (var param in parameters)
                        command.Parameters.AddWithValue(param.Key, param.Value ?? DBNull.Value);
                }

                using (var reader = await command.ExecuteReaderAsync())
                {
                    var props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

                    while (await reader.ReadAsync())
                    {
                        var obj = new T();

                        foreach (var prop in props)
                        {
                            if (!reader.HasColumn(prop.Name) || reader[prop.Name] == DBNull.Value)
                                continue;

                            prop.SetValue(obj, Convert.ChangeType(reader[prop.Name], prop.PropertyType));
                        }

                        resultList.Add(obj);
                    }
                }
            }
        }

        return resultList;
    }
}

Step 2: Add a Helper Extension for Column Checking

public static class DataReaderExtensions
{
    public static bool HasColumn(this IDataRecord reader, string columnName)
    {
        for (int i = 0; i < reader.FieldCount; i++)
        {
            if (reader.GetName(i).Equals(columnName, StringComparison.OrdinalIgnoreCase))
                return true;
        }
        return false;
    }
}

This ensures that the code doesn’t break even if the stored procedure returns fewer or renamed columns.

Step 3: Create Your Repository

public class EmployeeRepository : BaseRepository
{
    public EmployeeRepository(string connectionString) : base(connectionString)
    {
    }

    public async Task<List<EmployeeModel>> GetEmployeesAsync(long departmentId)
    {
        var parameters = new Dictionary<string, object>
        {
            { "@DepartmentId", departmentId }
        };

        return await ExecuteStoredProcedureAsync<EmployeeModel>("spGetEmployeeDetails", parameters);
    }
}

Step 4: Your Model Class

public class EmployeeModel
{
    public long EmployeeId { get; set; }
    public string EmployeeName { get; set; }
    public string Department { get; set; }
    public DateTime JoiningDate { get; set; }
}

The property names must match the column names in your stored procedure result set.

Step 5: Example Stored Procedure

CREATE PROCEDURE spGetEmployeeDetails
    @DepartmentId BIGINTASBEGIN
    SELECT EmployeeId, EmployeeName, Department, JoiningDate
    FROM Employee
    WHERE DepartmentId = @DepartmentIdEND

4. How Reflection Helps Here

Reflection allows us to:

• Inspect properties of the model at runtime
• Match them dynamically with SQL result columns
• Assign values safely without writing manual mapping logic

So even if you add a new model tomorrow, you don’t have to write a new mapper — it just works automatically.

5. Error Handling and Logging

You can easily extend the base repository for:

• Exception handling using try-catch
• Logging errors using Serilog or NLog
• Handling DBNull values safely

Example

try
{
    // Execute logic
}
catch (SqlException ex)
{
    _logger.LogError(ex, $"Error executing {storedProcedureName}");
    throw;
}

6. Advantages of This Approach

7. When to Use and When Not To

Use it when:

• You have many stored procedures returning tabular data.
• You follow repository/service architecture.
• You want to reduce mapping boilerplate.

Avoid it when:

• You use Entity Framework with LINQ and tracking.
• You have dynamic column structures that frequently change.

8. Advanced Enhancements

You can extend this further by:

• Returning DataSet for multi-table results.
• Mapping to dynamic objects.
• Adding caching for stored procedure results.
• Using Dapper underneath for performance boost.

Conclusion

By combining the Repository Pattern with Reflection, you can turn your database access layer into a powerful, dynamic engine. You no longer need dozens of repetitive stored procedure methods — one reusable method can handle them all. This approach keeps your architecture clean, flexible, and future-ready, especially in projects with heavy SQL-based business logic.

Best ASP.NET Core 10.0 Hosting

The feature and reliability are the most important things when choosing a good ASP.NET Core 10.0 hosting. HostForLIFE is the leading provider of Windows hosting and affordable ASP.NET Core , their servers are optimized for PHP web applications such as the latest ASP.NET Core 10.0 version. The performance and the uptime of the ASP.NET Core  hosting service are excellent, and the features of the web hosting plan are even greater than what many hosting providers ask you to pay for. At HostForLIFE.eu, customers can also experience fast ASP.NET Core hosting. The company invested a lot of money to ensure the best and fastest performance of the datacenters, servers, network and other facilities. Its data centers are equipped with top equipment like cooling system, fire detection, high-speed Internet connection, and so on. That is why HostForLIFE.eu guarantees 99.9% uptime for ASP.NET Core . And the engineers do regular maintenance and monitoring works to assure its ASP.NET Core  hosting are security and always up.