Generate Business Object Classes

I often include a module called Utilities.bas in my projects. It contains routines that are not directly used by the application, but instead are invoked in the Debug window, usually to generate code snippets for the project.

For example, I have a routine that generates the skeleton of a class whose properties are mapped to the fields of a table, view, or stored procedure-as in the Business Object class described in Deborah Kurata’s book Doing Objects in Microsoft Visual Basic 5.0 (pp. 476-479, Que, 1999, ISBN: 1562765779). Writing the code for this class is tedious, especially when the table has many fields. Imagine writing all the Get and Let property subs for a table with 20 or 30 fields:

 Sub GenTableClass(strTableName As String)	Dim ccon As CConnection	Dim sql As String	Dim rs As Recordset	Dim fld As Field	Dim strName As String	Dim strType As String	'connect	'CConnection is your generic Connection (or 	'Database)	'class that connects to your database	'when initialized. In my case, this class has 	'a property called Conn that returns a 	'Connection object. 	'	Set ccon = New CConnection	sql = "select * from " & _		strTableName & " where 1=2" 		'no need to return a row	Set rs = ccon.Conn.OpenRecordset(sql, _		dbOpenSnapshot)	Debug.Print	'generate private variables	Debug.Print _		"'Private Data members for table: " & _		strTableName	For Each fld In rs.Fields		strName = fld.Name		strType = VBType(fld.Type)		'prefix is your choice		Debug.Print "Private m_" & strName & _			" as " & strType	Next fld	Debug.Print	'Now, generate let and get property subs	For Each fld In rs.Fields		strName = fld.Name		strType = VBType(fld.Type)		'property get statement		Debug.Print "Public Property Get " & _			strName & "() as " & strType		Debug.Print vbTab & strName & " = " & _			"m_" & strName		Debug.Print "End Property"		Debug.Print		'property set statement		Debug.Print "Public Property Let " & _			strName & "(in" & strName & " As " & _			strType & ")"		Debug.Print vbTab & "m_" & strName & _			" = " & "in" & strName		Debug.Print "End Property"		Debug.Print	Next fld	rs.Close	Set rs = NothingEnd SubPrivate Function VBType(strFldType As String) As _	String	'You may have your own conversion	Select Case strFldType		Case dbChar, dbMemo, dbText			VBType = "String"		Case dbByte, dbInteger			VBType = "Integer"		Case dbCurrency			VBType = "Currency"		Case dbLong			VBType = "Long"		Case dbDate, dbTimeStamp, dbTime			VBType = "Variant"		Case dbFloat, dbDouble, dbNumeric			VBType = "Double"		Case dbSingle			VBType = "Single"		Case dbBoolean			VBType = "Boolean"		Case Else			VBType = "Variant"	End SelectEnd Function

If I want to write a class for the Person table, for example, I go to the Debug window and type:

 GenTableClass "Person"

I then cut and paste the generated code to my target class module.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: