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.
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.
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.
- Press the Connect button to connect to the MySQL database using the connection string displayed in the textbox.
- Select a database, and then select a table.
- 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:
Dim oGeneratorSQL As New cGenerate_SQL
oGeneratorSQL.ConnectionString = oSelectTable.ConnectionString
oGeneratorSQL.DatabaseName = oSelectTable.DatabaseName
oGeneratorSQL.TableName = oSelectTable.TableName
oGeneratorSQL.UseDBFramework = True
oGeneratorSQL.InsertFrameworkIfMissing = False
Dim oCodeViewer As New frmViewCode
oCodeViewer.RichTextBox1.Text = oGeneratorSQL.GenerateInsertCode()
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.
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
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.
Dim vbObj As Object = oThis.CreateInstance(sClassName, False, _
BindingFlags.Default, Nothing, Nothing, Nothing, Nothing)
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 = _
sClassNameShort, sPropName, _
If sIsSerializable = "TRUE" Then
Dim oDBType As TypeCode = _
Dim oPropType As TypeCode = _
If oDBType <> oPropType Then
Select Case Type.GetTypeCode(oProp.PropertyType)
Dim iTemp As Integer = oRS(sPropName)
If iTemp = 0 Then
oProp.SetValue(vbObj, False, Nothing)
oProp.SetValue(vbObj, True, Nothing)
oProp.SetValue(vbObj, oRS(sPropName), Nothing)
Else oProp.SetValue(vbObj, oRS(sPropName), Nothing)
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
GetSelectedRecords() function examines the class structure, generates the proper SQL query, runs the query against the database, and returns the results as an
Dim oDown As New cMerchants
Dim sConn As String = _
Dim sSQL As String = "WHERE MerchantID = '" _
& sMerchantID & "'"
Dim oArrayMerchants As New ArrayList
oArrayMerchants = oDown.GetSelectedRecords(sSQL, sConn)
If oArrayMerchants.Count > 0 Then
- 01-26-2006 - Original submission to The Code Project.