SQL and XML: Use XML to Invoke and Return Stored Procedures Over the Web

ike many developers, we are finding that our n-tier line-of-business projects are evolving from traditional Windows®-based clients to generic browser-based ones. Such systems use Microsoft® Internet Information Services (IIS) and ASP for Web page delivery, Visual Basic® components for middle-tier business objects, and Microsoft SQL Server™ for data storage.
      These are great tools that provide a lot of flexibility, but this flexibility comes at a price. When users request new features for the application, it can be time-consuming for developers to add the support to each tier. This is true even for simple requests, like adding an address line to the purchase order screen. New methods—and sometimes classes—must be defined at each layer to implement such requests, and the components must be redeployed.
      It is so common for users or front-end developers to ask for additional information that we set out to find a way to speed up the process. The architecture we developed uses XML from the front end to invoke specially written SQL Server stored procedures, and returns the results from those procedures as XML back to the front end. Because only certain stored procedures can be called, and because the XML can be intercepted and manipulated in the middle tier, we feel that we have preserved the benefits of n-tier development while getting rapid application development (RAD) back into the system. Another nice benefit is that with the data floating around in XML, it's easy for front-end developers to transform it for a pleasing UI presentation. This article explains the architecture we have created to accomplish this.
      Source code and compiled components are available for download from the link at the top of this article.

The First Attempt

      We knew from the beginning that data delivery to the presentation tier should be in the form of XML because this would allow us to use XSL to transform the XML to HTML (support of generic browsers was important to us, as was the ability to modify the presentation without recompiling). However, it was not clear to us how the XML would be created and how it would be passed from wherever it was created to the presentation tier. For robustness we wanted to encapsulate the creation and parsing of XML. Encapsulation would also let us hide the details of XML use from developers who may not be familiar with the MSXML parser.
      We achieved these goals by developing a technique that wraps XML schemas in Visual Basic objects. We defined a schema for each set of data to be presented. For example, to support a purchase order screen, we defined a purchase order XML schema. Given a specific XML schema and a Visual Basic template class that we designed, it was quite simple to create classes whose properties corresponded to the elements of the schema. You merely had to set the object's properties, then read the object's XML property to obtain the corresponding XML.
      The objects worked just as well in reverse. That is, you could assign an XML string to the object's XML property, which would trigger the object to parse the XML (using the MSXML parser) and set its public properties appropriately. Two members of our design team, Dave Grundgeiger and Patrick Escarcega, reported on this technique in the article "XML Wrapper Template: Transform XML Documents into Visual Basic Classes" in the January 2001 issue of MSDN Magazine.
      Our technique was successful. We had achieved a uniform, reusable way to generate and pass XML between the tiers of our applications. Development turnaround time was reduced because we were using a template class that was easy to modify. However, we felt there was still too much effort involved when a front-end developer needed additional information. When such a change was needed, we had to modify the XML schema to incorporate the new information, modify the class that wraps the modified XML, and modify the data tier to supply the new data. We also had to modify the business tier to pass the new data to the presentation tier, and modify the presentation tier to use the new data. We wanted to do better.

The Solution

      What if, instead of defining a unique XML schema for each set of data coming to the presentation layer, we let the data itself define the XML format? And what if instead of having unique classes for each set of data, we had one generic piece of code that would fetch the data and create the XML? If this would work, we could save two or three days of development time for each additional set of data we needed to support! In fact, this is precisely what we were able to achieve with this design. The architecture is shown in Figure 1. It has the following components:
ASP pages In our design, ASP pages serve only to instantiate COM objects, where the real work happens.
Presentation tier objects This component makes calls to the business tier to get data in XML format. It then transforms the XML into HTML and writes it to the IIS Response object.
Business tier objects This component exposes objects and methods for use by the presentation tier. Which objects and methods will be exposed at this level is determined by business needs; they don't reveal the underlying mechanism by which data is retrieved (discussed next).
Data access adapter component and data access component These two components work together to offer XML-based invocation of stored procedures and to return the results in XML.
Stored procedures These are an integral part of the architecture. We did not want to build a generic XML-based database access mechanism, as this would weaken the separation of tiers. Instead, the generic mechanism can only access stored procedures that have been written in the proper format. This means that only the stored procedures themselves have any knowledge of how the database is structured.
      In the following sections, we'll explain the operation of each tier so that you can see how a request for information is fulfilled through our architecture. The meat of the article is in the sections on the data access adapter component, the data access component, and the stored procedures. The sections preceding them describe how the core components are called and how the return values are used.

