Click here to Skip to main content
15,867,686 members
Articles / Web Development / HTML

Wizard and CRUD Applications to Build other CRUD Applications

Rate me:
Please Sign up or sign in to vote.
4.65/5 (41 votes)
8 Jun 2010GPL37 min read 110.2K   4.1K   146   17
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:

Image 1

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:

Image 2

Image 3

Image 4

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

Image 5

Image 6

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

Image 7

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.

Image 8

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

Image 9

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

Image 10

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

Image 11

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.

Image 12

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.

Image 13

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.

Image 14

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:

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

<nobr>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)


Written By
United States United States
I'm a UI engineer with an eye for UX and a passion for model-driven UIs.

I usually build UIs for startups in the San Francisco Bay Area.

My hobby open source project is Evolutility, a minimalist low-code platform with a model-driven UI, a model-driven backend, and a set of models to play with.

More about me on my GitHub page.

Comments and Discussions

 
QuestionDoes EvoDico require additional web.config Pin
Member 1293641321-Dec-17 10:45
Member 1293641321-Dec-17 10:45 
GeneralMy vote of 5 Pin
csharpbd20-Nov-12 20:16
professionalcsharpbd20-Nov-12 20:16 
GeneralRe: My vote of 5 Pin
Olivier_Giulieri20-Nov-12 20:23
Olivier_Giulieri20-Nov-12 20:23 
GeneralMy vote of 5 Pin
Kanasz Robert5-Nov-12 2:45
professionalKanasz Robert5-Nov-12 2:45 
GeneralRe: My vote of 5 Pin
Olivier_Giulieri5-Nov-12 19:17
Olivier_Giulieri5-Nov-12 19:17 
QuestionLicensing Pin
kilroyFR4-Feb-12 4:54
kilroyFR4-Feb-12 4:54 
AnswerRe: Licensing Pin
Olivier_Giulieri7-Feb-12 17:02
Olivier_Giulieri7-Feb-12 17:02 
GeneralMy vote of 5 Pin
Manoj Kumar Choubey3-Feb-12 23:00
professionalManoj Kumar Choubey3-Feb-12 23:00 
GeneralRe: My vote of 5 Pin
Olivier_Giulieri7-Feb-12 17:02
Olivier_Giulieri7-Feb-12 17:02 
QuestionDoes Evolutility support? Pin
khoirom14-Sep-11 15:55
khoirom14-Sep-11 15:55 
AnswerRe: Does Evolutility support? Pin
Olivier_Giulieri14-Sep-11 18:33
Olivier_Giulieri14-Sep-11 18:33 
GeneralAwesome Stuff Pin
nez55522-Dec-08 20:26
nez55522-Dec-08 20:26 
GeneralRe: Awesome Stuff Pin
Olivier_Giulieri27-Dec-08 16:47
Olivier_Giulieri27-Dec-08 16:47 
GeneralNew version available on SourceForge.net Pin
Olivier_Giulieri4-Dec-08 23:08
Olivier_Giulieri4-Dec-08 23:08 
GeneralVery Nice Pin
Paul Conrad3-Sep-08 19:21
professionalPaul Conrad3-Sep-08 19:21 
GeneralRe: Very Nice Pin
Olivier_Giulieri20-Sep-08 17:38
Olivier_Giulieri20-Sep-08 17:38 
GeneralIdeas Pin
Tim Schwallie26-Aug-08 5:05
Tim Schwallie26-Aug-08 5:05 

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

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