Ado Examples and Best Practices

Creating and passing a user-defined structure is another technique that can be used to transmit information back from the middle tier or between modules in your application. User-defined structures use the Type operator to describe the variables that are passed, and they use an identical Type declaration on the client end to mirror the same structure. Actually, the Type declarations should be declared as Public in a public class. This way both the client and other applications using the Type can see the definition. The advantage to using user-defined structures is that you transmit just the structure—not any extra DDL as you do with a Recordset or XML, or, to some extent, with Variant arrays.

As with similar approaches (such as using delimited strings), you have to construct your own update operations. This means you have to understand how to construct a WHERE clause pointing to the rows to be updated, or how to execute the appropriate stored procedure to do the job for you.

Another issue to be considered in the user-defined structure approach is how you will handle multiple rows. That is, an individual structure element stores the data from a single row, such as a single customer or part. To store multiple rows, you have to declare an array of the template structure described in the Type declaration. Once the data arrives, you have to unravel the structure, row by row, placing the right elements in the right columns of a grid or other control.

A typical Type statement looks like this:

Type objCustomer ID as Long Name as String Address as String Phone as String Photo as Variant End Type

Setting the values of the individual customer objects is done in two steps. First you need to declare the Customer object using the Type-declared datatype.

Dim MyCust as objCustomer Dim MyCustArray() as objCustomer

If you choose to create an array of the typed structure, you simply create an array declaration referencing the Type-declared datatype. However, the dimension (size) of the array—the number of rows—is usually set elsewhere. That is, the component that fills the array usually changes the size of the array to match the number of rows retrieved. Because of this you declare the client-side of the array without indicating the boundaries. The second step is to simply set the values in the object.

With MyCust .ID = 22 .Name ="Fred" .Address="123 Silly Street" .Phone="Unlisted" End With

When you're ready to retrieve the data on the client side, the called function can return either a single structure or an array of the typed structure.

MyCust = GetOneCustomer( ) MyCustArray = GetSetOfCustomers( )

Once the data arrives, it appears in a one-dimensional array. You can determine how many rows are available by using the UBound function, which returns the upper boundary of the array. For example, if your GetSetOfCustomers function returned 50 rows, the following RowsFound code would return 50 (or 49 if you start indexing the array at 0).

RowsFound = UBound(MyCustArray, 1)

The UDT Server-side Code

On the server, you use the same common Type declaration to describe the data, but in this case when you dimension the array (if you use one), you have to fix a size before referencing the array. However, if you build it larger than necessary, or not large enough, you can use the ReDim (Preserve) function to reset the size of the typed array. To make this clearer, let's walk through a sample application that uses user-defined structures to pass data from tier to tier.

The application is called User-Defined Types and is located on the companion CD. It is divided into two parts: client-side and server-side. The server-side code is responsible for executing several queries—each illustrates the use of passing user-defined structures back to the client. The client application manages input parameters and writes the returning data to list boxes and a couple of grid controls. We'll get to the client application in a few pages, but first let's first walk through the server-side code.

The first declaration, in the following code, describes the objAuthor structure. It's used to describe a single row of information gathered about a selected author. In this case, we'll use this structure to define a single row of an array of qualifying authors—but I'm getting ahead of myself.

Option Explicit Public Type objAuthor Author As String Year_Born As Variant End Type

The next section describes another structure. In this case, it's tTitle, which is used to describe a single title fetched by ISBN. The structure is a mix of strings, both fixed and variable-length. It could contain a combination of any other Visual Basic datatype—even arrays of types.

Public Type tTitle Publisher As String City As String ISBN As String * 20 Year_Published As String Title As String Cover As String End Type

In an effort to remove literal string references when fetching Parameter and Field object elements, I create item-by-item enumerations in the following section of code. These can dramatically improve performance. We discussed this earlier in Chapter 8.

Private Enum enuRSAuthor Author Year_Born End Enum Public Enum enuParms ReturnStatus ISBN Publisher City Year_Published Title Cover End Enum