The Sample Web Application

      In the code available for download, we have included a sample Web application that uses our technique. The application provides a simple UI to the Northwind sample database included with SQL Server. Instructions for deploying the components are covered later in this article.


Figure 2 Employee Login Page

      After installing the components, the sample Web app can be run by browsing to the application's login.asp page, shown in Figure 2. Enter an Employee ID (1 through 9) and click the Log In button. This brings you to the Employee Detail page, which shows information about the logged-in employee and the orders associated with the employee (see Figure 3).


Figure 3 Employee Detail Page

      The Employee Detail page is created by requesting XML data from our generic SQL Server data access component, and transforming that XML to HTML using XSL. This article is not about XSL, so we won't discuss how the transform happens, but the XSL we used is included in the download. Rather, we will explain how our generic components allow SQL Server data to be transformed into and out of XML.

ASP Pages

      The data retrieval process starts when the user browses to one of the ASP pages in the system. We'll examine the process by walking through some sample code. The code implements a hypothetical intranet application where employees log in to perform order entry. The user logs in by browsing to login.asp and entering an employee ID. Upon successful login, the user is redirected to empdetail.asp. The empdetail.asp page fully exercises our RAD architecture, so we will follow its operation in detail throughout the remainder of this article.
      The empdetail.asp page is shown here:

<%

  

  
'create a writer component and then call the employee detail

  

  
'function to create this page for display.  The function will take

  

  
'care of a user trying to log into here with an invalid logon

  

  
Set Writer = Server.CreateObject("COMASP.cWriter")

  

  
Response.Write(Writer.EmployeeDetail())
  

  

  
'destroy the writer component

  

  
Set Writer = Nothing

  

  
%>

Empdetail.asp instantiates a cWriter object from our presentation tier (discussed in the next section) and invokes that object's EmployeeDetail method. The EmployeeDetail method generates the actual HTML that is presented to the browser.
      The ASP pages in our example application are listed in Figure 4, along with their functions.

Presentation Tier Objects

      Our presentation tier is implemented in the Visual Basic project COMASP.vbp, and is compiled into spdaui.dll. It contains a single class, cWriter, which has several methods that can be called from our ASP pages. Our design style is to have as little code as possible directly on ASP pages. Our component maintains a reference to the ASP scripting context, which in turn gives it access to the IIS Application, Request, Response, Server, and Session objects. With access to these objects, our Visual Basic component can do anything that ASP script can do (and more, of course, because we can code in Visual Basic rather than VBScript).
      To obtain a reference to the ASP scripting context, our class exposes a method called OnStartPage. When an ASP page instantiates an object, IIS automatically calls this method on the object, if present, and passes in a reference to the scripting context. The OnStartPage method then saves this reference internally for later use by other methods in the class. Note that in order to use the scripting context, the Visual Basic project must have a reference to "Microsoft Active Server Pages Object Library" (asp.dll).
      The methods exposed by the cWriter class are shown in Figure 5. Let's look specifically at the EmployeeDetail method, which is called by VBScript running on empdetail.asp. This method instantiates a business tier object, MiddleTier.cEmployees, and calls that object's GetDetail method to get information about the employee whose ID is specified in the call. The value returned from GetDetail is a string in XML format. Here's the relevant code:

Set oEmployees = New MiddleTier.cEmployees

  

  
strEmployeeDetailXML = oEmployees.GetDetail(lEmpID)

      The XML is then loaded in to an MSXML DOM object for transformation according to the XSL located in empdetail.xsl. The code for this, still from the EmployeeDetail method, is shown here:

Set oXML = New MSXML.DOMDocument

  

  
Set oXSL = New MSXML.DOMDocument

  

  
oXML.loadXML strEmployeeDetailXML

  

  

  
oXSL.Load App.Path & "\xsl\empdetail.xsl"

  

  
EmployeeDetail = oXML.transformNode(oXSL)

The result is a string in HTML format, which is the return value from the EmployeeDetail function, and which is ultimately displayed in the user's browser.

Business Tier Objects

      Our business tier is implemented in the Visual Basic project called MiddleTier.vbp and is compiled into spdamid.dll. It contains two classes, cEmployees and cOrders, which retrieve data about employees and orders, respectively. In the previous section, we showed how the presentation tier calls the cEmployees object's GetDetail method to retrieve information about the logged-in employee. Let's have a look at this method to see what happens to the call from there.
      The GetDetail method instantiates a cAdapter object from the DataAccessAdapter component (discussed in the next section), sets that object's properties, and calls its CallSP method:

Set oAdapter = New DataAccessAdapter.cAdapter

  

  
With oAdapter

  

  
    .SPName = "Employee_GetDetail"

  

  
    .Parameters.Item("EmployeeID").Value 

  

  
        = lEmployeeID

  

  
    strXML = .CallSP(daRecords)

  

  
End With

      By setting properties, the calling code tells the data access adapter which database stored procedure is to be called and what the values of any parameters are. Invoking the CallSP method actually calls the stored procedure. The return value from the CallSP method is a string in XML format that encodes the results of the stored procedure call in a way that we'll describe later in this article. The resulting XML could be manipulated further in the business tier if desired, but in our example it is returned to the presentation tier for transformation to HTML.

Data Access Adapter

      The data access adapter is one of two generic components in the example (the other being the data access component described in the next section). Together these two components provide the ability to invoke specialized stored procedures and return their results in XML. The data access adapter is implemented in the Visual Basic project, DataAccessAdapter.vbp, and is compiled to spdaad.dll. It contains four classes: cAdapter, cParameter, cParameters, and IExpose.
cAdapter This is the component's main class. It is the only class that is publicly creatable. To use the data access adapter, the caller instantiates an object of this class, sets its properties, and calls its CallSP method. The properties of the cAdapter class are SPName and Parameters.
      The SPName property is set to indicate which database stored procedure is to be called. The value in this property must match the name of an existing stored procedure that has been coded in the proper format. It's important to note that this is not a generic mechanism for invoking any stored procedure. If an attempt is made to access a stored procedure that is not in the proper format, an error will occur.
      Setting the SPName property has a side effect. The SPName property Let procedure queries the database (through the data access component) for the list of parameters that must be supplied with the specified stored procedure. The cAdapter object's Parameters collection is loaded accordingly for the convenience of the calling code.
      The Parameters property is a read-only reference to a cParameters object. The calling code sets parameter values through the parameters property.
      The cAdapter class has a single method, CallSP. Its syntax is:

  Public Function CallSP(ReturnType As ReturnDataEnum) As String
  

This function has a single parameter, ReturnType, of type ReturnDataEnum. The value of this parameter indicates what kind of results the caller is expecting from the stored procedure. The definition of this enumeration is:

Public Enum ReturnDataEnum

  

  
    daParameters = 0

  

  
    daRecords = 1

  

  
End Enum

A value of daParameters indicates that the stored procedure returns data as one or more out parameters. A value of daRecords indicates that the stored procedure returns data as resultsets. Note again that it is the caller who must specify this information.
cParameter This class represents a single parameter to be passed to the stored procedure. The calling code obtains a reference to a cParameter object through the cAdapter object's Parameters collection. The parameter name is used as a key into the Parameters collection. For example, this line sets the value of a parameter named EmployeeID:

  oAdapter.Parameters.Item("EmployeeID").Value = lEmployeeID
  

