On the fly Code Generation with Evolutility

• Download demo project - 264 Kb
• Download source - 87 Kb

Introduction This article presents a generic web user interface for CRUD 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 (create, read, update, delete) 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".

Contents Introduction Sample Applications Security and users access management Collaboration and user comments Evolutility web control properties Server events Navigation Links How does it work? Using the code Points of Interest

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 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.org.

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 in the page 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 definition (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 set bind 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 follow:

• 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 the tool 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:

• EN - English
• FR - French

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:

• Top
• Top_and_Bottom
• None

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 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 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:

1. The page loads the control (potentially set properties of the control with custom code within the page)
2. The control checks its properties
3. The control retrieves the metadata of the component to manage
4. 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)
5. Based on properties and page parameters, the control determines which mode (login, view, edit, list, search, advanced search or export) to display
6. 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)
7. The control generates the user interface for the mode and the data to show
8. 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:

1. Copy the directory "EvolutilityDemo" which contains the web site to your web server.
2. Create a new SQL Server database.
3. Change the database connection string in the "appSettings" section of the Web.config file (or in every ASPX page).
4. Run the SQL script called Evolutility-Common.sql on your database.
5. If you want to use login and passwords for multiple users, run the SQL script called EVOL-MultiUser.sql
6. 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.org.

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 tools can save time while building applications, but it also makes maintenance and customizations much easier. It may even make them 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.

You must Sign In to use this message board.

FAQ    Noise Tolerance Very HighHighMediumLowVery Low   Layout Expand Posts & RepliesThread ViewNo JavascriptNo JS + Preview   Per page 102550

Msgs 1 to 19 of 19 (Total in Forum: 19) (Refresh) FirstPrevNext

