The official PASS Blog is where you’ll find the latest blog posts from PASS community members and the PASS Board. Contributors share their thoughts and discuss a wide variety of topics spanning PASS and the data community.

Handling Flexible Search Needs Using Stored Procedures and Dapper

Introduction

Data access from applications has traditionally followed one of two routes: either using stored procedures or using ORMs. Both have their advantages and drawbacks. In this article, we look at how to handle flexible searching needs using the stored procedure approach.

Problem

Traditionally, even systems that perform data access only through stored procedures face challenges when it comes to searching data. While simple CRUD is easy, searching on multiple fields can lead to one of several suboptimal solutions. Developers may introduce a stored procedure for each search combination – this often happens when a system evolves its search capabilities – or they may introduce branching logic to existing stored procedures, or they may decide to introduce an ORM just for searching, such as Entity Framework or LINQ-to-SQL.

Approach

So how do we avoid these issues? We combine two concepts: executing Dynamic SQL with sp_executesql and using Dapper as a lightweight ORM to map objects. Together, these will allow us to search using a flexible combination of terms without code or plan bloat.

Requirements

We are building an API using .NET Core on the Wide World Importers database. We have already created model classes that mirror our database objects and have basic CRUD functions covered. We have received a request for an endpoint that will return Orders with associated Order Lines that match the following search criteria using “and” logic: Customer ID, Sales Person ID, Contact Person ID, Order Date, Expected Delivery Date, Customer Purchase Order Number, Stock Item ID, Description, Quantity, and Unit Price. Any, all, or none of those search parameters may be supplied by consumers of the API. Additionally, it should be easy to add more parameters without breaking existing code.

Solution

The Stored Procedure

First, we write our stored procedure. We are going to place some decently complex logic in here, so we will look at each part in turn.

The declaration of the stored procedure is as expected. Note that all parameters are optional with a default value of ‘NULL’. The purpose of this will be explained further down.

From here, we set up the base of our dynamic SQL statement:

Next, we add each parameter to a table variable like so:

After we have checked all the parameters, we add them together to form a single where clause and add that to our base SQL statement.

We create the parameter list:

Lastly, we call sp_executesql using our dynamic SQL, the parameter list, and each of the parameters we passed in above.

Because we have created fully parameterized SQL, SQL Server will create only one execution plan for each combination of parameters we pass in. Additionally, we can safely pass the NULL parameters, and sp_executesql will ignore those. It is even possible to pass sorting criteria and filter by other tables by adding joins dynamically. (Note: the code included in the Github repository at the end of the article also contains debug logic).

The API

Our ASP.NET Core 3.1 application already has a connection string to our database available via configuration (and injectable using .NET Core’s built-in dependency injection framework). We also have other conveniences set up.

Our first step is to define our search model:

From here, we define our controller endpoint:

Now, we need to define our repository method. This is a three-step process.

First, we need to create a mapper to help Dapper map the result of our stored procedure:

Then, we add the method signature to the interface for the Order Repository:

Last, we wire up the method inside the Order Repository. We only add parameters that have values from the API, and we create a concrete version of the mapper.

Finally, all that is left is to build our code and test the endpoint. As it is a normal RESTful API, we can use a variety of tools to test our GET method, even using Chrome.

Conclusion

In this article, you have learned how to leverage dynamic SQL to create a flexible stored procedure that can be called from Dapper and used for flexible search needs from a RESTful API. This method can even be extended for use with Entity Framework Core using the ‘FromSqlRaw’ method. Using this technique allows for plan reuse and flexible searching while avoiding potentially ugly ORM-generated SQL. 

Additional Reading and Resources

Source Code from the article: https://github.com/danielmallott/flexible-search-with-dapper
Wide World Importers database: https://github.com/microsoft/sql-server-samples/tree/master/samples/databases/wide-world-importers
Brent Ozar on Dynamic SQL: https://www.brentozar.com/sql/dynamic/
Dapper: https://github.com/StackExchange/Dapper
Entity Framework Core Raw SQL: https://docs.microsoft.com/en-us/ef/core/querying/raw-sql

Daniel Mallott
About the author

Dan Mallott is a Chicago-based consultant for West Monroe Partners in their Technology Practice. His passion is for service-layer development, particularly database technologies, including Microsoft SQL Server and Apache Cassandra.

Recent projects have included a new API layer written in .NET CORE for a mid-sized US health insurer that unifies many disparate data sources and develops a data roadmap for migrating from a DataStage and Sybase-based architecture to an event processing architecture for another mid-sized US health insurer.

In his spare time, he can be found writing his own software instead of using package solutions, or on the ice as an ice hockey player and referee.

Please login or register to post comments.

Theme picker

Back to Top
cage-aids
cage-aids
cage-aids
cage-aids