DACBuilder � Data Access objects generation tool based on XML and XSL templates transformation

• Download demo project - 380 Kb
• Download source - 304 Kb

Introduction

When I started working in database programming and complex business implementations, the need for a data access framework was definitive. I started to build my own classes that accessed data objects. Of course, starting from 0, the effort was bigger and I thought that a "general" data access framework could help me and my team. The short time didn't allow me to imagine such an infrastructure, for at least one database management system and one programming language. But when I started worked at the Prodigy company, the desire was materialized. I discovered a complex framework implementation which reflected many of the SQL Server database tables' features, like tables and column definitions, constraints and relationships. Of course, the data access classes that mapped database tables were auto-generated with a specific tool. But the application scope is not to explain that complex framework. Data Access classes generation is a common feature, used by many programmers and, even Microsoft has its Entrerprise Library and will implement LINQ which likely be a powerful tool. The DACBuilder intends not to implement another data access layer generator, but to show how easily you can generate classes in different programming languages, mapped on tables in different data sources. The key for this generation is XML and XSL transformations.

An example

Here is an example of code generated in VBScript, for a database management system accessed by an ODBC driver. Consider a table named "Items", with the following structure:

ID � int
PK Name � varchar(255)
Price � decimal (18,2) 
CategoryID � int � FK to "Categories" table

Const adUseServer = 2

Const adCmdText = 1
Const adCmdStoredProc = 4

Const adInteger = 3
Const adVarChar = 200
Const adDate = 7
Const adDecimal = 14
Const adBoolean = 11
Const adBinary = 128
Const adVariant = 12

Const adParamOutput = 2

Class DACItems

    'BEGIN Private members

    
    Private xID
    Private xName
    Private xPrice
    Private xCategoryID
    
    Private cnnString
    Private cnn

    'END Private members


    Public Sub Initialize()
        
        xID = -1
        xName = ""
        xPrice = -1
        xCategoryID = -1

        cnnString = "" 'fill in with the specified value

    End Sub
        
    Public Sub Uninitialize()
        Set cnn = Nothing
    End Sub

    'BEGIN Properties

    
    Public Property Let ID(vData)
            xID = vData
    End Property

    Public Property Get ID()
            ID = xID
    End Property

    Public Property Let Name(vData)
            xName = vData
    End Property

    Public Property Get Name()
            Name = xName
    End Property

    Public Property Let Price(vData)
            xPrice = vData
    End Property

    Public Property Get Price()
            Price = xPrice
    End Property

    Public Property Let CategoryID(vData)
            xCategoryID = vData
    End Property

    Public Property Get CategoryID()
            CategoryID = xCategoryID
    End Property

    'END Properties

    
    'BEGIN Connection


    Private Sub OpenConnection()
        Set cnn = CreateObject("ADODB.Connection")
        With cnn
            .Provider = "MSDASQL"
            .CursorLocation = adUseServer  'Must use Server side cursor.

            .ConnectionString = cnnString
            .Open
        End With
    End Sub

    Private Sub CloseConnection()        
        cnn.Close
        Set cnn = Nothing
    End Sub

    Public Property Let ConnectionString(vData)
            cnnString = vData
    End Property

    Public Property Get ConnectionString ()
            ConnectionString = cnnString
    End Property
    
    'END Connection

    
    'BEGIN Standard methods


    Public Function Add()
        Dim bRet
        OpenConnection
        
        Dim cmd 
        Set cmd = CreateObject("ADODB.Command")
        Set cmd.ActiveConnection = cnn
        cmd.CommandType = adCmdText
        cmd.CommandText = "INSERT INTO Items VALUES(?, ?, ?, ?)"
            
        Dim param
        
        Set param = cmd.CreateParameter("", adInteger)
        param.Value = xID
        cmd.Parameters.Append param

        Set param = cmd.CreateParameter("", adVarChar, 255)
        param.Value = xName
        cmd.Parameters.Append param

        Set param = cmd.CreateParameter("", adDecimal, 18, 2)
        param.Value = xPrice
        cmd.Parameters.Append param

        Set param = cmd.CreateParameter("", adInteger)
        param.Value = xCategoryID
        cmd.Parameters.Append param

        Dim result
        cmd.Execute result
        CloseConnection
        
        bRet = result > 0
        Set cmd = Nothing
        Add = bRet
    End Function

    Public Function Edit()
        Dim bRet
        OpenConnection
        
        Dim cmd
        Set cmd = CreateObject("ADODB.Command")
        Set cmd.ActiveConnection = cnn
        cmd.CommandType = adCmdText
        cmd.CommandText = "UPDATE Items SET Name = ?," & _ 
                          " Price = ?, CategoryID = ? WHERE ID = ?"
            
        Dim param
        
        Set param = cmd.CreateParameter("", adVarChar, 255)
        param.Value = xName
        cmd.Parameters.Append param

        Set param = cmd.CreateParameter("", adDecimal, 18, 2)
        param.Value = xPrice
        cmd.Parameters.Append param

        Set param = cmd.CreateParameter("", adInteger)
        param.Value = xCategoryID
        cmd.Parameters.Append param

        Set param = cmd.CreateParameter("", adInteger)
        param.Value = xID
        cmd.Parameters.Append param

        Dim result
        cmd.Execute result
        CloseConnection
        bRet = result > 0
        Set cmd = Nothing
        Edit = bRet
    End Function
    
    Public Function Delete()
        Dim bRet
        OpenConnection
        
        Dim cmd
        Set cmd = CreateObject("ADODB.Command")
        Set cmd.ActiveConnection = cnn
        cmd.CommandType = adCmdText
        cmd.CommandText = "DELETE FROM Items WHERE ID = ?"
            
        Dim param
            
        Set param = cmd.CreateParameter("@ID", adInteger)
        param.Value = xID
        cmd.Parameters.Append param

        Dim result
        cmd.Execute result
        CloseConnection
        bRet = result > 0
        Set cmd = Nothing
        Delete = bRet
    End Function
    
    Public Function Read()
        Dim bRet
        Dim rs
        Set rs = CreateObject("ADODB.Recordset")
        OpenConnection
        
        Dim cmd
        Set cmd = CreateObject("ADODB.Command")
        cmd.CommandType = adCmdText
        cmd.CommandText = "SELECT * FROM Items WHERE ID = ?"
            
        Dim param
        
        Set param = cmd.CreateParameter(", adInteger)
        param.Value = xID
        cmd.Parameters.Append param

        Set rs = cmd.Execute
        If Not(rs.EOF) Then
                
            xID = CInt(rs("ID"))
            xName = CStr(rs("Name"))
            xPrice = CDbl(rs("Price"))
            xCategoryID = CInt(rs("CategoryID"))
            bRet = True
        End If
        CloseConnection
        bRet = False
        Set rs = Nothing
        Set cmd = Nothing
        Read = bRet
    End Function
    
    
    Public Function ReadCategoriesItems()
        Dim rs
        Set rs = CreateObject("ADODB.Recordset")
        OpenConnection
            
        Dim cmd
        Set cmd = CreateObject("ADODB.Command")
        Set cmd.ActiveConnection = cnn
        cmd.CommandType = adCmdText
        cmd.CommandText = "SELECT * FROM Items WHERE CategoryID = ?"
            
        Dim param
        Set param = cmd.CreateParameter("", adInteger)
        param.Value = xCategoryID
        cmd.Parameters.Append param
        Set rs = cmd.Execute
        Set cmd = Nothing
        ReadCategoriesItems = rs
    End Function

    
    'END Standard Methods
    
    'BEGIN Other DAC logic
    
    'END Other DAC logic
        
End Class

You may see that the auto-generated class has some properties, mapped on columns that belong to the Items table, and some methods (Add, Edit, Delete, Read), responsible for inserting, updating, deleting, and reading a record from the table, and the method ReadCategoriesItems, which returns a RecordSet with all items for a specified category. You can see the SQL statement for the methods which uses the unnamed parameters format. This is an option. If the data source is SQL Server and you want to execute a stored procedure with named parameters (which is the default for code generation), you are able to do that.

DB Systems, table structure, and XML output

The code generation is made starting just from a table structure. If you define the appropriate fields structure, tables, and the relationships between them, the DACBuilder job is simple and powerful. So, the starting point is the database. The tables structure can be reflected in objects. The DACBuilder application brings the structure of a specified table or view and, if applicable, the structure of a specified stored procedure or a function (routine). It filters on tables or views, for persistent objects, and on stored procedures and functions for routines. There are multiple choices for code generation:

• data access classes in different languages of the .NET platform (for now, just C# and Visual Basic .NET);
• data access classes for other languages PHP, VB 6.0, VBScript, and JScript;
• SQL Server stored procedures for insert, update, delete, select, and search;
• UI features (design and code) for .NET Windows Forms, WebForms (aspx pages and ascx controls), and HTML;
• XML format resources generation;
• XML schema generation;
• general custom fields implementation;
• connection strings for multiple data sources generation.

The code is generated in a rich text edit box and not directly in a file on disk, but you can save the file with the specified extension, depending on what it contains (SQL script or code). It has a simple color syntax highlighting based on the current generated content language. For .NET languages, it has a compiling option and an assembly generation command (with run-time reference of the required assemblies), of course with error management. Depending on what database system you choose, the application reads the tables contained by the specified database. The implementation is available just for SQL Server, Access, and MySQL, and for every system there exists specific methods to reach the metadata information. In every situation, the output is an XML document which is transformed using a template for every chosen action. The XML structure is a simple one, with two levels (table information � level 1, columns information � level 2).

The XML for the table in the given example is:

<table name="Items" friendly_name="Items" 
                  base="" namespace="" abbreviation="">
  <column column_name="ID" column_friendly_name="ID" 
      ordinal_position="1" is_nullable="No" data_type="int" 
      numeric_precision="10" numeric_precision_radix="10" 
      numeric_scale="0" identity="0" 
      constraint_type="PRIMARY KEY" referenced_table="" /> 
  <column column_name="Name" column_friendly_name="Name" 
      ordinal_position="2" is_nullable="No" data_type="varchar" 
      character_maximum_length="255" character_octet_length="255" 
      constraint_type="" referenced_table="" /> 
  <column column_name="Price" column_friendly_name="Price" 
      ordinal_position="3" is_nullable="No" data_type="decimal" 
      numeric_precision="18" numeric_precision_radix="10" 
      numeric_scale="2" constraint_type="" referenced_table="" /> 
  <column column_name="CategoryID" column_friendly_name="CategoryID" 
      ordinal_position="4" is_nullable="No" data_type="int" 
      numeric_precision="10" numeric_precision_radix="10" 
      numeric_scale="0" constraint_type="FOREIGN KEY" 
      referenced_table="Categories" /> 
</table>

The meaning for the attributes contained in the XML nodes is explained in the following table:

For SQL Server, the procedure which returns metadata information is called GetTableColumnsXML and contains a FOR XML EXPLICIT clause in the SQL statement. The information is brought from the INFORMATION_SCHEMA views: tables, columns, KEY_COLUMN_USAGE, table constraints, and referential constraints.

For MS Access systems, the method to get metadata information is performed using OLEDB features, as the GetOleDbSchemaTable method provided by the OleDbConnection class. This is useful, as not only a MS Access system can be analysed, but any other OLEDB provider. The output is the XML document obtained using the DataSet object XML output.

For MySQL systems, the analysis is performed in a specific way, using the SHOW statement: "SHOW FULL COLUMNS FROM tableName". The ODBC driver 3.51 for MySQL is required, as the application uses ODBC objects to get information. The output is an XML document obtained as in the MS Access case.

For both MS Access and MySQL systems, and any other new system which could be implemented, the XML output must be transformed in the DACBuilder XML format (which was explained before). The transformation is performed using a specific template (msaccess_2_sql.xsl or mysql_2_sql.xsl).

The object responsible for obtaining information about metadata in the database systems is called DBHelper, which is included in the DACCreator namespace, in the referenced DACCreator assembly. The code in this assembly is not provided and explained because of time and space missing. Of course, it will be available on demand. The DBHelper() constructor receives a connection string and a database system type. The database type is specified using an enumeration called DBTypes:

public enum DBTypes
{
    SQL = 0
    , ORACLE = 1
    , MSACCESS = 2
    , MYSQL = 3
}

The Oracle system for DBTypes.ORACLE is not implemented.

An example of how to get information about a table in a database of a specific system is provided in the GetStructure method of the BForm application main form:

DBHelper dbH = new DBHelper(ConnectionString, dbType);
XmlDocument doc = dbH.GetTableStructure(cboTables.SelectedValue.ToString()); 
switch(dbType)
{
    case DBTypes.MYSQL:
        CommonGenerator mysql_DACGen =
              new CommonGenerator(cboTables.SelectedValue.ToString(), doc);
        mysql_DACGen.Generate(templatesPath + "mysql_2_sql.xsl");
        doc.LoadXml(mysql_DACGen.CommonCode);
        break;
    case DBTypes.MSACCESS:
        CommonGenerator msaccess_DACGen = 
             new CommonGenerator(cboTables.SelectedValue.ToString(), doc);
        msaccess_DACGen.Generate(templatesPath + "msaccess_2_sql.xsl");
        doc.LoadXml(msaccess_DACGen.CommonCode);
        break;
    default:
        break;
}

The CommonGenerator class is used to transform XML documents (either in files on disk, or in memory as XmlDocument objects). The method used is called Generate. If the transformation is performed with no errors, the output is stored in the CommonCode property, as a string (which could be XML, HTML, or text, depending on the method attribute of the xsl:output element in the XML document passed as parameter for the Generate method). The code for transformation consists of a few lines of code:

XPathNavigator nav = StructureDOM.DocumentElement.CreateNavigator();
XmlDocument xsldoc = new XmlDocument();
xsldoc.Load(xslTemplatePath);
XmlNamespaceManager nsmgr = new XmlNamespaceManager(xsldoc.NameTable);
nsmgr.AddNamespace("xsl", "http://www.w3.org/1999/XSL/Transform");
XmlUrlResolver resolver = new XmlUrlResolver();
XslTransform trans = new XslTransform();
trans.Load(xsldoc, resolver, this.GetType().Assembly.Evidence);
TextWriter writer = new StringWriter();
XmlTextWriter xmlWriter = new XmlTextWriter(writer);
xmlWriter.Formatting = Formatting.Indented;
trans.Transform(nav, null, xmlWriter, null);
string sOutput = writer.ToString();

StructureDOM is the XML document which is subject to the transformation, passed as a parameter in the CommonGenerator constructor.

Routines handling is available only for the SQL Server system. The routines list is brought using the same DBHelper object, with the method GetRoutines, which uses the INFORMATION_SCHEMA.ROUTINES view. The stored procedure which returns metadata information is called GetRoutineParametersXML, and uses the INFORMATION_SCHEMA.PARAMETERS view. Of course, the SQL statement contains the FOR XML EXPLICIT clause to format data in the DACBuilder XML format. Most attributes found on a base table are available for stored procedure parameters.

The XML output obtained by gathering metadata information is then added to a DataSet object with two tables. The DataSet object is passed as a data source to the dgColumns grid in which you can edit information. It is recommended that you give a simple and concise friendly name for the parent object (table or routine) and child objects (columns or parameters) because they will be the names you will use later in the applications which are using the generated DAC objects.

Template parameters

Regarding objects generation, the technique is the same for all objects. Starting from a table or a routine structure, applying different templates, different objects are generated, either as a SQL statement (stored procedure), as a class in a specified programming language, or as a schema or HTML code. Because the XSL templates can be customized using a xsl:parameter element, a visual feature is implemented to allow changes for these parameters. It is about a form class that receives in its constructor a parameter of the DataTable type, which contains all the parameters in the XSL stylesheet. The application loads the stylesheet in an XmlDocument object, searches for all existing parameters, and creates a DataTable object, dtParameters, with three columns:

• name � parameter name;
• value � parameter value;
• select � boolean attribute specifying whether the parameter gets its value from a constant value, or from a select attribute (if it is true, it means that the value is obtained from the parsed XmlDocument).

Having a template with this collection of parameters (xsl:param):

<xsl:param name="auto_increment" select="boolean(/table/column[@identity=1])"/>
<xsl:param name="pk">PRIMARY KEY</xsl:param>
<xsl:param name="fk">FOREIGN KEY</xsl:param>
<xsl:param name="getID">0</xsl:param>
<xsl:param name="insert_sp" select="concat(/table/@friendly_name, '_INSERT')"/>
<xsl:param name="update_sp" select="concat(/table/@friendly_name, '_UPDATE')"/>
<xsl:param name="delete_sp" select="concat(/table/@friendly_name, '_DELETE')"/>
<xsl:param name="select_sp" select="concat(/table/@friendly_name, '_SELECT')"/>
<xsl:param name="use_noname_parameters">0</xsl:param>
<xsl:param name="use_DataAccessComponent">0</xsl:param>
<xsl:param name="use_CustomFields">0</xsl:param>

The configuration of a form instance will be:

Depending on the parameter values, the generated objects can substantially differ, which gives applications a powerful extensibility. The form is called frmParameters, and it is opened as a modal dialog in the PrepareXslParameters method of the main application form. The private member xslParametersDoc of type XmlDocument is filled with a valid XSL stylesheet, and its xsl:param collection is transformed in a DataTable object.

xslParametersDoc = new XmlDocument();
xslParametersDoc.Load(xslFileName);
XmlNamespaceManager nsmgr = new 
  XmlNamespaceManager(xslParametersDoc.NameTable);
nsmgr.AddNamespace("xsl", 
  "http://www.w3.org/1999/XSL/Transform");
XmlNodeList parameters = 
  xslParametersDoc.SelectNodes("/xsl:styl" + 
  "esheet/xsl:param", nsmgr);
DataTable dtParameters = new DataTable();
dtParameters.Columns.Add(new 
  DataColumn("name", typeof(string)));
dtParameters.Columns.Add(new 
  DataColumn("value", typeof(string)));
dtParameters.Columns.Add(new 
  DataColumn("select", typeof(bool)));

The form is instantiated with the DataTable object which serves as the data source for the editable grid.

frmParameters frm = new frmParameters(dtParameters);
...
dtParameters.DefaultView.AllowNew = false;
dgParameters.DataSource = dtParameters.DefaultView;

Only the value attribute is editable, and the modified parameters are returned back into the XSL document using an event on the frmParameters form.

frm.SelectParameters += new 
    SelectParametersEventHandler(frm_SelectParameters);

The delegate SelectParametersEventHandler definition is:

public delegate void 
  SelectParametersEventHandler(object sender, 
  SelectParametersEventArgs e);

The class SelectParametersEventArgs contains the parameters DataTable object with the new values (in a property called Parameters). The handler allocated for the event SelectParameters takes the Parameters DataTable and returns all the xsl:param nodes into the xslParametersDoc:

XmlNamespaceManager nsmgr = new 
      XmlNamespaceManager(xslParametersDoc.NameTable);
nsmgr.AddNamespace("xsl", 
      "http://www.w3.org/1999/XSL/Transform");
XmlNode node;
XmlElement elem;
bool bIsSelect;
foreach(DataRow dr in e.Parameters.Rows)
{
    node = xslParametersDoc.SelectSingleNode("/xsl:stylesh" + 
           "eet/xsl:param[@name='" + 
           dr["name"].ToString() + "']", nsmgr);
    if(node != null)
    {
        elem = (XmlElement)node;
        bIsSelect = (bool)dr["select"];
        if(bIsSelect)
            elem.SetAttribute("select", dr["value"].ToString());
        else
            node.InnerText = dr["value"].ToString();
    }
}

If the user press OK on frmParameters, the xslParametersDoc object is passed to the Generate method of the CommonGenerator object, and the output is rendered using the new parameter values; otherwise, the output is rendered with the default parameter values.

DAC Transformation

Next, I will try to explain a DAC transformation. Using the table in the example at the beginning of the article, we will generate a simple data access container (DAC) with access on a SQL Server system, and as programming language - C#. First, we choose the table Items from the tables combo box. Next, in the structure menu, choose the Get structure command. This will automatically fill the dgColumns DataGrid with the metadata information about the columns. The grid will have two nested tables:

• the first containing information about the selected table;
• the second (child), containing information about table columns.

We can fill the values for friendly_name, namespace, and abbreviation (suppose this DAC will be used in an accounting system, so we decide its abbreviation will be ACC) in the first table. We can now fill some information about the table columns. The second column of the second table is column_friendly_name which will represent the property names. All the other fields could be changed. For example, if we do not have a constraint with the Categories table defined in the database, we can force to have it in the application; even this technique is not recommended (we can change the constraint_type value with "FOREIGN KEY" and the referenced_table value with "Categories"). After all the modifications are done, we can generate the DAC class. From the .NET DAC menu, we can choose the Simple DAC (SQL) command. The frmParameters window is called because the corresponding stylesheet contains the parameters. Let's explain the parameters:

• auto_increment � specifies if the columns has the identity attribute.
• pk - primary key.
• fk - foreign key.
• getID - if 1, it will output the inserted primary key value.
• insert_sp - insert stored procedure name, usually table_name + "_INSERT".
• update_sp - update stored procedure name, usually table_name + "_UPDATE".
• delete_sp - delete stored procedure name, usually table_name + "_DELETE".
• select_sp - select stored procedure name, usually table_name + "_SELECT".
• use_noname_parameters - if 1, creates SQL statements using the OLE DB unnamed parameters format "?".
• use_DataAccessComponents - if 1, the DAC object will not be independent, but it will be part of a Data Access Component framework, which provides connection information, transaction, and so on (in addition, the .NET Data Access Container and .NET Data Access Component commands provide the classes used in conjunction with the generated DAC objects in this framework).
• use_CustomFields - if 1, generates supplementary statements for the Custom Fields usage.

The main stylesheet template is:

<xsl:template match="/">using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
<xsl:if test="$use_CustomFields=1">
using System.Reflection;
using System.IO;
using DotNetScripting;
using CustomFields;
</xsl:if>

namespace <xsl:value-of select="/table/@namespace"/>.DAC
{

    public class <xsl:value-of select="/table/@abbreviation"/>
           DAC<xsl:value-of select="/table/@friendly_name"/>
           <xsl:if test="$use_DataAccessComponent=1"> 
           : DataAccessContainer</xsl:if>
    {
        public <xsl:value-of 
               select="/table/@abbreviation"/>DAC
               <xsl:value-of select="/table/@friendly_name"/>()
        {
            <xsl:if test="$use_DataAccessComponent=1">
            this.TableName = "<xsl:value-of select="/table/@name"/>";
            this.AutoIncrement = 
               <xsl:choose><xsl:when test="$auto_increment=1">
               true</xsl:when><xsl:otherwise>false
               </xsl:otherwise></xsl:choose>;
            
            <xsl:if test="$use_CustomFields=1">
            LoadCustomFields();
            </xsl:if>
        </xsl:if>
        }
        
        #region Private members
        
        <xsl:apply-templates select="/table/column" mode="member"/>
        
        private DataRow x<xsl:value-of select="/table/@friendly_name"/>Row;

        #endregion

        #region Properties
        
        <xsl:apply-templates select="/table/column" mode="property"/>
        
        public DataRow <xsl:value-of select="/table/@friendly_name"/>Row
        {
            get
            {
                return x<xsl:value-of select="/table/@friendly_name"/>Row;
            }
        }
        
        #endregion
        
        
        <xsl:if test="$use_DataAccessComponent!=1">
        #region Connection
        
        <xsl:call-template name="connection"/>
        
        #endregion
        </xsl:if>
        
        #region Standard methods
        
        <xsl:choose>
            <xsl:when test="$use_DataAccessComponent!=1">
        <xsl:call-template name="methods"/>
            </xsl:when>
            <xsl:when test="$use_DataAccessComponent=1">
        <xsl:call-template name="methods_DataAccessComponent"/>
            </xsl:when>
        </xsl:choose>
        
        #endregion
        
        
        #region Other DAC logic
        
        #endregion
        
        
        <xsl:if test="$use_CustomFields=1">
        <xsl:call-template name="CustomFields"/>
        </xsl:if>
        
    }
        
}
</xsl:template>

You can see how by filtering using the parameter values, the result is different. Other templates are also used, either by calling, or by applying. An example of calling a template is used for connection, when the use_DataAccessComponent parameter value is not equal to 1:

<xsl:if test="$use_DataAccessComponent!=1">
#region Connection

<xsl:call-template name="connection"/>
    
#endregion
</xsl:if>

The template called connection contains the private members and public properties and methods used to open connections to a database. An example of applying a template is used to create the private members and public properties mapped on table columns:

<xsl:apply-templates select="/table/column" mode="member"/>
<xsl:apply-templates select="/table/column" mode="property"/>

The mode attribute is used to differentiate between the templates with the same match (column, in our example). There are different templates, based on the code you want to output, SQL statements, or programming language. Depending on the selected language, there will be supplementary templates, special for members or property data types, parameter types, default values, or cast. The parameter use_DataAccessComponent is important, because it includes the object in a different framework. If you want to use this framework, you have to generate the DataAccessContainer and DataAccessComponent classes for the specified namespace - SQL, OleDb, or Odbc. The DataAccessContainer class is a base class for DAC objects, containing virtual methods like Add, Edit, Delete, and Read. The DataAccessComponent class can handle DAC objects, either one or multiple, in a transaction or not. The objects will not have anymore connection management, because the connection can be opened by the DataAccessComponent class. Though, a valid connection object and a valid data access command must be provided to the objects. The DataAccessContainer has a public property called RowState, of DataRowState type, which tells to DataAccessComponent what kind of operation to perform on a DAC object. The derived DAC objects must override the Add, Edit, and Delete methods. The ExecuteOperation method receives a DAC object as parameter and, depending on the RowState property value, inserts, updates, or deletes it. The ExecuteOperations methods receive an ArrayList of DAC objects, and perform the same actions on it, either in a transaction or not.

The classes code is available on the .NET DAC menu, Data Access Container or Data Access Component commands. You have to compile and create an assembly with these two classes (the .NET DAC->Compile and the .NET DAC->Create assembly methods). The generated assembly must be referenced in order to compile a generated DAC object in the DataAccessComponent environment.

In this way, the framework can be customized with additional features, because it is recommended to have a balanced approach of the DAC framework and the database features to get the best performance in an application. The DataAccessComponent class provides additional methods for executing stored procedures and reading data.

DB routine execution

The DACBuilder application provides functionality for generating .NET code that allows SQL routine or query execution. It builds a class called DAC_<sp_name> with a method called <sp_name>, where <sp_name> is the procedure name. If the use_properties parameter is set to 1, then it creates properties for this class, instead of using method parameters. Every routine parameter will have its own property. In other cases, the methods will receive a parameter for every routine parameter. The query parameter is used to differentiate between the executing non query statements and received result sets.

Compiling code

The .NET generated DAC objects code can be compiled and saved as binary in assemblies. The compiling and assembly generation is performed by objects residing in the DotNetScripting namespace. This powerful feature was the subject of a CodeProject article, DotNetScript. Thank you, jconwell, for the interesting article. It uses System.Reflection and System.CodeDom.Compiler to compile, create assemblies, add resources, instantiate objects, execute methods, get or set property values, and handle errors. If the code which is subject to compilation needs to reference assemblies in the GAC or in files, the DACBuilder is able to do it, using the three buttons in the right corner of the BForm window. The first button provides a popup menu with the GAC assemblies. The application doesn't use the GAC API to retrieve the assemblies installed in the GAC; this could be a good subject for another article.

The second button loads an assembly located on disk, while the third button removes it from the list of referenced assemblies.

Resources generation

The DACBuilder application is able to generate resources in the managed ResX format. Resources generation is done as VS does, using a native data grid which has an XmlDataDocument as a data source and a rich edit text box. Here is the code example:

doc = new XmlDataDocument();
doc.DataSet.ReadXmlSchema(templatesPath + "template.resx");
doc.Load(fileName);
            
doc.DataSet.Tables[0].RowChanged += new 
  DataRowChangeEventHandler(frmResources_RowChanged);
doc.DataSet.Tables[0].RowDeleted += new 
  DataRowChangeEventHandler(frmResources_RowDeleted);
dgResources.DataSource = doc.DataSet.Tables[0].DefaultView;

XML Schema generation

The XML schema generation is performed in a similar way, using a specific stylesheet (dataset_schema.xsl). The template contains specific XML schema elements, and the output is done by using the xml method of the xsl:output element:

<xsl:output method="xml" omit-xml-declaration="yes"/>

SQL scripts generation

The SQL stored procedures are generated in the same way, using XSL stylesheets with different parameters. The stored procedures which can be generated are for insert, update, and delete, for selecting a single record, and for search using default parameters. Other features include table creation, and insert statements for all rows in the table. This last command must be used carefully, because it is not recommended to access it for tables with large number of records. The SQL statements can be parsed and executed on the current server. You have to be also careful when you are executing statements, because most are DDL statements which can affect your database. An example of how to generate a SQL statement is for the search stored procedure. The procedure receives parameters for every column in the table. The parameters have default values, which are �1 for numeric values, and NULL for other data types:

<xsl:template match="column" mode="parameter_default">
<xsl:if test="position()>1">, 
</xsl:if> @<xsl:value-of select="@column_name"/>
[<xsl:value-of select="@data_type"/>]
<xsl:call-template name="data-length-precision-scale">
    <xsl:with-param name="data-type" select="@data_type"/>
    <xsl:with-param name="length" select="@character_maximum_length"/>
    <xsl:with-param name="precision" select="@numeric_precision"/>
    <xsl:with-param name="scale" select="@numeric_scale"/>
    </xsl:call-template> = <xsl:call-template 
          name="select_primitive_type_default_SQL">
       <xsl:with-param name="data_type" select="@data_type"/>
    </xsl:call-template>
</xsl:template>

The procedure builds a select SQL statement string with filters for parameters not having the default values. It means that if the user wants to have a filter on only one parameter, it gives a value only for that parameter. The template for a parameter filter is:

<xsl:template match="column" mode="column_filter">
IF @<xsl:value-of select="@column_name"/> 
   <xsl:call-template 
      name="select_primitive_type_default_comparison_SQL">
   <xsl:with-param name="data_type" select="@data_type"/>
   </xsl:call-template>
BEGIN
    IF @filter = N''
        SET @filter = N' <xsl:value-of select="@column_name"/>
            <xsl:call-template name="select_primitive_type_comparison_SQL">
            <xsl:with-param name="data_type" select="@data_type"/>
            <xsl:with-param name="object" 
                    select="concat('@', @column_name)"/>
        </xsl:call-template>
'
    ELSE
        SET @filter = N' AND <xsl:value-of select="@column_name"/>
            <xsl:call-template 
                    name="select_primitive_type_comparison_SQL">
            <xsl:with-param name="data_type" select="@data_type"/>
            <xsl:with-param name="object" 
                    select="concat('@', @column_name)"/>
        </xsl:call-template>
'
END
</xsl:template>

The procedure will build the SQL string, and append the filter if there is one (at least one parameter has a value different than its default value), and the string will serve as a parameter for the sp_executesql statement, with the search stored procedure parameters and values:

DECLARE @filter NVARCHAR(4000)
SET @filter = N''

<xsl:apply-templates select="/table/column" mode="column_filter"/>

IF @filter != N''
    SET @stmt = @stmt + N' 
WHERE 
' + @filter

PRINT @stmt

EXEC sp_executesql @stmt
    , N'
    <xsl:apply-templates 
          select="/table/column" mode="parameter"/>
'    
    , 
    <xsl:apply-templates select="/table/column" 
              mode="parameter_itself_value"/>

The output generated for table Items will be:

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
                 WHERE ROUTINE_NAME = 'Items_SEARCH')
BEGIN
    DROP PROCEDURE [Items_SEARCH]
END

GO

CREATE PROCEDURE [Items_SEARCH]
(
     @ID        [int] = -1,
 @Name        [varchar](255) = NULL,
 @Price        [decimal](18, 2) = -1,
 @CategoryID        [int] = -1
)
AS
DECLARE @stmt NVARCHAR(4000)
SET @stmt = N'
SELECT
     [ID] AS [ID]

, [Name] AS [Name]

, [Price] AS [Price]

, [CategoryID] AS [CategoryID]


FROM [Items] AS [Items]
'

DECLARE @filter NVARCHAR(4000)
SET @filter = N''


IF @ID != -1
BEGIN
    IF @filter = N''
        SET @filter = N' ID = @ID
'
    ELSE
        SET @filter = N' AND ID = @ID
'
END

IF @Name IS NOT NULL
BEGIN
    IF @filter = N''
        SET @filter = N' Name LIKE ''%'' + @Name + ''%''
'
    ELSE
        SET @filter = N' AND Name LIKE ''%'' + @Name + ''%''
'
END

IF @Price != -1
BEGIN
    IF @filter = N''
        SET @filter = N' Price = @Price
'
    ELSE
        SET @filter = N' AND Price = @Price
'
END

IF @CategoryID != -1
BEGIN
    IF @filter = N''
        SET @filter = N' CategoryID = @CategoryID
'
    ELSE
        SET @filter = N' AND CategoryID = @CategoryID
'
END


IF @filter != N''
    SET @stmt = @stmt + N' 
WHERE 
' + @filter

PRINT @stmt

EXEC sp_executesql @stmt
    , N'
     @ID        [int],
 @Name        [varchar](255),
 @Price        [decimal](18, 2),
 @CategoryID        [int]
'    
    , 
     @ID = @ID, @Name = @Name, @Price = @Price, @CategoryID = @CategoryID
GO

Other SQL features are "meta" scripts for the fnGetExtendedProperty function, GetTableColumnsXML procedure, GetRoutineParametersXML procedure, KeyTables table creation statement, and the GET_NEXT_ID procedure. The last two are useful as a mechanism for generating numeric values for primary keys, when you don't want to have an identity attribute.

UI generation

The UI features are useful to generate very simple interfaces for the DAC object. It provides both UI characteristics for visual objects such Windows Forms, web forms, or HTML pages, and class code with methods using the DAC object. There are stylesheets for each UI platform and .NET language (C# and VB.NET). The commands UI->Simple DAC Methods (Web and Windows) generates code which can be saved, compiled, and included in an assembly.

Custom fields implementation

The Custom Fields implementation is a technique destined to enhance the functionality for a base table and its DAC object. This implementation was started at Prodigy, and it was customized on the existing data access framework. It allows end users to add new fields to any table in the database. The implementation is available only for SQL Server. The "meta" objects you need to work with custom fields are available in the Custom Fields menu DB Entities command (for SQL statements) and DAC Entities (for C# or VB.NET). DAC Entities must be compiled and added as a reference to the generated Custom Fields DAC objects. The DB implementation is simple, the system manages tables that has custom field properties, the primary key for this table (the system requires that the table must have a primary key only on a single column), and custom properties. These objects define the structure of the custom fields. The table CustomFieldsProperty contains the following structure:

• PKProperty � property identifier (primary key).
• FKPropertyType � property type (foreign key referencing the CustomFieldType table).
• FKColumn � column identifier (foreign key referencing CustomFieldsTableColumn).
• DefaultValue � property default value.
• PropertyName � property name (it must not include any other character except letters, numbers, and '_').
• FKPropertyList � property list identifier (if the property supports multi-values).

The values for custom properties are stored in tables specific to the chosen type. The currently implemented ones are for String, DateTime, Bool, Decimal, and Integer. Any values table has the following structure:

• FKProperty � property identifier (foreign key referencing the CustomFieldsProperty table).
• FKParent � primary key value in the base table.
• PropertyValue � property value (the type is different, depending on the table � CustomFieldsValuesString, CustomFieldsValuesInteger, CustomFieldsValuesBool, CustomFieldsValuesDateTime, CustomFieldsValuesDecimal). The DAC objects reflecting the custom field DB entities are generated by the DACBuilder application and they don't differ by any other DAC object.

The custom fields management can be done by a non-programmer user, by adding, modifying, and deleting properties in the frmCustomFieldsManagement form:

For the table Items, we have defined four custom fields in this manner:

• MeasureUnit � String;
• StockLimit � Decimal;
• IsInStock � Bool;
• ExpireDate � DateTime.

Using the Generate View command, a view with all the base columns and/or custom fields is generated. The view name is CustomFields_<table_name>. This view can be used in reports, and will help users to perform any operations allowed on custom fields (filters, aggregates etc.). The view is generated using the GetTableCustomFieldsXML stored procedure, and consists of LEFT JOIN operations between the base table and a subquery specific to every custom property which is defined. To make a difference between two properties of the same type, the subquery receives an alias with the name CustomFieldsValues<type>_<PKProperty>. This is the join for the MeasureList custom field (PKProperty = 5, type - String):

LEFT JOIN (
SELECT 
    CustomFieldsValuesString.FKParent AS ID
    , CustomFieldsValuesString.PropertyValue AS [MeasureUnit]
FROM CustomFieldsValuesString
WHERE CustomFieldsValuesString.FKProperty = 5
) AS CustomFieldsValuesString_5 ON Items. = CustomFieldsValuesString_5.ID

The view was created using the DACBuilder application. Several SQL select statements are performed on this view, and the result output is shown below:

Using the Generate Read SP command, a select stored procedure is generated. It is very similar to the view, except that it receives the PKObjectID@ parameter for filtering just a single record. For the Items table, the statement is very similar to the previous discussed view, and the WHERE clause looks like:

WHERE Items.ID = @PKObjectID

The Generate Class command provides the most interesting Custom Fields feature. It generates the custom fields DAC class for the selected table. Because this class is generated at run-time and have to be handled by a binary object, the properties and methods have to be homogenized. The class will have members and properties for every custom field defined. The properties type is CustomFieldsProperty, a class which reflects custom field properties in code (PropertyID, PropertyName, PropertyTypeID, ParentID, PropertyValue). The method Initialize() sets "meta" values for custom fields (PropertyID, PropertyName, PropertyTypeID). The ParentID property contains an integer value which specifies the key for the record in the main table (Items). The custom fields DAC object has methods for inserting, updating, and deleting values. The method called Save performs insertion if there is no value existent, and updates if the corresponding value exists. Using Reflection, the binary DAC object will load the custom field DAC object at run-time. The custom field DAC object can be put in an assembly file, and the main DAC object which will consume it must be prepared for this.

In the DAC object generation, if you set the use_CustomFields parameter to 1 in the parameters window, the DAC object will know that it has custom fields. The DAC object will contain additional custom fields implementation:

#region Custom Fields

private bool xHasCustomFields = false;

public bool HasCustomFields
{
    get
{
        return xHasCustomFields;
    }
}        
        
private string execAsmbDir;
private string customFieldsAssemblyLocation;
private Assembly customFieldsAssembly;
private string customFieldsObjectName;
private object xCustomFields = null;

public object CustomFields
{
    get
    {
    return xCustomFields;
    }
    set
    {
        xCustomFields = value;
    }
}

private void LoadCustomFields()
{
    Assembly execAsmb = Assembly.GetExecutingAssembly();
    execAsmbDir = execAsmb.Location;
    execAsmbDir = 
       execAsmbDir.Substring(0, 
       execAsmbDir.LastIndexOf("\\") + 1);
    customFieldsObjectName = "DACCustomFields_Items";
    customFieldsAssemblyLocation = 
       execAsmbDir + customFieldsObjectName + ".dll";
    if(File.Exists(customFieldsAssemblyLocation))
    {
        xHasCustomFields = true;
        customFieldsAssembly = 
          Assembly.LoadFile(customFieldsAssemblyLocation);
        xCustomFields = 
          DotNetScriptEngine.CreateInstance(customFieldsAssembly, 
          customFieldsObjectName, null);
    }
}

#endregion

The assembly name must be in the class name form: DACCustomFields_<table_name>, and it have to be located in the DAC object assembly directory. The LoadCustomFields() method will be called in the DAC object constructor. Both methods Add and Edit will contain custom fields saving:

if(xHasCustomFields)
{
    xCustomFields = 
      DotNetScriptEngine.SetProperty(xCustomFields, 
      "Connection", cnn);
    xCustomFields = 
      DotNetScriptEngine.SetProperty(xCustomFields, 
      "Command", cmd);
    xCustomFields = 
      DotNetScriptEngine.SetProperty(xCustomFields, 
      "ParentID", this.xID);
    DotNetScriptEngine.ExecMethod(xCustomFields, 
      "Save", null);
}

The DotNetScriptEngine class (in the namespace DotNetScripting) will set the properties, and will call the Save method for the xCustomFields object. For deletion, the method called will be Delete, and for reading, Read. To read custom field values, you can use the GetProperty static method of the DotNetScriptEngine class:

CustomFieldsProperty oMeasureUnit = 
  (CustomFieldsProperty) 
  DotNetScriptEngine.GetProperty(objDAC.CustomFields, 
  "MeasureUnit");
MessageBox.Show(oMeasureUnit.PropertyValue.ToString());

Notice that the main DAC object doesn�t refer in any way the custom field properties, but only an object, called CustomFields, because it doesn�t know what kind of custom fields it has. Using Reflection, you can get custom fields properties of type CustomFieldsProperty for the CustomFields property (of type object) of the main DAC object.

Other features

The last useful feature is changing the connection for any of the DB systems at run-time, and connection strings generation for a large range of DB systems (more information on www.connectionstrings.com).

Thanks

Thanks Andreea Govorosciuc for the useful help on the Custom Fields management form, connection changing, and connection strings generation.

Using the application

When you build the project for the first time, be sure you have activated the post-build event, which creates the templates folder in the application folder and copies all the template files. Then you can deactivate the post-build event (an easy way is to write the REM instruction before all the post-build event commands).

When you start the application, you have to set the connection information (Tools->New connection...). If it is a SQL Server connection, you have to run some "meta" routines to be able to work with the DACBuilder application (fnGetExtendedProperty, GetTableColumnsXML, GetRoutineParametersXML).

The code generation benefits of the editing column information and the customized template parameters. The column information editing must be done carefully, because it affects the generated code. It is recommended to edit information regarding friendly names, namespaces, and abbreviation.

On .NET languages code generation, I recommend the usage of the DataAccessComponent and DataAccessContainer classes, because they provide additional features for reading data and executing operations.

The SQL code execution on the server must be done with caution, because they are DDL statements. Before you execute a drop / create table statement, you must backup your database.

When you want to compile .NET code, be sure you have referenced appropriate assemblies, located either in GAC, or on disk. You can find useful libraries in the Templates\Libraries folder; even they can be generated by the application (as "meta" libraries). The assemblies are DataAccessComponent.dll, DataAccessContainer.dll (for SQL, OLEDB, ODBC), DACCreator.dll, CustomFields.dll, DotNetScripting.dll, RJS.Web.WebControl.PopCalendar.dll (an ASP.NET calendar control, available here), and DataGridCustomColumns.dll (native DataGrid combobox columns implementation, available here).

History

• 2005-03-01 - First DAC implementation (insert, update, select, delete stored procedures) for SQL Server and .NET languages.
• 2005-05-01 - Resources generation implementation.
• 2005-10-20 - Other stored procedures generation.
• 2005-10-28 - XML Schema generation.
• 2005-11-10 - Simple DAC for OLEDB and ODBC generation.
• 2005-12-15 - UI generation.
• 2006-01-26 - Other languages implementation.
• 2006-02-14 - SQL routine handling.
• 2006-02-20 - DataAccessComponent implementation.
• 2006-03-03 - Custom fields implementation.
• 2006-03-15 - Connection strings creation.