Table of Contents

Exporting database_schema_into_XML_Document.  Transforming_the_XML_Document_into_the_ASP_with_XSLT templates Why_XML_and_XSLT ?

Database Web Application Wizard.

by Dmitriy Shapiro

It is a long road for any software company to efficiently migrate reusable components, frameworks, design features, code conventions etc. from one project to another. To make this road shorter programmers who are proficient with those reusable elements can write the "wizards" for the specific problem domain.

For example - Database Web Application. In most cases regardless of the nature of the data, applications have to perform common operations: View, Search, Sort, Add, Delete, Update Data. Those operations have to be repeated for almost every table in the database. In addition application can have common database access, security, error handling, mail notifications, concurrency control mechanisms. It would be nice if code for all those operations was created automatically, so a programmer can concentrate on implementing specific business rules and move on.

I would like to demonstrate how to start Database Web Application Wizard with:

• Database catalog (meta-data, schema)

• Reusable homegrown components (server-site includes for simplicity)

• Extensible Markup Language (XML)

• Extensible Stylesheet Language Transformations (XSLT)

• Common well-known object models (OLE DB, MSXML Parser)

Application can be done in two stages

1. Exporting the database schema into the XML Document
2. Transforming the XML Document into the Active Server Pages with XSLT templates

You can find "Why XML and XSLT ?" section at the end of this article.

Exporting database schema into XML Document.

Our sample is an University Department database that links departments, students, instructors and courses.

PK - Primary Key FK - Foreign Key U - Unique Constrain SQL Script for Tables in Department database

There are several methods to programmatically retrieve Meta information from the database:

We use SQL-DMO Objects since we dealing with Microsoft SQL Server. Here are some fragments of the Visual Basic source code.
First we need to connect to an instance of Microsoft SQL Server.

Then retrieve a Department database 

Next step is get all user tables, fields, keys, constrains and create an XML Document (database.xml).
Create <Tables> Element and append it with <Table> Elements for each User Table (SQLDMOObj_UserTable ).

Add Name attribute to the  <Table> Element.

Create <Fields> Element and append it with <Field> Elements. 
Add <Fields> Element to <Table> Element.

Each <Field> Element contains 

• requered attributes: Name, Type, Length and AllowNulls 
• optional attributes: IsForeignKey, ReferencedTable, ReferencedField, UniqueField

This is a fragment of the generated XML Document (database.xml) for the Student Table. 

    <Table Name="Course">
      <Fields>
        <Field Name="idCourse" Type="int" Length="4" AllowNulls="No" InPrimaryKey="Yes" Identity="Yes"/>                                  <Field Name="idDepartment" Type="int" Length="4" AllowNulls="No" IsForeignKey="Yes" 
                  ReferencedTable="Department"  ReferencedField="idDepartment"/>
         <Field Name="idInstructor" Type="int" Length="4" AllowNulls="No" IsForeignKey="Yes" 
                  ReferencedTable="Instructor" ReferencedField="idInstructor"/>
        <Field Name="Name" Type="char" Length="50" AllowNulls="No" UniqueField="Yes"/>
        <Field Name="CreditHours" Type="int" Length="4" AllowNulls="No"/>
      </Fields>
    </Table>

Transforming the XML Document into the ASP with XSLT templates

Once we have database as XML, we can apply XSLT Templates to generate Active Server Pages for the specific database operations. In order to do it we use  MergeXMLFileXSLFile function.

Function MergeXMLFileXSLFile(ByVal XMLFile As String, _
                                ByVal XSLFile As String, _
                                ByVal ParamNames As Variant, _
                                ByVal ParamValues As Variant)
    Dim proc As IXSLProcessor
    Dim XML As FreeThreadedDOMDocument
    Dim XSL As FreeThreadedDOMDocument
    Dim XSLTemplate As XSLTemplate
    Set XML = CreateObject("MSXML2.FreeThreadedDOMDocument")
    Set XSL = CreateObject("MSXML2.FreeThreadedDOMDocument")
    XML.Load XMLFile
    XSL.Load XSLFile
    Set XSLTemplate = CreateObject("MSXML2.XSLTemplate")
    XSLTemplate.stylesheet = XSL
    Set proc = XSLTemplate.createProcessor()
    proc.input = XML
    Dim i As Integer
    For i = 0 To UBound(ParamNames)
        proc.addParameter ParamNames(i), ParamValues(i)
    Next
    On Error Resume Next
    call proc.Transform
    If Err > 0 Then
        MergeXMLFileXSLFile = Err.Description
        Exit Function
    End If
    MergeXMLFileXSLFile = proc.output
    Set proc = Nothing
    Set XSLTemplate = Nothing
    Set XML = Nothing
    Set XSL = Nothing