Master - Detail? georani 3:13 21 Mar '09   Please, where can I find a Master-Detail example? Sign In·View Thread·PermaLink Re: Master - Detail? Olivier Giulieri 6:06 21 Mar '09   The Wine cellar demo is a good example. I'll have many more examples in a week with Chinook demo database. http://evolutility.org/chinook/Chinook_by_Evolutility.aspx[^] Sign In·View Thread·PermaLink NEw version available on SourceForge.net Olivier Giulieri 0:06 5 Dec '08   Evolutility version 2.4. The most important change is that the code base is now in C# instead of VB.net. This will not change anything for end-users but will change many things for developers interested in modifying the code and participating in our open source project. This release include: - Support fo Japanese, Spanish and Portuguese (in addition to English and French) - Better cross-browser support (Master-details now works in FireFox and Safari too) - Improved performances (and smaller generated HTML) - Look & feel improvements - Improved icon set http://sourceforge.net/projects/evolutility/[^] Sign In·View Thread·PermaLink ItemID not set the bill 5:11 18 Oct '08   Hi, very useful app, i'm trying to use the control together with a few others for the same record, and i'd need to get the ItemID, the primary key of the record being shown, if listing all records or searching, "should" return zero? currently i'm just getting zero irrespective of what's on the screen. using version 2.3.1 i've put a label on the page just to show what the status is, an even if i set the ID & navigate to a record, it still isplays zero. is there anything i'm missing? protected void Page_Load(object sender, EventArgs e) { Label1.Text = Evo1.ItemID.ToString(); } Sign In·View Thread·PermaLink Re: ItemID not set Olivier Giulieri 17:05 21 Oct '08   Sorry, that must be a bug. I'm currently working on moving to AJAX and a lot is going to change in the next few months. Sign In·View Thread·PermaLink Re: ItemID not set the bill 22:26 21 Oct '08   that's great news, i've found a work-around, the ItemID property is cleared when the object is disposed, so i created a new event ItemIDChanged thats fired each time _ItemID is changed with an action of either SET (_ItemID!=0) or RESET(_ItemID==0), then check the view mode as well, display my second control if we're in view or edit mode. not an ideal hack, but i'm just playing with the control at the moment to see if it'll kill anything else. Sign In·View Thread·PermaLink The files of source and demo are Empty???????? jmptrader 3:31 13 Oct '08   Please! to release source source and demo ! Thanks! Jorge Sign In·View Thread·PermaLink Re: The files of source and demo are Empty???????? Olivier Giulieri 11:33 13 Oct '08   The files attached to the article are not empty. Just download the ZIP files and extract them. Also, a more up-to-date versions are on SourceForge http://sourceforge.net/project/showfiles.php?group_id=225915[^] Sign In·View Thread·PermaLink Excellent Design approach Donald E. Kemper 5:53 11 Sep '08   Do you have any “out of the box” applications worked up other than the samples provided? The first thing that jumped to my mind was a BLOG. Have you put together a database schema / GUI for something like that using EVOLutility? Sign In·View Thread·PermaLink Re: Excellent Design approach Olivier Giulieri 14:27 12 Sep '08   Blog may not be the best fit. Evolutility is for structured data (well defined fields of different types). Blogs are very much like web pages and need rich text editing. Maybe later when Evolutility matures. You are right, I should have real “out of the box” applications. That's my next step. I'll start by posting my simple bug tracking app soon. I'll follow up with more. Sign In·View Thread·PermaLink Re: Excellent Design approach DEK46656 11:01 13 Sep '08   The first “app” that came to my mind would be a trouble-ticketing system of some type. I would think that a BLOG wouldn’t be that difficult. You already have the "comment" portion implemented. However, I don’t have the knowledge of what it takes to put the field into a form element. To be able to flip back and forth between view and edit and tie a XHTML editor control into that is easy enough to describe; implementing that would be a whole other story. Anyway, I’m spending time working with what you’ve done here. I’ll reiterate my initial comment; excellent design approach. Sign In·View Thread·PermaLink Field validation antoine beloeuvre 8:27 2 Sep '08   What a nice project! I looked at Evolutility documentation and looked for ways to add server side validators. The only event I found is DBChange. It would be nice to be able to add server side validation and allow access to the field values. Do you plan to add this feature? Sign In·View Thread·PermaLink Re: Field validation Olivier Giulieri 12:12 2 Sep '08   Thanks for your feedback. You are right, custom validation is really needed. I've been thinking about it too. I'll try to have it ready soon. For now, I'd like to keep a centralized list of bugs and enhancement requests on SourceForge. File bug http://sourceforge.net/tracker/?func=add&group_id=225915&atid=1066274[^] File enhancement request http://sourceforge.net/tracker/?func=add&group_id=225915&atid=1066277[^] If you have the time, you are welcome to contribute any bug fix or enhancement to Evolutility source code. Sign In·View Thread·PermaLink csharp zhaojicheng 3:15 27 Aug '08   I like csharp more than vb Sign In·View Thread·PermaLink Re: csharp Olivier Giulieri 11:46 29 Oct '08   I have a good news for you. I converted the code from VB.net to C#. It is available for download on SourceForge at http://evolutility.svn.sourceforge.net/viewvc/evolutility/trunk.tar.gz?view=tar[^] Sign In·View Thread·PermaLink License Chris Maunder 5:40 25 Aug '08   Just wondering: why GPL? cheers, Chris Maunder CodeProject.com : C++ MVP Sign In·View Thread·PermaLink Re: License Guido_d 22:39 25 Aug '08   darn, I almost wanted to use it until I noticed the license .. none of my projects is compatible with GPLed software (obviously in a commercial environment). Sign In·View Thread·PermaLink 2.00/5 (1 vote) Re: License Olivier Giulieri 3:01 26 Aug '08   I'm offering it under dual licensing. I'm sure we can find a way. Sign In·View Thread·PermaLink Re: License Olivier Giulieri 3:03 26 Aug '08   I wanted to keep my options open and I'm planning to provide a dual licensing scheme. Sign In·View Thread·PermaLink

Last Visit: 18:20 21 Nov '09     Last Update: 18:20 21 Nov '09 1