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:
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:
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.