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:
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",
"dnn_",
"User"
);
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");
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",
"nvarchar(50)",
true
);
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.
- Create a query,
SelectQuery q = db.CreateQuery();
- Add tables with alias (optional) and link them,
QueryTable qusers = q.AddTable(
users,
"U"
);
The other tables are added similarly. The tables need to be joined:
q.Join(
qorders,
qusers
);
q.Join(qorders,qproducts);
SQLpp will handle the rest of the Join
process.
- Add fields
q.AddField(
FieldExpression.Link(
qusers,
users.get_Field("ID")
),
"UserID"
);
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.
- 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,
".",
"MyNamespace",
"StoredProc"
);
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
{
MsSqlDatabaseAdaptor msSql = new MsSqlDatabaseAdaptor(
"MyCatalog",
"",
"dbo",
"connectionstring"
);
Database db = new Database( msSql );
DbTable users = db.AddTable("User","","User");
DbTable products = db.AddTable("Product","ex_","Product");
DbTable orders = db.AddTable("Order","ex_","Order");
users.AddPrimaryKey("ID","int");
orders.AddPrimaryKey("ID","int");
products.AddPrimaryKey("ID","int");
orders.AddForeignKey( users );
orders.AddForeignKey( products );
products.AddField("Name","nvarchar(50)",true);
products.AddField("Price","decimal",true);
orders.AddField("Quantity","int",true);
orders.AddField("Timestamp","timestamp",true);
CsGenerator cs = new CsGenerator(
db,
"c:\\Inetpub\\wwwroot\\DnnFramework\\DotNetNuke\\DesktopModules",
"UserQuotes",
"StoredProc"
);
cs.AddTable(orders);
cs.AddTable(products);
SelectQuery q = db.CreateQuery();
QueryTable qusers = q.AddTable(users,"U");
QueryTable qorders = q.AddTable(orders,"O");
QueryTable qproducts = q.AddTable(products,"P");
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") );
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>
<configSections>
<section name="mynamespace"
type="MyNamespace.Config.DataProviderConfigurationHandler, MyNamespace"/>
</configSections>
<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):
public class ProductData : IDisposable
{
private IDataReader m_dr;
private int m_ProductID;
private String m_ProductName;
private decimal m_ProductPrice;
public ProductData(IDataReader dr)
{
if (dr == null)
throw new ArgumentNullException("datareader");
m_dr = dr;
}
public ProductData()
{
m_dr = null;
}
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;
}
public void Close()
{
if (m_dr != null)
{
m_dr.Close();
m_dr = null;
}
}
public void Dispose()
{
Close();
}
public int ProductID
{
get{ return m_ProductID;}
set{ m_ProductID=value;}
}
public String ProductName
{
get{ return m_ProductName;}
set{ m_ProductName=value;}
}
public decimal ProductPrice
{
get{ return m_ProductPrice;}
set{ m_ProductPrice=value;}
}
}
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:
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.
ProductData pd = new ProductData();
pd.ProductName = "a product name";
pd.ProductPrice = (decimal)49.99;
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))
{
if (!pd.Read())
throw new Exception("...");
string name = pd.ProductName;
}
- Update a product:
productDB.UpdateProduct( pd );
productDB.UpdateProduct(pid, name, price);
- 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:
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()
{
System.Web.Caching.Cache cache = System.Web.HttpContext.Current.Cache;
string providerKey = m_ProviderName + m_ProviderType + "provider";
if ( cache[providerKey] == null)
{
DataProviderConfiguration providerConfiguration =
DataProviderConfiguration.GetProviderConfiguration(m_ProviderType);
Provider provider =
providerConfiguration.Providers
(providerConfiguration.DefaultProvider);
DataProvider dataProvider =
providerConfiguration.DataProviders("Product");
String type = dataProvider.Type( provider.Name );
Type t = Type.GetType(type, true);
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[
</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
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).