SUMMARY One of the most significant features of ADO.NET is its integration with XML. Developers can either use an ADO-like API to access the data or work directly with an XML representation of the data. This article demonstrates how both of these techniques can be used together to create Web applications that take advantage of XML standards such as XSLT. The example presented here is a bug tracking application built using C# and the.NET Framework. The development of the application covers several topics including data access using ADO.NET, the presentation of data using XSLT stylesheets, and the integration of ADO.NET with the .NET XML Framework..
ML support has been one of the key design goals for ADO.NET. As in the other parts of .NET, XML is the native data representation format in the ADO.NET architecture. This gives the .NET Framework XML classes a leading role in working with all types of data, whether the original data source is hierarchical or relational. Developers have the choice of working with the ADO.NET classes or working directly with the underlying XML.
Let's examine how you can use the .NET Framework XML classes in conjunction with ADO.NET to build a Web-based bug tracking database. To demonstrate some of the uses of XML in creating applications, I made the application configurable via an XML file and used XSLT to present the XML as an HTML page.
Figure 1Bug Tracker Main Page
I'll start by taking a look at the sample application. There are two main pages: a page listing the bugs in a project (see Figure 1) and a form for entering the details of a particular bug. Clicking on a column heading sorts the list by the values in the column. Clicking on a bug ID or caption brings up the form shown in Figure 2.
Figure 2Bug Input Form
Each bug has a number of mandatory fields: a bug ID, title, description, and the name of the person who first created the bug entry. There are also fields for the status of the bug, the browser type, the platform that the bug is found on (the version of the Windows® or Macintosh OS), and the version number of the software being tested. Another project may require different fields.
One of my goals for the design of the bug tracking application was to produce a system that could be configurable without the need to write any code or design new HTML forms. The obvious candidate for a configuration file was an XML document. It's not too hard to devise a schema for this application. Figure 3 shows the XML configuration file I used to produce the bug form. Later I'll show how the XML configuration file is used to configure a project in the application.
Accessing Data Using ADO.NET
Now that you have an idea of how the application works, I'll show you how to access the data using ADO.NET. One of the big surprises in ADO.NET is that there is no recordset object. Although the recordset is very powerful, it contains many APIs, and it's big. It's hard to move a recordset across platforms and firewalls because it's based on a COM binary standard. Although there is an equivalent of the ADO forward-only recordset in ADO.NET (the DataReader), most of the new API is designed around the DataSet, a client-side cache of the database. The DataSet is simply a local buffer of the tables and their relationships. This disconnected approach is more scalable because it reduces the number of active connections to the database.
The DataSet, DataTable, DataRow, and DataColumn classes are used to access and manipulate the data in the DataSet. Since the tables in the DataSet are held in XML format, you can also use the .NET Framework XML classes to access and manipulate the data in them. Another benefit of storing the data in XML format is that it overcomes the problem of moving data across different platforms, firewalls, or the Internet.
How do you get data from the database into the DataSet? A set of classes called the Managed Provider allows you to connect to the data source and read and write data. A Managed Provider is similar to an OLE DB provider and comprises the Connection, Command, DataReader, and DataAdapter classes. Figure 4 illustrates the main components of the .NET Managed Data Provider and DataSet classes.
Figure 4Managed Provider and DataSet Classes
It is worth mentioning here that reports of the demise of ADO are premature. The idea behind ADO.NET was to provide a new model for data access in the .NET Framework rather than replace all the functionality in the existing ADO architecture. (For more information on the origins of ADO.NET, refer to "Introducing ADO+: Data Access Services for the Microsoft® .NET Framework" from the November 2000 issue of MSDN® Magazine).
Managed Providers and the SQLStoredProc Class
First, let's look at how to use a Managed Provider. The database schema for my application is shown in Figure 5. As you can see, the project table contains the details of the projects in the system. The incident table has one entry for each bug and the audit table stores a record for each update to the bug information. The remaining tables describe the project-specific fields. The fields table stores the definitions of the project-specific fields, which are linked to the project via the project_fields table. If the field has a list of predefined options, the options table stores them. The audit_extra table links an audit to this additional project information.
The Connection and Command classes in the Managed Provider will be familiar to ADO programmers. You create a connection to the database using the Connection Class, then set up a command to access the database using the Command class. I've used SQL Server™ 2000 for the database to take advantage of the optimized SQL Server Managed Provider, but SQL Server 7.0 also works in this context).
Although it is straightforward to set up a Command object, it still requires a fair amount of code, so I've wrapped up the functionality needed to access my stored procedures in the SQLStoredProc class, which is included in the code download (at the link at the top of this article).
The constructor takes a connection as its only argument and then sets up a new Command object with the connection, as shown here:
// constructor for SqlStoredProc
public SqlStoredProc (SqlConnection conn)
{
this.m_sqlCMD = new SqlCommand();
this.m_sqlCMD.Connection = conn;
}
The SetCommand method sets up the stored procedure in the SqlCommand object. The three arguments it takes are the name of the stored procedure to run, a DataAdapter, and the type of DataAdapter command to create. This can be a select, insert, update, or delete command. The Set Command method first assigns the CommandText property of the Command object to the name of the stored procedure; the CommandType property of the Command object is then set to StoredProcedure. The Command object is then assigned to either the SelectCommand, InsertCommand, UpdateCommand, or DeleteCommand property of the DataAdapter, as you can see in Figure 6.
The DataAdapter is the link between the database and the DataSet. It retrieves data from the data source using the Command object assigned to the SelectCommand property and then creates and populates the tables in the DataSet. The DataAdapter also resolves changes back to the data source using the Command objects assigned to the InsertCommand, UpdateCommand, and DeleteCommand properties.
The AddParam method is used to wrap the code that adds a parameter to a stored procedure. It is an overloaded method that takes four arguments. In the following method, the first three arguments define the name, type, and size of the parameter and the fourth is the value to insert into the parameter:
// adds a parameter to the stored procedure with a value
public void AddParam (string param, SqlDbType type, int size,
object value)
{
SqlParameter sqlParam;
sqlParam = this.m_sqlCMD.Parameters.Add (param, type, size);
sqlParam.Value = value;
}
The second AddParam method, with five arguments, is used with the InsertCommand (discussed later). The first three arguments define the name, type, and size of the parameter. The fourth argument is the direction of the parameter (input or output) and the last argument is the source column in the DataSet to synchronize with:
// adds a parameter to the stored procedure with the
// parameter direction
// and a source column from a dataset
public void AddParam (string param, SqlDbType type, int size,
ParameterDirection direction, string sourceCol)
{
SqlParameter sqlParam;
sqlParam = this.m_sqlCMD.Parameters.Add (param, type, size);
sqlParam.Direction = direction;
sqlParam.SourceColumn = sourceCol;
}
You can see the class in action by creating a Command object to retrieve the audit information for a particular bug in a project. The code in Figure 7 creates a Connection object a DataAdapter object and a SqlStoredProc object, and uses the SqlStoredProc methods to create and then set up a Command object for the GetAuditTrail stored procedure.
Before I create and populate the DataSet, let's take a look at the GetAuditTrail stored procedure that retrieves the data for the bug entry form (see Figure 8). As you see, I return all the data to display the bug entry form in a single stored procedure using multiple SELECT statements to avoid repeated calls to the database.
DataSet
When the DataAdapter populates the DataSet, it gives the DataTables default names of Table, Table1, Table2, and so forth. These names can be mapped to user-friendly names using the TableMappings collection in the DataAdapter:
Everything is now in place to create the DataSet and populate it with the results of the query in the stored procedure:
DataSet ds = new DataSet();
myDataAdapter.Fill(ds);
The DataSet now contains a collection of tables corresponding to the tables retrieved in the stored procedures. The next step is to define the hierarchical relationships between these tables.
The DataRelation object is used to establish a relationship between a parent and a child table based on a common key. The DataRelation objects are added to the DataSet using the Relations Collection. The following example sets up a relationship between the project and incident tables:
When you put this all together, the code retrieves the audit information for all the bugs in a project. Figure 9 shows the XML representation of the data in the DataSet.
As an alternative, you can define the relationships by creating an XML Schema Definition (XSD) document and loading it into the DataSet using the ReadXMLSchema method. I won't go into the details of XSD, but be sure to check out the Visual Studio® .NET data designers, which include a tool for visually creating XSD.
Using XSLT to Display the Bug Form
Now take a look at how the XML data can be displayed in HTML using an XSLT stylesheet. You could, of course, use the ASP.NET Web Forms for the presentation layer (see Figure 10) and both are excellent approaches to separate the data from the presentation code. I'm using XSLT because I want to show how the .NET Framework makes it easy to develop XML-based applications. I'll also take the opportunity to show off some of the features of XSLT.
As it often causes confusion, I'll give a brief mention to the difference between XSL and XSLT. XSL (Extensible Stylesheet Language) handles both the XML transformations (XSLT) and the formatting process to render the output, the XSL-FO. XSL Formatting Objects have been designed to provide a more precise and sophisticated visual layout than HTML plus CSS. As the split between XSLT and XSL-FO is comparatively recent, the terms XSL and XSLT are often used interchangeably. In many applications, including my bug-tracking system, HTML and CSS will suffice, so I render the HTML using XSLT alone.
You can find the XSLT stylesheet used to generate the bug entry form in the incidentform.xsl file in the code download. The stylesheet makes use of three XSLT coding techniques. The first uses standard HTML with the addition of XSLT tags that are used to retrieve data from the XML. For example, the following XSLT fragment from the stylesheet sample code retrieves the value of the the projectname element:
The second technique makes use of the looping instruction xsl:for-each, which in turn uses an XPath expression to select a set of nodes and performs the same processing for each node in the set. The xsl:for-each statement in Figure 11 processes all of the Audit nodes to output the Audit history list.
The third technique uses template rules, which are the XSLT equivalent of functions. Template rules can be named and invoked based on a symbolic name or on an XSLT pattern (a subset of XPath expressions). The fragment in Figure 12 from the stylesheet shows the template rule that matches the OptionDef element and displays the controls on the bug form. Figure 12 also contains an example of the xsl:if test instruction. If the XPath expression in the xsl:if test element evaluates to true, then the children of the element will be processed. Also, note the use of the syntax:
name="{fieldname}"
This is an attribute value template and is simply shorthand for inserting XPath results into an attribute value. The XPath expression is evaluated and the result is converted to a string.
There are standard functions included in the XSLT and XPath specification. I've used the position function in the following fragment to generate a two-column grid of controls in the bug form:
Here, the position function returns the number assigned to the current node in the list (starting from 1). I process two OptionDef nodes at once to create a two-column grid for the project-specific controls. I therefore skip every second node when the XPath expression ((position() mod 2) = 1) evaluates to false.
This is only a brief introduction to XSLT stylesheets. For a more detailed look at the XSLT specification, see "XSL Transformations: XSLT Alleviates XML Schema Incompatibility Headaches" by Don Box, Aaron Skonnard, and John Lam in the August 2000 issue.
The .NET XML Framework provides the XslTransform class, an XSLT processor that implements most of the functionality of the W3C XSLT Version 1.0 recommendation. The XML Framework also provides the XPathDocument class, an XML cache highly optimized for XSLT processing and the XPath data model.
The following code snippet uses these classes to display the bug entry as an HTML form:
StringReader xmlSR = new StringReader(ds.GetXml());
XPathDocument xmlDoc = new XPathDocument (xmlSR);
XslTransform xslTran = new XslTransform();
xslTran.Load (Server.MapPath("incidentform.xsl"));
//Transform and output
xslTran.Transform(xmlDoc, null, Response.OutputStream);
Note that the XPathDocument constructor takes a Stream as its argument, so I've used the StringReader class to create a stream from the DataSet XML. Now that the bug form is up and running, the next consideration becomes how to submit the user changes entered back to the database.
Updating the Database Using the DataAdapter
As I mentioned in a previous section, the DataAdapter is the bridge between the DataSet and data source and provides the means to resolve changes back to the data source through the Update method. The Update method takes an instance of a DataSet as its first argument and a DataTable object or DataTable name as its second argument. When you call the Update method, the DataAdapter analyzes the changes that have been made to the DataSet and executes InsertCommand to submit new rows. Meanwhile, UpdateCommand is executed to submit modified rows, and DeleteCommand is executed to remove rows from the database.
The CommandBuilder class automatically generates the InsertCommand, UpdateCommand, and DeleteCommand procedures, but these will only work if your DataTable maps to or is generated from a single table. More often than not, you will need to specify your own InsertCommand, DeleteCommand, or UpdateCommand to control how the updates should be processed. For example, you may want to improve performance by updating multiple rows in one SQL query, reducing the number of round-trips to the data source.
Another common reason for specifying your own InsertCommand is to return the new primary key ID for an Identity column. This is shown in Figure 13, where a new bug audit row is inserted into the database. As you can see, I first specify a SelectCommand using my SqlStoredProc wrapper class and fill it with a blank Audit row to create the DataTable columns in the DataSet. The next step is to specify an InsertCommand that will insert the new values into the database and return the new primary key ID for the inserted row. The SQL statement to insert the Audit row and return the new identity value looks like this:
How do you get back the AuditId parameter? This is where the second version of the AddParam method in my SqlStoredProc class comes into play. This method takes the parameter direction as one of its arguments and the DataTable column as another argument. For all input parameters, the parameter direction is set to ParameterDirection.Input and the database is updated with the value in the DataTable column. If you set the parameter direction to ParameterDirection.Output, as in the case of the AuditId, the value will be returned into the specified DataTable column.
Using the wrapper class, it doesn't take a lot of code to set up the Command objects. To make things even easier, Visual Studio .NET provides a Data Adapter Configuration Wizard that will build the connection string and commands and even create the stored procedures in the database. Figure 14 shows one of the configuration screens. There are also some advanced SQL generation options available in the wizard (including an option to retrieve identity column values).
Figure 14Configuration Wizard
Now that I've created the SelectCommand and InsertCommand for the audit table, the next step is to insert the data that has been submitted from the bug form into the DataSet. A new row is added to the DataSet using the NewRow method:
To resolve the changes back to the database, I call the Update method of the DataAdapter:
myDataAdapter.Update (ds, "audit");
It's now time to update the project-specific fields. I again use my SqlStoredProc class to create the required commands and parameters and fill the DataSet Extra table with a blank row. The code is similar to Figure 13, and you can find it in the "Insert new audit rows" section of the updateaudit.aspx file in the download.
A problem I ran into here is that the form submits the fieldname of the project-specific field, but I need to insert the fieldid into the audit_extra table of the database. The solution is to retrieve the fieldid first, as shown in the following SQL statement:
SELECT @fieldid=fieldid FROM fields
WHERE fields.fieldname = @fieldname;
INSERT INTO audit_extra (fieldvalue, auditid, fieldid)
VALUES (@fieldvalue, @auditid, @fieldid);
I then iterate through the Request.Form collection to find the project-specific fields and fill the appropriate columns with the field values, as you can see in Figure 15. The last step is to once again call the now familiar Update method of the DataAdapter to resolve the changes back to the database.
Look at the code to create a completely new bug record. The complete code is similar to Figure 13 (included in the "Create new incident" section of the updateaudit.aspx file in the download). If no incidentid is passed to the page, I create a new bug record (incident) using a SelectCommand to fill the DataSet with a blank row and a custom InsertCommand to insert the values into the database and return the new primary key ID. The Update method of the DataAdapter is then called to commit the changes.
Generating the Bug List
No prizes for guessing how I'm going to create a list of bugs for a particular project. The complete code to generate the list is in the list.aspx file in the download. First I create the Connection and DataAdapter objects and use my SqlStoredProc class to set up the SelectCommand for generating the list. The SQL statement for selecting the list data is shown in Figure 16.
Now, just add the relationships in the DataSet and apply the XSLT transform. In this case I also pass a parameter to the stylesheet using an XsltArgumentList to specify the column to sort by.
// add the parameter for sorting the list
XsltArgumentList xslArg = new XsltArgumentList();
if (Request.QueryString["sort"] != null)
{
xslArg.AddParam ("sort", "", Request.QueryString["sort"]);
}
// transform and output
xslTran.Transform(xmlDoc, xslArg, Response.OutputStream);
Figure 17 is an excerpt from the XSLT stylesheet used to produce the bug list as it introduces a few new stylesheet instructions. The xsl:param instruction declares the sort parameter which is used in an xsl:choose instruction, the equivalent of a C++ switch statement. The sort parameter is tested against each column name, and if a match is found the template rule to find all Incident nodes is invoked with the appropriate sort order as defined by the xsl:sort instruction. Xsl:sort collates values alphabetically by default. To collate the values numerically as in the case of the incidentid, the data-type attribute must be set to "number."
Creating a New Project
My final task is to create a new project using the XML bug project configuration schema I talked about at the beginning of the article (a sample XML project configuration file was shown in Figure 3). You could use one of the .NET XML Framework classes to parse the XML file using XPath expressions and then write the values into the DataSet. You can, however, read XML directly into the DataSet, so why not transform the XML configuration file schema into the DataSet schema and read the XML into the DataSet? Transforming XML documents from one schema to another is one of the strengths of XSLT. Here the transformation is straightforward. Figure 18 shows the XSLT used to transform the project configuration schema into the DataSet schema. The code for creating a project can be found in the createproject.aspx file in the download. The first part of the code where I create the SelectCommand and InsertCommand for the Project and OptionDef tables will be familiar.
The next step is to transform the project definition XML file into the DataSet schema:
XPathDocument xmlDoc = new XPathDocument (Server.MapPath
("projdef.xml"));
XslTransform xslTran = new XslTransform();
xslTran.Load (Server.MapPath ("generateproj.xsl");
MemoryStream xmlMS = new MemoryStream();
XmlReader xmlStream = new XmlTextReader(xmlMS);
xmlStream = xslTran.Transform(xmlDoc, null);
The resulting XML is put in a stream so it can be passed into the DataSet ReadXML method. I then use the DataSet Merge method to combine the new DataSet with the existing DataSet and schema.
DataSet ds2 = new DataSet();
ds2.ReadXml (xmlStream);
ds.Merge (ds2);
I now have four DataSet tables containing the Project, OptionDef, Option and ProjectFields data. I first call the DataAdapter Update method for the Project and OptionDef DataSet tables to insert the configuration file values into the Project and Fields tables in the database. The option table presents a problem in that the database table stores a fieldid to relate an option to the appropriate field, whereas the project configuration XML defines the relationship through the hierarchy. Help is at hand, though, with the DataRelation collection in the DataTable class. I can use this collection to determine which fields contain which options by calling the GetParentRows method in the DataRow class:
DataRow[] arrRows;
DataRelation myRel in ds.Tables["Option"].ParentRelations[0];
foreach(DataRow myRow in ds.Tables["Option"].Rows)
{
arrRows = myRow.GetParentRows(myRel);
myRow["fieldid"] = arrRows[0]["fieldid"];
}
Populating the ProjectFields table in the DataSet is easier:
Lastly, I call the DataAdapter Update method for the Option and ProjectField DataSet tables to insert the values into the database. I don't need a special InsertCommand for these tables so I can take advantage of the automatically generated Commands.
Conclusion
This is my third iteration of a bug tracking system. The first used ADO; the second used ADO with XML extensions. This ADO.NET version was the simplest to design and it works across firewalls and requires fewer active connections.
The XML configuration file makes it easy to create a new database without writing code. As all the fields are customizable, the system is not confined to bug tracking. You could, for example, create a call-logging database or even a database to keep track of project tasks. Want a version for WAP phones? A new data view can be created with XSLT without changing any application code.
Whether you use XSLT or Web Forms will be a matter of preference. If the HTML output is complex or uses lots of script, I'd use Web Forms and the tools in Visual Studio .NET. Whatever you choose, the fact that .NET is built on Web standards opens many development options.
Roy Margolis is a director of Tigerteam Software Limited, a software development and technology consulting company in London. Roy and his team develop Web applications for a wide range of clients. When Roy is not following the ups and downs of Chelsea Football Club, he can be reached at roy@tigerteam.co.uk.
.gif)
.gif){kind=small}
ML support has been one of the key design goals for ADO.NET. As in the other parts of .NET, XML is the native data representation format in the ADO.NET architecture. This gives the .NET Framework XML classes a leading role in working with all types of data, whether the original data source is hierarchical or relational. Developers have the choice of working with the ADO.NET classes or working directly with the underlying XML..gif){kind=tracking}
.gif){kind=tracking}
.gif){kind=tracking}
.gif){kind=tracking}