Note that if the caller specifies a name that is invalid for the stored procedure named in the cAdapter object's SPName property, an error will occur.
cParameters This class is a collection of cParameter objects.
IExpose This class is an interface definition only. We defined the IExpose interface in order to support debugging and testing. It is implemented by the cAdapter class. We'll describe this interface later in the article, when we introduce the stored procedure test application that we built.
      The cAdapter class's CallSP method packages up the stored procedure name and parameters into XML format and then passes that XML to the data access component. The data access component unpacks the stored procedure name and parameters and uses ADO to invoke the stored procedure. It then takes the return data, packages it up in XML, and returns it to the data access adapter. We built it this way because we wanted the data access component to be entirely oriented toward XML. That is, XML is passed to it to invoke stored procedures and XML is returned containing the results. The purpose of the data access adapter is to allow callers to invoke data access component functionality without having to supply their own XML.
      For the sake of simplicity, here is a paraphrased version of the critical code in CallSP. (The actual code does some extra things to keep track of intermediate information for the purpose of supplying this information through the IExpose interface.)

' The following are already assigned:

  

  
' strSPName holds the name of the stored procedure.

  

  
' m_objParmCol holds the cAdapter object's cParameters collection.

  

  
' ReturnType holds a value from the ReturnDataEnum enumeration.

  

  

  
Set oAccessor = New DataAccess.cAccessor

  

  
Set oXML = New MSXML.DOMDocument

  

  

  
strInvokingXML = m_objParmCol.ParametersXML(strSPName)

  

  
strResultXML = oAccessor.GetDataXML(strInvokingXML, ReturnType)

In this code fragment, the CallSP method invokes the cParameters collection's ParameterXML method to wrap the parameters (and the supplied stored procedure name) into properly formatted XML. (We'll describe this format later.) After the parameters and stored procedure name are wrapped up, the XML is passed to the GetDataXML method of the data access component's cAccessor class.

Data Access Component

      The data access component is the workhorse of the system. This component takes requests in the form of XML, parses those requests into a stored procedure name and parameters, calls the stored procedure, and returns the results in XML. The data access component is implemented in the Visual Basic project DataAccess.vbp and is compiled to spda.dll. It contains a single class, cAccessor, that exposes two methods: the GetDataXML method and the ParameterXML method.
GetDataXML Call this method to invoke a stored procedure and receive the results. The syntax of GetDataXML is:

Public Function GetDataXML(ByVal SPXMLState As String, _

  

  
    ReturnType As ReturnTypeEnum) As String

SPXMLState is an XML string that indicates which stored procedure to call and which values to pass for its parameters (if any). The format of this XML is described later in this article, in the section titled "Invoking Stored Procedures with XML."
      The value of ReturnType indicates what kind of results the caller is expecting from the stored procedure. The definition of this enumeration is as follows:

Public Enum ReturnTypeEnum

  

  
    daParameters = 0

  

  
    daRecords = 1

  

  
End Enum

A value of daParameters indicates that the stored procedure returns data as one or more out parameters. A value of daRecords indicates that the stored procedure returns data as resultsets. Note that this enumeration mirrors the ReturnDataEnum enumeration exposed by the data access adapter.
      The value returned from GetDataXML is an XML string that contains the results from the stored procedure. The format of this XML is described later in this article.
ParameterXML Call this method to retrieve an XML-based representation of the stored procedure from the database after the SPName property has been set. The syntax is:

Public Function ParameterXML(ByVal StoredProcedureName As String) _

  

  
    As String

The single parameter, StoredProcedureName, indicates the stored procedure in the database. The return value is an XML string that represents the stored procedures XML representation, including parameters and their data type and direction. The format of this XML is described later in this article.

Stored Procedures

      In order for this mechanism to work, there must be stored procedures in our database that follow a precise format. We devised this format to be flexible in the type and amount of data that can be returned. We also took into account the fact that the only direct consumer of our stored procedures is the data access component, which builds XML from the returned data. Therefore, we developed a scheme that would support the need to deliver stored procedure results as XML.
      Our stored procedure format uses multiple resultsets to return the data necessary to build our XML. If you didn't know that ADO is able to return multiple resultsets in a single call, you should realize that this is a great way to reduce server round-trips when retrieving data that is related, but that doesn't belong in the same resultset. For example, a Web page that displays the orders taken by an employee will have header information about the employee, followed by rows of order information. Rather than making two calls to the database to retrieve both sets of information, you can make a single call to a stored procedure that returns two resultsets.
      Figure 6 shows a stored procedure that uses five SELECT statements to return five separate resultsets. The code to call such a stored procedure and loop through the resultsets could like the following:

' Assume cmd is already Dimmed and Set.

  

  

  
' Get the first resultset.

  

  
Set rs = cmd.Execute()

  

  

  
Do While Not (rs Is Nothing)

  

  

  
    '

  

  
    ' Do something with rs...

  

  
    '

  

  

  

  
    ' Get the next resultset.

  

  
    Set rs = rs.NextRecordset

  

  

  
Loop

This is the same as using a single resultset except for the While loop and the call to NextRecordset.
      The first resultset specifies the name of the XML dataset that is to be created. The name is used by the data access component to name the outermost element in the returned XML. This resultset is followed by one or more resultset pairs. The first resultset in each pair provides two additional names that are used for naming elements in the XML, and the second resultset of each pair provides the actual data to be returned. For an example, look at the Employee_GetDetail stored procedure, shown in Figure 6. The data access component loops through these resultsets (as shown in the previous code snippet), and produces the XML shown in Figure 7. (For brevity, the figure shows only two <Order> elements. The actual data in the code download has many more.)
      As mentioned, the stored procedure shown in Figure 6 returns five resultsets—one for each SELECT statement. Let's take a look at each one in turn.
      The first resultset is produced by this SELECT statement:

  SELECT "EmployeeDetail" AS DataSet
  

This creates a resultset having a single record with a single field, called DataSet. The value of this field is EmployeeDetail. The first resultset always has a single record and a single field named DataSet. The data access component uses the value of this field as the name of the outermost element in the returned XML (see Figure 7).
      Next, there is a pair of resultsets, created by these two SELECT statements:

SELECT "Employees" AS NextResultset, "Employee" AS RecordType

  

  

  
SELECT EmployeeID, LastName, FirstName, Title, TitleOfCourtesy

  

  
FROM Employees

  

  
WHERE EmployeeID = @EmployeeID

The first resultset provides the names to be used for this data in the XML. This resultset has a single record with two fields: NextResultSet and RecordType. The value of NextResultSet (in this case, "Employees") names the XML element that surrounds the entire set of data in the XML, and the value of RecordType (in this case, "Employee") names the XML element that surrounds each record. The second resultset in the pair provides the data itself. In this case we're retrieving a single employee record from the Employee table, based on the employee ID passed into the stored procedure. Note in Figure 7 how the field names in this resultset are used as element names in the XML returned by the data access component.
      Finally, look at the resultsets in Figure 7 produced from the SELECT statements in Figure 6. Again, the first resultset provides the names to be used for this data in the XML returned by the data access component. The second resultset provides the actual data—in this case, the list of orders associated with the given employee. Again, compare this to the resulting XML in Figure 7.

Returning Data through Out Parameters

      Besides returning resultset data, our framework also supports returning data through out parameters. (However, we do not support returning data through resultsets and out parameters at the same time.) Here is a stored procedure that returns a value through an out parameter.

CREATE PROCEDURE Employee_GetIDFromOrder

  

  
   @OrderID AS INT,

  

  
   @EmployeeID AS INT = NULL OUTPUT

  

  
AS

  

  

  
—get the employee id for the order that was specified

  

  
SELECT @EmployeeID=EmployeeID FROM Orders WHERE OrderID=@OrderID

This stored procedure accepts an order ID and returns the employee ID in an out parameter. The GetEmployeeID method of the cOrder class in the middle tier of our example code invokes this stored procedure through the data access adapter as shown here:

