Related Resources: Video: How DB2 Express-C Saved the World, Part 1 Download DB2 Express-C XML Everywhere: DB2 9 and Ajax, Part 1 Download the Listings for this Article Introducing IBM DB2 Viper Workbench, Featuring the XQuery Visual Builder

Part 1 covered installing and configuring your free download of DB2 9 Express-C and populating your AJAXDEMO database. Now you will learn how to create a Web Service in Visual Studio 2005 using the DB2 9 Add-ins to interact with those services using asynchronous JavaScript.

You have several options for creating a Web Service based on a DB2 connection. Once again, the new Add-ins give you some very nice tools to speed your development time.

In Visual Studio, establish a connection to your new database. Select Tools -> Connect to Database and choose IBM DB2, which was added as an option when you installed the Visual Studio Add-in (see Figure 1).

When adding the new connection, you'll see the following options:

1. Select or enter server name: you'll probably have only one option here.
2. Enter information to log on to the server: this should be the User ID and password you specified during your Express-C installation.
3. Select or enter a database name: AJAXDEMO should be selected by default if it's the only database you've created so far, otherwise select it from the dropdown.

You can ignore the other options and the Advanced properties list for this demo. Click "Test Connection" to make sure your User ID / Password combo is correct and you're able to connect to the table (see Figure 2).

Figure 1. Choose Data Source

Figure 2. Test Connection

If you expand the new connection and one of the tables, you'll see that you've successfully connected to your new database through Visual Studio and can now access your data. If you right-click on Data Connections, you'll see that you can add new connections or create new IBM databases from the IDE.

So in case you're wondering, yes, you could have created the database and tables, and entered the data, all through your Data Connection using the DB2 9 Add-in. Now you know. But the context menus from the tables are much more interesting.

Right-click on "HEROES_NOXML". Among several options, you'll see two related to Web services: "Deploy IBM Web Service" and "Generate Web Methods" (Figure 3). Select "Generate Web Methods" and in the "Identify Database Enabled for Web Services" dialog, enter the Server name you selected from the dropdown when creating the database connection, as well as the Database name "AJAXDEMO" and User name / Password you previously used.

In the "IBM Web Service" wizard, enter "HeroData" as the Web service name. The rest of the fields should be filled in automatically. When you create the service, the DB2 Add-in generates all the code you need to connect to the database and pull a list of values from the specified table. Your code should look something like Listing 1. Note the use of the IBM.Data.DB2 namespace in lieu System.Data.SQL.

Listing 1: Auto-generated DB2 Web Service Code

using System;
using System.Data;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Web;
using IBM.Data.DB2;
using System.Data.Common;  
   
   [WebService()]
   [WebServiceBinding()]
   public class HeroData
   {
      [WebMethod()]
      public virtual DataSet select()
      {
         IBM.Data.DB2.DB2Connection db2Connection1 = 
new IBM.Data.DB2.DB2Connection();
         IBM.Data.DB2.DB2DataAdapter db2DataAdapter1 = 
new IBM.Data.DB2.DB2DataAdapter();
         IBM.Data.DB2.DB2Command db2SelectCommand1 = 
new IBM.Data.DB2.DB2Command();
         db2Connection1.ConnectionString = "database=AJAXDEMO;user
id=db2admin;server=DELL:db2c_DB2;password=changeme";
         db2DataAdapter1.SelectCommand = db2SelectCommand1;
         db2DataAdapter1.SelectCommand.CommandType = CommandType.Text;
         db2DataAdapter1.SelectCommand.CommandText =
@"select * from NULLID.HEROES_NOXML";
         db2DataAdapter1.SelectCommand.Connection = db2Connection1;
         System.Data.DataSet ds = new System.Data.DataSet();
         try
         {
            db2DataAdapter1.Fill(ds);
         }
         catch (DB2Exception ex)
         {
            throw ex;
         }
         finally
         {
            db2Connection1.Close();
         }
         return ds;
      }
   }

Figure 3. Results of Auto-Generated Web Service

To test the Web service, compile and navigate to HeroData.asmx, then click "select" and "Invoke". You should see something like Figure 3.

In Visual Studio, you have approximately 2,078 different ways of connecting to your data (a rough estimate). The technique generated by the add-in returns a DataSet, as you can see in Listing 1. For most purposes, this is good enough, especially when using non-XML data types.

But as you can see in this example, you have a lot of descriptive information that you just don't need for Ajax apps. This particular result set would require a lot of JavaScript parsing before you can use it to populate your DOM. For our purposes, it would be simpler to return an XmlDataDocument rather than a DataSet. So make a couple of small tweaks to the WebMethod code:

• For clarity, change the method name to "getNoXML()". Changing the name should trigger a tooltip dropdown that allows you to change the name globally, an opportunity you should seize upon.
• In the method declaration, which now reads "public virtual DataSet getNoXML()", change "DataSet" to "XmlDataDocument".
• At the bottom of the method, where it says "return ds;" either delete or comment out that line and replace it with the following:

  XmlDataDocument xd = new XmlDataDocument(ds);
  return xd; 

Figure 4. Results of Web Services with Tweaks

That's how easy it is to convert your DataSet to XML data. Compile and test. The result should look like Figure 4—much shorter, much easier to use with Ajax.

So far, so good. You have your database set up, with data, and a Web Service to grab the results. Now all you need is your HTML and JavaScript (and CSS). But before you dive in to the Ajax portion of your test app, there's a bit of a problem looming. Well, less of a problem and more of a major limitation.

Take a look at the fields and field contents. With most fields, you can easily identify a label to use for the data, the column name, and the data itself. But a lot of the data here didn't fit into common fields. Traditionally, when capturing unstructured data, your data tables will include a catch-all field into which you dump everything that can't be parsed into clean and tidy columns. You may have several different reasons for this:

• Data comes from multiple non-standardized sources, each with their own column names and field types.
• You have so many potential Name/Value pairs that to include them all in your data structure would require an unreasonably large table or a more complex database architecture.

You have several ways of dealing with this quandary, but each solution poses new problems. For example, you could:

• Store the data in a CLOB in HTML format. Problem: HTML data presents limitations that all require custom workarounds, such as UI issues and data updates. Also, pre-formatted data becomes less available for other applications.
• Use a different markup (or develop your own), such as JSON that's more useful to your JavaScript code. Problem: data is still unavailable to other apps. It also becomes much harder for humans to read and update and requires custom conversion code when importing from other sources.
• Store the data as straight text. Problem: eliminates any custom formatting options when displayed in your Web app.

In this particular case, the most common data points, such as "POWER" and "WEAKNESS," have been given their own columns. But even these columns are sometimes null. The rest of the data has been dumped into the Notes field in a way that precludes any kind of formatting and is also less available to other apps.

You probably saw this coming, but the optimum solution would be to store the non-standard data as an XML document. With DB2 9's XML data type, you immediately gain several advantages:

• You can store custom data in standard XML format.
• While you can associate an XSD or DTD document with the XML column so that each row stores data in the same XML structure, you don't have to; every row can have a completely unique XML doc and structure.
• The XML data is more available to other applications.
• The XML documents can be searched with DB2's implementation of XQuery, an XML Query Language.
• In fact, the XML documents can be searched using a variety of query languages mixed together – XQuery, SQLXML, and SQL. Likewise, you can mix data types easily, querying both relational and XML data simultaneously, as you'll see in a moment. For an excellent research piece on mixing XQuery and SQL, read "Integration of SQL and XQuery in IBM DB2."

If you go back and look at the structures of the two sample tables you created earlier, you'll notice that the "XML" version only has three fields. That's because even the more common fields will be included in the individual XML documents. This is a good time to import those documents and look at this data type in action.

XML fields can't be edited in place, but can be added and updated as entire documents. Probably the easiest is via the IMPORT method, described in more detail in "XML and Beyond: Four New Features from IBM's Viper." To do this, create five individual XML files for each of the five sample records using the XML data in Listings 2 through 6. Save these to a new directory: "C:\PALS".

Listing 2: hero1.xml

<HERO>
   <NOTE title="Power">Can mimic any sound she hears.</NOTE>
   <NOTE title="Alter ego">Mariah Carey</NOTE>
</HERO>

Listing 3: hero2.xml

<HERO>
   <NOTE title="Power">Telepathy</NOTE>
   <NOTE title="Weakness">Gets cranky if he stays up too late.</NOTE>
   <NOTE title="Current age">8</NOTE>
   <NOTE title="Additional weakness">Lactose intolerant.</NOTE>
</HERO>

Listing 4: hero3.xml

<HERO>
   <NOTE title="Power">Super strength; can reattach severed body parts.</NOTE>
   <NOTE title="Weakness">Not too bright.</NOTE>
   <NOTE title="Secret Origin">A somewhat clumsy man, Hank "Bullhead" 
   Durham fell into a vat of fresh glue and was promptly struck by lightning. 
   Since then, whenever he loses a part of his body, he has simply to hold it in place 
   and it becomes reattached.</NOTE>
</HERO>

Listing 5: hero4.xml

<HERO>
   <NOTE title="Power">Glows under a black light.</NOTE>
   <NOTE title="Weakness">Not having a black light around.</NOTE>
   <NOTE title="Note">Usually relegated to sidekick, though once played 
   a crucial role in a mission busting an underground pot growers cabal.</NOTE>
</HERO>

Listing 6: hero5.xml

<HERO>
   <NOTE title="Power">Ability to text message at light speed.</NOTE>
   <NOTE title="Weakness">Thumbsucking (a nervous habit in times of great stress).</NOTE>
   <NOTE title="Former Codename">Twiddle</NOTE>
</HERO>

Next, save Listing 7 to the same directory as "heroes.del". The format of this file is pretty simple—essentially a comma-delimited list of the table rows, with an "XDS" tag in place of the XML column specifying its source file. In this case, the HeroID field has been left out since it's an auto-generated Identity column, so you have only two other fields.

Listing 7: heroes.del

Mockingbird,<XDS FIL='hero1.xml' />
Indigo,<XDS FIL='hero2.xml' />
Indivisibull,<XDS FIL='hero3.xml' />
Dayglo,<XDS FIL='hero4.xml' />
Thumbalina,<XDS FIL='hero5.xml' />

Figure 5. HEROES_XML Populated

To populate your table, open a Command Editor window and make sure you're targeting the AJAXDEMO database. Copy Listing 8 into the window and execute.

Listing 8: insert.sql

IMPORT FROM 'C:\PALS\heroes.del' OF DEL
XML FROM 'C:\PALS'
MODIFIED BY IDENTITYMISSING
INSERT INTO NULLID.HEROES_XML

Hopefully, you'll see a message ending with "Number of rows committed = 5". If so, you've successfully populated your HEROES_XML table. To confirm, open up the table. Under the Notes column, you'll see ellipses ("…"), as in Figure 5.

Figure 6. XML Field Contents

Click on one of the ellipsis to open the XML Document Viewer, which displays the XML document in either Tree View (Figure 6) or Source View.

Notice that the structure of the XML document has been deliberately chosen to minimize the number of XML nodes for each row, but you can build it however you want. Here I decided to include the data name as a "title" attribute for the node, with the data value as the node value. You'll see in the next sections how the JavaScript has been written directly to this format. Implementing a solution like this might require some standardization if you're gathering data from multiple data sources. But in today's interconnected world, chances are that your sources have already standardized in some way. So if you're pulling in multiple streams in a consistent format, such as RSS feeds, you can write the JavaScript to that format just as easily. XML data types make an excellent resource for doing just that type of aggregation.

Go back to your HeroData.cs file. For convenience, take the connection string out of the getNoXML() method and assign to a class-level private variable, such as:

private String connString = "…";

Instead of generating the Web method through the add-in, add the code in Listing 9 manually.

Listing 9: getWithXML Webmethod

 [WebMethod()]
       public virtual XmlDocument getWithXML(int heroID)
       {
           DB2Connection conn = new DB2Connection();
           conn.ConnectionString = connString;
           conn.Open();
           DB2Command cmd = conn.CreateCommand();
           XmlDocument heroXml = new XmlDocument();
           String sel = @"select XMLELEMENT 
          
          (NAME ""HERODATA"", XMLELEMENT (NAME ""HeroName"", HERONAME),  
          XMLELEMENT (NAME ""Notes"", NOTES)) 
          from NULLID.HEROES_XML where HEROID = " + heroID.ToString() + ";";
           cmd.CommandText = sel;
           XmlReader xr = cmd.ExecuteXmlReader();

           try
           {
                heroXml.Load(xr);
           }
           catch (DB2Exception ex)
           {
               throw ex;
           }
           finally
           {
               conn.Close();
           }
           return heroXml;
       }

At the top, in your resource declarations, add the following:

using System.Xml; 

Your complete HeroData.cs code should look like Listing 10.