Okay, we're ready for the code—there are four functions to come. One to open the connection (for all of the other functions) and three others that fetch specific UDTs for the client—authors by ISBN, title by ISBN, and a list of appropriate ISBNs (that have related rows in the Covers table).

We start by constructing an ADO Command object in the following code. We don't have a choice in this case; we are executing a stored procedure that returns OUTPUT parameters. Okay, so I'm combining two examples in one module—sue me. The primary purpose of the Command object is to manage the query's Parameters collection for us.

Private cn As Connection Private cmd As Command Private rs As Recordset Private i As Integer Public Function GetTitle(ISBNWanted As String) As tTitle Dim objRetTitle As tTitle Set cmd = New Command OpenConnection With cmd .Name = "TitleByISBN" .CommandText = "TitleByISBN" .CommandType = adCmdStoredProc Set .ActiveConnection = cn

You'll find that ADO is somewhat challenged about how it maps the parameters in the target stored procedure. As we discussed earlier, we have a couple of choices here: construct the Parameters collection parameter-by-parameter (which is faster, but takes more code), or use the Refresh method (which is slower, but more reliable). In any case, when the Parameters collection is created, ADO and the layers expect the parameters to exactly overlay the parameter map exposed by the stored procedure. Messages complaining about missing parameters can be caused by too few parameters, by output parameters passed with no initial value (a bug), or by other confusion on your (or ADO's) part.

The parameter names you provide are meaningless. ADO only supports passing parameters to the data providers in order. That is, the first parameter must be created and passed first, the second after the first, followed by all other parameters in the order in which they are defined in the stored procedure. ADO does not support named parameters. That is, in ISQL or Query Analyzer, you can specify the stored procedure arguments by name in any order. This is what ADO does not support—until ADO 2.6^[5] comes online for the first time. While the server identifies the parameters by name and the error messages indicate which parameter is in error by name, ADO references parameters positionally. Get them out of order, or don't define them correctly, and you are toast. The ADO Parameter object names have no bearing on the names used by the stored procedure, unless you use Refresh. In this case, the parameter names match because they're generated and returned by the server along with the rest of the parameter properties.

.Parameters.Append _ .CreateParameter("ReturnStatus", adInteger, adParamReturnValue) .Parameters.Append _ .CreateParameter("ISBNWanted", adVarChar, adParamInput, _ 20, ISBNWanted) .Parameters.Append _ .CreateParameter("Publisher", adVarChar, adParamOutput, 20) .Parameters.Append _ .CreateParameter("City", adVarChar, adParamOutput, 50) .Parameters.Append _ .CreateParameter("Year_Published", adSmallInt, adParamOutput) .Parameters.Append _ .CreateParameter("Title", adVarChar, adParamOutput, 255) .Parameters.Append _ .CreateParameter("Cover", adVarChar, adParamOutput, 255) End With

After the Command is created and its parameters set (we set the input parameter value when we created the Parameter object), we can execute the query.

cmd.Execute Options:=adExecNoRecords

You don't see a Recordset? That's right, there isn't one. That's the advantage of using (just) OUTPUT parameters instead of a rowset. In this case, we know that only one row (should) result from a hit on the Titles table, given a specific (unique) ISBN number.

The following code references our single-row objRetTitle object and fills in the individual elements. We use the enumerations to make this easier for the human coder to read and to reference the elements faster. After we've filled in the structure from the data row, we simply pass it back to the client. Sure, we close the connection, but it gets closed anyway when the module goes out of scope.

With objRetTitle .ISBN = ISBNWanted .Publisher = "" & cmd.Parameters(enuParms.Publisher) .City = "" & cmd.Parameters(enuParms.City) .Year_Published = "" & cmd.Parameters(enuParms.Year_Published) .Title = "" & cmd.Parameters(enuParms.Title) .Cover = "" & cmd.Parameters(enuParms.Cover) End With cn.Close GetTitle = objRetTitle End Function

This next function returns a set of authors that wrote a specific book (based on the ISBN). Unlike the previous GetTitles function, this function returns an array of the objAuthor structures.

