In the administrative software, one of the most common forms is data grid. It appears in many parts of the application for reports, alerts or just as a representation of the system objects. Data grids are a great candidate for a wrapper around the tools you use to generate them. Dynamic queries aim to give you a centralized way of creating data grids and changing their appearance as well as data just by configuration. This article aims to show how to centralize data-grids in a software application. We are also describing common problems you can face while developing data grid oriented software, solutions with dynamic query approach, and technical implementation details to adopt the pattern.
INTRODUCTION
Developing data grid oriented applications you may face a number of problems.
- Repeating code
There are many ways that show how to generate the grids with external libraries. However, no matter what you choose, you end up with big pieces of code repeating all over your application. The problem is that most of the libraries you use support more functionalities than the typical form actually needs.
- Often changing requirements related to data appearance
Parts of the appearance of the data grid, such as paging, records number shown in a page, ability to sort or filter a column, initial sort or filter, can be frequently changed in order to respond to every user's requirements and needs.
- Often changing requirements related to data nature
A data set is potentially prone to change due to changing business requirements. For example, over time specific data may require a combination with different data which will change its origin query.
- Authorization problem
This problem is related to safety and the idea not to expose data unintended for the user. To tackle all of the problems, we decided to expose a centralized part of the code for creating data grids. In the database, we have the following structure:
In the SysPlaceholder table we store SQL queries which have the role of dynamic database views.
In the DynamicQuery table we make a relation between the SQL and the actual data grid object. Using different settings, multiple grids can share a common SQL query for their origin data.
DYNAMIC QUERY CONFIGURATION AND DYNAMIC QUERY CONFIGURATION SETTING
Configuration uses two tables – DynamicQueryConfiguration and DynamicQueryConfigurationSetting. There is a set of configuration rules assigned to the dynamic query for easier maintaining of a large number of settings. For example, you can have a configuration “Standard view” – that contains the settings – paging, filtering, etc. and another one - “Simple view” - which has only paging.
Columns of the data grid are described in the DynamicQueryColumn table which contains different metadata for the columns, like title, order, is the column has sorting or filtering, it’s datatype etc. It is important to note that the workname of the column should match a selected field from the SQL. For example, if we have the column City in the SysPlaceholder used by the DQ, we need to have Select [sth] as City from [table].
Often, in the data grid we don’t have only data. DynamicQueriesAction is a table that stores different actions which the grid can have. Every action has handler which can be standard button, special button, select of the row, action in the toolbar of the grid, etc. It also has an ActionName which refers to a name of a function in the front-end.
The backend service in our case is a web API OData controller. An obstacle we faced here was to create a view model with dynamic properties. We used Odata Open types [1]. We also used IQuerable [2] functionality in .NET. The automatic generation of SQL statements gave us the ability to manage the authorization. We created a helper that gets filtered subset from every table. It, then, replaces the sets referred from the SysPlaceholder SQL with these subsets by string replace in the SQL string.
In the front end, we have created a wrapper object that takes care of the visualization of the grid. It communicates with the backend service and it is with different unique dynamic queries id-s from different parts of the client’s code.
CONCLUSION
Using the centralized way of creating data grids – dynamic queries - we were able to simplify and speed up the software development. We write less code and we are more flexible to change the appearance of the data grids just from configuration, without the need for redeployment procedures. We have cleared our way to create a Dynamic Query administration and even enabled the users of our product to manage all configurations by themselves. In addition, one of the main benefits is the security. Thus, all of our queries safe and the whole client code has access to save subset of the data.
Written by: Elena Kirilova (Software Developer)
REFERENCES
https://blogs.msdn.microsoft.com/odatateam/2014/07/20/tutorial-sample-open-complex-type-step-by-step-with-web-api-2-2-for-odata-v4-0/ http://stackoverflow.com/questions/34354230/where-to-find-translated-linq-to-entity-query-to-sql
