Why I Created SqlBuilder with Dapper – A Practical Utility for Modern .NET Applications

In many of my projects, I found myself repeatedly writing stored procedures (procs) just for performing standard CRUD (Create, Read, Update, Delete) operations—especially for simple master tables. This was not only time-consuming but also added unnecessary boilerplate to the solution. Most of these procs followed a similar structure with only slight variations in table names and columns, which led me to think: Why not automate this?

 1. Motivation Behind SqlBuilder

Rather than repeatedly writing stored procedures for every table and operation, I wanted a solution that allowed me to define table names, filters, and options at runtime and generate SQL dynamically. That’s where SqlBuilder was born. It offers methods for generating INSERT, UPDATE, DELETE, SELECT, and pagination queries using a consistent and type-safe approach.

For more complex logic, I still use stored procedures via a DapperContext class. Here, helper attributes like [IgnoreParam] and [IgnoreOnInsert] help control how properties are mapped to SQL parameters.

In short, this utility gave me a way to:

• Eliminate repetitive SQL and stored procedure writing.
• Automatically generate SQL queries dynamically and safely.
• Use a clean, attribute-driven approach to fine-tune query generation.
• Improve development speed and reduce boilerplate across projects.

This solution has now become a core part of my backend development workflow.

2. Dynamic SQL Generation for Real Use Cases

SqlBuilder is designed to build SQL queries dynamically based on runtime inputs like filter columns, operators, and sorting logic. It supports reusable, parameterized queries without the need to hardcode logic for each table, allowing for rapid development of grid listings, search pages, and filtered reports.

3. Multi-Database Support (SQL Server, MySQL, PostgreSQL)

The utility supports SQL Server, MySQL, and PostgreSQL out of the box. It handles:

- Database-specific identifier quoting ([], ``, "")
- Different pagination syntax (OFFSET FETCH, LIMIT OFFSET)
- SQL operators compatible with the respective engines

4. IMemoryCache Integration for Performance

To improve performance and reduce repeated SQL string generation, the library integrates with IMemoryCache. Each generated query is cached using a unique cache key composed of the table name, filter columns, and SQL operators. Parameter values are not cached—they're passed via DynamicParameters.

This allows the SQL to be reused efficiently across requests while ensuring that dynamic values stay safe and separate.

5. SQL Injection Safety by Design

Although the utility dynamically generates SQL text, all values are passed using parameterized queries via Dapper's DynamicParameters. This ensures that the application is protected from SQL injection vulnerabilities.
SELECT * FROM [Category] WHERE [Name] LIKE @Name;

6. Powerful Filtering, Searching, and Pagination

The utility offers methods like:
- BuildDynamicFilterQueryWithParams
- BuildCountQueryWithFilters
- BuildPagedQuery
These handle dynamic WHERE clauses, LIKE and IN operators, sort order, and pagination—all essential for user-facing tables and search features.

7. Operator Enum for Safety and Clarity

I introduced a SqlOperator enum to standardize supported SQL operators (=, LIKE, <, >, IN, etc.). This allows consistent logic and compile-time safety when specifying filters. An extension method ToSqlString() translates enum values to database-specific SQL syntax.

8. Clean, Decoupled Architecture

The library separates query generation (SqlBuilder) from execution (DapperContext). This makes it flexible, reusable across ASP.NET Core APIs, Blazor apps, background services, and even future desktop or MAUI apps.

9. Built for Real-World Development

Some real-world features and use cases include:
- Nullable support for optional filters
- Case-sensitive identifiers in PostgreSQL
- Auto-generated parameter names like @Name_0, @Status_1
- Reusable logic for audit logs, export features, and dashboard metrics
- BuildSelectIn, BuildSearchQuery, and dynamic column selectors

10. Extensible by Design

Future versions of the library may include:
- Lambda/Expression-based filtering
- JOIN and subquery builders
- Automatic logging/debug tracing of SQL
- Entity-to-table mapping via conventions or attributes
- Advanced filter chaining

Tips 

1. Full Project code  (I have tested all code in API Project)
2. How-to-use-SQL-Builder
3. MSSQL Database SQL script

Conclusion

The RS.Dapper.Utility library with its SqlBuilder class is a pragmatic tool born from hands-on experience building data-heavy enterprise applications. It reduces repetitive work, ensures consistency, speeds up delivery, and adapts to multiple database engines while maintaining performance and security.

If your project uses Dapper and involves significant CRUD operations, this utility might save you hours of boilerplate code and help you deliver faster with cleaner architecture.

Note: I have tested RS.Dapper.Utility with MSSQL and MYSQL database, PostgreSql databse testing is pending .This may not be the perfect solution, and there’s always room for improvement, but I have tested this and I can say it will save your time. I’d love to hear your feedback—please share your thoughts or suggestions if you spot anything that can be enhanced.

Thanks, for reading the blog, I hope it helps you. Please share this link on your social media accounts so that others can read our valuable content. Share your queries with our expert team and get Free Expert Advice for Your Business today.