C#: A SQL Server XML and ASP.NET Runtime Application

Article
04/05/2007

Carl Nolan
Microsoft Corporation

March 2002

Summary: Details the construction of a Web-based customer service solution using the Microsoft .NET Framework, Microsoft SQL Server 2000 XML functionality, and XSLT documents. With Microsoft ASP.NET runtime support, forms authentication mechanism, and the system XML classes, creating high performance and scalable XML-based inquiry systems is greatly simplified. (34 printed pages)

Download Csharpsqlxmlhttpapplication.exe.

Introduction
The .NET Framework Application
Presentation Layer
XML Data Layer
Data Access Layer
Security Implementation
Http Application Layer
Web Service Extensions
Conclusion

Introduction

Many years ago I had the fortune to work in the design of a Web-based customer service solution. With the advent of Microsoft® SQL Server XML functionality and the Microsoft .NET Framework, I decided to once again re-visit this solution to develop a similar application utilizing the Microsoft .NET Framework, SQL Server 2000 XML functionality, and XSLT documents.

The functionality of the application was simple: to allow customers to view customer, order, and order detail information in a secure environment. The secure environment is such that authenticated users are restricted to a defined customer set. Finally, a set of administrative users—their purpose being to assist generic users through the same user interface—required unrestricted data access.

If one is familiar with SQL Server URL Query access, a solution using template queries and HTML rendering stylesheets should present itself. The limiting factor for the pure SQL Server solution would be the inability to provide a flexible authentication system from which the authorization system is dependant; hence the reason for this application.

The .NET Framework Application

The application to be presented consists of the following pieces, a Microsoft ASP.NET Http handler architecture for processing requests, a customer request class for controlling authorization checks and HTML rendering, XSLT documents for XML to HTML transforms, customer order and customer security classes, XML returning stored procedures, and the physical database layer, in addition to a (forms) authentication mechanism.

Figure 1. Northwind schema

The data store used for the application was the sample Northwind database; the relevant tables are outlined in Figure 1.

Presentation Layer

The driving force behind the application design was the presentation layer. The premise of the developed solution was to present, using an HTML interface, customer, order, and order details information from the Northwind database. This was in support of two types of users, the administrative and generic user.

The first step in the application is the user authentication, from which the user is logged into the system. This is achieved using the .NET forms authentication mechanism that persists user login information in the form of an Http cookie. Other authentication solutions that could have been used include Microsoft Windows® and Microsoft .NET Passport.

After login the presentation path was dependant on the user type. The administrative user is defined as having the capability to view all customer information. For this reason, the administrative user's home page is a list of countries for which there are customers; selecting a country from the list will display the appropriate list of customers. The generic user is defined as having access to a predefined list of customers. For this reason, the generic user's home page is that list of customers for which the generic user has authorization. Once a list of customers has been presented, HTML navigation will present a customer detail page, a list of customer orders, an order detail summary page, and a complete customer and order summary page.

Table 1. Presentation flow

Page Description	Operation	XML Stored Procedure Style Sheet
Country List	GetCustomersCountries	xml_customer_cty_list customercountry.xslt
Customers for Country	GetCustomersByCountry	xml_customer_cty customerlistcty.xslt
Customers for User	GetCustomersByUser	xml_customer_user customerlistuser.xslt
Customer Detail	GetCustomerById	xml_customer_id customerheader.xslt
Order Listing	GetCustomerOrders	xml_customer_orders customerorders.xslt
Order Details	GetCustomerOrderDetails	xml_order_details customerorderdetails.xslt
Customer and Order Summary	GetCustomerSummById	xml_customer_summary customersummary.xslt

The application creates each HTML page through the use of an XML-returning stored procedure, which is then transformed into HTML using an XSLT document. Table 1 outlines each page, the name of the operation to support the XML retrieval, the name of the associated XML returning stored procedures, and finally the corresponding XSLT document from which the HTML is generated.

XML Data Layer

As previously stated, the application makes use of SQL Server 2000 XML functionality, each operation in Table 1 has a corresponding XML-returning stored procedure. In designing these stored procedures, the structure of the resultant XML required consideration. Several techniques could be used, as this application demonstrates.

To retrieve XML results directly from the database, the FOR XML clause of the SELECT statement is used; with one of either three modes RAW, AUTO, and EXPLICIT.

RAW mode means each row in the resulting XML has the generic row identifier. AUTO mode returns query results in a simple nested XML tree. Each table in the FROM clause, listed in the SELECT statement, is represented as an XML element of the same name. The SELECT columns are then mapped as attributes of the elements. The hierarchy is determined based on the order of the tables identified by the columns of the SELECT statement. To structure the XML identifiers column, alias names should be used.

EXPLICIT mode specifies the shape of the XML tree, the query specifying all the information to produce what is known as a universal tree. This means that the query must, in addition to specifying the required data, specify all the meta data. Other important benefits of EXPLICIT mode are that columns can be individually mapped to either attributes or sub-elements and one can generate sibling hierarchies.

In writing XML queries, one must remember that the XML identifier names are case sensitive, important when performing the XSLT transforms. In addition within the XSLT documents one also has to remember to prefix the XML identifiers with @ symbols for attributes.

Modifying XML Hierarchy with Views

The first challenge in formulating the XML came when the XML hierarchy required flattening; information for a single hierarchal layer is derived from more than a single table. To achieve this, the XML query can use views rather than underlying tables. The view, being seen as a single table, flattens the resulting XML hierarchy.

This was a requirement for presenting customer order information. In this case, one needed to present customer order information, with each order element containing shipper information:

SELECT Customer.CustomerID CustomerId,
   Customer.CompanyName CompanyName, ContactName, OrderID OrderId,
   CONVERT(CHAR(12),OrderDate, 107) OrderDate,
   CONVERT(CHAR(12),ShippedDate, 107) ShipDate,
   ShipName, Freight,
   (SELECT COUNT(*) FROM [Order Details] OrderDets
      WHERE OrderDets.OrderID = Orders.OrderID) ProductCount,
   Orders.CompanyName ShipCompany, Orders.Phone ShipPhone
FROM Customers Customer
INNER JOIN dbo.view_orders Orders
   ON Customer.CustomerID = Orders.CustomerID
WHERE Customer.CustomerID = @custid
FOR XML AUTO, ELEMENTS

The VIEW in the query is simple INNER JOIN query:

SELECT Orders.*, Shippers.*
FROM Orders
INNER JOIN Shippers ON Orders.ShipVia = Shippers.ShipperID

In the generated XML, the view is treated as a single hierarchy; namely Orders.

FOR XML EXPLICIT Queries

For queries requiring a more complex XML structure, the FOR XML EXPLICIT clause can be used. EXPLICIT mode is used for the order details and customer order summary pages. As stated, the structure of the XML hierarchy is determined by a generated universal table, thus the use of views are only relevant for query simplification. Take for example the query for the order detail information:

SELECT 1 Tag, NULL Parent,
   Orders.CustomerID [CustomerOrder!1!CustomerId],
   Customers.CompanyName [CustomerOrder!1!CompanyName!element],
   Customers.ContactName [CustomerOrder!1!ContactName!element],
   Orders.OrderID [CustomerOrder!1!OrderId],
   CONVERT(CHAR(12),OrderDate, 107) [CustomerOrder!1!OrderDate!element],
   CONVERT(CHAR(12),ShippedDate, 107) [CustomerOrder!1!ShipDate!element],
   Orders.ShipName [CustomerOrder!1!ShipName!element],
   Orders.Freight [CustomerOrder!1!Freight!element],
   Orders.CompanyName [CustomerOrder!1!ShipCompany!element],
   Orders.Phone [CustomerOrder!1!ShipPhone!element],
   Orders.ShipAddress [CustomerOrder!1!ShipAddress!element],
   Orders.ShipCity [CustomerOrder!1!ShipCity!element],
   Orders.ShipPostalCode [CustomerOrder!1!ShipPostalCode!element],
   Orders.ShipCountry [CustomerOrder!1!ShipCountry!element],
   NULL [OrderDetails!2!ProductId],
   NULL [OrderDetails!2!ProductName!element],
   NULL [OrderDetails!2!UnitPrice!element],
   NULL [OrderDetails!2!Quantity!element],
   NULL [OrderDetails!2!DiscountPercent!element],
   NULL [OrderDetails!2!ExtendedPrice!element]
FROM Customers
INNER JOIN dbo.view_orders Orders
   ON Customers.CustomerID = Orders.CustomerID
WHERE Customers.CustomerID = @custid
AND Orders.OrderID = @order

UNION ALL

SELECT 2, 1,
   Orders.CustomerID [CustomerOrder!1!CustomerId],
   NULL [CustomerOrder!1!CompanyName!element],
   NULL [CustomerOrder!1!ContactName!element],
   Orders.OrderID [CustomerOrder!1!OrderId],
   NULL [CustomerOrder!1!OrderDate!element],
   NULL [CustomerOrder!1!ShipDate!element],
   NULL [CustomerOrder!1!ShipName!element],
   NULL [CustomerOrder!1!Freight!element],
   NULL [CustomerOrder!1!ShipCompany!element],
   NULL [CustomerOrder!1!ShipPhone!element],
   NULL [CustomerOrder!1!ShipAddress!element],
   NULL [CustomerOrder!1!ShipCity!element],
   NULL [CustomerOrder!1!ShipPostalCode!element],
   NULL [CustomerOrder!1!ShipCountry!element],
   OrderDetails.ProductID [OrderDetails!2!ProductId],
   OrderDetails.ProductName [OrderDetails!2!ProductName!element],
   UnitPrice [OrderDetails!2!UnitPrice!element],
   Quantity [OrderDetails!2!Quantity!element],
   CAST((Discount*100) AS NUMERIC(7,2))
      [OrderDetails!2!DiscountPercent!element],
   (UnitPrice * Quantity * (1-Discount))
      [OrderDetails!2!ExtendedPrice!element]
FROM dbo.view_orders Orders
INNER JOIN dbo.view_orderdetails OrderDetails
   ON Orders.OrderID = OrderDetails.OrderID
WHERE Orders.CustomerID = @custid
AND Orders.OrderID = @order

ORDER BY [CustomerOrder!1!CustomerId],
   [CustomerOrder!1!OrderId], [OrderDetails!2!ProductId]
FOR XML EXPLICIT

One of the main tasks in using EXPLICT mode is ensuring that the Tag and Parent properties are correctly set. Hence, in this case a UNION ALL is used to return the required Customer and then Order information. The final ORDER BY is important to correctly relate the orders with the appropriate customer. The CAST function is used to ensure correct datatype is returned from the query: numeric rather than a float.

As one can see, EXPLICIT mode, although verbose, offer greater flexibility in the generation of the resulting universal tree. To get a better understanding of this concept of a universal table, run the previous query without the FOR XML EXPLICIT clause. The output will be the universal table.

Data Access Layer

As in most applications, access to the database is controlled though a data access layer; the CustomerOrders class. The purpose of this class is to abstract the stored procedure calls and present XML back to the Http application. The chosen return data type is an XPathDocument, rather than an XmlDocument. The reasons are twofold: The resultant XML obtained from SQL Server is a document fragment (may not have a root node) and hence cannot be loaded directly into an XmlDocument, and secondly an XPathDocument offer the best performance for performing transforms.

Obtaining and Returning XML

The structure of each method call with the class is identical: format a SQLCommand and its parameters, set its active connection, execute the XML reader, and return the constructed XPath document. As only the SQLCommand settings differentiate the data access method calls, the XML processing is managed by a private method:

private XPathDocument CommandToXPath(SqlCommand northwindCom)
{
   // setup the local objects
   SqlConnection northwindCon = null;
   XmlReader xmlReader = null;
   XPathDocument xpathDoc = null;
   // set base command options
   northwindCom.CommandType = CommandType.StoredProcedure;
   northwindCom.CommandTimeout = 15;
   // now execute the command
   try
   {
      // setup the database connection
      northwindCon = new SqlConnection(dbConnectionString);
      northwindCon.Open();
      northwindCom.Connection = northwindCon;
      // execute the command and place into an Xpath document
      xmlReader = northwindCom.ExecuteXmlReader();
      xpathDoc = new XPathDocument(xmlReader, XmlSpace.Preserve);
   }
   catch (Exception ex)
   {
      throw new ApplicationException
         ("Cannot Execute SQL Command: " + ex.Message, ex);
   }
   finally
   {
      // dispose of open objects
      if (xmlReader != null) xmlReader.Close();
      if (northwindCon != null) northwindCon.Close();
   }
   return xpathDoc;
}

The ExecuteXmlReader method of the SqlCommand class returns an XmlReader representing the returned XML. This reader is used to construct the XPath document. Prior to making this method call, the calling method will create a new SqlCommand, specify which stored procedure to execute, and define the appropriate parameters collection.

Class Methods

As one can again see from Table 1, the class methods correspond 1-to-1 with an XML-returning stored procedures and a Web application-presenting function. Figure 2 outlines the public and private methods for the CustomerOrders class. The class methods are all simple get methods, and return an XPath document representation of the required XML.

Figure 2. CustomerOrders Class diagram

The only work of the class methods is to define the stored procedure and associated parameters. No authorization checks are performed, that was done in an earlier step. Consider the method to return the details of a customer order:

public XPathDocument GetCustomerOrderDetails
   (string customerId, int orderId)
{
   // setup the command object to return the XML Document Fragment
   SqlCommand northwindCom = new SqlCommand("xml_order_details");
   // set up the stored procedure parameters
   SqlParameter customerParam = new SqlParameter
      ("@custid", SqlDbType.NChar, 5);
   customerParam.Direction = ParameterDirection.Input;
   customerParam.Value = customerId;
   northwindCom.Parameters.Add(customerParam);
   SqlParameter orderParam = new SqlParameter
      ("@order", SqlDbType.Int);
   orderParam.Direction = ParameterDirection.Input;
   orderParam.Value = orderId;
   northwindCom.Parameters.Add(orderParam);
   // return the XPath document
   return CommandToXPath(northwindCom);
}

As one can see, all the XML work is accomplished in the CommandToXPath method.

Security Implementation

At this point, a word is warranted on the security implementation. As was already mentioned, the CustomerOrders class makes no assumptions regarding security; that is left to the Http application. The security mechanisms are supported through database entities, stored procedures, and a security class, along with ASP.NET forms authentication mechanism.

Table 2. Security operations

Description	Operation
Authenticate the user. Used on the Login page to validate the user name and password.	ValidateUserLogin
Obtain user information. Used on the default page to display the user name and other information.	GetUserInfo
Authorize the user for the customer. For a generic user request, validate read access to the requested customer.	ValidateUserCustomer
Obtain a list of administrative users. Within the Http application, used to determine if the authenticated user is an administrator.	GetAdminUsers

The basic operations required to support authentication and data authorization are listed in Table 2.

Physical Data

Throughout this application the data model used is that of the Northwind database. However, for the requirements of mapping users to customers and supporting authentication and authorization, extensions were needed.

To isolate the security implementation, a separate database was developed called NWSecurity, consisting solely of two tables: the first is a user table in which administrative users are marked with a bit flag:

CREATE TABLE dbo.NWUser (
   UserName         NVARCHAR(64)   NOT NULL,
   EmailAddress   NVARCHAR(128)   NOT NULL,
   UserPassword   NVARCHAR(64)   NOT NULL,
   FirstName      NVARCHAR(64),
   LastName         NVARCHAR(64),
   LastAuth         DATETIME         DEFAULT NULL,
   AdminUser      BIT             DEFAULT 0,
   CONSTRAINT PK_NWUser PRIMARY KEY (UserName),
   CONSTRAINT UQ_NWUser_Email UNIQUE (EmailAddress)
)

The second table is a user customer table defining customer assignments. For a valid authorization check, a user is either an administrator or is allowed access to that customer:

CREATE TABLE dbo.NWCustomer (
   UserName         NVARCHAR(64)   NOT NULL,
   CustomerID      NVARCHAR(5)      NOT NULL,
   LastViewed      DATETIME         DEFAULT NULL,
   AllowAccess      BIT             DEFAULT 1,
   CONSTRAINT PK_NWCustomer PRIMARY KEY (UserName, CustomerID),
   CONSTRAINT FK_NWCustomer_NWUser FOREIGN KEY (UserName)
      REFERENCES NWUser (UserName)
      ON UPDATE CASCADE
      ON DELETE CASCADE
)

The NWSecurity database contains several stored procedures that support security operations: validation of a user to access customer information, validation of a login user name and password, retrieval of user information, and retrieval of an administrative user list. Table 3 lists these stored procedures based on the calling class method.

Table 3. Security stored procedures

Class Method	Stored Procedure
ValidateUserLogin	usp_validate_user_login
GetUserInfo	usp_get_user_info
ValidateUserCustomer	usp_validate_user_customer_read
GetAdminUsers	usp_admin_users

In designing the stored procedures, the assumption was made that most users of the system will be generic users. For this reason, a read of the table NWCustomer is performed in preference to that of NWUser, used for an administrative user check. This is important when one considers every request for a generic user is preceded with an authorization check.

Security Class

Once again, as with the application data access layer, the methods of the security class correspond 1-to-1 with a stored procedure. A CustomerSecurity class, as outlined in Figure 3, acts as an abstraction layer between the application and the physical data.

Figure 3. CustomerSecurity Class diagram

In the case of the ValidateUserLogin, the return value is an enumeration:

public enum LoginReturnCode
{
   NoAccess = 0,
   AccessIncorrectPassword = 1,
   AccessAuthenticated = 2
}

The values of the enumeration match with the values returned from the corresponding stored procedure. Thus in this scenario, the non-query stored procedures return value can be cast to the enum type:

securityCom.ExecuteNonQuery();   
LoginReturnCode lrcValue = (LoginReturnCode)returnParam.Value;

The GetAdminUsers method differs from the others in that it returns a simple array of administrative user names:

public Array GetAdminUsers()
{
   // define array list to hold user names
   ArrayList userList = new ArrayList();
   using (SqlConnection securityCon = GetDbConnection())
   {
      using (SqlCommand securityCom =
         new SqlCommand("usp_admin_users", securityCon))
      {
         securityCom.CommandType = CommandType.StoredProcedure;
         // execute the command to obtain the resultant dataset
         SqlDataReader dataNW =
            securityCom.ExecuteReader(CommandBehavior.CloseConnection);
         // with the data reader parse values into a searchable array
         while(dataNW.Read())
         {
            userList.Add((string)dataNW["UserName"]);
         }
         dataNW.Close();
      }
   }
   // convert array list into an Array and return
   Array userArray = userList.ToArray(typeof(String));
   Array.Sort(userArray);
   return userArray;
}

Using the SqlConnection and SqlCommand (objects disposed of when they go out of scope), a SqlDataReader is constructed. The data reader is then parsed to obtain the list of user names, their values being placed into an array list. The array list is then simplified as an array of string variables containing user names.

Forms Authentication

The tenet behind authentication is validating a user for access into the system. For all users, whether an administrative or generic user, authentication against the user database table is performed.

To force authentication a custom forms implementation was implemented. Within the Web application Web.config file the authentication and authorization sections are modified to force forms authentication and deny access to the anonymous user; other supported mechanisms are Windows and Passport authentication:

<authentication mode="Forms">
   <forms name="CustomerServiceApp" loginUrl="login.aspx"
      protection="All" timeout="30" path="/" />
</authentication>
<authorization>
   <deny users="?" />
</authorization>

The next task was writing the login.aspx page to validate users against the database. The purpose of this page is to allow the user to enter a user name and password, validate them against the database, persist a user token within a cookie for later invocation, and finally direct the user back to the originally requested page. Again, the .NET Framework makes all these tasks surprisingly simple.

The UI for the page is very simple, two text boxes, one for the user name and the other for the password, a check box to enable the user to decide if the authentication will persist between browser sessions, and finally a Login button; all are standard HTML tags with a RUNAT tag of server. Simple server-side Web form controls are used to ensure values are entered in the user name and password fields. In addition, a server-side label is used for feedback to the user.

All the actual work of authentication is performed in server-side code. Upon page posting, the entered credentials are validated using the ValidateUserLogin method of the CustomerSecurity class:

// get required query string parameters
string userName = UserName.Value;
string userPassword = UserPassword.Value;
// create a customer security object and make call to validate the user
CustomerSecurity customerSecurity = new CustomerSecurity();
CustomerSecurity.LoginReturnCode loginReturn =
   customerSecurity.ValidateUserLogin(userName, userPassword);

Here, UserName and UserPassword are the initialized server-side controls. If the appropriate login return code is received, the user is authenticated and redirected to there requested page:

if (loginReturn == CustomerSecurity.LoginReturnCode.AccessAuthenticated)
{
   FormsAuthentication.RedirectFromLoginPage
      (UserName.Value, PersistForms.Checked);
}

The static RedirectFromLoginPage method redirects the user to the originally requested page, after issuing an authentication ticket. The second parameter accepts a Boolean value specifying whether or not a durable cookie is issued. This is derived from the PersistForms HTML check box.

The final important piece of the authentication mechanism is deciding the roles in which to place the authenticated user. Included with the application global.asax file is an authentication event that allows one to redefine a user principal from the original user identity. At this time, user roles can be defined.

protected void Application_AuthenticateRequest
   (Object sender, EventArgs e)
{
   HttpContext context = HttpContext.Current;
   if (!(context.User == null))
   {
      if (context.User.Identity.AuthenticationType == "Forms" )
      {
         string userName = context.User.Identity.Name;
         string[] userRoles = new string[1];
         // define the role based on locating a admin user
         if (Array.BinarySearch(GetAdminUsers(Context), userName) >= 0)
         {
            userRoles[0] = "Admin";
         }
         else
         {
            userRoles[0] = "Generic";
         }
         // create the new generic principal
         GenericPrincipal gp = new GenericPrincipal
            (context.User.Identity, userRoles);
         context.User = gp;
      }
   }
}

The user role is defined as either Administrative or Generic. The GetAdminUsers method caches and returns an Array of administrative users; more about this below.

Http Application Layer

The Http application utilizes the ASP.NET Http runtime support; a logical replacement for ISAPI extension and filter APIs, giving one a means of interacting with the low-level request and response services of the Microsoft IIS Web server. The following are the key interfaces that are utilized within the application:

IHttpHandler: Implemented to process synchronous Http requests. The ProcessRequest method must be implemented to provide custom URL execution.
IHttpAsyncHandler: Implemented to process asynchronous Http requests. The ProcessRequest method is implemented through the BeginProcessRequest and EndProcessRequest methods.
IHttpHandlerFactory: Implemented to create new IHttpHandler objects. The sole purpose being to dynamically manufacture new handler objects that implement the IHttpHandler interface.

An IHttpHandlerFactory implementation processes each request by looking at the authenticated user. For administrative users, an IHttpHandler implementation called CustomerAdmin is returned. For generic users, an IHttpAsyncHandler implementation called CustomerGeneric, is returned.

To support the handler implementations, a CustomerRequest class is used as outlined in Figure 4. This class has a publicly exposed method named ProcessRequest. Using the provided HttpContext, it will review the Http request and output the required HTML. This class in turn consumes the CustomerOrders and CustomerSecurity classes for data access and security checking.

Figure 4. CustomerRequest Class diagram

As a single Http handler application is processing all requests, the appropriate function and rendered HTML is determined by a Function parameter passed in the URL query string; the appropriate user's home page is displayed when the function is not present. Table 4 outlines these function codes and the supporting CustomerOrders methods.

Table 4. Application function codes

Administrative Function	Generic User Function	Class Methods
null		GetCustomersCountries
CC		GetCustomersByCountry
	null	GetCustomersByUser
CH	CH	GetCustomerById
OS	OS	GetCustomerOrders
OD	OD	GetCustomerOrderDetails
CS	CS	GetCustomerSummById

Based on this function parameter, the appropriate class method is called, which returns the XML representation of the data. This is then transformed into HTML using the appropriate XSLT document and passed into the Http Response stream.

IHttpHandler Implementations

For an administrative user, a CustomerAdmin handler instance is returned that implements IHttpHandler. For a generic user, the CustomerGeneric handler is returned that implements IHttpHandlerAsync.

So how does one determine an administrative user? This is where the concept of role-based programming comes into play. One merely has to look at the previously defined user role:

if (context.User.IsInRole("Admin")) >= 0)
{
   return (new CustomerAdmin());
}
else
{
   return (new CustomerGeneric());
}

Before outlining the handler implementation, let's discuss the IHttpHandler Interface:

public interface IHttpHandler
{
   void ProcessRequest(HttpContext context);
   bool IsReusable {get;}
}

The single method ProcessRequest is called for each Http request. The given HttpContext object provides references to the intrinsic server objects used to service the Http requests; such as Request, Response, Session, and Server. The IsReusable property indicates if the IHttpHandler instance is reusable. In both the implementations given, the handler will always be considered reusable unless an application exception is thrown.

For exception handling, an ApplicationException-derived class is defined. The purpose of the new Exception class is to provide a property, Terminated, which the handler implementation uses to determine if the handler should remain in the pool. A true value indicates a processing exception such that the handler object should be removed from the processing pool.

The IHttpHandler implementation for an administrative user, CustomerAdmin, is listed below:

public class CustomerAdmin: IHttpHandler 
{
   private bool reuseHandler;
   private CustomerRequest customerRequest;
      
   public CustomerAdmin() 
   {
      // ensure object is to be pooled
      reuseHandler = true;
      // cache the user customer request object
      customerRequest = new CustomerRequest();
   }

   // property to indicate class reuse state
   public bool IsReusable
   {
      get 
      {
         return reuseHandler;
      }
   }

   // process the HTTP request called by the application process
   public void ProcessRequest(HttpContext context) 
   {
      try 
      {
         customerRequest.ProcessRequest(context);
      }
      catch (CustomerRequestException ex) 
      {
         // take handler out of the pool if the application error
         reuseHandler = !ex.Terminated;
         CustomerRequestUtilities.ProcessException(context, ex);
      }
      catch (Exception ex) 
      {
         // take handler out of the pool and display an error page
         reuseHandler = false;
         CustomerRequestUtilities.WriteTraceOutput
            (context, "Process", ex.Message);
         CustomerRequestUtilities.ProcessException(context, ex);
      }
   }
}

The ProcessRequest method creates an instance of the CustomerRequest class, calling the corresponding ProcessRequest method to process the Http request. If the CustomerRequest object throws a CustomerRequestException, the handler has the option to decide if the handler should remain in the execution pool.

In contrast, the handler implementation for generic users implements the IHttpAsyncHandler Interface. This interface has a BeginProcessRequest method that must initiate an asynchronous call to the Http handler. For generic user requests, a separate process performs authorization checks. As this check is performed asynchronously, it made sense to demonstrate the asynchronous handler from the generic user implementation.

To perform the asynchronous call, a delegate is defined for the ProcessRequest method. The compiler generated BeginInvoke and EndInvoke methods are then used to invoke the ProcessRequest method of a CustomerRequest instance, asynchronously:

internal delegate void ProcessRequestDelegate(HttpContext context);

// start the processing of the async HTTP request
public IAsyncResult BeginProcessRequest
   (HttpContext hc, AsyncCallback cb, Object extraData) 
{
   // save the callback reference
   callback = cb;
   context = hc;

   // start the async operation to handle the customer request
   try 
   {
      // create the delegate and reference the callback method
      ProcessRequestDelegate processDelegate = new ProcessRequestDelegate
         (customerRequest.ProcessRequest);
      AsyncCallback processCallback = new AsyncCallback
         (this.ProcessRequestResult);
      // call the compiler created begin invoke method
      IAsyncResult result = processDelegate.BeginInvoke
         (context, processCallback, this);
   }
   catch (Exception ex) 
   {
      // take handler out of the pool and display an error page
      // cannot start the async process - infrastructure error
      reuseHandler = false;
      CustomerRequestUtilities.WriteTraceOutput
      (context, "Process", ex.Message);
      throw ex;
   }

   // return my async result indicating the calling status
   processAsyncResult = new ProcessAsyncResult();
   processAsyncResult.AsyncState = extraData;
   return processAsyncResult;
}

// function to be called upon completion
internal void ProcessRequestResult(IAsyncResult result) 
{
   try 
   {
      // obtain a reference to the original calling class
      ProcessRequestDelegate processCallback = (ProcessRequestDelegate)
         ((AsyncResult)result).AsyncDelegate;
      // call the end invoke capturing any runtime errors
      processCallback.EndInvoke(result);
   }
   catch (CustomerRequestException ex) 
   {
      // take handler out of the pool if the application error
      reuseHandler = !ex.Terminated;
      CustomerRequestUtilities.ProcessException(context, ex);
   }
   catch (Exception ex) 
   {
      // take handler out of the pool and display an error page
      reuseHandler = false;
      CustomerRequestUtilities.WriteTraceOutput
         (context, "Process", ex.Message);
      CustomerRequestUtilities.ProcessException(context, ex);
   }
   finally 
   {
      processAsyncResult.IsCompleted = true;
      callback(processAsyncResult);
   }
}

As one can see, the Http request is being processed in two parts. The BeginProcessRequest initiates the asynchronous call through the delegates BeginInvoke method; errors at this stage are of the infrastructure type. Through the delegate and callback object, the EndInvoke is called upon completion of the asynchronous process to obtain return data; including exceptions.

The status of the Http handler is made known to the calling class through an implementation of the IAsyncResult Interface, called ProcessAsyncResult.

CustomerRequest Class and HTML Rendering

The purpose of the Http handler classes is to manage a CustomerRequest object that performs the HTML rendering and authorization checks. It consumes the CustomerOrders class to obtain the XML information and transforms this into HTML using the appropriate XSLT document. A single exposed method, ProcessRequest, is used:

public void ProcessRequest(HttpContext context) 
{
   // define initial state of the object
   validProcess = 0;
   this.context = context;
   userType = context.User.IsInRole("Admin")?
      UserType.AdminUser : UserType.GenericUser;

   // obtain function code as no security check is required for null
   string functionCode = context.Request.QueryString["Function"];

   // look to see if authorization is required and start the process
   bool performAuthorization;
   if (userType == UserType.GenericUser && functionCode != null) 
   {
      performAuthorization = true;
      // start the thread that performs the security validation
      validSecurity = 1;
      threadSecurity.Start();
   }
   else 
   {
      performAuthorization = false;
      // admin user or null funciton code so security always true
      validSecurity = 0;
   }

   // get the customer XML data and associated stylesheet name
   XPathDocument docCust;
   XslTransform docStyle;
   ReturnCustomerXml(out docCust, out docStyle);

   // if performed a security check join with processing thread
   if (performAuthorization) 
   {
      // join with the security thread with a timeout of 5 seconds
      if (!threadSecurity.Join(2500)) 
      {
         validProcess = 2;
         CustomerRequestUtilities.WriteTraceOutput
            (context, "Security", "Unable to Complete Security Check");
         try 
         {
            threadSecurity.Abort();
         }
         catch (Exception) {}
      }
   }

   // if all process and security valid output the required HTML
   if (validSecurity == 0 && validProcess == 0) 
   {
      // output the required XML and performing the transformation
      docStyle.Transform(docCust, null, context.Response.Output);
   }
   else 
   {
      bool terminated = (validSecurity < 2 && validProcess < 2)?
         false : true;
      // on error throw exception (traces will have been written)
      throw new CustomerRequestException
         ("Process or Security Error encountered.", terminated);
   }
}

The method uses two tri-state values, validProcess and validSecurity, to indicate the state of the processing: 0 indicates all is valid, 1 indicates no errors thrown but unable to process request, and 2 indicates processing errors encountered.

It is in the private ReturnCustomerXML method in which the appropriate CustomerOrders method is called: an XPathDocument and loaded XslTransform are the outputs. The Transform method of the XslTransform then outputs the HTML to the response stream:

private void ReturnCustomerXml
   (out XPathDocument docCust, out XslTransform docStyle)
{
   // define the return values
   docCust = null;
   docStyle = null;
   string styleName = "";

   try
   {
      // define variables for function calls
      string functionCode = context.Request.QueryString["Function"];
      string userName, customerId;
      string countryCode;
      int orderNumber;
      // construct the appropriate XPath Document from function
      switch (functionCode)
      {
         case null:
            if (userType == UserType.AdminUser)
            {
               // obtain a XPath Document of customer countries
               docCust = customerOrder.GetCustomersCountries();
               styleName = "customercountry.xslt";
            }
            else
            {
               // obtain a XPath Document of customer user listing
               userName = context.User.Identity.Name;   
               docCust = customerOrder.GetCustomersByUser(userName);
               styleName = "customerlistuser.xslt";
            }
            break;
         case "cc":
            if (userType == UserType.AdminUser)
            {
               // obtain a XPath Document of customer listing
               countryCode = context.Request.QueryString["Country"];      
               docCust = customerOrder.GetCustomersByCountry
                  (countryCode);
               styleName = "customerlistcty.xslt";
            }
            else
            {
               validProcess = 1;
               CustomerRequestUtilities.WriteTraceOutput
                  (context, "Process", "Function cc not available");
            }
            break;
         case "cs":
            // obtain a XPath Document of customer summary
            customerId = context.Request.QueryString["Customer"];      
            docCust = customerOrder.GetCustomerSummById(customerId);
            styleName = "customersummary.xslt";
            break;
         case "ch":
            // obtain a XPath Document of customer header information
            customerId = context.Request.QueryString["Customer"];      
            docCust = customerOrder.GetCustomerById(customerId);
            styleName = "customerheader.xslt";
            break;
         case "os":
            // obtain a XPath Document of order summary information
            customerId = context.Request.QueryString["Customer"];      
            docCust = customerOrder.GetCustomerOrders(customerId);
            styleName = "customerorders.xslt";
            break;
         case "od":
            // obtain a XPath Document of order detail information
            customerId = context.Request.QueryString["Customer"];      
            orderNumber = Int32.Parse
               (context.Request.QueryString["Order"]);      
            docCust = customerOrder.GetCustomerOrderDetails
               (customerId, orderNumber);
            styleName = "customerorderdetails.xslt";
            break;
         default:
            validProcess = 1;
            CustomerRequestUtilities.WriteTraceOutput
               (context, "Process", "Unknown Function Code");
            break;
      }
   }
   catch (Exception ex)
   {
      validProcess = 2;
      CustomerRequestUtilities.WriteTraceOutput
         (context, "Process", "Error: " + ex.Message);
   }
   // load the appropriate stylesheet for the transform
   if (validProcess == 0) docStyle = GetStyleSheet(context, styleName);
   return;
}

The premise of the ReturnCustomerXml method is simple; using the requested function code call the appropriate CustomerOrders method obtaining the XPathDocument and load the required XslTransform from the derived XSLT file name. To speed up processing, the XSLT documents are preloaded and held in application cache.

Within this class, one will notice the WriteTraceOutput method; its purpose is to write information to the trace file. This provides a mechanism to view informational messages regarding processing exceptions through the trace.axd page.

User Authorization

Authorization differs from authentication in that it validates all requests, not for access to the application, but to ensure the user is restricted to a defined subset of data. This is achieved by restricting a user to a set of assigned customers, as defined in the NWCustomer table.

For generic users, a dedicated processing thread is used to perform authorization checks through the ValidateCustomerSecurity method. The validity of the check is indicated by the appropriate tri-state value:

private void ValidateCustomerSecurity()
{
   // query string variables
   string customerId = context.Request.QueryString["Customer"];
   string userName = context.User.Identity.Name;
   if (customerId != null && userName != null)
   {
      try 
      {
         // check against the database for the return code
         if (customerSecurity.ValidateUserCustomer(userName, customerId))
         {
            validSecurity = 0;
         }
         else
         {
            validSecurity = 1;
            CustomerRequestUtilities.WriteTraceOutput
               (context, "Security", "Customer/User not Valid");
         }
      }
      catch (Exception ex)
      {
         validSecurity = 2;
         CustomerRequestUtilities.WriteTraceOutput
            (context, "Security", "Error: " + ex.Message);
      }
   }
   else
   {
      validSecurity = 1;
      CustomerRequestUtilities.WriteTraceOutput
         (context, "Security", "Customer/User not Specified");
   }
   return;
}

When processing the Http request, the security thread is started and later joined with the processing thread using the Join method. At this point the security status is validated prior to rendering the HTML.

Object Pooling and Application Caching

Twice it has been mentioned that static data has been cached within the application for an array of administrative users and loaded XSLT documents. Markedly improved performance is noticed when data is cached rather processed for each request.

The current HttpContext manages a reference to an application Cache object. Data cached within this object may be marked invalid by various mechanisms including a defined time span, an absolute time, and a file change notification event.

The Authentication event uses the context cache for the array of administrative users—the array is obtained and loaded by the GetAdminUsers method:

private Array GetAdminUsers(HttpContext context)
{
   string adminUsersFile = "adminusers.xml";

   // obtain a reference to the admin users list from the cache
   Array usersArray = (Array)context.Cache[adminUsersFile];
   // the the cached items does not exist the load from the server
   if (usersArray == null)
   {
      try
      {
         // get the current list of the admin users
         CustomerSecurity customerSecurity = new CustomerSecurity();
         usersArray = customerSecurity.GetAdminUsers();
      }
      catch (Exception ex)
      {
         // if an error just create a blank array
         // so the cache will not try and load for each request
         usersArray = new ArrayList().ToArray(typeof(String));
         CustomerRequestUtilities.WriteTraceOutput
            (context, "Security", ex.Message);
      }
      // place the admin users array in cache for 20 minutes
      string adminUsersPath = context.Server.MapPath
         ("//CustService/adminusers.xml");
      context.Cache.Insert(adminUsersFile, usersArray,
         new CacheDependency(adminUsersPath),
         DateTime.Now.AddMinutes(20), TimeSpan.Zero);
   }
   // return the array of admin users
   return usersArray;
}

One should also notice that the cache has a dependency on an XML file in addition to a 20-minute expiration. Included with the code is a script that will generate this file. One can use this as a mechanism to refresh the cache upon changing the list of administrative users.

In a very similar fashion, the CustomerOrders class has a GetStyleSheet method that returns a loaded XSLT document from the cache. The code in this case is very similar to the administrative user array functionality. Initially the cache object is obtained and cast to an XslTransform object:

XslTransform docStyle = (XslTransform)context.Cache[styleName];

If the cache is empty, never loaded, or invalidated, the appropriate XSLT document is loaded and placed into cache. The cache in this situation is solely dependant on the file from which the XslTransform was loaded:

if (docStyle == null)
{
   // from the style sheet name get the required style sheet path
   string stylePath = context.Server.MapPath
      ("//CustService/" + styleName);
   // load the required style sheet
   docStyle = new XslTransform();
   docStyle.Load(stylePath);
   // place the style sheet into the cache
   context.Cache.Insert(styleName, docStyle,
      new CacheDependency(stylePath));
}

