Develop a store locator application using IBM DB2 pureXML and ASP.NET

Create a Web app to browse and maintain store information and plot coordinates on a map

We live in a connected and open world, one where data flows freely and where a vast amount of useful information can be found on the Web. In the past, if you wanted to find where the nearest store for your favorite retailer was located, you would probably look it up in the telephone directory, find the company's phone number, call them, and ask for directions to their nearest outlet. This method is a recipe for getting lost, wasting time, and a general frustration for the customer. Today, however, this has all changed. Now you simply open your Web browser and visit the company's Web site, where you can usually find a "Store Locator" feature that will help you find the store nearest to you, and conveniently plot it on a map to make it easier to find. In this tutorial, you will learn to develop such a feature using C# ASP.NET and an IBM DB2® database.


Joe Lennon, Software developer, Core International

Photo of Joe LennonJoe Lennon is a 24-year-old software developer from Cork, Ireland. Joe is author of the forthcoming Apress book Beginning CouchDB, and has contributed several technical articles and tutorials to IBM developerWorks. In his spare time, Joe likes to play football (soccer), tinker with gadgets and work on his Xbox 360 gamer score.

08 December 2009

Also available in Chinese

Before you start

This tutorial is geared towards developers who want to learn how to store data in XML format in a database, connect to DB2 from a .NET application, and learn how to develop applications that leverage mapping APIs. To follow this tutorial, familiarity with the .NET framework and C# is recommended. You should also have some experience of developing with HTML and/or XML, and of communicating with a database management system using SQL. When you complete this tutorial, you will have the skills to create map-enabled .NET applications powered by a DB2 database using pureXML® to store XML natively.

About this tutorial

Recent versions of the IBM DB2 database management system include a feature known as pureXML, which allows for XML data to be stored natively in a relational database. The concept of pureXML is unique in that it not only allows for the native storage of XML, but it also includes native processing of XML data directly in the database, as opposed to converting it into relational data or storing it as flat text. The beauty of pureXML is that it allows you to store a combination of traditional relational data that is stored in tables and columns, with document-oriented XML data, which is generally self-contained in a single XML document. IBM DB2 provides a series of methods by which you can work with this hybrid of data: SQL, SQL/XML, and XQuery.

Frequently used acronyms

  • API: Application programming interface
  • HTML: Hypertext Markup Language
  • KML: Keyhole Markup Language
  • RSS: Really Simple Syndication
  • SQL: Structured Query Language
  • UI: User interface
  • URL: Uniform Resource Locator
  • XML: Extensible Markup Language

When it comes to developing Web applications, XML is often a good choice for a data storage model as it is flexible and allows for schema changes to be made very easily without having an adverse affect on existing data. In addition, since XML is an open standard, you can easily take this data and use it on virtually any platform and development framework. Add to that the fact that many open Web services and APIs make data available as XML, and you can see why XML storage is so important when it comes to the Web.

In this tutorial, you will develop a Store Locator application using C# ASP.NET that stores its data in an IBM DB2 database. The underlying data will be stored in a database table with a relational ID column and an XML column where the actual store location data will be held. You will create relational views of this data that make it simple to use Visual Studio's data components to connect UI elements directly to your DB2 data, before learning how to import some existing XML documents into the database in bulk.


To follow the steps in this tutorial, you will need to have the following software installed:

  • IBM DB2 Express-C 9.5 or later
  • Microsoft® Visual Studio® 2008 (earlier versions might work but some features might not be present)
  • IBM DB2 Database Add-ins for Visual Studio

See Resources for the links to download these prerequisites. When developing the .NET application, you will use the DB2 .NET provider to communicate with the DB2 server from your code. You must enable this provider before you can use it in Visual Studio, however. After you have installed both DB2 and Visual Studio, run the Configure DB2 .NET Data Provider tool as found in Start>Programs>IBM DB2>[DB2 Instance Name]>Set-up Tools.

Setting up the database

In this section, you will create a new DB2 database for the Store Locator application. You will then create a table to store each location and two views to give a relational representation of the XML data. Finally, you will import a series of XML documents into the database using a .del file and the DB2 IMPORT command.

Creating the database

The first step is to create the DB2 database itself. To do this, open the DB2 Command Editor from Start>Programs> IBM DB2 >[DB2 Instance Name]>Command Line Tools>Command Editor and enter the following command: create database storeloc using codeset UTF-8 territory US.

After a minute or so, DB2 should respond with a message like: DB20000I The CREATE DATABASE command completed successfully.

Tip: You can quickly execute commands in Command Editor by pressing Ctrl+Enter.

Next, you need to tell Command Editor to connect to the newly created database. You can do this by issuing the command: connect to storeloc.

DB2 should respond with some database connection information and the message: A JDBC connection to the target has succeeded.

Next you'll create the table where the store location data will be stored.

Creating the store table

Create the store table with the CREATE TABLE statement in Listing 1 below.

