| n the April 2000 issue of MSDNâ¢ Magazine, I discussed creating components in Visual BasicÂ® for MicrosoftÂ® Transaction Services (MTS). This month Iï¿½ll take a step back and talk about the procedure involved in creating such components. In order to illustrate the process that I use to create powerful applications, I will build parts of a Web application that will allow a user to retrieve and update information from the Pubs sample database that ships with SQL Serverâ¢.|
When creating components, you must first figure out what functionality you need and how the components will be used. Therefore, I will begin by defining the key objects required to service a major part of my application (see Figure 1).
Building the Classes As you know, you canï¿½t build components that use a database without a good database tool. In this project I used Microsoft Access 2000 as the interface to the Microsoft Data Engine (MSDE). This allowed me to work with the Pubs database and build my components while on the road, then transfer everything to the server once I was back in the office.
The first project I created in Visual Basic was DBService. This project contains the Recordset class, which implements all of the database work for the other components. It is the black box that the other objects use to get to the database. The interface for Recordset as it appears in the Class Builder utility is shown in Figure 2.
|Figure 2 Recordset in Class Builder |
Next, I created a project group and added a new project (an ActiveXÂ® DLL) named PubsBook. Then I created the three classes for this project: Author, Royalty, and Title. Author is the only class that currently has an interface (see Figure 3).
The current interface for Author consists of the following methods: RetrieveAuthorsByAuID, RetrieveAuthorNameAndID, and AddAuthor. It was easy to implement the two retrieval methods. Both of them call stored procedures and pass back an ActiveX Data Object (ADO) recordset as the return value. This is straightforward code that is easy to write and debug. As I created each method, I also built the stored procedures that were called by that method. I created the stored procedures in my Microsoft Access project that points to the MSDE Pubs database.
Although not much error handling is required for these methods, you will notice that all of the methods do have error handlers. All of the error handlers call the Raise method of the Err object to generate a custom runtime error that can be trapped by the calling code. The error message is generated with this code:
The error message will contain the name of the method in which the error occurred, in addition to the error number and error description. The error handlers do not check for specific error codes that relate to either Visual Basic or SQL Server/ODBC runtime errors. Instead, the error messages return the original error and bubble the error back to the calling application, as mentioned previously. The returned error message includes the method name, and can be decoded by the calling code. This is an area where you may want to add more specific error handling to your application in order to trap and handle errors.
Err.Raise Err.Number, "AddAuthorError", Err.Description
Testing After I created the first two projects, I wanted to start testing the methods as I built them. You might be surprised to hear this, but I used Visual Basic, instead of ASP, to test the application because this allowed me to test the classes in one environment and get all the bugs worked out. Plus, I donï¿½t need to compile a component after I make changes to it just to test it. Not to mention that the Visual Basic debugger is quite powerful and easy to use. I could debug the code in ASP, but thereï¿½s no need to do that until the components are working correctly in Visual Basic.
To test the components, I added another project to the project group. Itï¿½s a standard EXE which originally consisted of one form. I left the project and form with their default names, Project1 and Form1. This project will become the test harness for the components.
To test the functionality of the Author class, I created an interface with Form1. This interface has a combobox used to test both of the retrieval methods, and a large textbox used to display results from the RetrieveAuthorsByAuID method. After creating the form, I added references to PubsBook and the ADO 2.1 Library. The PubsBook component contains the Author class that I was going to test, and the ADO library is used to type the variables that handle the recordset returned by some methods. I also set Project1 as the startup project to use it for testing.
Once I created the form, I began adding the code to test the class. First, I added the code shown in the Form_Load event for Form1 (see Figure 4). This code calls the RetrieveAuthorNameAndID method of Author to retrieve all of the author names and author IDs. The code then loops through this recordset and loads the combobox with author IDs. Itï¿½s simple code, but functional enough to test the RetrieveAuthorNameAndID method.
Notice the With operator in the Do...While loop. The With operator allows you to access the methods and properties of an object without explicitly referencing the object in the With block. This can actually speed up your application because using With explicitly references the object and lets COM work with the methods and properties faster.
Also notice in the Do...While loop that the value is extracted from the recordset by explicitly referencing the Fields collection and Value property. The syntax works like this:
But why use the longhand syntax when you can use this instead:
Well, the longhand syntax explicitly lets ADO know exactly what to do, while the shorthand syntax makes ADO look at its default properties, probably causing it to take longer to process the request. Granted, the additional time may not be much, but why not do it explicitly and avoid the performance hit?
The next step in the process was to launch Project1/Form1 (using the F5 key). The first time I ran the test application, I found my errors in RetrieveAuthorNameAndID. Once I worked those out, I had author IDs in the combobox. So far, so good.
Next, I added the code to the cboAuthors_Click event. This code executes when the user selects an author ID from the combobox. The selected author ID is passed to the RetrieveAuthorsByAuID method, which returns a recordset of authors that match the selected author ID (this should always be one author). Working the bugs out of the RetrieveAuthorsByAuID method also went pretty quickly.
Now for the hard part. The insert and update methods of any object are usually the hardest to create and test. They take extra work because they need error checking, and since those methods actually send data to the database, you can run into problems if the wrong data gets passed to the method.
|Figure 5 Form2 Publisher Test Form |
I added the code to the AddAuthor method to execute the AddAuthor stored procedure and pass in the appropriate values. To test AddAuthor, I updated Form1 to call two new forms, one of which would be used to test AddAuthor. I added the two buttonsâ"Publisher and Authorâ"to Form1. Clicking on the Publisher button will display Form2 (see Figure 5). This form tests the AddPublisher method of the Publisher class in the PubsVendors component. Selecting the Author button will display Form3, which tests the AddAuthor method of the Author class (see Figure 6).
|Figure 6 Form3 Author Test Form |
Next, I added the code shown in Figure 7 to the Click event of the Insert button on Form3. This code tests the AddAuthor method. At this point, I pressed F5 again and tried to run Form3. I entered data in the form, and clicked the Insert button. The original interface for the AddAuthor method did not include the Contract field:
Clicking Insert without the Contract field data generates the following error:
AddAuthor(sAu_id As String, sAu_Lname As String,
sAu_Fname As String, sPhone)
This is a SQL Server error passed back to the application, indicating that a specific column cannot be null.
Run-time error '-2147217900 (80040e14):
Cannot insert the value NULL into column 'contract',
table 'Pubs.dbo.authors'; column does not allow nulls.
Fixing this error was interesting because the Contract column in the Authors table is a bit column. As I found out the hard way, you cannot use bit as the data type for a variable in a stored procedure. With some testing, I found that using an Int data type in the stored procedure worked fine. This made it easy because now I could use a matching Integer data type in the Visual Basic component. I should also point out the way that SQL Server handles a bit column. If you run a query against the Authors table, it will show True or False for the Contract column. If the bit column is 0, then it shows False; if it is 1, it shows True. Once I discovered this, I modified the AddAuthor interface and the Click event for the Insert button in Form3 to properly set the value for the Contract column to either 0 or 1. Then I went back to testing.
I also found that entering data without a phone number generates an error. This is because Phone is set to not allow nulls. Proper error handling in the method could trap for this, so I added the following code to the AddAuthor method:
If any of the Au_id, Au_Lname, Au_Fname, or Phone fields are blank, the return value to the method is set to an error statement and the method is exited with Exit Function. If iContract is not 0 or 1, it is set to 0 (False). The calling code only needs to check the return value, and if it is not "" then an error occurred. Since the error message is in plain text, it can even be displayed to the user, as this code from the Insert_Click event shows:
If Len(sAu_id) = 0 Then
AddAuthor = "Author ID cannot be blank"
If Len(sAu_Lname) = 0 Or Len(sAu_Fname) = 0 Then
AddAuthor = "Author Name cannot be blank"
If Len(sPhone) = 0 Then
AddAuthor = "Phone cannot be blank"
If iContract < 0 Or iContract > 1 Then
iContract = 0
Another little problem that I found during testing concerned the AddAuthor method. Since the database work is actually done by the Recordset method of the DBService component, I technically donï¿½t need a reference to ADODB in my PubsBook project. However, since I wanted to use the ADO data types of adChar and adInteger, the component needed a reference to ADODB. This was not obvious because the errors generated by the database call indicated a type mismatch. Guess how I found the error? I used the debugger in Visual Basic to check the data type and value contained by the constant.
If Not sReturn = "" Then
txtStatus.Text = sReturn
Adding Option Explicit to the class module will also find this because Visual Basic will flag a constant that is not defined as an undefined variable. You can force Visual Basic to add Option Explicit to all of your classes, modules, and forms by selecting Tools | Options and checking the Require Variable Declaration box on the Editor property page. I was doing my development on a system on which Visual Basic was recently reinstalled, and this option was not set.
The AddPublisher method in the Publisher class is built almost exactly like the AddAuthor method. The structure is the same and Form2 tests it in the same manner as Form3.
Implementing in ASP So far I havenï¿½t said much about MTS or ASP. Once the classes are debugged and working, then itï¿½s time to put them in MTS and ASP. But until then, Project1 is a good test harness to check them with. Besides, when it comes time to create the ASP code to use the objects, I can copy them using code from the forms and paste into the ASP page. With a little tweaking, the ASP application will be working in no time at all.
All of the code referenced in this column is available from the link at the top of this article. To use the code, you must modify the Password module and DBUtil module (both are in the DBService project) to point to your database with the correct security information. The application uses the standard Pubs database with no modifications, except for the addition of the stored procedures (see Figure 8).