Dynamic Stored Procedure Mapping Using Reflection in Repository Pattern

Introduction

In large enterprise applications, you often deal with dozens of stored procedures for CRUD operations, reporting, imports, exports, and business logic.
Traditionally, developers write a separate C# method for each procedure — manually mapping parameters, reading DataTables, and converting results into models.

But this leads to repetitive code, hard-to-maintain repositories, and high coupling between your C# layer and SQL.

A better approach is to make your repository dynamic, where it can automatically map any stored procedure to a C# model — using Reflection.

In this article, you’ll learn how to implement a Dynamic Stored Procedure Mapper using Reflection inside a Repository Pattern, with full control, strong typing, and clean 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

Feature	Benefit
Reusable	One method for all stored procedures
Dynamic	Automatically maps models
Strongly Typed	No manual DataTable parsing
Clean	Keeps repository lean and focused
Scalable	Works across modules and entities

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.