Skip to content

Latest commit

 

History

History
69 lines (43 loc) · 5.24 KB

README.md

File metadata and controls

69 lines (43 loc) · 5.24 KB
Raw

Dynamic Filters (a.k.a. FilterParseSearchParameters)

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:

  1. Json parameter sets.
  2. 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.

Demo Web Client App Screenshot

Prerequisites

Installation & Setup

  1. Start by forking or cloning this repository to your computer, and opening the DynamicFilters solution in Visual Studio.
  2. 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.
  3. Optionally: Change the connection string in \DemoWebClient\appsettings.json in case you're not using default settings (localhost server, DemoDB database, Windows Authentication).
  4. Whenever you want to run the app: Right click on the \DemoWebClient\runme.bat executable and Run it as Administrator.
  5. 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 )

Presentation

This GitHub repository also includes an accompanying Powerpoint presentation, available here:

Main Stored Procedures

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:

Main Meta-Data Tables

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: