Computer Genius Blog :: aka “TheGarage”

Some interesting stuff here, a cornucopia actually, including a circular periodic chart.

Share This

6:00 AM, The Garage — I don’t have much time to write this morning but I have been busy in the tech world so It’s time to start a new series. I am still working on sortin gout the VB6 code base for the guys I am currently working for. Remember, they are the ones with over a hundred MS Access databases interfacing their SQL data. I just about have them sold on developing a library of reusable business objects so I am going to bring everyone up to date on the progress and do a few follow-up articles on the process as it continues.

I support the majority of the Access applications (along with the people who use them) as well as several IIS web applications built using Microsoft’s Web Classes architecture (along with the people who use them) and the Ceridian HR/Payroll application and SQL database (along with the people who use them.) The guy who created the mess wrote all VB code in all these applications made some attempts to encapsulate the common objects but really didn’t understand the basic concept of Object Oriented Design. For example, he built a class for database connectivity but all of the connection parameters were hard coded in modules running the main application code, which of course defeats the whole purpose of reusable, low-maintenance code.

12:00 PM, on-site, Tx City — One of the main purposes of OOD and OOP is encapsulation, meaning all the components of an object, both methods and properties are encapsulated withing the object. As a programmer, if I want a new database connection and I have my database connectivity defined in a reusable object, dbConn for example, I want to be able to request a database connection and not have to worry about how the connection is made.

So if I need to write a little utility that lists all employees and their job codes I want to be able to get a database connection by simply requesting it. I don’t want to have to set connection strings and server names and passwords and such. In a large enterprise there is likely not only many databases, but many database (SQL/Oracle/Domino/etc) servers as well. Changes made to the back-end infrastructure will break any code that has the connection parameters hard wired.

For example, here is how the apps are all written now using ODBC DSN connections:

Set dbADC = New DBConn
dbADC.DBToUse = “DSN=TimeAtt”
Set dbBuildSec = New DBConn
dbBuildSec.DBToUse = “DSN=BuildSec”

If a database is moved or a server crashes all the DSN configurations have to be changed on every computer running the application, which is not as bad as this scenario:

conn = New OleDb.OleDbConnection(”PROVIDER=SQLOLEDB;” _
& “server=Server_002;” _
& “Initial Catalog=BuildSec;” _
& “User Id=userid;” _
& “Password=pwd;”)

in which case every instance of the code in every instance of every application that uses the code would have to be changed. In short, a nightmare — in which I have been living for about five months now.

So what if one chooses to do things my way? Well the concept of building a class to manage database connectivity is sound but heck what’s the use if robust code is not the end result. Where the previous coders failed is in not understanding encapsulation or how to build overloaded constructors (polymorphism) in their class so the class can handle disparate requests when a new object is instantiated from the class.

Set myTandA = new DBConnection(”TimeAtt”)
Set myBuildSec = new DBConnection(”BuildSec”)
Set myEmpRpts = new DBConnection(”HRRpts”, “Access”)

Then the constructors in my DBConnection class might look something like this:

Public Class DBConnection
Private mConnectionString As String
Private mConn As ADODB.Connection

Sub New()

    ‘This is the default constructor and will return an error

End Sub

Sub New(ByVal dbname As String)
        ‘Connection strings defined in Constant declarations

        Select Case UCase(dbname)

            Case “TimeATT”
                mConnectionString = TIME_&_ATTENDANCE_CONNECTION_STRING

            Case “VHR_DATASQL”
                mConnectionString = HR_CONNECTION_STRING

            Case “EARNHIST”
                mConnectionString = EARNHIST_CONNECTION_STRING

            Case “BUILDSEC”
                mConnectionString = BUILDSEC_CONNECTION_STRING

         End Select

         mConn = New ADODB.Connection
         mConn.ConnectionString = mConnectionString
         mConn.Open()
End Sub

Sub New(ByVal dbname As String, ByVal dbtype As String)

    ‘this contructor will be used to open non-sql data sources
    ‘valid types will be Access, ODBC, etc)

End Sub

Pretty sweet huh. Now if something happens in the server room or even if database vendors are changed, the code only has to be updated in the class definition to reflect the changes and the programs utilizing the class go on like nothing ever happened.

To be continued…

Share This