Listing 1. Create the store table
create table store (
    store_id int not null generated by default as identity,
    info xml not null,
    primary key(store_id)

As you can see, the store table has two columns: store_id (the primary key) and info. As you might expect, the store_id is a unique identifier for the row. This column uses the clause generated by default as identity, which will automatically increment the value by one as each row is inserted, much like the auto_increment feature in the MySQL database. The info column is of the XML type, which means that its contents will be native XML data. Executing this statement results in the following response: DB20000I The SQL command completed successfully.

Before moving on to the next section, look at a sample of an XML document that will be stored in the info column in the store table (Listing 2).

Listing 2. Sample XML document
    <title>Boston, MA (Newbury)</title>
            <street>Newbury Street</street>
        <phone>(617) 555-9476</phone>
        <fax>(617) 555-9477</fax>
    <manager>Thomas Ryan</manager>
        <day name="Monday">09:00 to 18:00</day>
        <day name="Tuesday">09:00 to 18:00</day>
        <day name="Wednesday">09:00 to 18:00</day>
        <day name="Thursday">09:00 to 21:00</day>
        <day name="Friday">09:00 to 21:00</day>
        <day name="Saturday">10:00 to 15:00</day>
        <day name="Sunday">Closed</day>

Each store location in the database will have a document like the one in Listing 2 stored in the info column of the store table. As you can see, the parent XML element is <store>, and this contains a series of child nodes: <title>, <contact>, <manager>, <hours> and <location>, some of which have elements nested beneath them. Next, you will create two views that will map this XML data into relational columns, making it easier to map .NET components to the DB2 data.

Creating the store_view and store_hours_view views

The first view you will create is store_view, which maps all of the data in the XML document, other than the opening hours information, to relational-style columns that you can work with using standard SQL statements. Listing 3 displays the code for creating this view.

Listing 3. Creating the store_view DB2 view
create view store_view(
    store_id, title, address_street, address_city, address_state, 
    address_zip, phone, fax, email, manager, latitude, longitude, 
) as
    select s.store_id, x.*
    from store s, xmltable('$d/store' passing as "d"
        title varchar(200) path 'title',
        address_street varchar(200) path 'contact/address/street',
        address_city varchar(100) path 'contact/address/city',
        address_state varchar(2) path 'contact/address/state',
        address_zip varchar(15) path 'contact/address/zip',
        phone varchar(30) path 'contact/phone',
        fax varchar(30) path 'contact/fax',
        email varchar(255) path 'contact/email',
        manager varchar(200) path 'manager',
        latitude varchar(50) path 'location/latitude',
        longitude varchar(50) path 'location/longitude',
        zoom varchar(4) path 'location/zoom'
    ) as x

The code in Listing 3 creates a view named store_view, which has several columns, all of which (except for store_id) map to an element in the XML document contained in the info column. This is possible thanks to the XMLTABLE function, which allows you to use XPath expressions to map XML elements and attributes to relational-style columns. Executing this code should return a familiar message: DB20000I The SQL command completed successfully.

Next, you will create a second view, named store_hours_view. In the previous view, you might notice that there is no mapping of hours XML elements to relational columns. To do this you would need to create a separate column for each child element of the <hours> node, which is not only tedious to create a view from, but also means more work when you retrieve the data back using SQL. Instead, it's easier to create a separate view that will store each day on its own row. The code to create this view is in Listing 4.

Listing 4. Creating the store_hours_view DB2 view
create view store_hours_view(
    store_id, day, hours
) as
    select s.store_id, x.*
    from store s, xmltable('$d/store/hours/day' passing as "d"
        day varchar(20) path '@name',
        hours varchar(50) path '.'
    ) as x

This time around, you are creating a view which will contain a single row for each day of the week for each store. Each row will contain the store_id, the name (as found in the name attribute of each <day> element) and the hours. Again you should get a successful response when you run this code. In the next section you will learn how to load data into the database from a series of XML files, before testing that the views created in this section return the correct data.

Importing data from a series of XML files

To import the XML data into the database, you will need to take the xml folder from the tutorial source code and put it somewhere convenient. I copied this folder to my C: drive and renamed it to storeloc. This folder contains a series of XML files and a file named location.del. If you open location.del with your favorite text editor, you'll notice it contains several lines, each numbered and referencing the other XML files in the folder. You will now use this file to import the contents of the accompanying XML files into the DB2 database.

In the DB2 Command Editor, enter the command from Listing 5.

Listing 5. Importing bulk XML documents
import from "C:\storeloc\locations.del" of del
    xml from "C:\storeloc"
insert into store;

You should get a response such as: SQL3149N "7" rows were processed from the input file. "7" rows were successfully inserted into the table. "0" rows were rejected.

Let's verify that the data was inserted as expected. First, issue the following command to retrieve the contents of the store table, with the XML data in its native XML format: select * from store.

The result should look like the screen capture in Figure 1.

Figure 1. store Query Results
Screen capture of store Query Results tab (shows STORE_ID and INFO columns)

Next, click the More (...) button in one of the rows in this window. This will open the XML Document Viewer, which displays a tree structure of the XML document in the info column, as in Figure 2.

Figure 2. XML Document Viewer
Screen capture of Tree View tab in XML Document Viewer (with sample information from Listing 2

Before you move on, it would be wise to verify that the two views you created earlier are displaying the correct data. First, issue the following command to test the primary store_view view: select * from store_view.

This statement should display seven rows in the Query Results tab, with each and every column populated with data (including phone number, e-mail address, manager name, and latitude). See Figure 3 for a sample of these columns.

Figure 3. store_view Query Results
Screen capture of store_view Query Results

Finally, execute the following query: select * from store_hours_view.

Again, each column should be populated, and this time you should have 49 rows—seven for each store (7 stores * 7 days of the week = 49 rows). Your database is now ready, and in the next section you will begin the development of an ASP.NET application that is powered by it.

Creating the ASP.NET application

In this section you will learn how to create an ASP.NET Web application that connects to a DB2 database, allowing users to view different store locations, and allowing administrators to manage these locations. First, you will open a new ASP.NET Web Site project in Microsoft Visual Studio. Next, you will add a reference to the IBM DB2 Data Adapter, so you can work with DB2 databases in your code. Finally, you will create a Master Page, which will frame each and every page that you create with a header and navigation.

Setting up the ASP.NET project

Let's get started. Open Microsoft Visual Studio 200. To create a new Web Site, select File>New Web Site from the application menu. This will open the New Web Site dialog box , which looks like the screen capture in Figure 4.

Figure 4. New Web Site dialog
Screen capture of New Web Site dialog with default and custom templates, plus location and language fields

Keep the default option selected (ASP.NET Web Site) and make sure that File System is selected in the Location drop-down list and that Visual C# is selected from the list of languages. Finally, change the WebSite1 at the end of the location text box to Stores. Press OK to open the project.

Adding the DB2 Data Adapter Reference

To communicate with DB2 in C#, you will need to add a reference to the DB2 Data Adapter to your ASP.NET project. There are two ways of doing this:

  • Manually add the reference to the <assemblies> section of your project's web.config file.
  • Navigate to Website>Add Reference from the Visual Studio application menu. This will open the Add Reference dialog box, as in Figure 5.
Figure 5. Add Reference dialog
Screen capture of Add Reference dialog showing Component Name, Version, Runtime, and Path columns on .NET tab

Scroll down to find the component IBM.Data.DB2 and click it to select it. Now press OK to add the reference to your project. Before you move on to create the application's Master Page, take a moment to verify that the DB2 Data Adapter was added to your project. From Solution Explorer (see Figure 6), double-click on the web.config file to open it.

Figure 6. Solution Explorer
Screen capture of Solution Explorer

This XML file contains a host of important properties for your ASP.NET project. Find the <compilation> element, which should have a child node <assemblies>. One of the <add> child nodes beneath this element should read like the following: <add assembly="IBM.Data.DB2, Version=, Culture=neutral, PublicKeyToken=7C307B91AA13D208" />.

If this line is in your web.config file, the DB2 Data Adapter reference assembly has been added to your ASP.NET project. In the next section, you will create the Master Page for the project, which will house the header and navigation for the Store Locator application.

Creating the application Master Page

A Master Page is a feature of ASP.NET applications that allows you to define a layout for use by one or more of the pages in your application. A Master Page will often contain a header, navigation, and footer, which will stay the same in each content page that inherits the Master Page. Using Master Pages allows you to avoid repetitive code for laying out your Web application, while facilitating the definition of a standard look and feel that will be used by your pages.

In Visual Studio, right click on your Project in Solution Explorer, and choose Add New Item from the context menu. This will open the Add New Item dialog box, as in Figure 7.

Figure 7. Add New Item dialog
Screen capture of Add New Item dialog showing list of default templates, with Master Page selected

From this dialog, select Master Page from the Templates box, ensure that the Language selected is Visual C#, and that Place code in a separate file is checked. You can leave the default name (MasterPage.master) as is. When you are ready, press Add to create the Master Page. The MasterPage.master will open in the code editor window.

Your first change is to the title of the page, found between the opening and closing <title> tags, to Store Locator. Next, you need to add some attributes to the <body> tag, which will remove the page margin and facilitate the loading of Bing Maps later in this tutorial. Change the text of the <body> tag so that it looks as follows: <body id="pageBody" runat="server" style="margin: 0px; padding: 0px">.

Next, find the opening <div> tag (on the line beneath the <form> tag) and add a style attribute: <div style="margin: 0px; padding: 0px; font-family: Arial">.

Beneath this line (and above the <asp:ContentPlaceHolder> tag), add the following <div> tag to create the main heading for the application (see Listing 6).

Listing 6. Store Locator heading
<div style="background-color: #999966; font-weight: bold; 
  font-size: xx-large; padding: 5px, 15px; 
  border-bottom: 2px solid #000">
    <a href="ListStores.aspx" style="color: #fff; 
      text-decoration: none;">
        Store Locator

Next, create the navigation bar. Directly beneath the code you added from Listing 6 above, add the code in Listing 7 to create a bar with two sections, one which you will use to create a Search by State feature in just a moment, and the other which will house the links to the administration pages.

Listing 7. Navigation Bar
<div style="background-color: #333300; color: #fff; 
  padding: 5px 15px; border-bottom: 1px solid #000">
    <div style="width: 47%; float: left">
        Search by State:
    <div style="width: 50%; float: left; padding-top: 5px; 
      text-align: right;">
        <a href="AddNewStore.aspx" style="color: #fff">
            Add New Store
        <a href="ManageStores.aspx" style="color: #fff">
            Manage Stores
    <div style="clear: both; font-size: 1px;">&nbsp;</div>

Next, switch to Design view. Your Master Page should look something like the one in Figure 8 at this point.

Figure 8. Unfinished Master Page
Screen capture of unfinished Master Page with Search by State field and links to Add New Store and Manage Stores

From the Visual Studio Toolbox, drag a DropDownList control to your form and drop it to the right of the Search by State text in your Master Page. From the Properties window, find the ID property and change it from DropDownList1 to ddlStates. Next, drag a Button component onto your form, this time dropping it to the right of the DropDownList you just added. Change the ID property for this button to btnSearch, and change the Text property to Go!.

At present, the DropDownList component on the Master Page is Unbound, meaning that it has no data source and as a result, will not display any items. Let's rectify that by binding it to a data source. Right-click on the DropDownList and select Show Smart Tag. From this list, select Choose Data Source, which will open the Data Source Configuration Wizard. In this dialog, from the Select a data source options choose <New data source>. You will be asked where the application will get data from. Select Database and press OK.

On the next screen, press the New Connection button to open the bAdd Connectionb dialog. Next to Data Source, press Change and select IBM DB2, IDS and U2 Servers from the popup window. Back on the Add Connection page, enter your IBM DB2 connection credentials and select the STORELOC database. Press Test Connection to ensure that the application can connect to DB2, and press OK to add the connection. You should now be back at the Choose Your Data Connection window, where you should see your STORELOC database connection in the drop-down list, as in Figure 9.

Figure 9. Choose Your Data Connection dialog
Screen capture of Choose Your Data Connection dialog

Press Next to move on, and on the Save the Connection string screen, leave the default selections and press Next to go ahead to the Configure the Select Statement screen. Select Specify a custom SQL statement or stored procedure and press Next. In the SQL statement box under the SELECT tab, enter the following statement: select distinct(address_state) from store_view.

Press Next to go to the next screen and press Test Query to ensure that your statement returns rows. When you are ready, press Finish. You should now be back at the Choose a Data Source screen, and ADDRESS_STATE should be preselected in the two drop-down lists that you enabled (Figure 10). Press OK to close this dialog, and your DropDownList control should now have the text Databound.

Figure 10. Choose a Data Source dialog
Screen capture of Choose a Data Source dialog

Your Master Page is complete. The Design view of the final version should look like the screen capture in Figure 11.

Figure 11. Final Master Page view
Screen capture of final Master Page view with Search by State dropdown list, Go! button, and add and manage links

Before you move on, however, you need to define the action that will occur when you press the Go button next to the Search by State drop-down list. To do so, in Design View double-click the Go button. This will open the code file (MasterPage.master.cs) with the cursor inside the function btnSearch_Click. Inside this function, enter the following code: Response.Redirect("SearchResults.aspx?state=" + ddlStates.SelectedValue);.

This code will redirect the user to the SearchResults.aspx page, passing the Query String parameter state, with the value set to the currently selected state in the drop down list.

With the Master Page created and the project set up, you are now ready to develop the main Store Locator application. In the next section you will concentrate on producing the Client Interface, where a Web site visitor might come to browse through your company's stores.

Creating the client user interface

In this section, you will create the client user interface for the Store Locator application. You will create a landing page, giving a listing of all stores, for visitors on their initial visit to the Store Locator. Next, you will create a Search Results page, which will be called when you press the Go! button in the Master Page's Search By State section. Finally, you will create the View Store page, which will list the information stored about a particular store in the database.

The application home page

To create the application home page, right click the Project folder in Solution Explorer and select Add New Item from the Context Menu. Select Web Form from the list of templates and give the file the name ListStores.aspx. Make sure that the selected language is Visual C#, and that you select both of the Place code in a separate file and Select master page, as in Figure 12.

Figure 12. Add New Item dialog
Screen capture of Add New Item dialog, Web Form templage selected

You will now be asked to select a master page. Select MasterPage.master and click OK to continue. The new Web Form will open in Source view, and you'll notice that the code has two <asp:Content> section. The second of these, with the ContentPlaceHolderID value of ContentPlaceHolder1, is where you will put your components.

Switch to Design View and you will notice the MasterPage elements are slightly greyed out and that you cannot modify or access them. You should also notice that the ContentPlaceHolder is the white section of the page with a purple border around it. This is where you will place your Web components.

First drag a Label into this area. Change the Text property to Store List, and modify the Font properties so that it is x-large in size and that the text is bold. From the Data section of the Visual Studio toolbox, find the DataList component and drag it beneath the Store List label you just added. Right-click the DataList component and select Show Smart Tag, and select New data source from the Choose Data Source list. This screen should look familiar. Select Database on the first screen, and then select the Connection String you saved earlier from the Data connection list.

On the Configure the Select Statement screen, leave Specify columns from a table or view selected and choose the STORE_VIEW view from the list. The columns in your view should now be populated. Select STORE_ID and TITLE and press the ORDER BY button. In the first Sort By list, select TITLE> and press OK. Now press Next and on the following screen test that your query is working. If you are happy, press Finish to exit the dialog. Your data will now show up in Design View similar to that in Figure 13.

Figure 13. DataList
Screen capture of DataList with a vertical list of STORE_ID 0 through 4 with TITLE for each

As you can see, the list is currently showing both the Store ID and the Title, and all in a straight line at that. Not very attractive, is it? Fortunately, Visual Studio makes it very easy to style components like this. From the Smart Tag for the DataList, select Auto Format. From here select Mocha and press OK to apply the style. Next, select Property Builder, and from the General tab, under Repeat Layout enter 2 in the Columns box (since you want two columns as in Figure 15). Your DataList should now look like Figure 14.

Figure 14. Styled DataList
Screen capture of styled DataList with table-style list of STORE_ID 0 through 4 with TITLE for each

This looks a bit better, but in each item you only want to show the Title and have it link to the View Store page for that particular store. You can do this using the Edit Templates link in the Smart Tag, but instead let's make this change in the Source View. Within your <asp:DataList> object, you will find a tag <ItemTemplate>. Replace all the code inside the <ItemTemplate> tag with the code from Listing 8.

Listing 8. DataList Item Template Code
<a href="ViewStore.aspx?id=<%#Eval("STORE_ID") %>">
    <%#Eval("TITLE") %>
</a><br />

Let's have a look at how this looks when you run the application. Before you start the application, right click on ListStores.aspx in Solution Explorer and select Set as Start Page to make it the page that launches when you start your project. Now, press Ctrl+F5 to build and open your application in your Web browser. The result should look like Figure 15.

Figure 15. Store List page
Screen capture of Store List pagewith list of stores located in seven towns and states

In the next section, you'll create a similar style of page to this one, except that it will only show rows for a particular state, as selected in the main drop-down list in the Master Page.

Search Results page

Creating the Search Results page is very similar to the ListStores page. Right-click the Project folder in Solution Explorer and choose Add New Item. Make sure the selections you choose are the same as previously, but this time around, name the item SearchResults.aspx. Once again, select the MasterPage.master when asked to select a Master Page. Create a label, style it the same as previously, and give it the text Search Results. On a new line, enter the text Results for and drag a new label directly after this text. Change the ID of this label to lblState and clear the Text property. Also, make this label bold. Now, right-click on SearchResults.aspx in Solution Explorer and choose View Code.

The source code behind this file (SearchResults.aspx.cs) will appear, and you will see an empty Page_Load method. In this method, add the code from Listing 9.

Listing 9. Setting the selected state
if(Request.QueryString.Count > 0)
    lblState.Text = Request.QueryString[0];
    lblState.Text = "MA";

Next, go back to the Design view of the SearchResults.aspx file and add a couple of line breaks. From the Data section of the Toolbox, drag a GridView control onto the page. Once again, select New Data Source and run through the same process for selecting a data source as the previous section, except this time you select the following columns when you configureg the select statement: STORE_ID, ADDRESS_STREET, ADDRESS_CITY, ADDRESS_STATE, ADDRESS_ZIP, PHONE and EMAIL. Also, click on the WHERE button, as you only want to show stores in the selected state.

In the Add WHERE Clause dialog (see Figure 16), select ADDRESS_STATE in the list of columns, the = operator, and QueryString as the source. In the parameter properties, you should specify state as the QueryString field and MA as the default value. When you are ready, press Add to create this clause in your SQL statement.

Figure 16. Add WHERE Clause dialog
Screen capture of Add WHERE Clause dialog, fields completed as described

Again, feel free to run the Test Query function to make sure that your query actually works. When you are happy with the results, press Finish to close the wizard. As it stands, the Grid is pretty ugly—the headings are uppercase with underscores and it's very dull as there is no color. You can prettify the grid very easily using the Auto Format option. Again, select Mocha from the list of formatting styles. Also, check the Enable Sorting check box, which will allow users to sort the data by clicking on the heading for a particular column. To address, the issue of the column headings, click Edit Columns in the GridView's Smart Tag. This will open the Fields dialog box, as in Figure 17.

Figure 17. Fields dialog
Screen capture of Fields dialog with STORE_ID field selected

To change the header text for a column, select that column from the Selected fields list and edit the HeaderText property from the BoundField properties section. Repeat this for each column. The final change required on this page is to add a link to each row that will take you to the View Store page for that particular store. From the GridView's Smart Tag, select Add New Column. This will open the Add Field dialog, as in Figure 18.

Figure 18. Add Field dialog
Screen capture of Add Field dialog, fields completed as described

Select HyperLinkField from the type choices and give it the header text value of Actions. In the Hyperlink text section, choose the Specify text radio button and enter View. Finally, in the Hyperlink URL section, choose the Get URL from data field button and select the STORE_ID field. In the URL format string box, enter: ViewStore.aspx?id={0}.

You should now be ready to take the Search feature for a test drive. This time there's no need to change the start page, as you can search from any page in the application. Simply press Ctrl+F5 to build and run the application. In the Search by State drop down list, select a state and press Go! to navigate to the Search Results for that state. The result for CA looks like the screen capture in Figure 19.

Figure 19. Search Results for CA
Screen capture of Search Results for CA, listing Santa Rosa and Los Angeles stores

Next, you will put the last piece of the client side puzzle in place, the View Store page, where you can see all the data for a particular store.

View Store page

Once again, add a new item to your project, this time naming it ViewStore.aspx. Add a label, giving it the text Store Information. Add another label beneath this, making the text large and bold, with the text set to Store Details. Now drag a DetailsView control beneath this. Create a new data source once more, this time selecting all fields except for latitude, longitude, and zoom from the STORE_VIEW view. Add a WHERE clause on the STORE_ID column with a QueryString source from the field id, with a default value of 1, as in the Add WHERE Clause dialog in Figure 20.

Figure 20. Add WHERE Clause dialog
Screen capture of Add WHERE Clause dialog, as completed for the ViewStore item

Choose AutoFormat and select Mocha once again to style the DetailsView. Use the Edit Fields option in the DetailsView Smart Tag to change the header text of each field. Also change the HeaderStyle so that it is displayed in bold. Change the Width property of the DetailsView object to 475px so that all the data is displayed neatly. The Design View of your page should look something like Figure 21 at this stage.

Figure 21. Design View of ViewStore.aspx
Screen capture of Design View of ViewStore.aspx

Next, you will add the store's opening hours to this page. Add a new label beneath the DetailsView, make the text large, set it to bold and give it the Text property Store Opening Hours. Beneath this label, drag a GridView control, and use AutoFormat to set it to the Mocha style. Select New data source, and this time select your columns from the STORE_HOURS_VIEW view. Select the DAY and HOURS columns and press the WHERE button to define how to determine what store to select hours for. The values in this should be the same as used earlier, as in Figure 20. Use Edit Columns to format the Grid's heading labels, and press Ctrl+F5 to run the application. When the application runs, click on a Store to go to the View Store page for that store. You should see a result like the page (I clicked on the Seattle store) in Figure 22.

Figure 22. View Store page
Screen capture of View Store page with store details and opening hours for Seattle WA store

That's it for the View Store page, and that concludes the client interface for the application. In the next section you will learn how to create pages that allow you to add a new store and delete existing ones. You will return to the View Store page later in this tutorial when you add a Bing Map view of the store's location.

Creating the Admin User Interface

The Admin User Interface for the Store Locator application comprises two pages:

  • One to add new stores to the database. The Add New Store page is quite a departure from the way you've developed all the screens up until this point. Rather than using drag and drop components and wizards, you will create this screen mainly using code. The Manage Stores page is created using wizards, and is similar to the Search Results page, save for the fact that it displays all stores in a grid, and allows you to delete stores.
  • Another to manage existing stores, in other words, delete existing stores. The Manage Stores page is created using wizards, and is similar to the Search Results page, save for the fact that it displays all stores in a grid, and allows you to delete stores.

Add New Store page

As was stated in the introduction to this part of the tutorial, you will create the Add New Store page using quite a different technique to each of the other pages you have created up until this point. Let's dive right in. Add a new item to your project and name it AddNewStore.aspx. This time, stay in Source view and add the following code inside the ContentPlaceHolder1 section of the page (see Listing 10).

Listing 10. Add New Store Form
<asp:Label ID="Label1" runat="server" Font-Size="x-large" 
Font-Bold="true" Text="Add New Store" /><br />
<asp:Label ID="lblMessage" runat="server" Font-Size="large" 
Font-Bold="true" Visible="False" />
<table cellspacing="0" cellpadding="4" width="450" border="0" 
style="border: 1px solid #000">
    <thead><tr><th colspan="2" style="background-color: 
#ccc; border-bottom: 1px solid #000; text-align: left">Store 
    <tbody><tr><td width="150">Store Title:</td>
      <td width="300"><asp:TextBox ID="txtStoreTitle" runat="server" 
Columns="50" /></td>
    <tr><td width="150">Street Address:</td>
      <td width="300"><asp:TextBox ID="txtAddrStreet" runat="server" 
Columns="50" /></td>
    <tr><td width="150">City:</td>
      <td width="300"><asp:TextBox ID="txtAddrCity" runat="server" 
Columns="50" /></td>
    <tr><td width="150">State:</td>
      <td width="300"><asp:TextBox ID="txtAddrState" runat="server" 
Columns="50"  /></td>
    <tr><td width="150">Zip Code:</td>
      <td width="300"><asp:TextBox ID="txtAddrZip" runat="server" 
Columns="50" /></td>
<tr><td width="150">Store Manager:</td>
      <td width="300"><asp:TextBox ID="txtManager" runat="server" 
Columns="50" /></td>
<tr><td width="150">Phone Number:</td>
      <td width="300"><asp:TextBox ID="txtPhone" runat="server" 
Columns="50" /></td>
<tr><td width="150">Fax Number:</td>
      <td width="300"><asp:TextBox ID="txtFax" runat="server" 
Columns="50" /></td>
<tr><td width="150">Email Address:</td>
      <td width="300"><asp:TextBox ID="txtEmail" runat="server" 
Columns="50" /></td>
</tbody></table><br />

As you can see, the code in Listing 10 defines TextBox fields for each store detail column: Title, Address data, Manager, Phone, Fax and Email. Next, let's add some fields that will allow the entry of Opening Hours data (see Listing 11). You should add this code directly below the code from Listing 10, but before the closing </asp:Content> tag.

Listing 11. Add New Store Opening Hours Form
<table cellspacing="0" cellpadding="4" width="450" border="0" 
style="border: 1px solid #000">
    <thead><tr><th colspan="2" style="background-color: #ccc; 
border-bottom: 1px solid #000; text-align: left">Opening 
    <tbody><tr><td width="150">Monday:</td>
      <td width="300"><asp:TextBox ID="txtMonday" runat="server" 
Columns="50" /></td>
    <tr><td width="150">Tuesday:</td>
      <td width="300"><asp:TextBox ID="txtTuesday" runat="server" 
Columns="50" /></td>
    <tr><td width="150">Wednesday:</td>
      <td width="300"><asp:TextBox ID="txtWednesday" runat="server" 
Columns="50" /></td>
    <tr><td width="150">Thursday:</td>
      <td width="300"><asp:TextBox ID="txtThursday" runat="server" 
Columns="50" /></td>
    <tr><td width="150">Friday:</td>
      <td width="300"><asp:TextBox ID="txtFriday" runat="server" 
Columns="50" /></td>
<tr><td width="150">Saturday:</td>
      <td width="300"><asp:TextBox ID="txtSaturday" runat="server" 
Columns="50" /></td>
<tr><td width="150">Sunday:</td>
      <td width="300"><asp:TextBox ID="txtSunday" runat="server" 
Columns="50" /></td>
</tbody></table><br />

Finally, create a button at the end of the page, which, when clicked, will save the form data into the DB2 database (see Listing 12).

Listing 12. Button to save store data
<table width="450">
    <tbody><tr><td align="center">
        <asp:Button ID="btnAdd" runat="server" 
          Text="Add New Store" />

Add the code from Listing 12 directly beneath the code in Listing 11. With the layout of the Add New Store form created, it's time to add some code to actually save this data in the DB2 database. Right-click the ViewStore.aspx file in Solution Explorer and choose View Code to view the source code behind the page. All that should be there at present is an empty Page_Load page and some using statements to import .NET features into the file.

Below the last one of these using statements, add the following lines (see Listing 13).

Listing 13. Saving the data in the DB2 database
using IBM.Data.DB2;
using System.Xml;

Next, you need to create a method that will establish the connection to the DB2 database. Add this code directly above the Page_Load method declaration, but inside the class declaration (see Listing 14).

Listing 14. ConnectDb method
public static DB2Connection ConnectDb() {
    String dsn = "STORELOC";
    String userid = "USERID";
    String password = "PASSWORD";

    String connectString = "Database="+dsn+";UID="+userid+";PWD="+password;

    DB2Connection conn = new DB2Connection(connectString);
    try {
    } catch(Exception e) {

    return conn;

Of course, be sure to replace the USERID and PASSWORD values with your own credentials. With this method created, you can now turn your attention to handling the event when the Add New Store button is clicked. Switch to Design view and double-click on this button. This will bring you back to the code, with a new method btnAdd_Click. Inside this block, add the code from Listing 15.

Listing 15. Preparing the DB2 connection
DB2Connection conn = ConnectDb();
DB2Command cmd = conn.CreateCommand();
XmlDocument x = new XmlDocument();
try {

} catch(Exception ex) {
} finally {

Within the try block in Listing 15, you need to build up an XML document based on the values entered in the Web Form. First, look at the main store details, such as title, contact and manager details (see Listing 16).

Listing 16. Building up the XML document
XmlElement store = x.CreateElement("store");

XmlElement title = x.CreateElement("title");
title.InnerXml = txtStoreTitle.Text;

XmlElement contact = x.CreateElement("contact");
XmlElement address = x.CreateElement("address");
XmlElement street = x.CreateElement("street");
XmlElement city = x.CreateElement("city");
XmlElement state = x.CreateElement("state");
XmlElement zip = x.CreateElement("zip");
street.InnerXml = txtAddrStreet.Text;
city.InnerXml = txtAddrCity.Text;
state.InnerXml = txtAddrState.Text;
zip.InnerXml = txtAddrZip.Text;

XmlElement phone = x.CreateElement("phone");
XmlElement fax = x.CreateElement("fax");
XmlElement email = x.CreateElement("email");
phone.InnerXml = txtPhone.Text;
fax.InnerXml = txtFax.Text;
email.InnerXml = txtEmail.Text;

XmlElement manager = x.CreateElement("manager");
manager.InnerXml = txtManager.Text;

In Listing 16, you first create an XML element <store>, and append it to the XML document x. Next, create a <title> element and add it to the <store> element, then <contact> and its child nodes, and finally the <manager> element. With each element, you set the InnerXml value of the node to the Text property of the relevant text field from the Web form.

Directly below the code from Listing 16, add the code from Listing 17.

Listing 17. Building up the XML document (continued)
XmlElement opening_hours = x.CreateElement("opening_hours");

XmlElement Monday = x.CreateElement("day");
XmlElement Tuesday = x.CreateElement("day");
XmlElement Wednesday = x.CreateElement("day");
XmlElement Thursday = x.CreateElement("day");
XmlElement Friday = x.CreateElement("day");
XmlElement Saturday = x.CreateElement("day");
XmlElement Sunday = x.CreateElement("day");
Monday.SetAttribute("name", "Monday");
Tuesday.SetAttribute("name", "Tuesday");
Wednesday.SetAttribute("name", "Wednesday");
Thursday.SetAttribute("name", "Thursday");
Friday.SetAttribute("name", "Friday");
Saturday.SetAttribute("name", "Saturday");
Sunday.SetAttribute("name", "Sunday");
Monday.InnerXml = txtMonday.Text;
Tuesday.InnerXml = txtTuesday.Text;
Wednesday.InnerXml = txtWednesday.Text;
Thursday.InnerXml = txtThursday.Text;
Friday.InnerXml = txtFriday.Text;
Saturday.InnerXml = txtSaturday.Text;
Sunday.InnerXml = txtSunday.Text;

The code in Listing 17 follows along the same lines as before, except that with each <day> node you are also setting the name attribute, as well as the InnerXml property.

With the XML document prepared, you are now ready to add this to the database. Below the code from Listing 17, add the following code (see Listing 18).

Listing 18. Performing the Database Insert
cmd.Parameters.Add(new DB2Parameter("@XMLData", x.InnerXml));
String stmt = "INSERT INTO store(info) VALUES(@XMLData);";
cmd.CommandText = stmt;
int rowsAffected = cmd.ExecuteNonQuery();

if(rowsAffected > 0) {
    lblMessage.Text = "Store added successfully.";
} else {
    lblMessage.Text = "Could not add store.";

lblMessage.Visible = true;

That concludes the Add New Store page development. Press Ctrl+F5 to run the application. Click on the Add New Store link at the top of the page to go to the Add New Store Web Form, and fill in the form fields before you press the Add New Store button. Figure 23 shows the form in action.

Figure 23. Add New Store Form
Screen capture of Add New Store Form, fields contain sample information

Next you will create a page where you can delete locations.

Manage (Delete) Stores page

From Solution Explorer, right click on the Project Folder and select Add New Item, and add a new ASP.NET page to the project, named ManageStores.aspx. Once again, create a Label on this page, format it bold, and set the font size to large. Change the Text property to Manage Stores. Directly beneath this label, create another label, and set ID to lblMessage, set the Font-Size to Large, set the Visible property to false, and clear the Text property so it is empty.

Next, drag a GridView component beneath these labels. AutoFormat it to the Mocha style and set up a new data source. When you configure the SELECT statement, choose to Specify a custom SQL statement or stored procedure. In the SELECT tab, enter the following statement: select TITLE, PHONE, EMAIL, MANAGER, STORE_ID from SCHEMA.STORE_VIEW order by TITLE.

Don't forget to change SCHEMA to your own database schema name. Next, switch to the DELETE tab and enter the following statement: delete from SCHEMA.STORE where STORE_ID=@STORE_ID;.

Before you test that the page is working, go ahead and format the column headings in the GridView control (remove capitalization). Also, check the boxes for Enable Paging, Enable Sorting, and Enable Deletion in the GridView's Smart Tag.

Next, switch to Source view and find the <sql:DataSource> tag. Add a new attribute as follows: OnDeleted="OnRecordDeleted". Also, find the <asp:GridView> tag and add the following attribute: DataKeyNames="STORE_ID".

Next, toopen the source view for the page, right-click anywhere on the page and select View Code. Add the following method below the Page_Load method (see Listing 19).

Listing 19. OnRecordDeleted method
protected void OnRecordDeleted(object source, SqlDataSourceStatusEventArgs e) {
    lblMessage.Text = "Store was deleted successfully.";
    lblMessage.Visible = true;

Now press Ctrl+F5 to build and run the application. Click on Manage Stores to view the newly created Manage Stores page. As you can see, all of the stores are displayed (or the first ten are if you have over ten and have enabled paging). Try deleting a store. It should be removed from the GridView and you should see a message telling you that it was successfully deleted. Figure 24 shows the page.

Figure 24. Manage Stores Page
Screen capture of Manage Stores Page, displaying information for seven stores

That concludes the development of the admin interface for the Store Locator application. In the next section, you will add some neat features to the application by integrating the Bing Maps API into the View Store and Add New Store pages.

Adding Bing Maps functionality

Up until this point, you might notice that the Store Locator application has not used the latitude and longitude values that were imported along with the rest of the Store data from the series of XML files at the beginning of this tutorial. In this section, you will rectify that and implement the Microsoft® Bing Maps API, to display a map indicating the store's location on the View Store page. In addition, you will add a map to the Add New Store page, where you can Ctrl+Click on a point on the map to set the coordinates for the store to that point.

Viewing a store on a map

Open the ViewStore.aspx page and ensure that you are in Source view. First, define where the map will be displayed on the page. Right at the end of the file, just before the closing </asp:Content> tag, add the following code (see Listing 20).

Listing 20. Adding the myMap div
<asp:Label ID="lblLoc" runat="server" Font-Bold="True" 
  Font-Size="Large" Text="Store Location:"></asp:Label>
<div id="myMap" style="position: relative; width: 400px; 
height: 400px"></div>

Next, scroll up through the source view of the file until you reach the first <asp:Content> tag (it should have the ContentPlaceHolderID set to head, and currently be empty). Inside this tag, insert the JavaScript code from Listing 21.

Listing 21. Loading Bing Maps
<script type="text/javascript" 
<script type="text/javascript">
var map = null;
var pinid = 0;

function GetMap(lat, lng, zoom) {
    map = new VEMap('myMap');
    map.LoadMap(new VELatLong(lat, lng), zoom, 'Road', false);

    var shape = new VEShape(VEShapeType.Pushpin, map.GetCenter());

With the GetMap() JavaScript function added to the page, you now need to call this when the page loads. To open up the Code View of ViewStore.aspx.cs, right-click in Source view and select View Code. Add the following reference to the file by adding them beneath the list of using statements at the top of the file: using IBM.Data.DB2;.

First, add a new method to this file, just above the Page_Load method. Listing 22 shows the code for this method (ConnectDb).

Listing 22. ConnectDb Method
public static DB2Connection ConnectDb() {
    String dsn = "STORELOC";
    String userid = "USERID";
    String password = "PASSWORD";

    String connectString = "Database="+dsn+";UID="+userid+";PWD="+password;

    DB2Connection conn = new DB2Connection(connectString);
    try {
    } catch(Exception e) {

    return conn;

Next, add the following code (see Listing 23) in the Page_Load method so that it is called when the page loads.

Listing 23. Page_Load method contents
if(Request.QueryString.Count > 0) {
    DB2Connection conn = ConnectDb();
    DB2Command cmd = conn.CreateCommand();
    String stmt = "SELECT latitude, longitude, zoom FROM store_view WHERE store_id =

    cmd.CommandText = stmt;
    DB2Record row = cmd.ExecuteRow();

    HtmlGenericControl body = (HtmlGenericControl)Master.FindControl("pageBody");
    body.Attributes.Add("onload", "GetMap('" + row.GetString(0) + "', '" + 
row.GetString(1) + "', '" + row.GetString(2) + "')");

You are now ready to build and run the application. Press Ctrl+F5 to open your Web browser. Click on any store to go to the View Store page for that particular store. At the bottom of the page you should see a map with the store's location pinpointed on it, as in Figure 25.

Figure 25. Store Location using Bing Maps
Screen capture of Store Location information using Bing Maps

Next, you will learn how to set the latitude, longitude, and zoom level of the store's location when adding a new store to the database.

Setting a store's location using a map

The final feature to be added to the application is the ability to set a store location by pressing Ctrl+Enter on the map on the Add New Store page. First, open the AddNewStore.aspx file in Source view, scroll down to the bottom of the file, and insert the code in Listing 24 just before the <table> tag that contains the <asp:Button> component.

Listing 24. The Map table
<br /><table cellpadding="4" cellspacing="0" border="0" width="450"
  style="border: 1px solid #000;">
    <thead><tr><th colspan="2" style="background-color: 
#ccc; border-bottom: 1px solid #000; text-align: left">Store
    <tr><td colspan="2" align="center">Ctrl + Click to select 
a location<br />
    <div id="myMap" style="position: relative; width: 400px; height:
    <tr><td width="150">Latitude:</td>
    <td width="300"><asp:TextBox ID="txtLatitude" runat="server" Columns="50"
    <td><asp:TextBox ID="txtLongitude" runat="server" Columns="50" 
    <tr><td>Zoom Level:</td>
    <td><asp:TextBox ID="txtZoomLevel" runat="server" Columns="50"

Next, scroll up to the top as you did in the previous section, and add the code in Listing 25 to the first <asp:Content> tag (the empty one).

Listing 25. Loading Bing Maps and mouse events
<script type="text/javascript" 
<script type="text/javascript">
var map = null;
var pinid = 0;

function GetMap(lat, lng, zoom) {
    map = new VEMap('myMap');

    map.AttachEvent("onclick", MouseClick);

    function MouseClick(e) {
        if(e.ctrlKey) {
            var location = map.PixelToLatLong(new VEPixel(e.mapX, e.MapY));
            var shape = new VEShape(VEShapeType.Pushpin, location);

            document.getElementById('<%= txtLatitude.ClientID %>')
.value = location.Latitude;
            document.getElementById('<%= txtLongitude.ClientID %>')
.value = location.Longitude;
            document.getElementById('<%= txtZoomLevel.ClientID %>')
.value = map.GetZoomLevel();

This code will load the map on the default location, and listen for the mouse click event on the map. Any time the mouse is clicked, the code checks whether the Ctrl key was held down, and if so, any existing pushpins are removed from the map, and a new one is added at the location the Ctrl+Click was detected. The coordinates text boxes are updated to reflect the new position.

Next, you need to change the C# code file. Right-click anywhere in the AddNewStore.aspx page and choose View Code. Find the Page_Load method, and add the code from Listing 26 inside this method.

Listing 26. Loading the Map Control and setting the coordinates to readonly
HtmlGenericControl body = (HtmlGenericControl)Master.FindControl("pageBody");
body.Attributes.Add("onload", "GetMap()");
txtLatitude.Attributes.Add("readonly", "readonly");
txtLongitude.Attributes.Add("readonly", "readonly");
txtZoomLevel.Attributes.Add("readonly", "readonly");

This ensures that the map is loaded when the page has finished loading, and also sets the coordinates text boxes to readonly in such a manner that the data values set in them will still be sent to the server.

The final change you make is to the code that actually adds the XML data to the database in the method btnAdd_Click. Find the line that reads: Sunday.InnerXml = txtSunday.Text;.

Beneath this line, add the following code to ensure that the geo data gets added to the database along with the rest of the store's details (see Listing 27).

Listing 27. Code to add coordinates to the Database
XmlElement location = x.CreateElement("location");

XmlElement latitude = x.CreateElement("latitude");
XmlElement longitude = x.CreateElement("longitude");
XmlElement zoom = x.CreateElement("zoom");
latitude.InnerXml = txtLatitude.Text;
longitude.InnerXml = txtLongitude.Text;
zoom.InnerXml = txtZoomLevel.Text;

Your application is now ready to use. Press Ctrl+F5 to launch it once more, and this time navigate to the Add New Store page. Enter some details for the store, and at the end of the page, zoom in and pan around the map to find the store location, and when you are ready to select the location, hold Ctrl and click the position on the map where the store is located. Selected the wrong location? No problem, just Ctrl+Click again and the pushpin will move to a different location. Figure 26 shows this feature.

Figure 26. Selecting a location on the map
Screen capture of selecting a location on the map

That concludes the tutorial. Take a brief look in the next section at some suggestions that might improve theapplication.

Suggested improvements

With the skills you have learned during the course of this tutorial, you should be able to make some subtle but useful enhancements to the application. Some potential improvements include:

  • Search by other fields: zip code, city, and so on
  • Show multiple locations on the map
  • Display store details in a popup on the map itself
  • Get driving directions to a store
  • Export the store locations to a KML file so they can be viewed in an application such as Google Earth
  • Implement edit store features
  • Add a security layer around the admin interface so that only authorized and authenticated users can manage stores
  • Use a geocoding service to find the nearest store to you by entering your zip code


In this tutorial, you learned how to create a DB2 pureXML database that stores XML data natively inside a relational table. You learned how to create relational style views of this XML data, and how to import XML documents in bulk. Next, you learned how to create an ASP.NET application that uses the DB2 Data Adapter, which allows your .NET projects to communicate with a DB2 server. You created a series of client-side and admin pages that allow a user to find, view, add, and manage stores in the database. Finally, you added features to this application that plot the coordinates of a store on a Bing Map, and allow you to place a pushpin on a map to indicate where in the world a new store is located.


Store Locator source codelocator.source.zip19KB



Get products and technologies



developerWorks: Sign in

Required fields are indicated with an asterisk (*).

Need an IBM ID?
Forgot your IBM ID?

Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name

The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.


All information submitted is secure.

Dig deeper into XML on developerWorks

Zone=XML, Information Management
ArticleTitle=Develop a store locator application using IBM DB2 pureXML and ASP.NET