This repository includes an example front-end website, and a back-end database, for demonstrating fully-dynamic filtering capabilities (column, operator, value). Fully protected from SQL Injection, and based on "low-code development" principles.
It is the accompanying open-source project for the webinar Advanced Dynamic Search Queries by Eitan Blumin.
This is an enhanced version of FilterParseXMLParameters which is available here:
https://eitanblumin.com/2018/10/28/dynamic-search-queries-versus-sql-injection
The new version introduces two new methods for dynamically parsing filter sets:
- Json parameter sets.
- Table-Valued Parameters.
As mentioned above, this repository also includes a fully-functional demo web app, implemented in ASP.NET Core MVC + AngularJS, to demonstrate the intended functionality on the front-end side.
- .NET Core 2.2 Installed
- Microsoft SQL Server 2016 version or newer
- Microsoft Visual Studio 2017 Community or newer
- SQL Server Data Tools (SSDT) for Visual Studio
- Start by forking or cloning this repository to your computer, and opening the DynamicFilters solution in Visual Studio.
- Creating the Database: Do one of the following:
- Open the DemoDB_Create.sql script file and run it in your local SQL Server instance (must be in SQLCMD mode). Or:
- Manually publish the DemoDB.dacpac file into your database. Or:
- Open the DemoDB database project, and publish it to your local SQL Server instance.
- Optionally: Change the connection string in
\DemoWebClient\appsettings.jsonin case you're not using default settings (localhost server, DemoDB database, Windows Authentication). - Whenever you want to run the app: Right click on the
\DemoWebClient\runme.batexecutable and Run it as Administrator. - The web app should now be available at http://localhost:5000 (you may also build the app from the web project, and the address would be http://localhost:61907 )
This GitHub repository also includes an accompanying Powerpoint presentation, available here:
The "FilterParse" stored procedures are the "main engine" for this solution. They can be found here:
The last two procedures (for Json and Xml) also have versions which can be used as "wrappers" that relay the information into the first procedure (using Table Valued Parameters). This should improve performance for scenarios involving large filter sets:
Additionally, these two procedures also have alternate versions that implement "Encapsulation" using an additional inner sp_executesql command, which should improve performance issues caused by bad parameter sniffing:
In addition to the stored procedures, the following "meta-data" tables are also needed in order to hold important configuration of tables, columns and operators: