XML-based Data Abstraction Layer generator

• Download source files at Sourceforge
• Download demo project at Sourceforge

Introduction

This article presents an XML-based Data Abstraction Layer generator based on SQLpp. Once the XML script is defined, the generator will take care of:

• SQL code generation: database creation and stored procedure. The SQL is specialized to take into account the SQL syntactic difference from each provider,
• Config generation: XML entries for configuring the DAL,
• Caching: Supports caching thought the Cache application block,
• Null values handling: handles null values using the NullableTypes library,
• Business Logic Layer: for each table and for the procedures (C#),
• Abstract Data Provider: for each table and for the procedures (C#),
• Specialized Data Provider: customized for each data provider (C#),
• IDataReader strongly-typed wrapper classes for tables and procedures (C#): This reader automatically closes the reader (through the Dispose method), supports foreach semantics, and wraps the columns in properties to take advantage of the Intellisense.
• Supports for non-trivial queries and complex table join: the generator uses the Boost.Graph.Library on the background to generate the queries.

Some other interesting features are:

• No SQL manipulation: with the generator, you don't write a single line of SQL,
• Multiple database system support: MSSQL, MySQL. MS Access and Oracle are untested,
• For database not supporting stored procedures, stores the queries in the web.config if necessary,
• The C# code is documented!
• Rows can be signed: a column containing the crypted CRC of the row is automatically added.

The XML pilots the DAL generator presented in SQLpp, DAL generation framework and it makes obsolete the Sqlpp.NET layer.

Requirements

You will need the following projects to have a working DAL:

• Microsoft.ApplicationBlocks.Data, for the MSSQL provider.
• ByteFX, for the MySQL provider.
• NullableTypes for handling null values.
• NCrypto for digitally signed tables.
• Caching Application Block for .NET, for Windows Forms application.

Sample Database

Let me first illustrate the XML format with a simple Users/Products/Orders database whose schema is given in the figure below. Since, product and order are linked by a many-many relation, we add the ProductOrders table.

Adding tables

Tip: Use the XSD schema sqlpp.xsd provided with the project to ease up the creation of XML scripts.

We begin by adding the tables to the XML script:

<db>
    <tables>
        <table id="users" name="Users" field-prefix="User" >
        </table>
        ...
    </tables>
</db>

where:

• table/@id will be used to refer to the table when creating foreign keys,
• table/@name is the table name,
• table/@field-prefix is used to tag each field of the table: if you add a field named "ID", it will be generated as "UserID".

Populating tables

The following script adds Name field to the User table:

<db>
    <tables>
        <table id="users" name="Users" field-prefix="User" >
            <field id="userfirstname" name="Name" 
                  type="NVARCHAR(30)" nullable="false"/>
        </table>
    </tables>
</db>

where:

• field/@id will be used to refer to the field,
• field/@name is the name of the field. Remember that the table field-prefix is appended, so the real field name will be "UserName",
• field/@type is the type of the field. Any T/SQL type is accepted.
• field/@nullable tells if the field can be null. By default, this value is set to false.

Primary Keys

There are different ways of adding primary keys. In the definition of a field, you can add the primary-key attribute with value true:

<db>
    <tables>
        <table id="users" name="Users" field-prefix="User" >
            <field id="userid" name="ID" type="int" primary-key="true"/>
            <field id="userfirstname" name="Name" 
                                  type="NVARCHAR(30)" nullable="false"/>
        </table>
    </tables>
</db>

Using this method, the field is automatically tagged with IDENTIY(0,1). You can change the identity settings using the identity tag:

<db>
    <tables>
        <table id="users" name="Users" field-prefix="User" >
            <field id="userid" name="ID" type="int" primary-key="true"/>
            <identity field-ref="userid" start="0" step="1" />
        </table>
    </tables>
</db>

where:

• identity/@field-ref is the ID reference of the field to use as identity,
• identity/@start,step are respectively the start and step of identity.

In the case of multi-field primary-key, the above method does not work. Another tag, primary has to be used. This case will be illustrated later.

Reference constraints

Foreign keys are added using the reference tag. The addition of the fields is handled by the framework:

<db>
    <tables>
        <table id="users" name="Users" field-prefix="User" >
            <field id="userid" name="ID" type="int" primary-key="true"/>
        </table>
        <table id="orders" name="Order" field-prefix="Order" >
            <field id="userid" name="ID" type="int" primary-key="true"/>
            <reference id="rorderuser" table-ref="users" />
        </table>
    </tables>
</db>

where

• reference/@id will be used in the creation of queries to join tables,
• reference/@table-ref is the ID reference of the foreign table.

The reference contains other attributes to set-up the constraint according to your needs:

<db>
    <tables>
        <table id="users" name="Users" field-prefix="User" >
            <field id="userid" name="ID" type="int" primary-key="true"/>
        </table>
        <table id="orders" name="Order" field-prefix="Order" >
            <field id="userid" name="ID" type="int" primary-key="true"/>
            <reference id="rorderuser"
                             table-ref="users" 
                             nullable="false"
                             on-delete="no-action"
                             on-update="no-action"
                             />
        </table>
    </tables>
</db>

Multiple columns Primary keys

The technique to define the primary key given in the Primary Key section does not work for multi-field primary key. Take for example, the link table OrderProducts that creates the many-to-many relation between Order and Products, typically, the two foreign keys are good candidates for a primary key. In this case, the primary tag is used:

<db>
    <tables>
        <table id="orders" name="Order" field-prefix="Order" >
            ...
        </table>
        <table id="products" name="Products" field-prefix="Product" >
            ...
        </table>
        <table id="orderproducts" name="OrderProducts" 
                                       field-prefix="OrderProduct" >
            <reference id="ropo" table-ref="orders" />
            <reference id="ropp" table-ref="products" />
            <primary field-names="OrderID,ProductID" />
        </table>
    </tables>
</db>

where:

• primary/@field-names contains a comma separated list of field names. Those are SQL names of the fields, not the field IDs.

Unique and Index

Unique constraints and index are added using the unique and index tags:

<db>
    <tables>
        <table id="orders" name="Order" field-prefix="Order" >
            <field id="username" name="Name" type="NVARCHAR(30)" />
            <field id="userlastname" name="LastName" type="NVARCHAR(30)" />
            <unique field-names="UserName,UserLastName" />
            <index field-names="UserName" />
        </table>
    </tables>
</db>

where @field-names works similarly as in the primary tag.

Setting up the target database

So far, we have defined the structure of the tables but we have not chosen the target database. Sqlpp currently supports MSSQL, MySQL (MS Access is untested). You need to provide at least one target database output, but Sqlpp supports generation for multiple targets. The script below sets up the output for MSSQL:

<db/>
    <connections default="cpsql">
        <connection
            id="cpsql"
            name="CodeProject"
            catalog="CodeProject"
            provider-path=""
            database-owner="dbo"
            connection-string="data source=127.0.0.1;
                               Trusted_Connection=yes;Initial Catalog=CodeProject"
            adaptor="Sql" />
    </connections>
    ...

where:

• connections/@default is the default connection ID,
• connection/@id is the connection ID,
• connection/@name is the name of the connection,
• connection/@catalog is the name of the catalog,
• connection/@adaptor is the type of database. Currently supported are: SQL (MSSQL), MySQL, Access, Oracle (Access and Oracle are untested).

Setting up the generation

This is the final step to set up the generation:

<db  path=".."
    namespace="Quecha"
    proc-class-name="SP"
    proc-object-qualifier="SP"
    data-reader="true"
    data-set="true"
    xml-reader="false"
    web-cache="false"
    default-qualifier="cp_"
        >
...
</db>

where:

• @path is the output path for the files,
• @namespace is the target namespace,
• @default-qualifier is used to tag the table names.

The script is now ready to be analyzed by SQLpp. Open the command prompt and write:

>sqlpp script_name.xml

And check out the log messages to see if all the operations went OK.

Adding new queries

Constructing queries is a big topic and I will detail it in another article, until then, here comes an example.

Customized queries are added as db/procedures/procedure elements. In this example, we want to create the following procedure:

select all products ordered for a given user name, in a given date interval:

The XML code looks as follows:

<procedures>
<procedure name="ProductsPerUserPerDate">
<select >
   <columns>
      <column alias="ProductID">
        <link tableref="P" fieldref="productid" />
      </column>
   </column>
</columns>
<from>
  <qtable ref="users" alias="U" /> 
  <qtable ref="orders" alias="O" /> 
  <qtable ref="products" alias="P" />
  <qtable ref="orderproducts" alias="OP" /> 
  <join left="O" right="U" />
  <join left="OP" right="O" />
  <join left="OP" right="P" />
</from>
<where>
   <and>
      <left>
         <equal>
            <left>
               <link tableref="U" fieldref="username" />
            </left>
            <right>
               <local name="UserName" type="NVARCHAR(30)" />
            </right>
         </equal>
      </left>
      <right>
         <between>
            <test>
               <link tableref="O" fieldref="orderdate" />
            </test>
            <begin>
               <local name="BeginDate" type="DATETIME" />
            </begin>
            <end>
               <local name="EndDate" type="DATETIME" />
            </end>
         </between>
      </right>
      </and>
   </where>
</select>
</procedure>
</procedures>

Generated code

This section describes what has been generated by the above application.

Configuring the project : web.config entries

After adding the .cs files to the project and executing the .sql files, you need to update the web.config file with the generated webconfig.xml (Config directory).

Note that if you are outputting the DAL for non-web applications, you will need to set-up the Cache Application Block also. This set-up is rather complicated, so you should take a look at the documentation on their web-site.

Data row wrapper class

This class is not part of the DNN DAL document. The wrapper has the following advantages:

• fields are accessed through wrapper properties:
  • avoids spelling errors while retrieving the field name.
  • eases up programming through Intellisense.
• closes the reader on Dispose to avoid un-closed database connection.

The UserDataReader class is defined as follows (doc stripped out):

public sealed class UserDataReader :
        IDisposable
            ,IEnumerable
            ,IEnumerator
{
    #region Fields
    private IDataReader dr;
    private int userID;
    private NullableString userLastName;
    private String userName;
    #endregion

    #region Constructors
    public UserDataReader(IDataReader dr)
    {
        this.dr = dr;            
    }
        
    public UserDataReader()
    {
        this.dr = null;
    }
    #endregion

    #region Methods
    public void Close()
    {
        if (this.dr != null)
        {
            this.dr.Close();
            this.dr = null;
        }    
    }
        
    public void Dispose()
    {
        Close();
    }
        
    IEnumerator IEnumerable.GetEnumerator()
    {
        return this;
    }
        
    bool IEnumerator.MoveNext()
    {
        return this.Read();
    }
        
    public bool Read()
    {
        // checking non null arguments
        if (this.dr == null)
           throw new Exception("data reader is null");
        if (!this.dr.Read())
        {
            this.dr.Close();
            this.dr = null;
            return false;
        }
            
        this.userID=(int)this.dr["UserID"];
        this.userName=(String)this.dr["UserName"];
        this.userLastName=DBNullConvert.ToNullableString(this.dr["UserLastName"]);
        return true;         
    }
        
    void IEnumerator.Reset()
    {
        throw new NotSupportedException("Reset not supported");
    }
    #endregion

    #region Properties
    Object IEnumerator.Current
    {
       get
       {
           return this;
       }
    }
        
    public int UserID
    {
        get
        {
            return this.userID;
        }
        set
        {
            this.userID=value;
        }
    }
        
    public NullableString UserLastName
    {
        get
        {
            return this.userLastName;
        }
        set
        {
            this.userLastName=value; 
        }
    }
    public String UserName
    {
        get
        {
           return this.userName;
        }
        set
        {
           this.userName=value;
        }
    }
    #endregion
}

As one can see, data retrieval is done once only and hidden from the user.

Using the generated classes for manipulating tables

Let's start with a simple example where the Users table is manipulated.

DAL structure, extracted from the DNN DAL document.

• Creating the business logic object:
  C#

  // creating the users table business logic
  UserDB userDB = new UserDB();

• Add a new user. You can use the wrapper or directly pass the parameters to the AddUser method. If the table contains an auto-increment field, this field will be returned or the wrapper updated.
  C#

  // creating a product row wrapper
  UserDataReader dr = new UserDataReader();
  // setting fields, each field is a property
  dr.UserName = "Marc";
  
  // add the product
  userDB.AddUser(dr);

  You can also avoid using UserDataReader by calling directly:

  C#

  int uid = userID.AddProduct("name", new NullableString());

• Retrieve a user by ID. The UserDataReader class stores a IDataReader object on creation and closes it when disposed. The wrapper has a method Read that behaves similarly to IDataReader.Read.
  C#

  using(UserDataReader pd = userDB.GetUser(pid))
  {
      // try to reade the Datareader
      if (!pd.Read())
          throw ...;
      // pd contains the data.
      string name = pd.UserName;
  }
  // the reader is automatically closed from this loop (exception safe)

  You can also use foreach to do the same thing (now iterating over all the products):

  C#

  foreach(UserDataReader pd = userDB.GetUsers())
  {
     ...
  }

• Update a product:
  C#

  userDB.UpdateProduct( dr );
  userDB.UpdateProduct(uid, name, "..."); // equivalent

• Delete a product by ID:
  C#

  userDB.DeleteProduct( dr ); 
  userDB.DeleteProduct( uid ); // equivalent

These are the four main generated methods to manipulate the tables.

Using the generated classes for doing queries

Queries also have their own business logic object. In this case, it is called SPDB where the methods are named after the stored procedure name.

Providers

Currently, there are two available providers: MSSQL and MySQL. However, this could be extended to other database systems: Access and Oracle still need testing, ...

MSSQL provider

This provider uses the Microsoft Application blocks as in the DNN DAL description.

MySQL provider

This provider uses the ByteFX library available at SourceForge site. Since MySQL does not support stored procedures, the queries are stored in the web.config.

References

• DotNetNuke official site.
• SQLpp site.
• Microsoft.ApplicationBlocks.Data, for the MSSQL provider.
• ByteFX, for the MySQL provider.
• NullableTypes for handling null values,
• NCrypto for digitally signed tables,
• Caching Application Block for .NET, for Windows Forms applications.