Set oAdapter = New DataAccessAdapter.cAdapter

  

  
With oAdapter

  

  
    .SPName = "Employee_GetIDFromOrder"

  

  
    .Parameters.Item("OrderID").Value = lOrderID

  

  
    .CallSP daParameters

  

  
    lEmployeeID = .Parameters.Item("EmployeeID").Value

  

  
End With

      The previous code shows that the value of the stored procedure's EmployeeID parameter is found in the cAdapter object's Parameters collection after the call to that object's CallSP method.
      In addition to returning data through out parameters, a stored procedure can have an actual return value. For example, a stored procedure that creates a new order in the sample application is shown in Figure 8. This stored procedure returns the order ID of the order that was created. Our middle tier cOrders class has a corresponding CreateOrder method that contains this code:

Set oAdapter = New DataAccessAdapter.cAdapter

  

  
With oAdapter

  

  
    .SPName = "Order_CreateOrder"

  

  
    .Parameters.Item("EmployeeID").Value = lEmployeeID

  

  
    .Parameters.Item("CustomerID").Value = lEmployeeID

  

  
    .CallSP daParameters

  

  
    lNewOrderID = .Parameters.Item("RETURN_VALUE").Value

  

  
End With

As shown in this code, our framework adds an item to the cAdapter object's Parameters collection, called RETURN_VALUE. This item contains the value returned by the stored procedure (if there is a return value).

Invoking Stored Procedures with XML

      It's possible to invoke our stored procedures by feeding XML strings directly to the data access component. This is done by instantiating a cAccessor object and calling its GetDataXML method. You can find the syntax of this method in the "Data Access Component" section earlier in this article.
      The XML passed as the GetDataXML method's SPXMLState parameter specifies which stored procedure is to be called and what values are to be supplied as its parameters. For example, here is some XML that invokes our Employee_GetDetail stored procedure (shown in Figure 6):

  <CallSP SPName="Employee_GetDetail">
  
    <RETURN_VALUE Direction="4" Datatype="3" /> 
  
    <EmployeeID Direction="1" Datatype="3">1</EmployeeID> 
  
  </CallSP>
  

The outermost element must be <CallSP>. The SPName attribute of this element indicates the name of the stored procedure that is to be called. If the stored procedure has parameters, a return value, or both, these are represented as child elements of the <CallSP> element. For parameters, the name of the parameter is used as the name of the element. For the return value, the element is named RETURN_VALUE. The Direction attribute indicates the direction of the parameter. The direction value that appears here is passed to ADO when the stored procedure call is made. The direction value can be any of the values from ADO's ParameterDirectionEnum enumeration, defined in ADO:

  Public Enum ParameterDirectionEnum
  
      adParamUnknown = 0
  
      adParamInput = 1
  
      adParamOutput = 2
  
      adParamInputOutput = 3
  
      adParamReturnValue = 4
  
  End Enum
  

The Datatype attribute indicates the native type of the data being passed. The values come from ADO's DataTypeEnum enumeration. In the XML shown previously, both the return value and the EmployeeID parameter have a data type of adInteger (3 is the value of the adInteger constant, which is defined by ADO). The full list of constants in this enumeration is too lengthy to include here; see DataTypeEnum in the MSDN Online Library for more information.

Testing Your Stored Procedures

      We have written a test application that is capable of exercising the data access component and any stored procedures that have been written for it. We have found this test application to be invaluable both in verifying the correctness of stored procedures and in visualizing the XML that is returned by the component for any specific stored procedure. In addition, we have modified the data access component to implement a special interface that is only used by the test application. This interface provides a detailed look into the XML being used by the component. We'll discuss this in more detail shortly.


Figure 9 The Test Application

      To use the test application, run spdatest.exe in the downloaded code. The test application is shown in Figure 9. It is hardcoded to use the Northwind_SQL data source. To view the list of procedures available in this data source, click the down arrow by the Stored Procedure Name combobox. The test application queries the data source and displays the stored procedure names in the dropdown list. Note that the list shows all of the stored procedures in the database, not just those that were written for this architecture. Therefore, be careful to select a stored procedure that you know is written for the architecture. We have supplied these:

• Customer_ListCustomers
• Employee_GetDetail
• Employee_GetIDFromOrder
• Order_CreateOrder
• Order_GetDetail
• Order_UpdateOrder
• Product_ListProducts
• ValidEmployee

For our purposes, we'll have a look at the Employee_GetDetail stored procedure.
      After selecting or entering a stored procedure name in the combobox, click the List Parameters button to retrieve the list of parameters that this stored procedure requires. The test application queries the data source for this information (see the sidebar "Programmatically Discovering the Parameters of a Stored Procedure" for more information). See the left side of the test application in Figure 10 for the results of the query.


Figure 10 The Returned XML

      Figure 9 shows that the Employee_GetDetail stored procedure has a return value and a single input parameter called EmployeeID. You must supply a value for this parameter before invoking the stored procedure. To do so, double-click on the parameter name, enter the desired value in the Value textbox, and click the Update button. Now click the Call Stored Procedure button to invoke the stored procedure. The test application uses the data you have provided to create an XML string to pass to the data access component's GetDataXML method. The component parses this string, calls the correct stored procedure with the parameters given, and then builds an XML return string that encodes the results. This XML is then displayed in the test application, as shown in Figure 10.
      Note that in addition to the Help tab, other tabs appeared in the test application window. These tabs show the various forms of XML that are used and generated by the data access component:

• Parameter List XML shows the internal behind-the-scenes XML passed from the data access component back to the data access adapter once the stored procedure name has been set. This XML lists the parameters for the specified stored procedure, and is used by the adapter to populate its cParameters collection. (This tab is available in the test application immediately after clicking the List Parameters button.)
• Call SP XML shows the internal XML passed from the data access adapter to the data access component to invoke the SQL Server stored procedure. This XML message contains the parameter values for the stored procedure being called.
• Raw Return XML shows the XML returned from the data access component to the adapter upon completion of the stored procedure call. This raw internal XML may include return recordset data or output parameters values from the stored procedure.
• Return XML shows the finished XML recordset data that is returned to the adapter's caller. This XML is exposed as the return value of the cAdapter class's CallSP method. A <NoRecordsReturned/> tag indicates that no recordset data was requested—typical for stored procedures that perform database updates.

Deploying the Components

      For best results, execute the deployment instructions in the order that we specify. All references to folder names are relative to the sample code base directory; in other words, the paths are relative to wherever you saved this code.
      To deploy the SQL Server stored procedures, follow these steps:

1. Start SQL Query Analyzer and log into the server on which you want to create the stored procedures. (It must be a SQL Server deployment that includes the Northwind sample database that ships with SQL Server.)
2. Change the database name in the dropdown list to reflect the name of the database—"Northwind".
3. Open the SQL_XML.sql file in the Database folder.
4. Press the Run toolbar button or the F5 key to run the script against the database to create the stored procedures.

      Note that the script will change the database name to Northwind for you, but it is always good practice to make sure that you are working against the correct database before executing scripts. The script will delete any existing stored procedures that have the same names as those being created. We don't anticipate that this will be a problem since few people have written additional stored procedures for the Northwind database. However, if you have written stored procedures against this database, to avoid losing any work you should make sure that no procedures you have written have the same names as the procedures the script will create.

Deploying the Data Access Components

      The data access component (spda.dll) uses a hardcoded DSN name to open the ADO Connection object to the database server. You will have to create a DSN called "Northwind_SQL" on the machine that will be running the data access component. Use Integrated Security authentication and choose to have the DSN start with the Northwind database.
      If you are unfamiliar with how to create DSNs, check SQL Server Books Online for information.

Deployment in COM+ and Non-COM+ Environments

      The compiled component included in the downloaded source code was compiled for deployment in a non-COM+ environment. However, it was constructed so that moving this component to a COM+ environment requires you to simply recompile the project with a change to a conditional compilation argument and uncommenting some code. See the steps for COM+ deployment (discussed shortly) for details. If you don't plan to deploy in COM+, follow these steps.

