Full Database Abstraction Layer Generator

Introduction

This article presents yet another Data Abstraction Layer generator (popular topic). The DAL structure is directly inspired from the DotNetNuke, DAL document and the SQL generation is done using SQLpp.

The generator takes care of pretty much everything for you:

• SQL code generation: database creation and stored procedure. Moreover, the SQL is specialized to take into account the SQL syntactic difference from each provider,
• Web.Config generation : XML entries for configuring the DAL,
• 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 Wrapper classes for tables and procedures (C#)

Some interesting features are:

• No SQL manipulation: with the generator, you don't write a single line of SQL,
• Multiple Database system support: MSSQL, MySQL. Others could be implemented easily,
• Handles the stored procedure problem by storing the queries in the web.config if necessary,
• Supports complex table join: the generator uses the Boost.Graph.Library on the background to generate the queries
• The C# code is documented !
• It's totally free for non-commercial and commercial use!!!

All downloads, docs and trackers are available from the SourceForge page.

Requirements

The following providers need some specific (free) libraries:

• MSSQL, Microsoft.ApplicationBlocks.Data
• MySQL, ByteFX

Moreover, to fully understand the DAL structure, you should have a look at the DNN AL document.

Illustrative example

The generation process will be illustrated with a simple module creation example. Suppose you want to create a Users-Products-Orders database defined by the following structure:

Since SqlppNet is still a GUI-less tool, so you need to write things down.

Database provider

The first step is to create a database adaptor. For example, the MSSQL provider is constructed as:

MsSqlAdaptor msSql = new MsSqlAdaptor(
    "MyCatalog",
    "providerpath",
    "dbo",
    "connectionstring"
    );

where

• MyCatalog is the database name
• providerpath is the provider path,
• dbo is the database owner,
• connectionString is the connection string for this provider,

Of course, you can create other adaptors later and generate code for them.

Database creation

Once the adaptor is created, a database is created and the adaptor is attached to it:

Database db = new Database( msSql );

Adding tables

Tables are added using the following function call:

DbTable users = db.AddTable(
    "User", // data name
    "dnn_", // object qualifier
    "User" // field prefix
    );

where

• the data name is used to compose the table name and create the wrapper classes:
  • Users will be the table name,
  • UserData, UserDataProvider, etc... will be the class names associated to Users
• as mentioned in the DAL document, the object qualifier is used to "decorate" the table name: dnn_Users is the real table name,
• the field prefix is used as an automatic field prefix appended to each field of the table (expect foreign key fields): if you add a field named ID, it will be renamed as "UserID".

The other tables are added similarly:

DbTable products = db.AddTable("Product","mytables_","Product");
DbTable orders = db.AddTable("Order","mytables_","Order");

Add primary keys

Primary key addition is straightforward:

users.AddPrimaryKey("ID","int"); // adds UserID INT as primary key

Notes:

• this method automatically defines an auto-increment on the primary key
• More complex primary key can be defined but are outside the scope of this article
• the int string is parsed internally by the framework and converted into a SQL type. The parser contains a large subset of the admissible SQL types.

Again, the other tables are treated similarly:

orders.AddPrimaryKey("ID","int"); 
products.AddPrimaryKey("ID","int");

Linking tables

It's now time to set the relations between the tables. Relations/constraints are enforced by adding foreign keys (reference constraints) or unique constraints to the table.

Adding the Users foreign key to the Orders table is done as follows:

orders.AddForeignKey( users );

Note that this method uses a lot of default settings such as the ON DELETE, ON UPDATE behavior. The Orders table now contains a UserID field referencing the Users table.

The foreign key to the products table is added similarly:

orders.AddForeignKey( products );

Add fields

Following the same logic, fields are added using Table.AddField:

products.AddField(
    "Name", // field name (remember that field prefix is added
    "nvarchar(50)", // type
    true // not null
    );
products.AddField("Price","decimal",true);

orders.AddField("Quantity","int",true);
orders.AddField("Timestamp","timestamp",true);

At this point, your database structure is ready.

Create some queries

Of course, you will need some queries on the database defined above. This is also handled by SQLppNet. Suppose we want to create the following query:

Select user id, product name, quantity, price and order id 
from users, products and orders.

1. Create a query,

   SelectQuery q = db.CreateQuery();

2. Add tables with alias (optional) and link them,

   QueryTable qusers = q.AddTable(
       users, // table to add 
       "U" // alias
       );

   The other tables are added similarly. The tables need to be joined:

   q.Join( 
       qorders, // table 
       qusers // reference table
       );
   q.Join(qorders,qproducts);
   

   SQLpp will handle the rest of the Join process.

3. Add fields

   q.AddField( 
       FieldExpression.Link(    // links qusers and userID field
           qusers, //
           users.get_Field("ID") // retreiving UserID field
           ),
       "UserID"  // giving the AS name
       );

   Other fields can be added the same way. In the C++ version, you can add more complicated SQL expressions such as aggregate functions, arithmetic expressions, etc... See note below.

4. Add Where clause (not wrapped yet, see note below)

Note: SQLppNet is a thin managed C++ wrapper around SQLpp. At the present time, the wrapper is incomplete. For more functionalities, go back to C++.

Setting up the generator

A C# generator object needs to be created and configured:

CsGenerator cs = new CsGenerator(
    db,              // the database to generator
    ".",             // output file path
    "MyNamespace",   // created namespace
    "StoredProc"     // name of the stored procedure class(explained later)
    );

The C# generator does not generate wrappers for all the database tables by default, you have to add the tables to be "generated" to the generator (since Users table already exists in the database, we don't need to generate it):

cs.AddTable(orders);
cs.AddTable(products);

You also need to add the specific queries you generated:

cs.AddProcedure( StoredProcedure.Wrap(q, "GetOrders") );

Generating the DAL

For sure the easiest part:

cs.Generate();

Resuming the code:

This is a short summary of the code you need to write to generate the simple database structure:

using namespace SQLppNet;
using namespace SQLppNet.Adaptors;
using namespace SQLppNet.Generators;
using namespace SQLppNet.Queries;

try 
{
    // creating MsSql adaptor
    MsSqlDatabaseAdaptor msSql = new MsSqlDatabaseAdaptor(
        "MyCatalog",
        "",
        "dbo",
        "connectionstring"
        );

    // creating db
    Database db = new Database( msSql );

    // add tables
    DbTable users = db.AddTable("User","","User");
    DbTable products = db.AddTable("Product","ex_","Product");
    DbTable orders = db.AddTable("Order","ex_","Order");

    // add pk
    users.AddPrimaryKey("ID","int");
    orders.AddPrimaryKey("ID","int");
    products.AddPrimaryKey("ID","int");

    // link tables
    orders.AddForeignKey( users );
    orders.AddForeignKey( products );

    // add fields
    products.AddField("Name","nvarchar(50)",true);
    products.AddField("Price","decimal",true);
    orders.AddField("Quantity","int",true);
    orders.AddField("Timestamp","timestamp",true);

    // create generator
    CsGenerator cs = new CsGenerator(
        db,
        "c:\\Inetpub\\wwwroot\\DnnFramework\\DotNetNuke\\DesktopModules",
        "UserQuotes",
        "StoredProc"
        );
    // adding tables to generate
    cs.AddTable(orders);
    cs.AddTable(products); 

    // a query
    SelectQuery q = db.CreateQuery();
    // adding tables
    QueryTable qusers = q.AddTable(users,"U");
    QueryTable qorders = q.AddTable(orders,"O");
    QueryTable qproducts = q.AddTable(products,"P");

    // joining tables
    q.Join(qorders, qusers);
    q.Join(qorders,qproducts);

    q.AddField( FieldExpression.Link(qusers, 
             users.get_Field("ID")),"UserID"  );
    q.AddField( FieldExpression.Link(qproducts,
             products.get_Field("Name")),"ProductName"  );
    q.AddField( FieldExpression.Link(qorders,
             products.get_Field("Price")),"Price"  );
    q.AddField( FieldExpression.Link(qorders, 
             orders.get_Field("Quantity")),"Quantity"  );

    cs.AddProcedure( StoredProcedure.Wrap(q, "GetOrders") );

    // generation
    cs.Generate();
}
catch(Exception ex)
{
   Console.WriteLine(ex.ToString());
}

So this is less than 100 lines long, it will generate a lot more lines than that.

Generated code

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

Generate file structure

The file/class structure is laid out as follows and "C# ready" (ready to be included in a C# project):

The SQL files are ready to be run by the OSQL tool.

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 file that looks like the following:

<web.config>
  <!--add this before system.web-->
  <configSections>
   <section name="mynamespace" 
    type="MyNamespace.Config.DataProviderConfigurationHandler, MyNamespace"/>
  </configSections>
  <system.web/>
  <!--add this after system.web-->
  <mynamespace>
    <data defaultProvider="Sql">
      <providers>
        <clear/>
        <add type="Sql" name="MyDatabase" 
          connectionString ="connectionstring" 
          providerPath ="" databaseOwner="dbo"/>
      </providers>
      <dataProviders>
        <clear/>
        <add name="Order" objectQualifier="ex_">
          <providers>
            <provider name="Sql" 
              type="MyNamespace.Data.OrderSqlDataProvider"/>
          </providers>
        </add>
        <add name="Product" objectQualifier="ex_">
          <providers>
            <provider name="Sql" 
               type="MyNamespace.Data.ProductSqlDataProvider"/>
          </providers>
        </add>
        <add name="StoredProc" objectQualifier="">
          <providers>
            <provider name="Sql" 
               type="MyNamespace.Data.StoredProcSqlDataProvider"/>
          </providers>
        </add>
      </dataProviders>
      <procedures/>
    </data>
  </mynamespace>
</web.config>

Normally, you just need to integrate this file into your web.config as specified.

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
  • ease up programming through Intellisense.
• closes the reader on Dispose to avoid un-closed database connection

The ProductData class is defined as follows (long code):

///<summary>A wrapper class for the ex_Products table.</summary>
///<summary>A wrapper class for the ex_Products table.</summary>
public class ProductData : IDisposable
{
    private IDataReader m_dr;
    private int m_ProductID;
    private String m_ProductName;
    private decimal m_ProductPrice;

     ///<summary>Create a data wrapper</summary>
     ///<param name="dr">a opened data reader.</param>
     ///<exception cref="ArgumentNullException">if dr is null</param>
     public ProductData(IDataReader dr)
     {
         if (dr == null)
             throw new ArgumentNullException("datareader");
         m_dr = dr;
     }

     ///<summary>Create a data wrapper</summary>
     ///<param name="dr">a opened data reader.</param>
     ///<exception cref="ArgumentNullException">if dr is null</param>
     public ProductData()
     {
         m_dr = null;
     }

    ///<summary>Reads the row data from the data reader</summary>
    ///<returns>true if data was read, false otherwize</returns>
    ///<exception cref="System.ArgumentNullException">
    ///Thrown if dr is null</exception>
    public bool Read()
    {
        if (m_dr == null)
            throw new Exception("data reader is null");
        if (!m_dr.Read())
        {
            m_dr.Close();
            m_dr = null;
            return false;
        }

        m_ProductID=(int)m_dr["ProductID"];
        m_ProductName=(String)m_dr["ProductName"];
        m_ProductPrice=(decimal)m_dr["ProductPrice"];
        return true;
    }
    ///<summary>Closes the reader, if any</summary>
    public void Close()
    {
        if (m_dr != null)
        {
            m_dr.Close();
            m_dr = null;
        }
    }

    ///<summary>Release and close the reader</summary>
    public void Dispose()
    {
        Close();
    }

    ///<summary>ProductID set/get property</summary>
    public int ProductID
    {
        get{ return m_ProductID;}
        set{ m_ProductID=value;}
    }

    ///<summary>ProductName set/get property</summary>
    public String ProductName
    {
        get{ return m_ProductName;}
        set{ m_ProductName=value;}
    }

    ///<summary>ProductPrice set/get property</summary>
    public decimal ProductPrice
    {
        get{ return m_ProductPrice;}
        set{ m_ProductPrice=value;}
    }

} // ProductData

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

Using the generated classes for manipulating tables

The generated classes mimics exactly the DNN DAL proposal (see scheme below). Let's start with a simple example where the Products table is manipulated.

DAL structure, extracted from the DNN DAL document.

• Creating the business logic object:

  // creating the products table business logic
  ProductDB productDB = new ProductDB();

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

  // creating a product row wrapper
  ProductData pd = new ProductData();
  // setting fields, each field is a property
  pd.ProductName = "a product name";
  pd.ProductPrice = (decimal)49.99;
  
  // add the product
  productDB.AddProduct(ref pd);

  As one can see, pd is passed as reference to the AddProduct method. In fact, the ProductID field is retrieved from the database (was an auto-increment field) and updated into pd. You can also avoid using ProductData by calling directly:

  int pid = productID.AddProduct("name", (decimal)49.99);

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

  using(ProductData pd = productDB.GetProduct(pid))
  {
      // try to reade the Datareader
      if (!pd.Read())
          throw new Exception("...");
      // pd contains the data.
      string name = pd.ProductName;
  }
  // the reader is automatically closed from this loop (exception safe)

• Update a product:

  productDB.UpdateProduct( pd );
  productDB.UpdateProduct(pid, name, price); // equivalent

• Delete a product by ID:

  productDB.DeleteProduct( pd ); 
  productDB.DeleteProduct( pid );

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 StoredProcDB where the methods are named after the stored procedure name (remember StoredProcedure.Wrap above). In fact, to call GetOrders stored procedure, we do:

• Using IDataReader

  StoredProcDC spDB = new StoredProcDB();
  using (GetOrdersData orderData = new GetOrdersData( spDB.GetOrders() ))
  {
      while(orderData.Read())
      {
         // orderData contains the current row data
      }
  }

• Using DataSet

  DataSet ds = spDB.GetOrdersDataSet();

This concludes the description of the visible part of the generated code. Let's see what's happening behind the scenes.

Behind the scene

The things happening behind the scene are quite similar to what you can find in the DNN DAL description. For instance, the method ProductDB.GetProduct is laid out as follows:

public IDataReader GetProduct(int ProductID)
{
    return ProductDataProvider.Instance().GetProduct(ProductID);
}

ProductDataProvider.Instance is a static method defined in ProductDataProvider.cs: it looks for a data provider constructor in the cache, if not found. It gets the data provider type from the Web.config and inserts it in the cache.

static public ProductDataProvider Instance()
{
    // Use the cache because the reflection used later is expensive
    System.Web.Caching.Cache cache = System.Web.HttpContext.Current.Cache;
    string providerKey = m_ProviderName + m_ProviderType + "provider";
    if ( cache[providerKey] == null)
    {
        // Get the name of the provider
        DataProviderConfiguration providerConfiguration  = 
         DataProviderConfiguration.GetProviderConfiguration(m_ProviderType);
        //  The assembly should be in \bin or GAC,
        // so we simply need to get an instance of the type
        Provider provider = 
            providerConfiguration.Providers
            (providerConfiguration.DefaultProvider);
        DataProvider dataProvider = 
            providerConfiguration.DataProviders("Product");
        String type = dataProvider.Type( provider.Name );
        // Use reflection to store the constructor of
        // the class that implements DataProvider
        Type t = Type.GetType(type, true);

        // Insert the type into the cache
        cache.Insert(providerKey, 
          t.GetConstructor(System.Type.EmptyTypes));
    }
    return (ProductDataProvider) 
      ((ConstructorInfo)cache[providerKey]).Invoke(null);
}

Providers

Currently, there are two available providers: MSSQL and MySQL. However, this could be extended to other database systems: OleDB, PostgreSQL, Oracle, ...

MSSQL provider

This provider uses the Microsoft Applications 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. For example, let's add a MySQL provider to the previous example:

MySqlAdaptor mysql = new MySqlDatabaseAdaptor(
    "MysqlDB",
    "",
    "",
    "connectionstring");
cs.AddAdaptor(mysql);

The following elements are added to the web.config:

<web.config>
  ...
  <mynamespace>
    <data defaultProvider="Sql">
      <providers>
        <clear/>
        <add name="Sql" catalog="MyDatabase" 
           connectionString ="connectionstring" 
           providerPath ="" databaseOwner="dbo"/>
        <add name="MySql" catalog="MysqlDB" 
              connectionString ="connectionstring" 
              providerPath ="" databaseOwner=""/>
      </providers>
      ...
      <procedures>
        <clear/>
        <add name="GetOrders">
          <versions>
            <version provider="MySql">
              <![CDATA[SELECT 
U.UserID AS 'UserID',
P.ProductName AS 'ProductName',
O.ProductPrice AS 'Price',
O.OrderQuantity AS 'Quantity'
FROM (ex_Orders AS O 
INNER JOIN Users AS U
ON U.UserID = O.UserID)
INNER JOIN ex_Products AS P
ON P.ProductID = O.ProductID
;]]>
            </version>
          </versions>
        </add>
      </procedures>
    </data>
  </mynamespace>

Want to contribute?

SQLppNet is an open source project that always needs new people and ideas to evolve. If you are interested, please drop a message on the SourceForge page.

References

• DotNetNuke official site
• Sqlpp site