Dales Workbench

Click here to advertise your product to over 25,000 VB programmers

May 1997

Return to Article Index
Dale's Workbench No:3
by Dale Shaw

This article assumes you're familiar with:

Visual Basic

Hi

When I set out to write a column on building VB objects I decided to cover some background theory before getting stuck into the object side of the business. This background stuff is discussed in the March and April issues of VB-Online and cover things such as variable scoping, procedure scoping and creating form properties and methods.

This month we are going to take a look at creating VB = objects using Class Libraries. Creating and using your own objects is straight forward but knowing when to use objects Vs standard procedures is far more difficult.

So we are going to start with a problem and look at where objects may be of benefit.

The Problem

I hate writing SQL queries inside VB. If you have ever written VB code that does SQL queries (using a couple of tables, a 'where' clause or two, an 'order by' etc) then you know what I mean and can probably skip this bit. If you don't know SQL then you may not easily identify with the problem but let me demonstrate.

A full intro to SQL is a bit complex for here but what I want to do is retrieve a subset of data from two separate database tables. What my code here is doing is building a SQL statement as a string and then passing that string to VB to open the result set.
Public Sub =
debtors].[terms], [debtors].[balance] from customer inner join debtors 
on 
[customer].[customerID]= [debtors].[customerID] where 
[customer].[company] like 'Smith*' order by City
and send it to VB (or DAO, to be more precise) to = execute. This example is pretty simple! Building (and de-bugging) something more complex is a nightmare! Messy, huh?

OK, let's look at how we can make things simpler.

There are several strategies we could use to ease the SQL headaches. First option would be to bury as much of the SQL stuff inside functions we would call from our main code. (This is the strategy I used with the above code). This keeps the rubbish out of our main code and keeps that readable ... but doesn't help much if we are often doing different types of SQL requests as we would be creating many SQL routines.

A second strategy might be to create a procedure that does standard SQL commands. That way we could just call this routine with some parameters and it could return the SQL code required. For example:
SQL  
CreateSQL("Customer", "Debtors",  
"Customer.CustomerID= Debtors.CustomerID",  
"Customer.Company", "Like", "Smith*",  
"City")
… but this is not a lot more readable. It also lacks a lot of flexibility as SQL is a very complex language and trying to define a set of parameters that would be flexible enough would be near to impossible.

A third strategy might be to use a series of Public variables in conjunction with a SQL creation procedure. The code might look like:

Option Explicit
Public SQLFields As New Collection
Public SQLTables As New Collection
Public SQLJoinType As New Collection
Public SQLJoinSpec As New Collection
Public SQLWhereFields As New Collection
Public SQLWhereOperator As New Collection
Public SQLWhereValue As New Collection
Public SQLOrderFields As New Collection

Private Sub cmdDisplay_Click()

' Build SQL criteria and then call MakeSQL to create the
' final string.

' First clear any existing stuff
ClearSQL

' Now build the criteria - tables and joins
SQLTables.Add "Customer"
SQLTables.Add "Debtors"
SQLJoinType.Add "I" ' Inner Join!
SQLJoinSpec.Add "customer.customerID= debtors.customerID"

' Now the fields
SQLFields.Add "customer.company"
SQLFields.Add "customer.customerID"
SQLFields.Add "debtors.terms"
SQLFields.Add "debtors.balance"

' Which Customer?
SQLWhereFields.Add "customer.company"
SQLWhereOperator.Add "L" ' Like
SQLWhereValue.Add "Smith"

' What Sort Order?
SQLOrderFields.Add "City"

' OK.... Build the SQL
Dim SQL As String
SQL = MakeSQL()

' and then we would send it to DAO and display the results or whatever.
End Sub

Now we are starting to approach a solution that is very flexible AND very readable. The use of Collections allows us to have any number of fields, tables, sort orders and selection criteria. The MakeSQL command has a relatively simple job of building the SQL command based around a number of loops. I haven't given the code for MakeSQL 'cause it's very similar to the code we shall soon see.

Many would argue that the above code would be a stack slower than building a SQL string as in the original example. They would be right!

My angle with the above code is to emphasise how = simple the 'client' code becomes. Want another field in the result set? Add just one simple line. Need to add an extra table into the query. A few simple line of code will do it.

Best of all, from my point of view, is that the code is easy to read and understand. Easy to maintain and modify. Very BUG-PROOF! Sure we could suffer a bit of performance loss but this is little compared to our overall app and is a good trade-off for the maintainability benefits we have gained.

What's wrong with this code? My only negative would be the use of Public variables exposes you to bugs caused by different sections of you code modifying the same variables. One routine may initialize the variables ready to call MakeSQL and another piece of code may clear of alter the field or table requirements.

Moving to Objects

Let's take the code from above and embed it inside a VB Class module. As you will recall from the previous two columns, any Functions or Subs become Methods of our new object class and any Public variables become Properties. I could have implemented the above code directly as a Class but lets add a few more procedures to make things simpler:
Private Sub cmdDemo_Click()
	' Create new  
instance of our class
	Dim SQLbld as New SQLBuild
	With SQLbld
		' Get ride of 
anything existing
		.Clear

		' Add the table s 
and joins
		.AddTable  
"customer",customer.customerID", "debtors", =
debtors.customerid"
		' Now the fields
		.AddField  
"customers.company"
		.AddField "customers.customerid"
		.AddField "debtors.terms"
		.AddField "debtors.balance"
		' A WHERE clause or two
		.AddWhere  
"company", "L", =
"Smith"
		' And a sort ORDER
		.AddOrder  
"City"
	End With

	' OK, we have the  
SQL, lets do something
	Dim SQL as string
	SQL= SQLbld.Statement
End Sub
The result? The simple, readable code from above with = the added benefit of letting our code define multiple instance of this object (and, most importantly, it's internal variables) so that we can be building multiple SQL statements simultaneously without them interfering with each other. We can steadly increase the functionality of the core object without affecting the existing 'client' code (as long as we are carefull!).

I hope that this little problem has helped to show you where objects can be used within your VB code. Many people would argue the use of objects allows you to build more re-usable code but well written procedures (combined with some Public variables) have always provided that. Well written class libraries can certainly be of use and I see the biggest advantage over standard procedures when:

You have a large number of parameters to set, particularly when many are optional

You may want to use the same code in several different ways simultaneously. You may want differnt instances with different properties.

The SQL Builder code discussed here is available as a downloadable ZIP that includes a form to help demonstrate how it works. I won't discuss the details here (view the code instead and shoot me an email if you have questions or suggestions).

Next month I will present a few more problems and discuss where objects would (or would not) suit.

Dale

About the Author

Dale Shaw is a consultant, developer and trainer for LAN Technologies in Auckland, New Zealand, http://www.lantech.co.nz/