Click here to Skip to main content
16,016,425 members
Articles / Programming Languages / C#
Article

Full Database Abstraction Layer Generator

Rate me:
Please Sign up or sign in to vote.
4.74/5 (28 votes)
22 Oct 20038 min read 280.6K   214   41
Yet another DAL generator with SQL generation, multi database support, C# code generation, etc...

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:

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:

Image 1

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:

C#
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:

C#
Database db = new Database( msSql );

Adding tables

Tables are added using the following function call:

C#
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:

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

Add primary keys

Primary key addition is straightforward:

C#
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:

C#
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:

C#
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:

C#
orders.AddForeignKey( products );

Add fields

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

C#
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:

SQL
Select user id, product name, quantity, price and order id 
from users, products and orders.
  1. Create a query,
    C#
    SelectQuery q = db.CreateQuery();
  2. Add tables with alias (optional) and link them,
    C#
    QueryTable qusers = q.AddTable(
        users, // table to add 
        "U" // alias
        );

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

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

    SQLpp will handle the rest of the Join process.

  3. Add fields
    C#
    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:

C#
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):

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

You also need to add the specific queries you generated:

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

Generating the DAL

For sure the easiest part:

C#
cs.Generate();

Resuming the code:

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

C#
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):

file/pathdescription
.root path
/ConfigConfiguration classes directory (Config namespace)
/Config/webconfig.xmlXML to include in the web.config of your application
/Config/DataProviderConfigurationHandler.csClass handling the data in the web.config file
/DataDAL classes directory (Data namespace)
/Data/DbSql.sqlSQL code for creating the Orders and Products table.
/Data/OrderAbstractDataProvider.csAbstract data provider class for the Orders table
/Data/OrderSqlDataProvider.csMSSQL data provider class for the Orders table
/Data/OrderStoreProcSql.sqlStored procedures used by the OrderSqlDataProvider data provider
/Data/OrderDB.csBusiness logic layer for the Orders table (OrderDB class)
/Data/OrderData.csDataRow wrapper of the Orders table
And the same for the products table and the stored procedures

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:

XML
<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):

C#
///<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.

Image 2

DAL structure, extracted from the DNN DAL document.

  • Creating the business logic object:
    C#
    // 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.
    C#
    // 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:

    C#
    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.
    C#
    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:
    C#
    productDB.UpdateProduct( pd );
    productDB.UpdateProduct(pid, name, price); // equivalent
  • Delete a product by ID:
    C#
    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
    C#
    StoredProcDC spDB = new StoredProcDB();
    using (GetOrdersData orderData = new GetOrdersData( spDB.GetOrders() ))
    {
        while(orderData.Read())
        {
           // orderData contains the current row data
        }
    }
  • Using DataSet
    C#
    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:

C#
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.

C#
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:

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

The following elements are added to the web.config:

XML
<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

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Engineer
United States United States
Jonathan de Halleux is Civil Engineer in Applied Mathematics. He finished his PhD in 2004 in the rainy country of Belgium. After 2 years in the Common Language Runtime (i.e. .net), he is now working at Microsoft Research on Pex (http://research.microsoft.com/pex).

Comments and Discussions

 
GeneralRe: Why not use DataTables for storing the retrieved data? Pin
Jonathan de Halleux24-Oct-03 3:30
Jonathan de Halleux24-Oct-03 3:30 
GeneralRe: Why not use DataTables for storing the retrieved data? Pin
Sumeet Kumar24-Oct-03 7:53
Sumeet Kumar24-Oct-03 7:53 
GeneralRe: Why not use DataTables for storing the retrieved data? Pin
Jonathan de Halleux24-Oct-03 3:40
Jonathan de Halleux24-Oct-03 3:40 
GeneralRe: Why not use DataTables for storing the retrieved data? Pin
Sumeet Kumar24-Oct-03 8:18
Sumeet Kumar24-Oct-03 8:18 
GeneralRe: Why not use DataTables for storing the retrieved data? Pin
Anonymous26-Oct-03 20:38
Anonymous26-Oct-03 20:38 
GeneralRe: Why not use DataTables for storing the retrieved data? Pin
Jonathan de Halleux24-Oct-03 5:21
Jonathan de Halleux24-Oct-03 5:21 
GeneralI still don't get it Pin
Marc Clifton23-Oct-03 13:50
mvaMarc Clifton23-Oct-03 13:50 
GeneralRe: I still don't get it Pin
Erick Sgarbi24-Oct-03 0:39
Erick Sgarbi24-Oct-03 0:39 
I agree. I believe field replacement would be good choice however the abstraction makes it quite hard to accomplish.

Cheers
Erick
GeneralRe: I still don't get it Pin
Jonathan de Halleux24-Oct-03 1:42
Jonathan de Halleux24-Oct-03 1:42 
GeneralRe: I still don't get it Pin
partyganger24-Oct-03 3:52
partyganger24-Oct-03 3:52 
GeneralRe: I still don't get it Pin
leppie24-Oct-03 22:16
leppie24-Oct-03 22:16 
GeneralRe: I still don't get it Pin
Jonathan de Halleux3-Nov-03 3:27
Jonathan de Halleux3-Nov-03 3:27 
GeneralRe: I still don't get it Pin
Sebastien Lorion27-Dec-03 23:53
Sebastien Lorion27-Dec-03 23:53 
GeneralOutstanding Pin
bigAPE23-Oct-03 8:50
bigAPE23-Oct-03 8:50 
GeneralRe: Outstanding Pin
Jonathan de Halleux23-Oct-03 22:13
Jonathan de Halleux23-Oct-03 22:13 
GeneralNice! Pin
Jeff Varszegi23-Oct-03 6:32
professionalJeff Varszegi23-Oct-03 6:32 
GeneralRe: Nice! Pin
Jonathan de Halleux23-Oct-03 21:58
Jonathan de Halleux23-Oct-03 21:58 

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.