Figure 7. Sample of getWithXML Web Service

Some things to note about this method:

• As I mentioned, you have a ridiculous number of ways to get at your data. You'll notice that this particular technique is a little shorter. It involves no DB2DataAdapter value, instead reading the results directly into an XmlReader value. This converts easily to an XmlDocument by using the XmlDocument.Load() method.
• Here, the return value is XmlDocument rather than XmlDataDocument, but the result is the same: they can both be accessed by JavaScript using responseXML, as you'll see later on.
• Take a close look at the select statement. You'll see a new command: XMLELEMENT. This and other handy XML-specific SQL commands help you format your result set as XML from within the SQL statement itself. Here, you're building a HERODATA node, which you then populate with both the nvarchar column, "HeroName," and the XML column "Notes." Doing this allows you to mix data types and quickly return a consistent and highly accessible result set.

Figure 8. Repeater Control Data Source

Compile and test by navigating to HeroData.asmx. When you Invoke your new getWithXML method, you see a field for "heroID," the method parameter. If you were able to insert all five rows into the HEROES_XML table the first try, then it will have automatically generated HeroID values of 1 through 5, so use one of those. Your result should look like Figure 7.

Time to build a basic Web page for interacting with your new Web Services. When you first created your project, Visual Studio helpfully gave you a Default.aspx to start with. Open it up and replace the contents with the code in Listing 11.

Listing 11: Default.aspx

<%@ Page Language="C#" AutoEventWireup="true"  
CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>DB2 AJAX Sample</title>
</head>
<body>
    <form id="form1" runat="server">
        <div id="heroList">

        </div>
        <div id="notes">Choose a Hero</div>
    </form>    
</body>
</html>

Figure 9. Choose Data Connection

The second <div> tag, "notes," will be populated dynamically using the JavaScript you write in the next section. It will hold the XML from your Web Service. But in order to choose a particular row, you'll need a way of selecting an ID from your table. The quickest and easiest technique is to use the Repeater Web object. This also gives you the chance to further explore DB2 integration with Visual Studio.

1. Switch to Design View and drag the Repeater control to your "heroList" <div> tag. You can find it under the Data block of tags in your Toolbox.
2. From your Repeater tag you can choose a data source. In the dropdown, choose "New data source…" as in Figure 8.
3. Select "Database" as your Data Source Type.
4. When choosing a Data Connection, you'll most likely only have one option in the dropdown, so select that (see Figure 9).
5. Since this is your first time accessing this data connection, you should get a prompt to save the connection to your config file, which you should accept (see Figure 10). Also accept the default name of "AJAXDEMOConnectionString".

Figure 10. Save Connection String

Figure 11. Configure SELECT Statement

6. To configure the Select Statement, as in Figure 11, choose the "HEROES_XML" table from the dropdown and select the HEROID and HERONAME fields. Do NOT select the NOTES field. Also, this is going to be a straight list population so you don't need a WHERE clause. But feel free to add an ORDER BY clause to change the order of the list. The SELECT statement at the bottom will write itself.
7. On the next page you'll get the chance to test the query, to make sure you're connecting to the database correctly. You should see a result set like the one in Figure 12.
8. As your last step, configure the Repeater list by selecting HERONAME to display in the control and HEROID to be the value of the control (Figure 13).
9. Switch back to Code View and you'll see the new control. You may need to cut and paste it to get it into your <div> tag (it should be inside the "heroList" tag). Right now, this is not going to display anything yet, so you'll need to add some templates. Inside the <asp:Repeater> control, add the following HeaderTemplate code. This starts an unordered bullet list.

<HeaderTemplate>
    <h3>Available Roster:</h3>
    <ul class="HeroList">
</HeaderTemplate>

Figure 12. Test Query for Repeater Control

Figure 13. Configure Repeater List

10. Next, add the ItemTemplate. This includes an onclick event that calls a yet-to-be-written JavaScript function and passes it the HeroID for the selected value. I've also added some onmouseover and onmouseout events to handle highlighting. Note the use of the DataBinder object to grab the data values.

    <ItemTemplate>                
        <li class="heroItem" 
        onclick="getHeroInfo(<%# DataBinder.Eval(Container.DataItem, "HeroID")%>)"
        onmouseover="this.className='heroItem_hl';"
        onmouseout="this.className='heroItem';">
    	<%# DataBinder.Eval(Container.DataItem, "HeroName")%>
        </li>
    </ItemTemplate>

