Dapper With Stored Procedures: A Simple Enterprise Pattern

Why teams choose Dapper

Dapper is lightweight, fast, and easy to understand. It does not hide SQL from the developer, which is useful when a project depends on stored procedures, reporting queries, and controlled database access.

The pattern

A clean implementation uses a repository method for every database operation. The repository creates parameters, executes a stored procedure, maps results to a model, and returns data to the service layer. The controller never needs to know the stored procedure name.

Stored procedure actions

For simple master modules, one action-based stored procedure can handle INSERT, UPDATE, DELETE, GETBYID, GETALL, and GETACTIVE. For transactional modules, keep procedures more focused when the business rule is complex.

What to validate

• Required fields before saving.
• Duplicate slug, email, or code values.
• Foreign key references before insert or update.
• Soft delete rules for records already used in transactions.

Practical advice

Use Dapper for database access, but keep business rules outside SQL where possible. SQL should protect data integrity and return reliable data. The service layer should decide how the application behaves.