our boss says, "We need our app to talk XML." Of course, XML is a solid data format, but what about the data formats that are already used by your company's applications? Your business applications may have a data access tier that reads and writes data to and from a database and sends the data back to the business services tier. What format does that data take? If you use an ActiveX® Data Objects (ADO) recordset, your business component is reliant upon the ADOR object library, which may or may not be desirable. You may also have invested in ASP code that expects data as an array so that your business component can be independent of ADO and can traverse the data more rapidly. But you also want to use XML so it is easy for your business component to send the data to yet another system on another platform. Obviously your data needs to take on various formats for various reasons. Wouldn't it be great if there were a single data access component that could encapsulate all data management and return the data in multiple formats?
I'll show you how you can do just that by creating a COM+ component that can be used from any Windows®-based application to return data in a number of formats including an ADO recordset, a two-dimensional array, or XML. The DataManager component I'll discuss can be used from multiple business components, too. I'll start by demonstrating how DataManager retrieves data. Then I'll walk through the highlights of the DataManager code so you can see what makes it tick. If you'd like to run the code while I traverse it in this column, you can find the DataManager component and source code as well as the testing interface at the link at the top of this article. This code was written under Windows NT® 4.0 and thus uses Microsoft Transaction Server (MTS) and the Shared Property Manager (SPM). Note that this code will work under Windows 2000, but these two components are replaced with COM+ services.
Let's start by looking at a simple application I wrote in Visual Basic® to test the DataManager component. The test application includes an interface to view data from the three formats. When the data is returned as an ADO recordset or an array, the data is loaded into a grid. When the data is returned as XML, it is loaded into a WebBrowser control. Also included in this code is a tiny business-tier component to fit between the interface and the data access component. After all, data is a lot easier to manage if you can display it in a familiar format.
When you run the test application, you will notice that it has a combobox that lets you select how you want your data returned. The data is then returned to either the first tab in a grid or the second tab in a WebBrowser control. (All controls in this application come with Visual Basic, so you can run this app out of the box.) Any error messages are displayed in the third tab's textbox. Figure 1 shows data from the SQL Server™ pubs database returned as an ADO recordset.
Figure 1 SQL Data as an ADO Recordset
If you want to retrieve the data from an array, select 2-D Array from the combobox and click OK. You will see the same data as in Figure 1. However, while Figure 1 shows the headings (field names) for each column (ProductID, ProductName, and so on), when the data is displayed as a two-dimensional array these column headings are not displayed. When you return an array, you do not get back the metadata that is built into an ADO recordset; therefore, you don't see the field names as column headings.
You could modify this approach so that the array of data also includes the metadata. In fact, you could modify DataManager to return the data in any format that you want. I'll show you where you can do this once I jump into the code.
If you choose to retrieve the data in an XML format, the results will look like Figure 2. Notice that the XML data contains field elements that describe the individual columns from the titles table. The data itself is also contained within the XML (if you scroll down below the metadata).
Figure 2 XML Metadata
OK, so now that you've seen how the data is displayed, let's take a look at how the DataManager is isolated into a single COM+ component that returns all three of these data formats.
The DataManager component has a public class called C_DataManager. To run a SQL statement, you call the ExecuteSQL method of C_DataManager. ExecuteSQL takes care of opening the connection to the appropriate database, executing the SQL command, formatting the data, and returning it to the calling application.
The ExecuteSQL method accepts the following four parameters:
- sSQL, the SQL you want to execute
- sINIAppName, the name of the business component that is calling DataManager
- sINIFullPath, the fully qualified path of the business component that is calling DataManager
- lDataReturnType, the format in which to return the data
DataManager assumes that the business component will use an INI file that contains the database settings specific to that application. After all, since multiple business applications can use the same DataManager, there must be a way to tell DataManager which set of database settings to use. The technique demonstrated creates an INI file for the business component that contains some basic database settings. In the INI file, the database type is specified, along with database server name and database name, as well as the application user ID and password—basically, all of the components of an ADO connection string. (This INI file, TestBL.ini, is included in the full code download.)
For the sINIAppName and sINIFullPath parameters, you pass in the name of the INI file (without the .ini extension), and its full path. For the sSQL parameter, you pass in the SQL you want to run. And for the lDataReturnType parameter, you specify the format for returning your data. To make this simple, DataManager exposes a public enumerator that the business component can pass into the ExecuteSQL method:
Public Enum enumDataReturnType
Establishing the Connection
The ExecuteSQL method runs the SetConnectionParameters subroutine (see Figure 3), which reads the business component's INI file to get the database settings. This subroutine gets all of the database settings values from the INI file and then sets them to a local Visual Basic type so you can open the connection to the database later.
The GetINIValue function (see Figure 4) first checks if the setting is stored in the MTS SPM. If it is, then GetINIValue reads the setting from there without ever opening the INI file. If not, then GetINIValue reads the INI file to get the setting, closes the INI file, saves the setting to the SPM, and passes the setting back as its return value. Using the SPM avoids the expense of hitting the INI file each time a call is made to the ExecuteSQL method.
Executing the SQL
Once the database settings are retrieved using the SetConnectionParameters subroutine, the ExecuteSQL method creates and opens an ADO Connection object. Next, it creates and opens an ADO recordset using the open Connection object. The recordset is opened as a client-side recordset and is promptly disconnected from the Connection object, as shown in Figure 5.
The final step, shown in Figure 5, is to format the data in the appropriate structure. If the calling application wants an ADO recordset, you simply pass the recordset back at this time. Otherwise, you call the function ConstructReturnData (see Figure 6), which formats the data for you and returns it.
The ConstructReturnData function simply evaluates the desired data format and either returns a two-dimensional array from the recordset's GetRows method or it formats the XML using the BuildXML function. The BuildXML function invokes the C_XML object's BuildXML method, which formats the XML from the ADO recordset. Basically, it loops through the ADO recordset's fields to get the metadata, and it loops through the rows and fields to get at the data itself. All the while, it uses the MSXML.DOMDocument object to create the XML.
Formatting the XML
To format the outgoing XML, the BuildXML method first creates the <xml> root tag. Then the <metadata> tag, which will contain all of the fields attributes, is created. Within the <metadata> tag, a <fields> tag is created to house each <field> tag:
'— Create XML document element
Set m.oXML.documentElement = m.oXML.createElement("xml")
'— With <xml>
'— CREATE METADATA
'— Create MetaData node
'— Create MetaData Fields node
There will be one <field> tag per field in the recordset and the tag will have attributes such as Name, Type, and IsNullable, among others. Figure 7 shows how these attributes are created and appended to the appropriate <field> tag.
Next, the <data> tag contains the <rows> tag. The <rows> tag contains a <row> tag for each row of data. Each <row> tag is assigned an index attribute to uniquely identify that row. The <row> also contains a <fields> tag, which contains a <field> element for every corresponding field in the ADO recordset. Figure 8 shows how the data is put together into the XML document.
Keep in mind that you can return the XML in any format that you like. I chose a simple format of nesting the metadata and the data separately within the XML. However, you could eliminate the need for the <metadata> tag by making the metadata attributes of the <data> tag's nested field elements. Or you could just eliminate the metadata entirely if that suits your needs. That is the beauty of XML: the format of the data that you return is entirely up to you!
In the end, you have a data access component that is completely independent of its calling applications. So you could have multiple business components utilizing DataManager, each hitting different database servers, and each retrieving the data in a different format. An enhancement that you could work into this tool is the ability to pass in a specified XML format. That way, you could tell DataManager exactly how you want the XML to be structured.
If you want to try out this code, I have included the entire source at the link at the top of this article. All you'll need is Visual Basic and the SQL Server pubs database and you can get up and running. Of course, you can customize this code to work with other databases as well.