11. Last, add a FooterTemplate to close the unordered list.

    <FooterTemplate>
        </ul>
    </FooterTemplate>

This completes the Repeater control. Go ahead and test it to make sure it's connecting to the database and retrieving data. Be forewarned though: the events will trigger a JavaScript error and the formatting will look less than pretty until you add styles, which you'll do in the next section.

If you've written Ajax code before, then this will add nothing new to your repertoire. Copy the code in Listing 12 and add it to your <head> tag. Alternatively, you can add Listing 12 to the project as a separate .js file and reference it in Default.aspx, but this walkthrough will assume the code is inline.

Take a closer look at what the code is doing:

• "var request;": the request object is declared globally since it's used in multiple functions.
• "function createRequest()": nothing unusual here, just browser-specific XMLHttp instantiation. (This app works on the latest versions of Microsoft Internet Explorer and Firefox.)
• "function getHeroInfo(heroID)": here's where it gets good. This is the function called from the onclick event in the Repeater control. It forms a request to the Web Service you created earlier, specifically the "getWithXML" method of HeroData. It then submits the HeroID via POST and sends all responses to showHeroInfo for handling.
• "function showHeroInfo()": the primary function in the app. Here's where it all happens. Note that if you had used the first Web Service, based on the table without an XML data type, then this function would be longer, more complex, and less versatile.

While you have fewer options for processing an XML result set than you do for generating one, you still have choices to make. Based on the expected XML structure, I've chosen to grab the "NOTE" node and iterate through its childNodes. Take another look at the sample XML result set from this Web Service then look at how it's being accessed:

var notesList = xmlDoc.getElementsByTagName("HERO")[0].getElementsByTagName("NOTE");

Parsing results correctly is usually the trickiest bit of business in an Ajax app. Here, DB2 9's use of XML data types and query language simplify the process a great deal by formatting your result set correctly with as little code as possible, letting you quickly get at the data you need and throw it into your DOM.

After identifying the relevant XML data, this code goes on to getting, and clearing, the destination <div> tag. When creating new document nodes based on the XML data, you again have some options because of the XML data type. By aggregating important but unrelated information into a single XML field and structuring it with identifying titles, you're able to loop through the childNodes and create both headings and body text in a single loop without additional exception handling (though a more complete app would include more error handling than I've written here). Note the use of "getAttributeNode" to access the "title" attribute.

In this example, I'm simply creating <h3> and <p> nodes based on the data, which can then be styled separately. No need to parse the Notes field in any way. No need to add special handling for individual fields. Obviously you can use different tags, such as <div> or <span> tags or even <ul>and <li>tags if you want to be fancy.

Speaking of styles, add one more bit of code to your <head> tag to clean the page up a bit:

Listing 13: Styles

<style type="text/css">
        ul {font-family:Arial; font-size:.9em; }
        li { list-style:none; font-family:Arial; }
        h3 { font-family:Arial; font-size:1.3em}
        p { font-family:Arial; font-weight:normal; font-size:.9em}
        #notes { float:right; border-width:1px; border-style:solid;
				 width:50%; padding:20px 20px 20px 20px;}
        #heroList { font-family:Arial; float:left; width:30%;
					padding:20px 20px 20px 20px;}
        .heroItem_hl { font-weight:bold; background-color:azure; }
        .heroItem { font-weight:normal; background-color:white; }
    </style>

Figure 14. Finished Product

Your completed Default.aspx page should look like Listing 14.

Since the server-side code has already been compiled, just navigate to your Default.aspx page and check out the results. If all goes well, you'll see something like Figure 14.

The "Available Roster" of your Superpals is populating directly from your DB2 9 data source. Select any one of them (notice the simple but sweet rollover action that took all of one line of code and two styles) and the right side gets populated from your XML field via a C# Web Service call. Each Hero has a different set of traits but you're able to display clean, formatted results with very little handling, in fact far less handling than if you'd tried to use a CLOB-based custom parsing routine.

This is one of the neat tricks you can do with DB2 9's new XML functionality. But it's not the only one. XML is everywhere—DB2 9 gives you the tools to master it.

Check out the following resources to download Express-C and learn more about XQuery and working with XML data types in DB2 9.

• Download DB2 9 Express-C
• Introducing IBM DB2 Viper Workbench, featuring the XQuery Visual Builder