Hot File

Full Database Abstraction Layer Generator

View: 2351    Dowload: 0   Comment: 0   Post by: naruto  
Author: none   Category: PostgreSQL   Fields: Other

0 point/23 review File has been tested

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

Introduction

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:

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

file/path description
. root path
/Config Configuration classes directory (Config namespace)
/Config/webconfig.xml XML to include in the web.config of your application
/Config/DataProviderConfigurationHandler.cs Class handling the data in the web.config file
/Data DAL classes directory (Data namespace)
/Data/DbSql.sql SQL code for creating the Orders and Products table.
/Data/OrderAbstractDataProvider.cs Abstract data provider class for the Orders table
/Data/OrderSqlDataProvider.cs MSSQL data provider class for the Orders table
/Data/OrderStoreProcSql.sql Stored procedures used by the OrderSqlDataProvider data provider
/Data/OrderDB.cs Business logic layer for the Orders table (OrderDB class)
/Data/OrderData.cs DataRow 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:

<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"/>
        <strong><add name="MySql" catalog="MysqlDB" 
              connectionString ="connectionstring" 
              providerPath ="" databaseOwner=""/></strong>
      </providers>
      ...
      <strong><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></strong>
    </data>
  </mynamespace>

Full Database Abstraction Layer Generator

Full Database Abstraction Layer Generator Posted on 15-05-2014  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#) 5/10 2351

Comment:

To comment you must be logged in members.

Files with category

  • Accessing: MySQL Database using MySQL C API

    View: 2266    Download: 2   Comment: 0   Author: none  

    Accessing: MySQL Database using MySQL C API

    Category: PostgreSQL
    Fields: Other

    0.75/6 review
    This article was written for beginners, developers that do not know how to access MySql database using MySQL C API and would like to have a small introduction.

  • Full Database Abstraction Layer Generator

    View: 2351    Download: 0   Comment: 0   Author: none  

    Full Database Abstraction Layer Generator

    Category: PostgreSQL
    Fields: Other

    0/23 review
    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...

  • Code .NET 2.0; Build Java; Run Linux

    View: 1902    Download: 0   Comment: 0   Author: none  

    Code .NET 2.0; Build Java; Run Linux

    Category: PostgreSQL
    Fields: Other

    1.5/3 review
    This article is in the Product Showcase section for our sponsors at CodeProject. These reviews are intended to provide you with information on products and services that we consider useful and of value to developers. This is a showcase review for...

  • Postgres Database Backup/Restore From C#

    View: 2749    Download: 2   Comment: 0   Author: none  

    Postgres Database Backup/Restore From C#

    Category: PostgreSQL
    Fields: Other

    0/9 review
    This article provides Postgres database backup/restore functionality. By using this application we can check whether a Postgres database is installed or not in a system. If a Postgres database is installed then the Postgres databases list is...

  • Installing Redmine on Windows in production

    View: 5143    Download: 0   Comment: 0   Author: none  

    Installing Redmine on Windows in production

    Category: PostgreSQL
    Fields: Other

    0/9 review
    This is a very simple step by step guide to install Redmine on Windows server in production. This guide is applicable to IIS 7+ based servers, which include Windows Server 2008, 2008 R2, Windows Vista and Windows 7. Latter two should not be used for...

  • A SQL Management Console for MSSQL 2000 & 2005, MySQL 5.0,...

    View: 2981    Download: 0   Comment: 0   Author: none  

    A SQL Management Console for MSSQL 2000 & 2005, MySQL 5.0,...

    Category: PostgreSQL
    Fields: Other

    0.64285714285714/7 review
    Since MSSQL is not holding the lion share of the market yet, integration/interaction with other RDBMS like PL/Oracle, MySQL, FireBird, DB2, is inevitable. With the class System.Data.Common in ADO .NET 2.0, different data providers can write the...

  • Creating an Interactive Map in ASP.NET 2.0 Using SharpMap

    View: 4081    Download: 5   Comment: 0   Author: none  

    Creating an Interactive Map in ASP.NET 2.0 Using SharpMap

    Category: PostgreSQL
    Fields: Other

    0/2 review
    Showing maps on the web is becoming more and more common. The most well-known ones are Google Maps and Microsoft's Virtual Earth. Creating your own websites with interactive maps require you to get hold on the map-data and convert this data to...

  • DevShot - Database Snapshots for Developers

    View: 810    Download: 0   Comment: 0   Author: none  

    DevShot - Database Snapshots for Developers

    Category: PostgreSQL
    Fields: Other

    0/2 review
    A few weeks back, I got an idea of writing a simple tool for switching database states between coding cycles. I thought this would be a good tool for anyone who is working on database related projects and have to switch between database states since...

 
Newsletter Email

File suggestion for you

File top downloads

logo codetitle
Codetitle.com - library source code to share, download the file to the community
Copyright © 2015. All rights reserved. codetitle.com Develope by Vinagon .Ltd