End Function

Operations that can be automated based on database schema Add, Delete, Update, View, Search, Navigate and Sort Records  Access to the children and parents for each Table
Business rules that can be added manually, or throug the project meta-data (may be described in the next article) Student cannot have more than 5 courses At least 50% of all courses have to be in the same department that Student belongs Course cannot have more than 5 credit hours Instructor cannot have more than 5 courses, etc…

Each operation has corresponding XSLT template. Let's have a look at TableToAddForm.xsl (source code). It contains instructions to create an Add Form, that allows user to enter the new data into the table. Call to MergeXMLFileXSLFile( "Database.xml", "TableToAddForm.xsl", Array("Table"), Array("Student")) returns text for StudentAddFrm.asp Active Server Page. This page (link will bring you to the code section):

• Contains reference to server-side includes
• Creates <form> element
• Sets the form's action attribute to appropriate Add Process Page
• Adds submit and reset buttons

As template went through the Field Elements, it applied instructions which are common for all Tables

• Create <input> element for all non Foreign Key Fields
• Create <select> drop-down list for the Foreign Key Fields, use ShowSelect function
• Do not show Identity Field
• Mark required Fields with asterisk
• Put Foreign Key Fields at the end
• Foreign key Field should be pointing to unique constraint Field of the Referenced Table

Output screen generated by StudentAddFrm.asp  

Sample project also contains template TableToAddPage.xsl (source) for an Active Server Page  that validates data 
and puts them into the database. Please note that it reports an Error if a Required Field is empty

Why XML and XSLT ?

I think the answer here is the potential, plus it is fun to work with. XSLT technology is here to stay and it is much more stable than a year ago. I admit that this is not the friendliest language, and there is no developer studio that allows you to debug it. But the language fits nicely with its ability to transform XML documents. And tools are coming (look for XSLT debugger), since the technology is gaining in popularity. 

I expect one day we will see an XML version of data definition language (DDL), so we can skip Exporting the database schema into the XML Document, and adjust scripts to standard Database XML Schema. Once in XML, the database schema can be integrated with other documents and can be transformed into all sorts of useful applications. 

Example 1: Localization

When we display information, we label it with the appropriate titles. Often these titles correspond to names of the underlying tables and fields. Why don't we extract all the table and field names into the separate XML Document (Titles.xml) and change the default titles for tables and fields:

<Field>
<Name>DepartmentNumber</Name>
    <Titles>
        <English>Department Number</English>
    </Titles>
</Field>

Then we can send this document to a third party agency for translation to Spanish, French, German and Italian. Of course they would not accept it, if it was not in XML form . Then the final output document will contain all appropriate titles:

<Field>
    <Name>DepartmentNumber</Name>
    <Titles>
        <English>Department Number</English>
        <Spanish>Nú­¥²¯ Del Departamento</Spanish>
        <French>Nombre De Service</French>
        <German>Abteilung Zahl</German>
        <Italian>Numero Di Reparto</Italian>
    </Titles>
</Field>

Now, when we display "DepartmentNumber", we can substitute this word with the appropriate title on a given language.

Example 2: Validation Rules 

Let's say we have product management give us all requirements in XML form. So, for Department Number they may look like this:

<Field>
    <Name>DepartmentNumber</Name>
    <Validation>
        <MinSize>3</MinSize>
        <CharType>notNumbers</CharType>
    </Validation>
</Field>

In the template that responsible for making update or add pages, check Validation Rules for any field. If the rule exists then write a special code to handle it.

Where to go ?

Wizards are most efficient on the initial stage of the project. They give you a framework that inherits previous project experience. You can build your own or get it from a third party. No matter what technology you use, any investments into the wizard will bring you benefits. Here are some tools that provide Database Web Application wizards:

Good luck.