Public Function GetAuthors(ISBNWanted As String) As objAuthor() Dim objRetAuthors() As objAuthor OpenConnection Set rs = New Recordset rs.Open "Exec AuthorsByISBN '" & ISBNWanted & "'", cn, , , adCmdText

We take a different approach in the preceding function (just for a little variety)—we don't create a Command object. All too often, Command object's a waste of time in the middle tier, especially if you don't have to deal with returning parameters. Instead, we create a concatenated query that calls the stored procedure. Because we know the parameter string won't contain single quotes (remember the 'O'Malley' condition?), we can safely construct the query string. Yes, even though this is a stored procedure, we still call it adCmdText. If we called it a stored procedure (adCmdStoredProc), ADO would try to manage the parameters on its own.

If the Recordset is returned with no rows, we have to fill in the structure with appropriate information as we do in the following code. This is why I defined the Year_Born element of the structure as a Variant—so I could set it to a string if there was no valid year.

If rs.State = adStateClosed Or rs.EOF Then 'No author on file for this ISBN ReDim objRetAuthors(1) objRetAuthors(1).Author = "Not on file" objRetAuthors(1).Year_Born = "Unknown"

Okay, we got some rows. In the following code, we start by redimensioning the objRetAuthors array to hold the required number of rows. It now contains n structures defined by objAuthors. We know from experience that books are written by 1 to 4 authors—so 10 should be big enough for the array. We step through the Recordset, row by row, moving data from the Field objects (referenced by the enumerations) to the array of user-defined objAuthors (again, referenced by enumerations). If we find that one of the Year_Born fields is set to null, we pass a string back to the user to make sure that the user doesn't think the author was born 2000 years ago. Because the database doesn't have anything written by St. Paul (who was born before year 0), this should not be a problem.

Else If Not (rs.EOF and rs.BOF) Then ReDim objRetAuthors(1 To rs.RecordCount i = 1 Do Until rs.EOF With objRetAuthors(i) .Author = "" & rs(enuRSAuthor.Author) ' Author If IsNull(rs(enuRSAuthor.Year_Born)) Then .Year_Born = "No year on file" Else .Year_Born = rs(enuRSAuthor.Year_Born) ' Year Born End If End With i = i + 1 rs.MoveNext Loop ReDim Preserve objRetAuthors(1 To i − 1) End If

After we have processed the rowset, we can clean up and pass the array of objAuthors back to the client, as shown next:

rs.Close cn.Close Set rs = Nothing Set cn = Nothing GetAuthors = objRetAuthors End Function

This next function fetches a set of ISBNs that have pictures and authors on file. We only really care about the first 50 of these, so we limit the query accordingly.

Public Function GetISBNs() As Variant OpenConnection Set rs = New Recordset rs.Open "select top 50 isbn from covers where isbn in" _ & (select isbn from title_author)", cn, , , adCmdText GetISBNs = rs.GetRows End Function

In this case, we return the rows as a Variant array. This approach makes the most sense in this case.

The final function simply opens the database connection for all of the other functions.

Private Function OpenConnection() as Connection Set cn = New Connection cn.Open "Provider=SQLOLEDB.1;Initial Catalog=biblio;Data Source=betav8", _ "Fred","" Set OpenConnection = cn End Function

The Client-side Code

Next, we need to see how to parse the user-defined structures once they arrive back at the client. Much of this code deals with building clip strings for the Grid control. We could have presented the data in a variety of other ways, including using TextBox controls or perhaps building a Web page. The client application looks like Figure 8-1 when it's running.

Figure 8-1: Presenting the rows on the client

The application initially presents a list of valid ISBNs in the ComboBox at the bottom of the form. When the user selects an ISBN, the cmdQuery button is fired and we look up the information on the chosen ISBN. The resulting row is painted into the top grid control. We then perform a second query to populate the grid with the set of authors for the selected book. Yes, some books have many authors—not this one.

The code looks like this:

Private Sub cmdQuery_Click() Dim objLocalTitle As tTitle Dim objLocalAuthors() As objAuthor Dim strGrid As String objLocalTitle = GetTitle(ISBNList.Text)

At this point, we have captured the selected ISBN from the ComboBox control list and passed it to the GetTitle function (described previously). We get back a user-defined structure, as declared in our common module.

The first task is to pass the filename of the picture to the Image control. The database has the IMAGE, but we fetched the filename and simply used that to fetch the data from one of the files stored at that path location on disk.

Image1.Picture = LoadPicture(objLocalTitle.Cover) With objLocalTitle strGrid = .Publisher & vbTab & .City & vbTab & .ISBN & vbTab & .Year_Published _ & vbTab & .Title & vbTab & .Cover End With With MSHFlexGrid1 .RowSel = 1 .ColSel = .Cols − 1 .Clip = strGrid .ColSel = 0 End With

Next, we step through the user-defined structure, creating a tab-delimited string as we go (as shown in the following code). This is passed to the grid control using the Clip property. This approach eliminates the need to step through the grid, row by row and column by column, referencing each cell—something to be avoided.

objLocalAuthors = GetAuthors(ISBNList.Text) MSHFlexGrid2.Rows = UBound(objLocalAuthors, 1) + 1 strGrid = "" For i = 1 To MSHFlexGrid2.Rows − 1 With objLocalAuthors(i) strGrid = strGrid & .Author & vbTab & .Year_Born & vbCr End With Next i With MSHFlexGrid2 .Row = 1 .Col = 0 .RowSel = .Rows − 1 .ColSel = .Cols − 1 .Clip = strGrid .ColSel = 0 .RowSel = 0 End With End Sub

Early in the application, we use the Form_Activate event to fill the ComboBox containing the valid ISBNs and set up the grid controls, as in the following code. We don't do this in Form_Load because we don't want the user to wait while this initial setup is done. This way, the form is painted immediately, and before the user has a chance to focus on the form (unless the user had too much coffee), the ComboBox is filled and the user can proceed to use the form. Because the list of valid ISBNs can (theoretically) change from time to time, this process is repeated each time the form is activated. You might consider moving intensive code and complex queries to a less frequently executed event.

Private Sub Form_Activate() Dim varISBNs As Variant Me.MousePointer = vbHourglass varISBNs = GetISBNs ISBNList.Enabled = False For i = 0 To UBound(varISBNs, 2) − 1 ISBNList.AddItem varISBNs(0, i) Next i ISBNList.Enabled = True ISBNList.ListIndex = 0 Me.MousePointer = vbDefault

In the Form_Initialize event, we pass back a Variant array constructed by the query to fetch a set of valid ISBNs. It's easy to loop through this Variant array and fill the ComboBox. Too bad the ComboBox doesn't support a Clip property. If it did, we could have passed back a delimited string and applied it directly to the Clip property to populate the list, as we do with the headings in the Titles grid.

With MSHFlexGrid1 .Rows = 2 .Cols = 7 .FixedRows = 1 .Row = 0 .Col = 0 .RowSel = 0 .ColSel = 6 .Clip = "Publisher" & vbTab & "City" & vbTab & "ISBN" _ & vbTab & "Year" & vbTab & "Title" & vbTab _ & "Cover" .Row = 1 .RowSel = 1 End With With MSHFlexGrid2 .Row = 0 .Col = 0: .Text = "Author": .ColWidth(0) = TextWidth(String(20, "M")) .Col = 1: .Text = "Born": .ColWidth(1) = TextWidth(String(10, "M")) .Row = 1 .Col = 0 End With End Sub

That's it. As you can see, passing user-defined types has a few challenges but is fairly easy to understand. It's also fast and easy on the net, because there is little DDL information being passed. However, as you can also see, it requires an intimate knowledge of what's getting built, sent, and received. It would be tough to decode the structure on the receiving end without the Type declaration. These Type declarations also must be cast in concrete before they are propagated through your development organization. If they change after you build your application, you are in the soup. However, in some cases (certainly not all), you can "simply" recompile and redeploy, assuming that the hard-coded assumptions on the data structures do not change.

^[5]ADO 2.6 ships with SQL Server 2000 due out in July, 2000. There should also be a web release soon thereafter.