Click here to Skip to main content
Click here to Skip to main content

Wizard and CRUD Applications to Build other CRUD Applications

, 8 Jun 2010
Rate this:
Please Sign up or sign in to vote.
Looking at the possibility of using CRUD applications to manage other CRUD applications inside a web browser and without any hand-coding. Also, presenting a step wizard deriving the database structure from the UI rather than the opposite.

Introduction

After looking at the minimalist meta-model for CRUD applications and a few examples of on the fly code generation, let's now see how we can make it easier to build and manage CRUD (Create, Read, Update, Delete) applications.

In this article we will explore the new possibilities which open up when we decide to drop the XML and save the metadata directly in the database. That way, we can use Evolutility (the "on the fly code generator") as a CRUD application to manage the metadata of other CRUD applications (the same way we already used it to manage the data in our previous examples of "to do list" or "address book"). Basically, the application becomes the application designer.

We will also look at a step wizard to build applications by deriving the DB structure from the UI instead of the more commonly used opposite way.

Moving metadata from XML to database

Sure, XML is "human readable", but even with XSDs, it may not always be "human writable", especially when it comes to metadata. To get rid of the XML, we could either build a designer reading and persisting XML, or we could move the metadata into the database and manage it with a new CRUD applications. The later option is much more fun and requires less code.

With our simple meta-model, going from XML to database is not too difficult:

  • We can start by using one DB table per XML element and one DB column per XML attribute. We have 4 elements in the meta-model: form, data, panel, and field. These can easily become 4 database tables.
  • As the relationship between form and data is a 1-to-1, we can normalize the schema by gathering them together in the same table.
  • Let's also add a table for the necessary list of values (here field types).
  • As a convention, let's prefix our tables with "EvoDico_" to indicate what the table set is about.
  • Finally (not shown in the schema), let's add a trigger to automatically delete fields and panels of a form when it gets deleted.

We are now left with the following database schema to store our applications metadata:

For simplicity, this set of tables can live in the same database as the applications data. In the real world, because the metadata doesn't change as often as the data, it may be advantageous to keep them in a separate database to make it easier to backup and restore them independently.

The SQL script to build it is provided in the demo project included with this article.

CRUD applications to manage other CRUD applications

With the former schema, we can now build CRUD applications to manage the metadata of our other applications.

We can move away from the implementation model (of the XML) and get closer to the user mental model by decoupling the UI structure from the database structure. By carefully organizing fields into panels and tabs we can, for example, separate the UI metadata from the database metadata. Also, some user-friendly field labels will be more meaningful than our previous XML attributes.

We can display an application summary like the following:





We will have a different CRUD application for drilling down on fields:



These CRUD pages are now the "designer" for other CRUD applications.

Keeping the designer handy

We can also blur the distinction between the application and the designer by letting users edit the metadata of specific UI elements at run-time. This is done by adding a little icon near each UI element of the application. These icons (color coded by element type) pop up the corresponding designer page shown in the previous paragraph (or some equivalent custom pages where the metadata is presented in a slightly different manner).

A step wizard to create CRUD applications

Let's now look at an even simpler approach: a step wizard. To get closer to the user mental model, we can totally omit from the wizard UI all references to the database. Internally, we can derive the database structure from the UI metadata (rather than the opposite).

To gather the minimum UI metadata necessary to build a CRUD application (here the same "to do list" example as used in the previous 2 articles) we will have the following steps:

Step 1 - Application definition: Gathers the application name, the entity name (as the user calls it) in singular and plural.

Step 2 - Data definition: Gathers the list of fields (labels and types).

Step 3 - Fields definition details: Specifies additional information necessary for each field. Field properties depend on the field type chosen in the previous step.

Step 4 - Search options: Decides which fields are included in the search, advanced search, and list result.

Step 5 - Panels layout: Gathers the list of panels used to visually group fields together (in View and Edit modes). Also specifies the relative width of each panel according to a "flow positioning" scheme.

Step 6 - Fields layout: Decides which fields belong to which panel (specified in the previous step), and the relative width of each field inside its panel.

Step 7 - The CRUD application is ready : This step shows the XML and SQL necessary for the application. Really, these could be hidden as most users may not know what to do with it, but developers should find it useful. This page also contains links to customize the application right away.

Although there was not any single reference to the database, the wizard can build all necessary tables and populate them with seed data automatically (you may have to tune it afterward for optimizations). The trick is that because the scope of CRUD is limited enough, there is only one driving table and we can use field labels for column names after trimming the comas and special characters, and replacing spaces by underscores. We are using our "UI field type" instead of real data types as discussed in the meta-model article. For "lists of values" we already know the pattern: an integer value in the driving table to store the primary key of the secondary table...

Because the wizard runs on the Web and applications do not need compilation, after the last step the new application is ready to use immediately.