An alternative to using the context cache is to cache all the appropriate data within the pooled handler or application scope variables. The benefits of the context cache are the pre-defined mechanisms by which the cache can be marked invalid and thus automatically reloaded.

In addition to context caching, the application is using the fact that the Http handlers are pooled. In support of this, each handler constructor creates an instance of the CustomerRequest class, which in turn creates and instance of the CustomerOrders and CustomerSecurity class. In addition the CustomerOrders class creates a Thread object, used for authorization checks.

In using this method of holding object references, it is important that each request, handled by the ProcessRequest method is stateless; each Http handler request must initialize all local processing variables. In addition, if an error is encountered, the Http handler object is marked as not-reusable, thus forcing the generation of a new handler and all associated objects.

The CustomerOrders and CustomerSecurity classes do not hold database connections, but rather just the connection string that is read from the configuration file. This allows the OLEDB resource pooling to manage the database connections.

Web Service Extensions

The application so far has worked on the premise that the consumer will use the Web application to access customer and order information. With the advent of the .NET Framework Web Services infrastructure, another solution is plausible: allow the XML to be returned from a Web Service method, enabling the consumer to integrate the application into the customer's own systems.

Web Methods and Attributes

The premise of Web Service is simple: expose the core class methods that return the customer and order information. Figure 5 outlines the CustomerWebService class definition in support of this requirement.

Figure 5. Web Service Class diagram

Each class method consumes the CustomerOrder class to obtain the resultant XML (in the form of an XPathDocument), and returns this as an XmlElement that the caller can consume as needed. The assumption is always that the user is authenticated and known; more on this follows. As an example, take the GetCustomerOrderDetails method:

[WebMethod(Description="Obtain the Customer Order Summary Information")]
[SoapHeader("soapCredentials",
   Required=false, Direction=SoapHeaderDirection.In)]
[SoapHeader("soapTicket",
   Required=true, Direction=SoapHeaderDirection.InOut)]
public XmlElement GetCustomerOrders(string CustomerID)
{
   // first validate the user has access to the customer
   ValidateCustomerSecurity(CustomerID);
   // call the method to get the customer information
   CustomerOrders customerOrders = new CustomerOrders();
   XPathDocument xpathCustDoc =
      customerOrders.GetCustomerOrders(CustomerID);
   // return the string representation of the XML
   return GetElement(xpathCustDoc);
}

The SoapHeader attributes and ValidateCustomerSecurity all deal with security, authenticating the users, and authorizing them for the customer data. The GetElement method takes the XPathDocument from the CustomerOrders class and transforms this into the required XmlElement:

private XmlElement GetElement(XPathDocument xpathCustDoc)
{
   // load the required style sheet to transform XPath into XML
   string xsltDocumentPath = Context.Request.PhysicalApplicationPath
+ "customerxmlnode.xslt";
   XslTransform docStyle = new XslTransform();
   docStyle.Load(xsltDocumentPath);
   XmlDocument docXml = new XmlDocument();
   // create a new string writer for the transform
   StringWriter stringWriter = new StringWriter();
   // perform and return the XmlDocument representation of the XML
   // assuming the document has a root node
   docStyle.Transform(xpathCustDoc, null, stringWriter);
   docXml.LoadXml(stringWriter.ToString());
   // return document element
   return docXml.DocumentElement;
}

This method takes an XPathDocument and performs a simple transform using an XSLT document:

<?xml version="1.0" encoding='UTF-8' ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" />
   <xsl:template match="/ | @* | node()">
      <xsl:copy-of select="@* | node()" />
   </xsl:template>
</xsl:stylesheet>

The purpose of this is simply to obtain the string representation of the XpathDocument, from which an XmlDocument and its root Element can be obtained. This transform assumes that the XPath document fragment has a root node. If this was not the case, the XSLT document would have be restructured to include one.

SOAP Authentication

As you can see, the actual implementation of the Web Service is straightforward; but what about security? There are many options for handling authentication and authorization within Web Services. Within the implementation above, custom SOAP headers are used. One solution to a custom SOAP header is an HttpModule that authenticates the user through the SOAP header. The technique I chose was a little different.

I defined two SOAP Headers as part of the Web Service. The optional SOAPCredentials header is used to pass in user credentials that are validated against the user database. Upon successful authentication, an authorization token is created and is an encrypted version of the user name with an expiration time, and is placed into the required SOAPTicket header. It is this ticket that is decrypted and validated on subsequent Web method calls, saving a round-trip to the database to perform the authentication.

Conclusion

Hopefully the solution presented here demonstrates the power of the Microsoft .NET Framework for writing applications that consume the XML functionality of SQL Server 2000. With the ASP.NET runtime support, forms authentication mechanism, and the system XML classes, creating high performance and scalable XML-based inquiry systems has been greatly simplified.

In addition, new possibilities exist with the inclusion of the Web Services infrastructure. Previously exposed Web-based applications can now be easily exposed as Web Services. This allows consumers to easily integrate the provided functionality into there own applications.

References

SQL Server and SQL Server XML

XML home page on MSDN

SQLXML and XML Mapping Technologies on MSDN

A Survey of Microsoft SQL Server 2000 XML Features

SQL Server 2000: New Features Provide Unmatched Ease of Use and Scalability to Admins and Users

ASP.NET Development

.NET Development home page on MSDN

ASP.NET page on MSDN

ASP.NET Architecture

Authentication and Authorization

Simple Forms Authentication

Carl Nolan works in Northern California at the Microsoft Technology Center in Silicon Valley. This center focuses on the development of Microsoft .NET solutions using the Microsoft Windows .NET platform.