The templates we’ve considered so far in this chapter are stored on the server. Keeping your templates on the server is a good way to control the data that users can access because only the queries defined in your server-side templates can be used. However, you might on occasion want to allow the template to be defined dynamically on the client and submitted to the SQL Server virtual directory. You would typically use this approach to avoid the necessity of creating a template virtual name and defining the server-side templates.
Templates can be posted to the server over HTTP, which allows you to design Web pages or client applications on which you construct a template dynamically before you submit it via the virtual directory. The results of the queries in the template are then returned to the client as an HTTP response. To enable this kind of database access, you must select both the Allow URL Queries and Allow POST options on the Settings tab of the Virtual Directory Properties dialog box in the Configure SQL XML Support In IIS MMC snap-in tool.
To post a template from a Web page, the easiest approach is to use a hidden form field to contain the template. For example, Northwind Traders might choose to use client-side templates in an intranet application that allows employees to retrieve customer data. You could use an HTML page similar to the following sample to retrieve details about a specific customer:
<HTML> <HEAD> <TITLE>Customer Details</TITLE> </HEAD> <BODY> <FORM action=‘http://webserver1/northwinddata’ method=‘POST’> <B>Employee ID Number</B> <INPUT type=text name=EmployeeID value=‘1’> <INPUT type=hidden name=xsl value=employee.xsl> <INPUT type=hidden name=template value=‘ <employeedata xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:header> <sql:param name="EmployeeID">1</sql:param> </sql:header> <sql:query> SELECT FirstName, LastName FROM Employees WHERE EmployeeID=@EmployeeID FOR XML AUTO </sql:query> </employeedata> ‘> <P><input type=‘submit’> </FORM> </BODY> </HTML>
You could also find this sample in the Demos\Chapter5 folder on the companion CD in a file named employee.html. In this code sample, you can see how the template is implemented as a hidden form field named template. This field is assigned a string value containing the XML template. Any parameters are automatically reconciled with HTML input elements of the same name, so in this example, the value posted to the server in the Employee ID Number text box will be used as the EmployeeID parameter in the template.
You can also see how you can apply an XSL style sheet to the data by including an HTML input element named xsl. The value for this input element should be a reference to an XSL file. The style sheet is processed on the server, and the HTML response is sent back to the browser.
Ifyou want to return XML to the browser, simply include a hidden HTML input element named contenttype with a value of text/xml.
Figure 5-5 shows what this sample looks like in Internet Explorer 5.
Figure 5.5 - The Customer Details page
The advantage of posting templates from HTML pages is that you can allow users to access HTML pages in a conventional Web site without having to manage a set of template files in a SQL Server virtual name. You will, however, still need to create a SQL Server virtual directory to post the templates to. The main disadvantage is that the SQL used to retrieve the data is no longer encapsulated and can be viewed in the source code of the HTML page containing the template.
You can also post templates from non-HTML client applications, such as COM+ components or Microsoft Windows applications. This procedure allows you to define queries on the client application without using ADO to communicate with the database server, thus forcing all requests to be routed through a Web server and potentially a firewall.
For example, you could use a COM+ component containing the following method to retrieve data from the Orders table in the Northwind database:
Public Function getOrderData(intOrderNo As Integer) Dim xmlHttp As MSXML2.xmlHttp Dim doc As MSXML2.DOMDocument Dim strQry As String ‘ Construct the template. strQry = "<?xml version=‘1.0’ ?>" strQry = strQry & "<orderdetails>" strQry = strQry & "<sql:query " strQry = strQry & "xmlns:sql=‘urn:schemas-microsoft-com:xml-sql’>" strQry = strQry & " SELECT orderid, orderdate, shipname FROM orders " strQry = strQry & " WHERE orderid = " & intOrderNo strQry = strQry & "FOR XML AUTO" strQry = strQry & "</sql:query>" strQry = strQry & "</orderdetails>" ‘ Load the template into the MSXML parser. Set doc = New MSXML2.DOMDocument doc.loadXML strQry ‘ Post the template. Set xmlHttp = New MSXML2.xmlHttp xmlHttp.Open "POST", "http://webserver1/northwinddata", False xmlHttp.setRequestHeader "Content-Type", "application/xml" xmlHttp.send doc ‘ Retrieve the results. getOrderData = xmlHttp.responseText End Function
Code similar to this is saved as post.vbs in the Demos\Chapter5 folder on the companion CD. In this example, the template is constructed as a string and then loaded into the MSXML parser, which allows your code to validate the XML template before sending it to the server. Next the template is posted to the server using the XMLHTTP object provided in the MSXML library. Notice that the Content-Type parameter in the request header is set to application/xml so that the data retrieved by the template will be returned to the client as an XML document. Finally the query results are read from the responseText property of the XMLHTTP object.
This approach to using templates means that most of the development effort is focused on the client application or business components. However, because posting templates requires that the virtual directory allow URL queries, you’re relying solely on database security permissions to restrict access to data. You should therefore carefully consider the design of your application and use server-side templates where possible because of the greater security they offer.