Generic Database Frontend– Filtering, Free Text Search, Sorting & Paging

• Download Database Frontend.zip - 1.1 MB
• Demo project

Introduction

This article describes a server and client side code including explanations of a generic database frontend web application. In addition, it provides a standard UI for all database tables, which can be customized to generate a specific UI or additional supplements in both client and server applications.

Background

As a developer, most of my projects have involved database UI implementations where the schema of the database was the essence of the system design. For all web apps or mobile apps I provided an admin that was straightforward CRUD (Create, Read, Update and Delete) UI for the databases behind the apps. There were other systems, in which most of the work was to provide repeated CRUD UI with minor customization. There are several tools that provide rapid development capabilities with specific code generation and ORM objects. Since performing customization using closed source tools can take more time than developing an entire code, I developed an open source tool that provides a better solution, which I improved during each project. This tool has saved me development time when generic UI was insufficient but also gave me a relatively easy way to customize special requirements, in both client and server sides. Since describing the entire generic infrastructure is too complex, I have divided the description of this tool into several articles. Each article describes additional generic features. The grid that is displayed in this article has almost no CSS. You are welcome to share with us your CSS suggestions.

Scope

This article deals with the most basic table features of Sort, Filter, Search, Paging and CRUD. The subsequent articles will deal with the following generic features: multi selection checklist, sub-grids, role-based security, Internationalization, dates, rich text, autocomplete – avoid using dropdowns and checklists with large tables, dependency between fields in a add or edit dialog such as Country and City, import & export, track changes, change multiple rows with a single dialog, formula columns, media management that connects popular storages such as Amazon or Azure, menus, CRUD driven workflow, grid editing, client side validation, additional view designs such as outlook style, Google style and contacts style, handle multiple schemas, implementation for other databases such as MySQL, and more. Each article will address one or more of the above features. Feedback from you will help to determine the order of these articles. In the meantime you are welcome to review the features in action in backand.com.

The Problem

The main challenge of data based systems is selecting the data that is displayed in a grid, as we do not necessarily want the whole data of the database table to appear, primarily since the user does not really need it. For example, in the case of a bit type column that has 0 or 1 values, we want to display a check sign because it is more meaningful for the user. In the case of a foreign key column with numeric values that refers to the Employees table, we want to display the employee full name instead of its numeric identification. In this article we will concentrate on presenting the relations between the tables in the database. To make it simple we will concentrate on the parent end of the relation. In the grid, instead of the foreign key values, we will show descriptive values and we will show a dropdown select element with the same descriptive values in add and edit dialogs. By default the system uses the first textual column as the descriptive statement of its table, though this may not always be the case. For example in the case of the Employees table, if we want to show the employee full name we can do the following:

employeessTableInfo.DescriptiveSelectStatement = "[Employees].[First Name] + ' ' + [Employees].[Last Name]";

In the following sections, we will use the child end of the relation to show multi selection checklist and expand and collapse sub-grids.

Using the code 

This system 

Server Side Architecture 

This system is implemented with ASP.NET MVC platform. In MVC (Model View Controller) the controller produces a model and assigns it to a view, which uses it to create the http response markup. This system does the same. The basic controller in this system is called TableController. It uses the TableModelGenerator to generate the TableModel. The tableController assigns the TableModel to the table views. The TableModelGenerator is using two main components: SqlGenerator and UiInfo. The SqlGenerator generates SQL statements for the controller actions. The UiInfo contains and enables configuration of general user interface information, such as the order and title of the columns and what style sheet classes to use. The TableModel also contains an object that enables further customization of the view depending on the data itself. In our example the Shipping Fee background cells have a different color depending in the cell value.

UI Info

