Preferred db connection

mega · June 18th, 2004, 12:32 PM

Hello world.

Iâm writing a simple class for creating a form drop down menu and I need to provide some sort of connection to a database. The way I see it there is three ways of doing that.

1. Add database function in the class, witch creates it own ADO objects
2. Provide a connection object to a function in the class
3. Write another class for db connections and call it from within my original class

There is pro and cons in all approaches, I will try to explain each point.

1. Strait forward, just write the connection in my class and almost no error handling needed. But I want to reuse my class without having to alter the database path or connection type (jet, oracle, ms SQL, excel etc.) every time I change project.
2. Write the connection in an include file or in my calling code and assign the connection object as a parameter to my class, just like the ActiveConnection property in a Command object. This is my preferred approach, but it will need extra amount of code every time I need to use the class compared to the other two solutions.
3. Heavy duty development in turns that I need to write a hole new class with error handling, different connection types and I/O controls, all changeable from outside the class and some guessing logic so that I donât have to provide all properties if I donât got them or is feeling lazy (or is close to a deadline). Probably need to do a lot of considering in performance too. But a really neat feature, I can use it with little amount of code and always know that it does the job perfect (with rollbacks etc.). Could also be used with almost all the classes I can think of.

Now, Iâll like to hear your thoughts before I begin. Have any of you done something liked this before? What are the pitfalls and what do you recommend?

Any replies are appreciated.

- mega

Imar · June 19th, 2004, 04:10 AM

Are you coding this for a classic ASP scenario? (I think so, but I wanted to check anyway)

Instead of commenting on these 3 solutions, can I suggest a fourth possibility?

Instead of assigning a ConnectionObject to the class, you could assign it a DataSource property (of type Recordset) instead.
With a DataSource, you make the class Data aware instead of Database aware. The way I see it, you have the best of both worlds. Your class gets the data it needs from a known datasource (a recordset), without knowing how it should retrieve that data. Internally, you can simply check the EOF property of the recordset to see if it contains data and then star outputting <option> elements.

This way your calling code can create the recordset anyway it wants; it could construct a recordset manually and then load it with data from an XML file, it could use the Connection's Execute method to retrieve a firehose recordset, or it could create connection and command objects to get the recordset.

Once you expose the DataSource property, you could also expose two other properties: DataValueMember and DataDisplayMember. These string properties determine the columns in the recordset that are used as the value and descriptions in your <option> elements.
Once you have these three properties, you're pretty close to rebuilding the ASP.NET drop-down. ;)

You could also create a DataBind (or another name) method that is responsible for outputting the actual <select> list.

Does this help?

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: A.D.I.D.A.S. by KoRn (Track 11 from the album: Life Is Peachy) What's This?

mega · June 29th, 2004, 06:47 PM

Hi Imar.

I can see your point but it doesn't offer the kind of simplicity that I want in usage. I want to be able to write something like this:

Code:

<%
  Dim lstBookTitle
  Set lstBookTitle= New cLST
  lstBookTitle.Fields = "lngBookID, strTitle"
  lstBookTitle.Table = "Book"
  lstBookTitle.Source = '?
  Call lstBookTitle.MakeDropDownMenu
%>

If I were to use your solution I've would need to do something like this:

Code:

<%
  Dim objConn, objRS, lstBogTitler
  Set objConn = Server.CreateObject("ADODB.Connection")
  Dim ConnString
  ConnString = "Provider=Microsoft.Jet.OLEDB.4.0; "
  ConnString = ConnString & "Data Source = C:\Datastores\aDataBase.mdb"
  objConn.ConnectionString = ConnString
  Set objRS = objConn.Execute("SELECT dataValue, dataName FROM data")
  Set lstBookTitle = New cLST
  lstBookTitle.DataSource  = objRS
  Call lstBogTitler.MakeDropDownMenu
  Close.objRS
  Set objRS = Nothing
  Close.objConn
  Set objConn = Nothing
%>

Let us say that I need 2 or more drop down menus, the code would suddenly grow out of proportions.
By the way youâre right this is classic ASP 3.0.
This is just a quick answer. I'll look into your suggestion as soon as I got time. Cheers!

- mega

Imar · July 2nd, 2004, 08:39 AM

Hi mega,

If you want, you can further extract the data access code from your presentation layer / ASP pages. Where you move it to depends a bit on your setup, the time you have available, and maybe even the scale of the project.

One of the easiest ways to accomplish this is to create generic functions in an ASP include file. These helper functions can perform the data access methods for you. Let's take your previous example and see how that ends up. I take a Categories list as an example. The number of items in this list depends on the access rights a user has (just making something up here)

Code:

[Function.asp]
Public Function GetDropdownCode(ByVal Table, ByVal IDCol, ByVal DescriptionCol)
  Dim SQL
  SQL = "SELECT " & IDCol & ", " & DescriptionCol & " FROM " & _ &
     Table & " ORDER BY " & DescriptionCol
  ' Code for data access here.
  ' Use common connectionstring to create a connection and 
  ' get a recordset
  Set GetDropdownCode = MyRecordset
End Function

This allows you to retrieve the drop down like this:

Code:

  Set lstBookTitle = New cLST
  lstBookTitle.DataSource  = GetDropdownCode "Category", "ID", "Description"
  Call lstBogTitle.MakeDropDownMenu

This makes it much easier to create multiple drop downs, without repeating the code.
You could create "overloaded" versions of these methods, that support direct SQL statements, for example.

Another alternative is to create COM+ objects that do this for you. In a more complicated scenario, with more variables like user rights, permissions etc, you could create a business object that performs a number of validations, data changing, whatever, and then calls a separate data access layer. This layer, another COM+ object, creates a bunch of ADO objects, gets the data and returns a recordset to the business layer which in turn returns it to the presentation layer. You would end up with something like this:

Code:

Dim myBusinessObj
Set myBusinessObj = Server.CreateObject("MyProject.MyBusiness")
myBusinessObj.User = Session("UserName")
myBusinessObj.Permissions = Session("Permissions")
Set lstBookTitle = New cLST
lstBookTitle.DataSource = GetCategories()
Call lstBogTitle.MakeDropDownMenu

Inside the GetCategories method of the MyBusiness class, some logic takes place that takes the user name / id and permissions into account. Then it constructs a SQL query and sends it off to the database layer, with a method like GetFirehoseRecordset(), for example.

Using COM+ object construction and configuration you can configure your data access layer to run under a specific account which solves a part of the security puzzle in your project. You can also configure the component to accept a connection string. This allows you to configure the connection string in one central location (the COM+ management console) so your data access object can use it. This way, you don't have to store the connection string in an include file and pass it along with every method call.

The good thing about the data access layer is that you can easily reuse it in different projects. You start off with a few methods and expand as your project grows. Older projects will continue to work, while newer project can benefit from the new functionality.

These are just some ideas. I could ramble on for hours about this topic, but unfortunately, I do not have that much time available right now.
If you have specific questions about any of the topics I mentioned, or want to discuss these ideas further, feel free to post here.

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Body Movin' by Beastie Boys (Track 6 from the album: Hello Nasty) What's This?