Contents
Introduction
This article presents a generic web user interface for CRUD (create, read, update, delete) applications implemented as a web control for ASP.net and SQL Server. Entirely metadata driven, this tool can adapt to different database structures.
With it the user interface (fields titles, positions, visual groups, CSS classes) and its database mapping (tables, columns, stored procedures) are not defined in the code but in external metadata. It may behave like a to do list, an address book, a wine cellar, or any CRUD application you may want to build. It is ideal to quickly build functional components for database web applications or web site administration pages (which only need simple validation rules).
This web control can be nested into any ASP.net page. It will generate at run-time all necessary web forms, manage user interaction, and database CRUD operations automatically.
Because this tool feeds on metadata to change web form, structure, and volume, I called it "Evolutility" which in biology means "The faculty possessed by all substances capable of self-nourishment of manifesting the nutritive acts by changes of form, of volume, or of structure".
Sample Applications
To illustrate how simple CRUD applications can be used for more than administration pages, this article provides a demo project with several sample applications:
- To do list
- Address book
- Memo pad
- Restaurant list
- Database structure documentation
- Resources for the Blinds
- Wine cellar
On one hand, from a developer point of view, these applications are similar because these use the same patterns and (if hand-coded) very similar code; on the other hand, from a user point of view, these are extremely different because these serve different purposes (like tracking lists of tasks or contacts, publishing a list of favorite restaurants, or managing a wine bottles inventory) and use very different sets of fields.
The "to do list" is the example used in my previous article on minimalist meta-model for CRUD applications so that the reader can get a hands on experience of metadata driven applications.
The "address book", "memo pad", "restaurant list", and "resources for the blind" are other simple CRUD applications provided to make the patterns obvious by repetition.
The "Database structure documentation" application is more interesting as it queries SQL Server metadata (system tables sysobjects and syscolumns) as if it where regular data to document the database automatically. It can be quite useful as it is always handy (published on the web) and always up-to-date because it is generated at run-time.
It can be used to documents tables:
as well as views, triggers, and stored procedures:
The last sample application is the "wine cellar" with a more complex UI using a larger number of fields grouped into panels and tabs:
and a master details for wine degustations.
All these sample applications are not running different compiled code but different instances of the same web control. Live demos available live at Evolutility.com.
A different kind of CRUD applications is discussed in my next article on Wizard and CRUD applications to build other CRUD applications.
Security and users access management
Evolutility web control has built-in features for user identification, row level security, collaboration, and user comments. You may use them or bypass them with your own custom code in the page nesting the control, for example to choose an XML file or another depending on the user.
At the component level (in the ASPX page)
The SecurityModel property of the control provides different ways of letting users view and update records:
- Single_User: every users can view or edit everything
- Single_User_Password: login and password are required to view or edit data
- Multiple_Users_RLS: each user only sees and edit his/her own data (row level security)
- Multiple_Users_Sharing: every user sees all data but can only edit records he/she created
In order to use Multiple_Users_RLS, and Multiple_Users_Sharing, the driving table must have a column called "UserID" (of type integer). Multiple_Users_Sharing also need a column called "Publish" (of type bit or integer).
The DBReadOnly property can prevent users from editing record, making the form Read-Only.
DBAllowDelete, DBAllowInsert, and DBAllowUpdate properties can use to allow or disallowed database functions like Delete, Insert and Update.
At the field level (in the metadata)
Individual Fields can be editable or Read-Only, using the readonly attribute of each field element.
Fields can be required or optional value (attributes required and optional).
In addition, you can choose to have different XML documents all together for different users.
User login is handled by Evolutility
When SecurityModel is set to Single_User_Password, or Multiple_Users_RLS, or Multiple_Users_Sharing, Evolutility will prompt users to login the first time the page is displayed.
The stored procedure for user/password validation can be specified in the XML using the splogin attribute of the data element.
A sample stored procedure for user identification called "EvoDemoSP_Login" is provided with the samples. It can be modified to fit your specific needs.
Sharing login information between different pages: Evolutility forms will share their login information with other Evolutility forms on the same web application which uses the same value for their DBApplicationID property. This way, users will only log-in once for all (or some) Evolutility forms.
Custom security
User identification: You can use your own login screen for each page with custom code forwarding to a login page if the user is not identified by your application.
Limiting access to specific fields for specific users: Let's take an example: two users can look at product information, and one is allowed to see the price of products but the other is not. You can create 2 component definitions (mapping to the same set of database tables), one including the product price, the other not. Then, use custom code in the page to identify the user and bind the Evolutility web control to one definition or the other.
Managing users
Managing users is as simple as any other entity; you just need to use Evolutility to map the users table.
You can create different pages as follows:
- one page for the administrator to manage users
- one page (using SecurityModel=Multiple_Users_RLS) to let each user edit his/her own profile
- one page (using SecurityModel=Multiple_Users_Sharing) to let users browse the profile of other users.
Collaboration and user comments
Any component can be set to allow users to post comments on records. This is done using the web control property UserComments. By default, comments are saved in the database table "Evol_Comment" but another table can be specified using the attribute dbtablecomments in the XML.
When allowing user comments, the number of comments on each record is displayed in lists:
Comments on record (if any) are displayed on the bottom of the page with a text box to enter new comments:
Evolutility web control properties
Most of the tool's flexibility comes from its metadata (described in my previous article on meta-model), but like all .NET web controls, it also has properties. Besides the standard WebControls properties inherited from the .NET class, Evolutility provides the following properties:
AllowSorting: Allow search results to be sorted by columns. Setting it to false removes the up and down arrows for sorting records in the list header.BackColorRowMouseOver: Determines color to highlight rows when the mouse cursor passes over.CollapsiblePanels: Determines if user can show and hide panels.DBAllowDelete: Properties used to allow or disallow specific database queries. DBReadOnly need to be False for these properties to take action.DBAllowExport: Enables the export feature (for lists and single records).DBAllowHelp: Will display the content of the help attribute of each field while in edit mode.DBAllowInsert: Properties used to allow or disallow database record insertion on the driving table. DBReadOnly need to be False for these properties to take action.DBAllowInsertDetails: Properties used to allow or disallow database record insertion on the details table.DBAllowPrint: Specify whether or not the “Print” button should be displayed on the toolbar.DBAllowSearch: Specify whether or not the "Search" and "Advanced Search" buttons should be displayed on the toolbar.DBAllowSelections: Displays a "Selection” button on the toolbar to provide a list of custom queries. Need queries and query defined in the XML.DBAllowUpdate: Properties used to allow or disallow modification of records on the driving table. DBReadOnly need to be False for these properties to take action.DBAllowUpdateDetails: Properties used to allow or disallow in-place edition of the details table.DBReadOnly: Make the page read only. Disables Add, Edit, and Delete on all records (toolbar buttons disappear when DBReadOnly is set to true).DesignDisplayMode: Allows you to get a WYSIWYG display of any mode of the web form during design time.DesignTabIndex: Allows you to get a WYSIWYG display of any tab of the Edit or View modes of the web form during design time.DesignWebPath: Set the path to the XML directory at design-time. This path is concatenated to the XML filename.DisplayMode (Read Only): Indicates the current display mode (View, Edit, List, Search, Advanced Search, or Selections).DisplayModeStart: Mode in which Evolutility is displayed the first time the page is called.ItemID (Read Only): ID of the current record.Language: Localization of all text displayed in the control and date format. English and French are currently available. Possible values:
NavigationLinks: Show navigation links (for First, Previous, Next, and Last records) on the bottom of the page when editing or viewing records.RowsPerPage: Number of rows displayed at one time in search results. This property is only available if a stored procedure for paging is specified in the XML of the application definition.SecurityKey: Key to allow credential sharing among components.SecurityModel: Manage users in 4 different ways with one of the following built-in security model:
- Single_user - no password protection
- Single_user_password - credentials required
- multiple_users_RLS - each user only sees and edit his/her own data (row level security)
- multiple_users_sharing - every user sees all data but can only edit records he/she created
Note: multiple_users_RLS and multiple_users_sharing require your object database table to have a column of type integer called "UserID".ShowTitle: Determines the display of a header title.SQLConnection: Connection string to the database. To avoid entering this property for every web control of your application, it can be set in the Web.config file of the application.Text: Introduction text only displayed the first time the form is called.Title: Application title.ToolbarPosition: Position/display of the toolbar. Possible values:
UseCache: Enables or disables caching of lists of values. Setting it to true will improve performances.UserComments: Allows users to post comments on specific records. Possible values:
- None
- Read only
- Logged Users
- Anonymous
UserID (Read Only): ID of the current user (0 if the user is not logged in or Evolutility is used in Single User mode).VirtualPathDesigner: Sets the virtual path to app designer.VirtualPathPictures: Sets the virtual path to the pictures used in fields.VirtualPathToolbar: Sets the virtual path to the toolbar pictures.XMLFile: Name of the XML file containing the metadata, it can be specified as an absolute path as "c:/EVOLUTILITY/XML/AddressBook.XML"; or as a relative path (recommended) as "XML/AddressBook.XML"; or with a wildcard <assemblypath> for the assembly path as "<assemblypath>AddressBook.XML" which allows the XML files to be in BIN directory with Evolutility.DLL.
Server events
Currently Evolutility provides one single event, DBChange that is raised when a record is Inserted, Updated or Deleted.
The event argument DBChange specifies the Action (Insert, Update, or Delete) and ID where action is a string specifying the database query and ID is the primary key of the record to which the action was applied to.
Custom code in the page nesting the control can be triggered by the web control doing an action to the database. Here is a code sample to display the last database action in a label.
C#
private void Evolutility1_DBChange(object sender,
Evolutility.Evolutility.DatabaseEventArgs e) {
switch(e.Action) {
case "INSERT":
Label1.Text = String.Format("Record {0} added.", e.ID.ToString());
break;
case "UPDATE":
Label1.Text = String.Format("Record {0} updated.", e.ID.ToString());
break;
case "DELETE":
Label1.Text = String.Format("Record {0} deleted.", e.ID.ToString());
break;
}
}
VB.net
Private Sub evo1_DBChange(ByVal sender As System.Object, ByVal e As
Evolutility.Evolutility.DatabaseEventArgs) Handles evo1.DBChange
Select Case e.Action
Case "INSERT"
Label1.Text = "Record " & e.ID & " added to database"
Case "UPDATE"
Label1.Text = "Record " & e.ID & " updated"
Case "DELETE"
Label1.Text = "Record " & e.ID & " deleted"
End Select
End Sub
This "single event implementation" works but is quite limited. Adding better server events is now one of the top priorities for Evolutility. Now that the UI and database code are in place the next step is to make the tool more open and extendable.
Navigation Links
When linking to a page which embeds the Evolutility web control, it is possible to alter the normal behavior of the control using request variables in the link. Possible examples are as follow:
Choosing a specific record
- View the record of ID=5:
MyPage.aspx?ID=5
Choosing a specific mode
To open the page in a specific mode, you can do the following:
- Create a new record:
MyPage.aspx?MODE=new - Edit the first record:
MyPage.aspx?MODE=edit - Edit the record of ID=5:
MyPage.aspx?MODE=edit&ID=5 - Search page:
MyPage.aspx?MODE=search - Advanced search page:
MyPage.aspx?MODE=searchadv - Selections list:
MyPage.aspx?MODE=selections - List of all records:
MyPage.aspx?MODE=list - List result of the query "amex":
MyPage.aspx?QUERY=amex
How does it work?
Every component is built and behaves the same way. One physical web page embeds the Evolutility web control. Properties of the control indicate which metadata to get, what the privileges are (read, insert, edit, delete...), and a few other options.
When the user calls a page for the first time:
- The page loads the control (potentially set properties of the control with custom code within the page)
- The control checks its properties
- The control retrieves the metadata of the component to manage
- The control checks parameters of the URL (for example "page.aspx?ID=4" would indicate that the record of primary key 4 must be displayed)
- Based on properties and page parameters, the control determines which mode (login, view, edit, list, search, advanced search or export) to display
- The control query the database for all defined fields of one record (if in mode view or edit), or a sub-set of the fields for several record (if in mode list), or doesn’t query the database at all (if in mode login or selections). For modes search and advanced search it may run several queries or none depending on the number of lists of values to display in the form (and if they are already cached or not)
- The control generates the user interface for the mode and the data to show
- The web server sends the page with the code generated by the control embedded in it.
The user will execute an action on the page for example by clicking one of the control toolbar buttons.
- The page calls itself again (rather than calling another page like most applications do)
- Steps 1, 2, 3, and 4 are executed like the first time the page was called
- The control determines if a database query must be executed or not to process the action triggered by the previous instance of the page (if the user clicked delete, it must delete the record previously edited…)
- The control execute database queries if necessary
- If the query returns an error, the control will add a header message to the generated user interface to notify the user
- Steps 5, 6, 7, and 8 are executed like the first time.
To run the demo project and sample applications, follow these steps:
- Copy the directory "EvolutilityDemo" which contains the web site to your web server.
- Create a new SQL Server database.
- Change the database connection string in the "appSettings" section of the Web.config file (or in every ASPX page).
- Run the SQL script called Evolutility-Common.sql on your database.
- If you want to use login and passwords for multiple users, run the SQL script called EVOL-MultiUser.sql
- Run any or all the other SQL scripts for the demos you want.
The easiest way to build applications using Evolutility is to start with an existing application and modify it to become the application you want. Just take any of the sample applications as a starting point, play with the XML by modifying the UI attributes or re-ordering fields. Then add or remove columns from the corresponding database table, and customize the XML accordingly.
An XML Schema Definition (XSD file) is included with the code to help you build and modify the metadata of the applications. The details of the XML syntax were discussed in my previous article on minimalist meta-model for CRUD applications or in more details on Evolutility.com.
Alternate ways to build applications with Evolutility are discussed in my next article on Wizard and CRUD applications to build other CRUD applications.
The project code is open source under Affero GPL v3 with dual licensing, hosted and updated at SourceForge. Enjoy... maybe improve it and contribute your code.
Points of Interest
This kind of tool can save time while building applications, but it also makes maintenance and customizations much easier. It may even make application development possible for non-technical users.
Also, in the case of commercial software, as (limited) customizations do not require re-compilation, it is ideal for customers, partners, and OEM because they will be able to customize or add screens without having access to the source code.
In addition, using a metadata driven approach to application development will reduce your code base to a fraction of what it usually is... and less code usually means less bugs.