The UI Info provides the ability to configure how to display the aspx and ascx views. The root object is the Schema Info. It has all the general information that is not related to any of the tables. For example, if I would like to add a logo image and place it on several views, the Schema Info will be the place to put such an item. I did not do it in this example, but you are welcome to add it yourself. The Schema Info also has a collection of Table Info objects. The table Info object contains all the general info of a table, for example, the table title, its page size, columns order etc., which means that each of the DataTables in the dataset has an associated Table Info object. The Table Info also has a collection of Data Info and here it gets a little bit tricky. The DataInfo is an abstract class; it can be either SimpleDataInfo, ParentRelationInfo or ChildRelationInfo. SimpleDataInfo relates to a non-foreign key DataColumn. ParentRelationInfo relates to the parent end of the DataRelation and ChildRelationInfo to the child end of the DataRelation. The ParentRelationInfo and ChildRelationInfo can relate to more than one DataColumn depending on the relation columns. In this article the ChildRelationInfo does not take an active part, but in future articles it will implement multi selection check-lists and sub-grids.

SQL Statements Generator,security and preformance

The SqlGenerator is responsible for generating SQL statements. All the SQL statements use SqlParameters to prevent SQL injections. An optimistic looking is assumed so it uses with(nolock) in all the FROM statements. To get the row count of the entire table the sysindexes table is used instead of count(*) for better performance.

Client Side Architecture

The entire client side is a UI example and so you can change it completely, but must follow two basic rules:

1. Always send the filter, sorting and paging parameters in all the ajax calls.
2. Use the controller name as a parameter because different tables use different controllers

Usage

To run the demo simply download it and open it with vs 2010 with ASP.NET MVC4, if you do not have it you can download it here. You will also need SQL Server running on your machine with the default .\sqlexpress instance. If you do not have SQL Server on your machine or you do not use the default instance, change the connection string in the web.config and point it to your SQL Server instance with the correct credentials. If you are getting this error message: "The user instance login flag is not supported on this version of SQL Server. The connection will be closed." remove the "User Instance=true" from the connection string.

Since this infrastructure is an open source and not a binary component, in order to use it in your solution, you will need to copy some files:

1. For the models and controllers, copy the Generic folder
2. For the views, copy the Views/Generic folder
3. In addition to the default jquery scripts, jquery-1.7.1.js and jquery-ui-1.8.20.js, copy the Script/Generic folder, it contains:
   1. database.frontend.js
   2. jquery.ajax.filter.js
   3. jquery.clearsearch-1.0.3.js 
   4. jquery.highlight.v4.js
4. For the CSS, in addition to the jQuery css, copy the Content/Generic folder

There are a few levels of usage. The first level, encompassing steps 1-4, enables you to connect your database to the system but without performing any customizations; it just uses the system default configuration. You will need to do a little inheritance and overriding. For the second level of usage, including also step 5, you can change the default configuration of the UiInfo component with your own customized information. You do not need to inherit or override anything for this level. For the third level, including steps 6-8, you can customize a view depending on the row's values. Finally, for the last level, step 9, you can simply create your own view or copy a view and add some more markup to it.

To use the standard UI without making any adjustments perform the following steps:

1. Add a dataset that will serve as the database schema. As described in the server side architecture. In our example it is NorthwindDataSet. By default, the system uses the connection string from the web.config with the "ConnectionString" configuration key. If you are using a different key then you should override the GetTableModelGenerator() method in the TableController and supply the connection string as a costructor parameter.
2. Inherit the Table Controller and Override the GetDataSet method to return the specific dataset you created. This inherited controller will be the base controller for all the tables. See the code example below.
3. Override the GetSchemaInfo and set the BaseController to the controller name you created. In our example the BaseController is NorthwindController.

   public class NorthwindController : TableController // replace NorthwindController with your controller
   {
       // add this method to your controller, without changing anything
       protected override DatabaseFrontend.Generic.UiInfo.SchemaInfo GetSchemaInfo()
       {
           DatabaseFrontend.Generic.UiInfo.SchemaInfo schemaInfo = base.GetSchemaInfo();
           schemaInfo.BaseController = GetControllerName(); 
   
           return schemaInfo;
       }
   
       // add this method to your controller and return your dataset instead of NorthwindDataSet
       protected override DataSet GetDataSet()
       {
           return new NorthwindDataSet(); // Replace NorthwindDataSet with your dataset
       }
   
       // in the web.config, if your connection string has a different key then "ConnectionString" then add this method to your controller and replace "your key" with your key
       protected override DatabaseFrontend.Generic.Models.TableModelGenerator GetTableModelGenerator()
       {
           // change the connection string
           return new DatabaseFrontend.Generic.Models.TableModelGenerator(System.Configuration.ConfigurationManager.ConnectionStrings["your key"].ConnectionString);
       }

4. Add another route to your routes. In vs 2010 by default, it is located in the RouteConfig class. In this new route, the controller should be the new controller you created and tableName should be the first table name that you want your users to see when they open the web site for the first time.

   public class RouteConfig
   {
       public static void RegisterRoutes(RouteCollection routes)
       {
           routes.IgnoreRoute("{resource}.axd/{*pathInfo}");
   
           // add this route
           routes.MapRoute(
                   name: "Default",
                   url: "{controller}/{action}/{tableName}",
                   defaults: new { controller = "Orders" /* Replace "Orders" with your controller */, action = "Index", tableName = "Orders" /* replace "Orders" with your first time table */ } 
               );
       }
   }

   ** It is recommended that the dataset will have the relationships between the tables, because in this way the system knows how to display meaningful data instead of primary key codes. If you are using the dataset designer and you have relationships in the database, the dataset will show them automatically, but if you want to remove them from the database, you can later add them only to the dataset.

Customizations

The Northwind example demonstrates how to inherit and override actions in the controllers. It is also possible to inherit and override the Model and the UI Info infrastructure but the design intention is that these actions would be unnecessary. The Model and UI Info is intended to be generic such that the controllers will be responsible for customized code. However, it is recommended to enhance the Model and UI Info with additional generic code as you see fit.

The following steps will describe some of the customization that you can do. If you want to add specific UI to the UI information object, perform this additional step:

5. In the GetSchemaInfo method get the Table Info objects that you want to change and change their properties

If you want, for example, to change the CSS of elements depending on the row’s data, like I did in the shipping fee example, then perform the additional following steps:

6. Inherit the Base controller you created before, and name it with the name of the table that you want to customize. In our example the OrdersController inherits the NorthwindController
7. Override the method GetCustomizationHandler and return a new Inherited class for CustomizationHandler
8. In this inherited class override the method GetDataCellCss. This method accepts the data row and the UI info object as parameters, so you can manipulate them and return a customized CSS. In our example we return red, yellow or green background of the cell depending in the value.

   public class OrdersCustomizationHandler : CustomizationHandler
   {
       public override string GetDataCellCss(DataInfo dataInfo, System.Data.DataRow row)
       {
           if (dataInfo.Name == ((DatabaseFrontend.Models.NorthwindModels.NorthwindDataSet)dataInfo.TableInfo.SchemaInfo.Schema).Orders.Shipping_FeeColumn.ColumnName)
           {
               decimal shippingFee = 0;
               if (Decimal.TryParse(dataInfo.GetValue(row), out shippingFee))
               {
                   if (shippingFee >= 100)
                   {
                       return CustomizationHandler.RedBackground;
                   }
                   else if (shippingFee < 100 && shippingFee >= 10)
                   {
                       return CustomizationHandler.YellowBackground;
                   }
                   else if (shippingFee < 10)
                   {
                       return CustomizationHandler.GreenBackground;
                   }
                   else
                   {
                       return base.GetDataCellCss(dataInfo, row);
                   }
               }
               else
               {
                   return base.GetDataCellCss(dataInfo, row);
               }
           }
           else
           {
               return base.GetDataCellCss(dataInfo, row);
           }
       }
   }

9. In this way you can override additional actions and handle them with customized logic to return a customized Index-view entirely different from the standard one. You can create one view and return it in the overridden GetIndexViewName method. In our example I return the OrdersIndex.aspx and OrdersTable.ascx views because I wanted to add a legend that describes the red, yellow and green colors. I only added some markup. You can create an entirely different markup and populate it with the Model.

   protected override string GetIndexViewName(TableModel tableModel)
   {
       return "~/Views/Northwind/OrdersIndex.aspx";
   }