Wrox Programmer Forums
|
Classic ASP Professional For advanced coder questions in ASP 3. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Professional section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 18th, 2004, 12:32 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 217
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to mega
Default Preferred db connection

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
__________________
- mega
Aspiring JavaScript Ninja
 
Old June 19th, 2004, 04:10 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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?
 
Old June 29th, 2004, 06:47 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 217
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to mega
Default

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
 
Old July 2nd, 2004, 08:39 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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?





Similar Threads
Thread Thread Starter Forum Replies Last Post
db connection dfeuerborn VB Databases Basics 1 August 19th, 2005 02:08 PM
db connection dfeuerborn Excel VBA 1 August 19th, 2005 02:24 AM
db connection dfeuerborn Access VBA 1 August 17th, 2005 10:33 AM
DB Connection stu9820 ASP.NET 1.0 and 1.1 Basics 1 September 26th, 2004 09:01 PM
Which is preferred, ODBC or OLEDB? uit SQL Server ASP 2 December 3rd, 2003 05:41 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.