Using the code

To run the demo project, follow these steps:

  • Copy the directory EvoDico 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 scripts which are in the SQL directory on your database in the following order:
    1. evodico.2.2.sql
    2. evodico-seed.2.2.sql
    3. evodico-sample.2.2.sql
  • Run the SQL scripts to create tables for the sample applications (in the SQL/Samples/ directory).

To build CRUD applications from scratch with minimal effort:

  • Start by running the wizard
  • Try your new application
  • Customize your new application
  • Maybe move it to XML for more flexibility (as the database repository doesn't support all features of the XML yet)
  • Tweak your database manually if necessary

Playing with fire

Finally, we can imagine using triggers to modify the database structure automatically when the metadata gets modified. It is quite dangerous (especially as we may have several applications running against the same set of tables) but it may be the way to go further into automatizing the process.

A possible trigger for such purpose can be something like the following:

    
    CREATE TRIGGER [EvoDico_Field_TR_Add] ON [EvoDico_Field] 
    FOR INSERT 
    AS  

    DECLARE  @dbtable nVARCHAR(100), @dbcolumn  nVARCHAR(100), 
        @maxlength INT,@typeID INT , @TypeSQL nvarchar(20)

    SELECT @dbcolumn=dbcolumn, @maxlength=INSERTED.maxlength, @dbtable=dbtable, 
        @TypeSQL=EvoDico_FieldType.sqlname 
    FROM INSERTED, EvoDico_Form, EvoDico_FieldType (nolock)
    WHERE  INSERTED.formID=EvoDico_Form.id AND EvoDico_FieldType.ID=INSERTED.TypeID 

    select @typeID=id  from sysobjects where name=@dbtable and xtype='u'
    if @typeID>0  
        begin
        if not exists (select id from syscolumns where name = @dbcolumn and id=@typeID)
            if @TypeSQL='nvarchar' 
                exec('ALTER TABLE ['+@dbtable+'] ADD ['+@dbcolumn+'] nvarchar(
                '+@maxlength+') NULL')
            else  
                exec('ALTER TABLE ['+@dbtable+'] ADD ['+@dbcolumn+'] '+@TypeSQL+' NULL')  
            end

Call for contributions to a budding new open source project

This is a work in progress. The code generator is stable but the wizard and the designer (the application to manage other applications) still need some work.

The code included with the article is also available and updated on SourceForge under Affero GPL v3 with dual licensing, and the web site for the project is www.evolutility.org. In your spare time, feel free to improve this code, and contribute. Thanks.

Download the latest version of Evolutility at Get Evolutility at SourceForge.net

License

This article, along with any associated source code and files, is licensed under The GNU General Public License (GPLv3)

About the Author

Olivier Giulieri

United States United States
I'm a software engineer living in California. I like to work on UI and databases. What I really enjoy is to describe UI in metadata, store that metadata (outside of the code) in a database or in XML, and then dynamically generate screens at run-time... which I do with my open source Evolutility.
 
Articles on Evolutility:
 
Beside Evolutility.org, my other creative project is ChakraDesign.com.

Comments and Discussions

 
GeneralMy vote of 5 Pinmembercsharpbd20-Nov-12 20:16 
GeneralRe: My vote of 5 PinmemberOlivier Giulieri20-Nov-12 20:23 
GeneralMy vote of 5 PinmvpKanasz Robert5-Nov-12 2:45 
GeneralRe: My vote of 5 PinmemberOlivier Giulieri5-Nov-12 19:17 
QuestionLicensing PinmemberkilroyFR4-Feb-12 4:54 
AnswerRe: Licensing PinmemberOlivier Giulieri7-Feb-12 17:02 
GeneralMy vote of 5 Pinmembermanoj kumar choubey3-Feb-12 23:00 
Nice
GeneralRe: My vote of 5 PinmemberOlivier Giulieri7-Feb-12 17:02 
QuestionDoes Evolutility support? Pinmemberkhoirom14-Sep-11 15:55 
AnswerRe: Does Evolutility support? PinmemberOlivier Giulieri14-Sep-11 18:33 
GeneralAwesome Stuff Pinmembernez55522-Dec-08 20:26 
GeneralRe: Awesome Stuff PinmemberOlivier Giulieri27-Dec-08 16:47 
GeneralNew version available on SourceForge.net PinmemberOlivier Giulieri4-Dec-08 23:08 
GeneralVery Nice PinmemberPaul Conrad3-Sep-08 19:21 
GeneralRe: Very Nice PinmemberOlivier Giulieri20-Sep-08 17:38 
GeneralIdeas PinmemberTim Schwallie26-Aug-08 5:05 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 8 Jun 2010
Article Copyright 2008 by Olivier Giulieri
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid