dbObject - Database code generation using attributes

• Download source code - 29.8 Kb

Introduction

This is a sample framework that I wrote. It works basically like a poor man's OR tool, generating VB.NET code from a database table, and using attributes to control updates and inserts.

When I design a system, I often like to start with the database design. Using a data model helps me imagine the objects that the system will be comprised of. During the "Object Mining" process, the easiest bit of information to glean about the new system is the data that will be pushed around.

Using Microsoft's Recordset is a great way to abstract a table into an object. The problem that I encountered was that much of the beautiful sheet metal fell off when I attempted to use Recordsets with MySQL. I began to write my own access layer, but found myself re-writing the same basic code over and over. Clearly, a code generator was needed.

So with this approach, I generate code from the table layout, with each field becoming a property. By examining the schema of the database table, I can apply attributes to the generated VB.NET code, and construct helper functions to select and update the underlying data.

Background

There are many fine articles on attribute based programming, several of them right here on The Code Project. MySQL information can be found here.

Using the code

The code presented here is an extract of the code that I use in several projects. In addition, I have written a Visual Studio .NET add-in that generates code and adds the code to the project that I am working on.

To use this code, you will need to have MySQL installed, configured, and running on your machine. You should also have the ODBC connector for MySQL installed. To cover those aspects is beyond the scope of this article.

You will need to create a database, named "dbObject". You should create an ODBC data source that points to the "dbObject" table, and test the connectivity.

Create a test table in the "dbObject" database.

DROP TABLE IF EXISTS `dbobject`.`protocols`;
CREATE TABLE  `dbobject`.`protocols` (
  `ProtocolType` varchar(20) NOT NULL default '',
  `Enabled` varchar(1) NOT NULL default '',
  `RootLocation` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`ProtocolType`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 
  COMMENT='Sample table to hold communication protocol descriptions.';

Running the program presents a simple Windows form that allows the selection of a database and a table.

1. Press the Connect button to connect to the MySQL database using the connection string displayed in the textbox.
2. Select a database, and then select a table.
3. Click the "Select" button to generate the VB.NET code from the schema of the selected table.

The actual code to generate the VB.NET class is rather simple:

' Instance a code generator and set the various props
Dim oGeneratorSQL As New cGenerate_SQL

oGeneratorSQL.ConnectionString = oSelectTable.ConnectionString
oGeneratorSQL.DatabaseName = oSelectTable.DatabaseName
oGeneratorSQL.TableName = oSelectTable.TableName
oGeneratorSQL.UseDBFramework = True
oGeneratorSQL.InsertFrameworkIfMissing = False

' Instance a form to display the generated code
Dim oCodeViewer As New frmViewCode

' Generate the code and show the form
oCodeViewer.RichTextBox1.Text = oGeneratorSQL.GenerateInsertCode()
oCodeViewer.ShowDialog()

The sample program opens a form with a rich text box control. The generated VB.NET code is set as the Text property of the rich text box. In my Visual Studio add-in, I use the Environment DTE to insert the generated code at the current cursor position.

Points of Interest

Because I intended to use code generation for various functions of my Visual Studio add-in, I designed the generation of objects to share a common interface, so that I could extend the functionality later.

One of the classes, the cGenerateProperty object, I use quite often in another Visual Studio add-in that generates a member variable and the property accessor methods. It's much easier to right-mouse click "Insert Property" and type "CustomerName" and let the add-in generate all the required code.

The dbObject_Framework

Most of the action occurs in the dbObject_Framework. When the code generator creates an object, it inherits it from the cBase_DBObj class in the framework.

Public Class cJobs Inherits cBase_DBObj
End Class

The dbObject_Framework handles most of the heavy lifting. I use reflection to get the various bits of information about a given class, then generate the SQL commands to insert, update, etc. based on the information.

' Instantiate a new object of the passed in ClassName
Dim vbObj As Object = oThis.CreateInstance(sClassName, False, _
          BindingFlags.Default, Nothing, Nothing, Nothing, Nothing)

' Get the Type object for this class,
' and from the type, get the properties
Dim vbClassType As Type = oThis.GetType(sClassName)
Dim oProps() As Reflection.PropertyInfo = Me.GetType.GetProperties()

For Each oProp As Reflection.PropertyInfo In oProps
    Dim sPropName As String = oProp.Name
    Dim sIsSerializable As String = _
        oDriver.GetPropertyAttribute(oThis, _
        sClassNameShort, sPropName, _
        "IsSerializable").ToUpper
    ' Only attempt the read the property
    ' from the database if the property 
    ' is marked "Serializable" via an attribute
    If sIsSerializable = "TRUE" Then
        ' Get the type code of the data from the database
        Dim oDBType As TypeCode = _
          Type.GetTypeCode(oRS(sPropName).GetType)
        ' Get the type code of the property on the object
        Dim oPropType As TypeCode = _
          Type.GetTypeCode(oProp.PropertyType)
        
        ' Convert the data
        If oDBType <> oPropType Then
            ' Convert the database data type
            ' into the object's property data type
            ' Note: In a future version that generates
            ' custom property types from the database, 
            ' this marshalling step will not have
            ' to happen, since the types should agree...
            Select Case Type.GetTypeCode(oProp.PropertyType)
                Case TypeCode.String
                    oProp.SetValue(vbObj, _
                       oRS(sPropName).ToString, Nothing)
                    Case TypeCode.Boolean
                    Dim iTemp As Integer = oRS(sPropName)
                    If iTemp = 0 Then
                        oProp.SetValue(vbObj, False, Nothing)
                    Else
                        oProp.SetValue(vbObj, True, Nothing)
                    End If
                Case Else
                    ' Treat it like a string 
                    oProp.SetValue(vbObj, oRS(sPropName), Nothing)
            End Select
        Else ' They match, so straight convert 
            ' Set the property values from the database 
            oProp.SetValue(vbObj, oRS(sPropName), Nothing)
        End If
    End If
Next

Using the classes generated with this tool is simply a matter of creating an object and calling the GetSelectedRecords function. The GetSelectedRecords function is inherited from the dbObject_Framework. It returns an ArrayList of objects that match the criteria.

ArrayList = Object.GetSelectedRecords( criteria, connectionString)

In the example code below, we instantiate a new cMerchants object, and call its GetSelectedRecords() method. You can pass an optional filter statement in the form of a a SQL WHERE clause.

The GetSelectedRecords() function examines the class structure, generates the proper SQL query, runs the query against the database, and returns the results as an ArrayList of cMerchants objects.

Dim oDown As New cMerchants
Dim sConn As String = _
    ConfigurationSettings.AppSettings("DBConnKey")

Dim sSQL As String = "WHERE MerchantID = '" _
                     & sMerchantID & "'"

Dim oArrayMerchants As New ArrayList
oArrayMerchants = oDown.GetSelectedRecords(sSQL, sConn)

If oArrayMerchants.Count > 0 Then
    ' Do Something
End If

History

• 01-26-2006 - Original submission to The Code Project.