1. Navigate to the Components\DataAccess folder.
2. Register spda.dll file in this folder using RegSvr32.exe. For details on registering COM components and DLLs, see "Registering COM Applications".

      To deploy the DataAccess DLL in COM+, follow these steps.

1. Open the DataAccess.vbp project in Visual Basic.
2. Open the cAccessor class and uncomment the line of code that contains "Implements COMSVCSLib.ObjectControl" and make sure there is a reference set to the COM+ Services Type Library.
3. Open the Project Properties and go to the Make tab and change the conditional compilation argument "Compile_MTS = 0" to "Compile_MTS = -1".
4. Recompile the project and then place the resulting DLL in a COM+ package.

      Note: you will have to export the package that contains this DLL to an MSI installer package for other client machines to get a reference to it. Please see "Component Services Help" in the Platform SDK for details on exporting packages.
      You can also run this component in Microsoft Transaction Server (MTS) in Windows NT® 4.0. To do this, set a reference to the MTS object control library and then change the Implements statement to point to the MTS library instead of the COM+ services library.

Deploying the Data Access Adapter Component

      The data access adapter component (spdaad.dll) must be deployed on the same machine that houses the business services and also on any machine that runs the test utilities.

1. Navigate to the Components\DataAccessAdapter folder.
2. Register spdaad.dll file in this folder using RegSvr32.exe. If this DLL is deployed on a different machine than the DataAccess DLL, you are going to have to communicate with the DataAccess DLL through DCOM. If the DataAccess DLL is hosted in COM+, it can be exported as an MSI installer package and run on the client machines to get the reference to it. Again, you can see "Component Services Help" in the Platform SDK for details on exporting packages.

Deploying the Middle Tier and COMASP Component

      The middle tier is the business services tier for this project. To deploy spdamid.dll, follow these steps:

1. Navigate to the Components\MiddleTier folder.
2. Register spdamid.dll in this folder using RegSvr32.exe.

      The COMASP component (spdaui.dll) needs to be deployed on the machine that will be running the Web server. Note that you must also deploy the XSL folder and all of its contents to the same folder on which the COMASP DLL is deployed and registered.

1. Navigate to the Components\COMASP folder.
2. Register spdaui.dll file in this folder using RegSvr32.exe.

Deploying the ASP Pages

      In order to deploy the ASP pages, you'll need to follow each of these steps:

1. Copy the ASP folder and all of its contents and subfolders to a location on the machine that will be serving these pages with IIS. You don't have to move the folder if you don't want to. Just point the virtual directory (which you will be creating in the next step) to the original location.
2. Create a new virtual directory on the IIS server that will be serving these pages. Point that virtual directory to the location on the machine where the ASP folder resides.
3. Open the Properties dialog for the new virtual directory created and choose the Directory Security tab. Click on the Edit button in the Anonymous access and authentication control frame. From this new dialog, click on the Edit button in the Anonymous access frame. Choose yourself or someone else that has permission to execute stored procedures against the Northwind database. Type in your password and deselect the "Allow IIS to control password" checkbox. Click on OK on all of the open dialogs to save the changes. You have to do this because IIS passes in the security information of the Anonymous user. Since this user typically doesn't have access to execute stored procedures in the database, we are simply changing the account IIS uses for the Anonymous user to an account that does have this access.

Deploying the Test Application

      Run the test application (spdatest.exe) on a machine that has the data access adapter component correctly installed.
      The editor used to prepare the solution provided by this article was XMLwriter (https://www.xmlwriter.com). For a list of editors that are available, review the XML/XSL editors list at the O'Reilly XML.com site (https://www.xml.com). Bear in mind that some editors were designed specifically with the XML to HTML transformations in mind, but they don't handle XML to XML transformations very well. You just have to find one that meets your needs.

Conclusion

      This technique was successful for us. It took about a week to write the generic components. After that, getting data between the front end and the database was simply a matter of passing XML. Passing the data as XML facilitated the insertion of machine boundaries because all data is passed by value, rather than by object reference. Having the data in XML format facilitates front-end development—to change the appearance of a screen, you simply change the XSL transform that is applied to the XML.