Page #72 (Chapter 10 - Retrieving and Storing Data in Databases)

	Chapter 10 - Retrieving and Storing Data in Databases
	Visual Basic Developers Guide to ASP and IIS
	A. Russell Jones

Copyright 1999 SYBEX Inc.

---

	Accessing Databases from WebClasses

	There are some specific differences between accessing databases from standard client-server programs and accessing databases from Web applications.

	Warning		Don't use WebClasses to access databases directly; use data access components instead.

	OK, that's not a commandment, but you violate it at your peril. You should usually place your database access code in a separate component and run it as an MTS (or COM+) component. If you don't do that, you limit the scalability of your application because all your code must run on one computer. Later in this book, you will build an application that uses multiple components and MTS, but so far, I've violated that rule, purely because it's more expedient to do so in order to create understandable examples. In other words, do as I say, not as I do.

	Get In, Get the Data, Get Out

	Your goal, in the server portion of a Web application, is to service the client's request as efficiently as possible while using as few server resources as possible. You can translate that goal to "Get in, get the data, and get out." Keep that goal in mind as you access databases. This section lists a few rules that apply to all Web applications that use databases:

	•		Only retrieve the fields you need. Granted, writing an SQL statement such as "SELECT * …" is much easier than writing all the fields. Taking SQL shortcuts such as leaving out the table names for unique fields (for example, writing au_id rather than Authors.au_id) is also tempting. Each time you take a shortcut, though, somebody else's code needs to fill in the missing pieces.

	•		Minimize locks. Open connections and cursors in read-only mode whenever possible. The database does not have to apply table, page, or record locks to retrieve data in read-only mode. For single queries, you'll never see the difference. Under heavy use, you will.

	•		Use firehose cursors. Whenever possible, use a forward-only, read-only cursor. This type of cursor has the least overhead and is the fastest type of cursor, thus saving resources and time.

	•		Use connections lavishly. This is directly opposed to your natural inclination to open a connection early in a program, leave it open by caching it in a variable, then use it whenever you want to. When you do that, you avoid the overhead (and the extra code) of closing the connection, but it's the wrong approach in a Web environment. Instead, you want to open connections as late as possible and release them as early as possible. This works because IIS and MTS can pool connections. The pooled connection doesn't actually close when your application closes it; the connection is released to the pool instead, so some other thread can use it.

	•		Disconnect recordsets when possible. Since ADO version 2, you can disconnect recordsets from their connections. To disconnect a recordset, set its ActiveConnection property to Nothing. Doing so frees the connection for use by another thread or application. You may disconnect Recordsets only with static cursors and with their CursorLocation property set to adUseClient.

	•		Cache data in files when possible. As you saw earlier in this book, you can cache preformatted table data in files. When the data values are static or don't change often, retrieving a file is much more efficient than accessing a database.

	•		Don't cache ADO objects. You should never store any ADO object in Session or Application variables. This is one of the first things most new WebClass and ASP programmers want to do, and it has an immediate deleterious effect on their applications. Store the data as individual variables, strings, or arrays, or use the StoredRecordset and StoredDictionary classes to cache data.

	•		Use Command objects and stored procedures. Writing all your SQL as dynamic SQL is tempting because it's so easy to change, but it's highly inefficient. Create stored procedures instead. Not only will you get an immediate performance boost, but you also will help isolate your application from changes if the database changes. That means you won't have to recompile and reinstall your application just because someone changed a field name on the server. Use Command objects and pass parameters to the stored procedures to obtain the recordsets.

	•		Use output parameters. Although you haven't seen any examples in this book (because using output parameters is beyond its scope), when you want to retrieve individual values from the database, you can—and should—use output parameters rather than returning recordsets. An output parameter is essentially the same as a return value from a function. You pass a variable in to hold the value, SQL Server fills the variable with the return value, and you retrieve the value when the stored procedure call returns. Output parameters save resources, time, and code.

	•		Don't concatenate long strings. As you saw in Chapter 4, "Introduction to WebClasses," when formatting table data, building short strings and periodically adding them to a longer return string is much more efficient than continually appending data directly to the return string.

	Write Functions to Simplify ADO

	One of the problems with most data access models is that because they're powerful, they can also require a good deal of repetitive code. To alleviate this problem, you can write wrapper functions for ADO that eliminate most of the code. The key is to make the wrapper functions sufficiently flexible so that you can open connections and recordsets of various types by using the same syntax for all calls.

	To do this, you must first realize that there are really only two types of database operations: those that retrieve data, and those that change either data or database objects. Therefore, it should be possible to construct a small number of methods to access the data. In fact, you can wrap ADO in only three functions, with minimal loss of functionality. They are:

	OpenConnection  Opens connections

	GetRecordset  Retrieves recordsets and/or values of output parameters

	ExecuteSQL  Updates/inserts/deletes data, or alters database objects

	The OpenConnection function isn't absolutely necessary, but it does simplify development by trapping errors associated with opening connections. How many times have you seen another developer (not you, of course) write code like this:

	Set conn = New Connection

Conn.Open "myConnection", "sa", ""

	I suspect we've all seen (and written) code like this numerous times. The code is technically perfect—but it won't always work. At some point in the life of your application, the connection will be unable to open. Maybe the network's down, maybe the database server is down, maybe someone changed the database name or DSN reference—who knows? That's the point: You don't know, so you must anticipate and plan for the unexpected. Therefore, in the real world, the code you must write looks more like this:

	Function myFunction()

     On Error Goto connectionError

     Set conn = New Connection

     Conn.Open "myConnection", "sa", ""

     On Error goto otherError

     ' do something

myFunctionExit:

     Exit Function

ConnectionError:

     Select Case Err.Number

        ' handle errors

     End Select

     Resume myFunctionExit

OtherError:

     ' handle errors here

     ' Resume myFunctionExit

End Function

	I don't know about you, but I certainly don't want to handle errors in each and every individual function. Instead, the idea is to centralize error handling by isolating specific operations in functions that are both easy-to-use and have robust error-trapping.

	The functions do require parameters, but most of them are optional, and all of them have default values. For example, to retrieve a read-only, forward-only recordset, you would write code like this:

	Set conn = openConnection(myConnectionString)

If not getRecordset(R, conn, "myStoredProc", _

    adCmdStoredProc, ALLOW_EMPTY) then

    ' show error here

End If

'  display results

	One function is not only easier to remember than multiple methods for retrieving recordsets, but also easier to teach to others and to maintain.

	You can write similar code to execute database commands and stored procedures that don't return recordsets. The ExecuteSQL function accepts parameters in a Dictionary object. The function builds ADO Parameter objects using the Dictionary keys as the names of the parameters. The Dictionary values are the parameter values. It uses the variable subtype (for example, String, Currency, Long, etc.) of the Dictionary values to translate Visual Basic data types into their corresponding ADO parameter type value constants. I've included the code for these and other useful functions on the Sybex Web site.

	So far, you've had to include classes in your projects to use objects. In the next chapter, you'll see how to use compiled ActiveX DLLs to extend the functionality of your applications. Although I'm sure you've built and referenced DLLs in standard projects many times, they won't work with WebClasses at runtime unless you run them under Microsoft Transaction Server. In the next chapter, you'll see how to set up external DLLs so you can use them from WebClass-based applications.