Chapter 16. Putting It All Together

In this chapter, you will use many of the skills acquired so far to build a set of integrated applications. The goal of these applications is to track the relative Amazon.com sales standings of my books on C#, ASP.NET, and VB.NET.

The Overall Design

To see how various technologies work together you will actually build two independent applications (a desktop application web services client and an ASP.NET application) tied together by a backend database. Specifically, you’ll create a desktop application that obtains data from Amazon’s web service and stores it in a table in a SQL Server database, and then you’ll display that data in your ASP.NET application.[1]

The SQL Server database is very simple. It is named AmazonSalesRanks and consists of a single table, BookInfo, as shown in Figure 16-1.

Designing the BookInfo table

Figure 16-1. Designing the BookInfo table

Tip

All the fields in this table are allowed to be null because you can’t control what information may or may not be available from Amazon at any particular moment. To make this design more robust, you might consider making the ISBN a primary key and rejecting any data that returns without an ISBN. This is left, as they say, as an exercise for the reader.

The Screen-Scraper Application

In previous editions of this book, we first implemented the desktop application as a screen-scraper that downloaded data from Amazon.com.

At that time, I pointed out that screen-scraping works great as long as Amazon always lists the rank in exactly this way, but if it doesn’t, the parsing will fail. Each time Amazon changes its pages, you must upgrade this program. In addition, I pointed out that the data on Amazon’s pages is copyrighted, and so there are serious legal and ethical issues about scraping Amazon’s pages.

Since the publication of the previous edition, Amazon has instituted security on its site that blocks simple screen-scraping applications. Since it also provides extensive support for web services, this chapter will implement the application using those web services, and we’ll cover screen-scraping in Chapter 21, where we’ll (legally and ethically) scrape screens from my own web site.

Creating the Web Services Client

In an effort to make its information available to developers and “Amazon Associates” (sites that allow its users to purchase books through Amazon.com), Amazon has created a set of web services. For more information, see http://www.amazon.com/gp/aws/landing.html.

Your application will use these web services, and you’ll need to download the Amazon Web Services developer kit for Version 4 (as of this writing). The three things you’ll need are an associatesTag (supplied by Amazon), a subscriberID (also supplied by Amazon), and the appropriate .wsdl file (publicly available through the Amazon Web Services pages).

Creating the Amazon proxy

As with all web services accessed through .NET, you must create a proxy class for your client. You do so by obtaining the WSDL document that Amazon supplies, and then compiling that with the command-line instruction:

wsdl /o:Amazon.cs AmazonWebServices.wsdl

Creating the desktop application

Create a new desktop application named (for example) AmazonWebServiceClient, and be sure to copy the Amazon.cs file you created from the .wsdl file into that project’s directory. Add the file to the project by right-clicking the project and choosing Add Existing Item.

Amazon provides far more information about its books and products than we’ll need, so we’ll keep it simple and extract only a subset of the information it has available.

In addition, it provides methods that return information about a collection of books, but for now we’ll greatly simplify the process (while sacrificing performance) by looking up each book one by one.

To do so you’ll create a set of XML files to contain the ISBNs of the books you want to track. I’ve divided these by technology so that I have a CSharpISBN.xml file, a VBNETISBN.xml file, and an ASPNET_ISBN.xml file.

Example 16-1 shows an excerpt from one of these files.

Example 16-1. CSharpISBN.xml

 <isbns>
  <isbn>193183654X</isbn> 
  <isbn>0130461334</isbn> 
  <isbn>1893115593</isbn> 
  <isbn>0130622214</isbn> 
  <isbn>1861007043</isbn> 
  <isbn>1861004982</isbn> 
  <isbn>0672320711</isbn> 
  <isbn>0596001819</isbn> 
  <isbn>0735612897</isbn> 
  <isbn>0735612900</isbn> 
  <isbn>0596003099</isbn> 
  <isbn>0596003765</isbn> 
  <isbn>0072133295</isbn> 
  <isbn>0672322358</isbn> 
  <isbn>0072193794</isbn> 
  <isbn>067232122X</isbn> 
  <isbn>1588801926</isbn> 
  <isbn>0672321521</isbn> 
  <isbn>0735615683</isbn> 
  <isbn>0201729555</isbn> 
  </isbns>

Tip

The problem, of course, is that these files are often out of date (some books go out of print, new books become available). We’ll solve that problem later in this chapter.

As each ISBN is read, the relevant values (title, publisher, rank) are found on the Amazon web site and stored in the Database table. A simple listbox is then updated to indicate progress. Once all the books are recorded, the system becomes dormant while a timer ticks down the remaining time between sessions. You can force a new session by clicking the Now button. This UI was intentionally created to be as simple as possible.

Example 16-2 is the complete desktop application, with analysis to follow.

Example 16-2. SalesRankDBWebServices

#region Using directives

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Windows.Forms;

#endregion

namespace AmazonWebServiceClient
{
   partial class AmazonWebServiceClient : Form
   {
      private int timeRemaining;
      const int WaitTime = 900; // 15 minutes
      private string connectionString;
      private System.Data.SqlClient.SqlConnection connection;
      private System.Data.SqlClient.SqlCommand command;

      public AmazonWebServiceClient( )
      {
         InitializeComponent( );
      }

      private void AmazonWebServiceClient_Load( object sender, EventArgs e )
      {
         // connection string to connect to the Sales Rank Database
         connectionString =
    "server=localhost;Trusted_Connection=true;database=AmazonSalesRanks";

         // Create connection object, initialize with 
         // connection string. 
         connection =
            new System.Data.SqlClient.SqlConnection( connectionString );

         // Create a SqlCommand object and assign the connection
         command =
            new System.Data.SqlClient.SqlCommand( );

         command.Connection = connection;
         timeRemaining = 1;  // when you first start up, get the info.
         UpdateButton( );

      }

      private void btnStart_Click( object sender, EventArgs e )
      {
         // toggle the timer
         updateTimer.Enabled = updateTimer.Enabled ? false : true;
         UpdateButton( );

      }

      private void btnNow_Click( object sender, EventArgs e )
      {
         timeRemaining = 2;
      }

      private void UpdateButton( )
      {
         btnStart.Text = updateTimer.Enabled ? "Stop" : "Start";
      }

      private void updateTimer_Tick( object sender, EventArgs e )
      {

         if ( updateTimer.Enabled )
            txtClock.Text = ( --timeRemaining ).ToString( ) + " seconds";
         else
            txtClock.Text = "Stopped";

         // hi ho, hi ho, it's off to work we go...
         if ( timeRemaining < 1 )
         {
            timeRemaining = WaitTime;  // reset the clock

            // create data set based on xml file
            DataSet BookData = new DataSet( );
            BookData.ReadXml( "aspnet_isbn.xml" );

            // iterate through, calling GetInfoFromISBN for 
            // each isbn found in file
            foreach ( DataRow Book in BookData.Tables[0].Rows )
            {
               string isbn = Book[0].ToString( );
               GetInfoFromISBN( isbn, "ASPNET" );
            }

            BookData = new DataSet( );
            BookData.ReadXml( "csharpIsbn.xml" );
            foreach ( DataRow Book in BookData.Tables[0].Rows )
            {
               string isbn = Book[0].ToString( );
               GetInfoFromISBN( isbn, "CSHARP" );
            }

            BookData = new DataSet( );
            BookData.ReadXml( "VBnetIsbn.xml" );
            foreach ( DataRow Book in BookData.Tables[0].Rows )
            {
               string isbn = Book[0].ToString( );
               GetInfoFromISBN( isbn, "VBNET" );
            }
         }
      }

      private void GetInfoFromISBN( string isbn, string technology )
      {
         if ( isbn.Length != 10 )
            return;

         
         AWSProductData productData = new AWSProductData( );
         ItemLookup lookup = null;
         try
         {
            ItemLookupRequest req = new ItemLookupRequest( );
            req.IdType = ItemLookupRequestIdType.ASIN;
            req.ItemId = new string[1];
            req.ItemId[0] = isbn;
            // req.SearchIndex = "Books";

            lookup = new ItemLookup( );
            lookup.AssociateTag = "libertyassocia00A";
            lookup.SubscriptionId = "0SD959SZV6KXV3BKE2R2";
            lookup.Request = new ItemLookupRequest[1];
            lookup.Request[0] = req;
         }
         catch ( System.Exception e )
         {
            lblStatus.Text = e.Message;
         }

         ItemLookupResponse response;
         Items info;
         Item[] items;
         Item item;

         int salesRank = -1;
         string author = string.Empty;
         string pubDate = string.Empty; 
         string publisher = string.Empty; 
         string title = string.Empty; 
         string strURL = string.Empty; 

         try
         {
            response = productData.ItemLookup( lookup );
            info = response.Items[0];
            items = info.Item;
            item = items[0];
            salesRank = item.SalesRank == null 
              ? -1 : Convert.ToInt32(item.SalesRank);
            author = FixQuotes( item.ItemAttributes.Author[0] );
            pubDate =   FixQuotes(item.ItemAttributes.PublicationDate);
            publisher = FixQuotes(item.ItemAttributes.Publisher);
            title = FixQuotes(item.ItemAttributes.Title);
            strURL = item.DetailPageURL;
         }
         catch ( System.Exception ex)
         {
            lblStatus.Text = ex.Message;
         }

         // update the list box
         string results = title + " by " + author + ": " +
            publisher + ", " + pubDate + ". Rank: " + salesRank;
         lbOutput.Items.Add( results );
         lbOutput.SelectedIndex = lbOutput.Items.Count - 1;

         // update the database
         string commandString = @"Update BookInfo set isbn = '" +
            isbn + "', title = '" + title + "', publisher = '" +
            publisher + "', pubDate = '" + pubDate + "', rank = " +
            salesRank + ", link = '" + strURL + "', lastUpdate = '" +
            System.DateTime.Now + "', technology = '" +
            technology + "', author = '" +
            author + "' where isbn = '" +
            isbn + "'";

         command.CommandText = commandString;
         try
         {
            // if no rows were affected, this is a new record
            connection.Open( );
            int numRowsAffected = command.ExecuteNonQuery( );
            if ( numRowsAffected == 0 )
            {
               commandString = @"Insert into BookInfo values ('" +
                  isbn + "', '" + title + "', '" + publisher + "', '" +
                  pubDate + "', '" + FixQuotes( strURL ) + "', " + 
                  salesRank + ", '" +
                  System.DateTime.Now +
                  "', '" + technology + "', '" + author + "')";

               command.CommandText = commandString;
               command.ExecuteNonQuery( );
            }
         }
         catch ( Exception ex )
         {
            lblStatus.Text = ex.Message;
            lbOutput.Items.Add( "Unable to update database!" );
            lbOutput.SelectedIndex = lbOutput.Items.Count - 1;
         }
         finally
         {
            connection.Close( );      // clean up
         }
      }     // close for GetInfoFromISBN

      private string FixQuotes( string s )
      {
         if ( s == null )
            return string.Empty;
         return s.Replace( "'", "''" );

      }
   } // end class
} // end name space

The program declares a connection string, along with SQLConnection and SQLCommand objects which will be initialized when the form is loaded:

private string connectionString;
private System.Data.SqlClient.SqlConnection connection;
private System.Data.SqlClient.SqlCommand command;

You can set the Load event by clicking the form and switching from Properties to Events. Double-click the Load event and the skeleton for the load event handler is created for you. Within that event handler, you’ll create your connection string (this example uses a trusted connection; you may need to provide a username and password depending on how your database is configured), and the connection and command objects are configured:

private void AmazonWebServiceClient_Load( object sender,EventArgs e )
{
   connectionString =
     "server=localhost;Trusted_Connection=true;database=AmazonSalesRanks";
   connection =
      new System.Data.SqlClient.SqlConnection( connectionString );
   command =
      new System.Data.SqlClient.SqlCommand( );

   command.Connection = connection;

The member variable timeRemaining is initialized to one second, and the buttons are updated to set the text on the Start button:

   timeRemaining = 1;  // when you first start up, get the info.
   UpdateButton();

}

Each time the timer clicks, the updateTimer_Tick method is called. If the timer is enabled (the user has not clicked Stop), the timeRemaining member variable is decremented, and when it hits 0 it is time to process the books:

if ( updateTimer.Enabled )
   txtClock.Text = ( --timeRemaining ).ToString() + " seconds";
else
   txtClock.Text = "Stopped";

// hi ho, hi ho, it's off to work we go...
if ( timeRemaining < 1 )
{

The first step is to reset the timer to WaitTime (a constant equivalent to 15 minutes) and then to process the .xml files:

timeRemaining = WaitTime;  // reset the clockDataSet BookData = new DataSet();
try
{
   BookData.ReadXml( "aspnet_isbn.xml" );
}

This creates a dataset, in which each row represents an entry in the XML file. Once the books are read, you extract each ISBN in turn, and call the helper method GetInfoFromISBN, passing in the ISBN and the “technology” under which this ISBN will be stored in the database:

foreach (DataRow Book in BookData.Tables[0].Rows )
{
   string isbn = Book[0].ToString();
   GetInfoFromISBN( isbn, "ASPNET" );
}

GetInfoFromISBN is the heart of the program; it is here that you contact the Amazon Web Service.

The first step is to ensure that the length of the ISBN is exactly 10 (a full check would use a regular expression to ensure that the ISBN is 9 integers followed by either an integer or the letter X, and then to perform a checksum on the ISBN [the final digit represents the checksum value], but that is left as an exercise for the reader).

The Amazon.cs file defines a number of useful objects. The ones we’ll use for this example include the AWSProductData , the ItemLookup and ItemLookupRequest, as well as the Item objects and collections. Here are the steps:

  1. Declare a new instance of the AWSProductData, which acts as the proxy to the Amazon web service.

  2. Call the ItemLookup method on the AWSProductData instance, passing in a properly initialized instance of ItemLookup.

  3. Get back an ItemLookupResponse object.

  4. Extract the Items array and from that get the first object (offset 0), an object of type Items.

  5. Ask that Items object for its Item property, which is an array of Items objects.

  6. Get the first Item in the array and from that Item, get all the information about the book you’ve requested.

To make this work, you must first create an instance of ItemLookupRequest and set its IDType property to the enumerated type ItemLookupRequestIdType.ASIN:

ItemLookupRequest req = new ItemLookupRequest();
req.IdType = ItemLookupRequestIdType.ASIN;

Initialize its ItemID array to hold one string, and set that string to the ISBN you are looking for:

req.ItemId = new string[1];
req.ItemId[0] = isbn;

Next, instantiate an ItemLookup object, and set its AssociateTag and SubscriptionID properties:

lookup.AssociateTag = "libertyassocia00A";
lookup.SubscriptionId = "Your ID Here";

Initialize its Request property to be an array of one object, and set that object to the ItemLookupRequest object you created earlier:

lookup.Request = newItemLookupRequest[1];
lookup.Request[0] = req;

Tip

Note that we’re using the method-invocation idiom as .NET assumes, but what is really going on is that we’re using SOAP to exchange messages with Amazon. We send “tell me about this book” and Amazon returns “here is information about the book.”

You’re ready to make your request. Do so in a try block to catch any exceptions that might be thrown in the process. Begin by invoking the ItemLookup method:

response = productData.ItemLookup( lookup );

Response should now be non-null. You might add error checking to handle a null response from Amazon (left out here to simplify the code). The Item property returns an object of type Items, which is an array of Item objects. You will extract the first Item object, which will contain information about the book you’ve requested:

info = response.Items[0];
items = info.Item;
item = items[0];

You can now set local variables to hold the values you’ve retrieved. The FixQuotes method is a helper method to convert single quotes in any string you receive so that they will not cause problems for the database: 

salesRank = item.SalesRank == null ? -1 :Convert.ToInt32(item.SalesRank);
author = FixQuotes( item.ItemAttributes.Author[0] );
pubDate =   FixQuotes(item.ItemAttributes.PublicationDate);
publisher = FixQuotes(item.ItemAttributes.Publisher);
title = FixQuotes(item.ItemAttributes.Title);
strURL = item.DetailPageURL;

With this information in hand, you are ready to update the listbox and, more important, to update the database.

When updating the database, you’ll first try an Update statement. If the number of rows affected is 0, the row doesn’t yet exist in the database, so you’ll insert the values.

Tip

This program would be more secure if it used parameterized queries. The query is left in-line to keep the example simple.

With that done, you’re ready to move on to the next ISBN.

Displaying the Output

This time, we’ll create a new ASP.NET web site, called AmazonSalesRanks, to display the information returned from Amazon. Drag three GridView objects onto the form, but don’t set up their data-binding; we’ll do so by hand. Example 16-3 shows the complete .aspx page, including the message printed above the grids, the titles for the grids, the last update label, and the text box used to decide how many rows to show in each grid.

Example 16-3. Displaying the output

<%@ Page Language="C#" CompileWith="Default.aspx.cs" 
ClassName="Default_aspx" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" 
                          "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Sales Ranks</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        The data found here is from the Amazon Web Service and 
        is stored in a local database. The data is updated every 15
        <br />
        minutes. This is a work in progress.<br />
        &nbsp;<br />
        <b>ASP Titles</b>
        <asp:GridView ID="gvASP" Runat="server" 
        OnRowDataBound="RowDataBound"
        AutoGenerateColumns="false"
        HeaderStyle-BackColor="PapayaWhip"
        BorderColor="#000099"
        AlternatingRowStyle-BackColor="LightGrey"
        HeaderStyle-Font-Bold=true
        Width="900">
            <Columns>
                <asp:TemplateField HeaderStyle-Width ="10">
                    <HeaderTemplate>
                        Position
                    </HeaderTemplate>
                    <ItemTemplate>
                        <asp:Label Runat="server" ID="Label1">
                          <%# rowNumber %></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField>
                    <HeaderTemplate>
                        Title
                    </HeaderTemplate>
                    <ItemTemplate>
                   <a href="http://www.amazon.com/exec/obidos/ASIN/<%# 
                           Eval("isbn")%>/" target="_blank"><%# 
                           Eval("title") %></a>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:BoundField HeaderText="Author"
                   ReadOnly="true" DataField="Author" />
                <asp:BoundField HeaderText="Publisher"
                   ReadOnly="true" DataField="Publisher" />
                <asp:BoundField HeaderText="Publish Date"
                   ReadOnly="true" DataField="pubDate" />
                <asp:BoundField HeaderText="Rank"
                    ReadOnly="true" DataField="Rank"
                    DataFormatString="{0:N0}"
                    ItemStyle-HorizontalAlign="right" />
            </Columns>
        </asp:GridView>
        <br />        
        <b>ASP Titles</b>
        <asp:GridView ID="gvCSharp" Runat="server"
        OnRowDataBound="RowDataBound"
        AutoGenerateColumns="false"
        HeaderStyle-BackColor="PapayaWhip"
        BorderColor="#000099"
        AlternatingRowStyle-BackColor="LightGrey"
        HeaderStyle-Font-Bold=true
        Width="900">
            <Columns>
                <asp:TemplateField HeaderStyle-Width ="10">
                    <HeaderTemplate>
                        Position
                    </HeaderTemplate>
                    <ItemTemplate>
                        <asp:Label Runat="server" ID="Label2">
                          <%# rowNumber %></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField>
                    <HeaderTemplate>
                        Title
                    </HeaderTemplate>
                    <ItemTemplate>
                      <a href="http://www.amazon.com/exec/obidos/ASIN/<%# 
                           Eval("isbn")%>/" target="_blank"><%# 
                           Eval("title") %></a>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:BoundField HeaderText="Author"
                  ReadOnly="true" DataField="Author" />
                <asp:BoundField HeaderText="Publisher"
                  ReadOnly="true" DataField="Publisher" />
                <asp:BoundField HeaderText="Publish Date"
                  ReadOnly="true" DataField="pubDate" />
                <asp:BoundField HeaderText="Rank"
                  ReadOnly="true" DataField="Rank"
                  DataFormatString="{0:N0}"
                  ItemStyle-HorizontalAlign="right" />
            </Columns>
        </asp:GridView>
        <br />
        <b>VB Titles</b>
        <asp:GridView ID="gvVBNet" Runat="server"
        OnRowDataBound="RowDataBound"
        AutoGenerateColumns="false"
        HeaderStyle-BackColor="PapayaWhip"
        BorderColor="#000099"
        AlternatingRowStyle-BackColor="LightGrey"
        HeaderStyle-Font-Bold=true
        Width="900">
            <Columns>
                <asp:TemplateField HeaderStyle-Width ="10">
                    <HeaderTemplate>
                        Position
                    </HeaderTemplate>
                    <ItemTemplate>
                        <asp:Label Runat="server" ID="Label3">
                          <%# rowNumber %></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField>
                    <HeaderTemplate>
                        Title
                    </HeaderTemplate>
                    <ItemTemplate>
                      <a href="http://www.amazon.com/exec/obidos/ASIN/<%# 
                           Eval("isbn")%>/" target="_blank"><%# 
                           Eval("title") %></a>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:BoundField HeaderText="Author"
                  ReadOnly="true" DataField="Author" />
                <asp:BoundField HeaderText="Publisher"
                  ReadOnly="true" DataField="Publisher" />
                <asp:BoundField HeaderText="Publish Date"
                  ReadOnly="true" DataField="pubDate" />
                <asp:BoundField HeaderText="Rank"
                  ReadOnly="true" DataField="Rank"
                  DataFormatString="{0:N0}"
                  ItemStyle-HorizontalAlign="right" />
            </Columns>
        </asp:GridView>
        <asp:Label ID="lblLastUpdate" Runat="server"
          Text="Last Update"></asp:Label>
        <br />
        Number to show in grid:
        <asp:TextBox ID="txtShowRecords" Runat="server"
          Width="48px" Height="22px"
          AutoPostBack="True"></asp:TextBox>
    </div>
    </form>
</body>
</html>

The key aspect of the HTML is the creation of three GridViews. They each work the same way, so we’ll focus on the first:

<asp:GridView ID="gvASP" Runat="server" 
 OnRowDataBound="RowDataBound"
 AutoGenerateColumns="false"
 HeaderStyle-BackColor="PapayaWhip"
 BorderColor="#000099"
 AlternatingRowStyle-BackColor="LightGrey"
 HeaderStyle-Font-Bold=true
 Width="900">

The GridView is named gvASP. A few properties are set, the most important of which is the event handler for the OnRowDataBound event and the Boolean property AutoGenerateColumns, which is set to False. This allows you to take direct control of the columns, which you do by creating a columns element:

<columns>
  ...
</columns>

The first column within the columns element is a template field column element. A template field column allows you to insert controls into the column. In the first instance, you’ll insert a Headertemplate (used to create a column header) with the text Position, and an asp:label control. That label will display, as its text, a row number. The mechanism for generating this row number is discussed in “Handling the RowDataBound Event,” later in this chapter.

<asp:TemplateField HeaderStyle-Width ="10">
    <HeaderTemplate>
        Position
    </HeaderTemplate>
    <ItemTemplate>
        <asp:Label Runat="server" ID="Label1"><%# rowNumber %></asp:Label>
    </ItemTemplate>

Tip

For a full explanation of template fields and the other elements used in this page, please see Programming ASP.NET (O’Reilly).

The second column is also a template field, this time with the column heading Title. The title itself is displayed by evaluating the title column in the current row in the data set to which this GridView is bound, and surrounding that title with a link to the appropriate page on Amazon.com. This makes the title a hyperlink the user can click.

<asp:TemplateField>
    <HeaderTemplate>
        Title
    </HeaderTemplate>
    <ItemTemplate>
        <a href="http://www.amazon.com/exec/obidos/ASIN/
        <%# Eval("isbn")%>/" target="_blank"><%# Eval("title") %></a>
    </ItemTemplate>
</asp:TemplateField>

Let’s take this apart. The first element is the template field:

<asp:TemplateField> 
</asp:TemplateField>

Within the template field are two template elements: the header and the item. The header is pretty straightforward. It has simple text (though it could have any kind of HTML):

<headertemplate>
     Title
</headertemplate>

The item template is a bit trickier:

<itemtemplate>
    <a href="http://www.amazon.com/exec/obidos/ASIN/<%# Eval("isbn")%>/" 
             target="_blank"><%# Eval("title") %></a>
</itemtemplate>

Tip

Note that you’ll want to test the title (and all other strings) to ensure that it is valid HTML.

We’ll evaluate this one from the outside in. The first thing to notice is the start of a normal hyperlink:

<a href="http://www.amazon.com/exec/obidos/ASIN/

However, the hyperlink is then appended with the result of evaluating the ISBN from the bound data:

<%# Eval("isbn") %>/"

This hyperlink tag has an attribute:

target="_blank"

That attribute causes the link to open a new instance of the browser. The body of the link (the displayed text of the link) is also an evaluated value:

Eval("title")

If the bound data has the title Programming Visual Basic .NET, Second Edition, and the ISBN 0596004389, this item emits the following HTML:

<a href=http://www.amazon.com/exec/obidos/ASIN/0596004389/target="_ blank">Programming Visual Basic .NET 2nd Edition </a>

The first two columns are tricky. The first is tricky because we need to do some work to create the rowNumber (see the code that follows), and the second because we need to wrap the bound value (the ISBN and the title) inside a hyperlink. The next four columns are easier; they are just bound to the data.

The first bound column has the header text Author, is marked as readOnly, and is bound to the author column in the row of the DataSet table to which this GridView is bound:

<asp:BoundColumn HeaderText="Author"
   ReadOnly="true" 
   DataField="author"/>

Notice that this is a self-closing element, as are the next three bound columns:

<asp:BoundColumn HeaderText="Publisher" 
   ReadOnly="true" 
   DataField="publisher"/>
<asp:BoundColumn HeaderText="Publish Date"
   ReadOnly="true" 
   DataField="pubdate"/>
<asp:BoundColumn HeaderText="Rank" 
   ReadOnly="true" 
   DataField="Rank" DataFormatString="{0:N0}"
   ItemStyle-HorizontalAlign="Right"/>

Implementing the Grid

The complete source code for the code-behind file is shown in Example 16-4, followed by the analysis.

Example 16-4. Code-behind file for SalesDisplay

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial classDefault_aspx
{
   protected int showRecords;
   protected int totalASP;
   protected int totalCSharp;
   protected int rowNumber = 0;

   protected override void OnLoad( EventArgs e )
   {
      // initialize number of records to show to 7
      if ( !IsPostBack )
      {
         showRecords = 7;
      }
      // if it is a post back, get the number
      // from the text box
      else
      {
         showRecords = Convert.ToInt32( txtShowRecords.Text );
      }

      // connect to the db
      string connectionString =
      "server=localhost;Trusted_Connection=true;database=AmazonSalesRanks";

      // pick records to display
      string commandString =
         @"Select top " + showRecords +
         " * from BookInfo where technology = 'ASPNET' order by rank";
      SqlDataAdapter dataAdapter =
         new SqlDataAdapter( commandString, connectionString );
      DataSet dataSet = new DataSet( );
      dataAdapter.Fill( dataSet, "aspBookInfo" ); // first table

      commandString = @"Select top " + showRecords + " * 
                   from BookInfo where technology = 'CSHARP' order by rank";
      dataAdapter = new SqlDataAdapter( commandString, connectionString );
      dataAdapter.Fill( dataSet, "csBookInfo" ); // second table

      commandString = @"Select top " + showRecords + 
        " * from BookInfo where technology = 'VBNET' order by rank";
      dataAdapter = new SqlDataAdapter( commandString, 
        connectionString );
      dataAdapter.Fill( dataSet, "vbBookInfo" ); // third table

      // create the data view and bind to the grid
      DataView aspDataView =
         dataSet.Tables[0].DefaultView;
      gvASP.DataSource = aspDataView;
      gvASP.DataBind( );

      rowNumber = 0;

      DataView csDataView = dataSet.Tables[1].DefaultView;
      gvCSharp.DataSource = csDataView;
      gvCSharp.DataBind( );

      rowNumber = 0;

      DataView vbDataView = dataSet.Tables[2].DefaultView;
      this.gvVBNet.DataSource = vbDataView;
      gvVBNet.DataBind( );

      // txtShowRecords.DataBind( );
      lblLastUpdate.Text = "Last updated: " + 
        dataSet.Tables[2].Rows[0]["lastUpdate"].ToString( );

   }
   void RowDataBound( object sender, GridViewRowEventArgs e )
   {
      this.rowNumber++;
   }
}

The program begins by declaring a number of local variables, the most important of which is the rowNumber, which is initialized to 0:

protected int rowNumber = 0;

The showRecords member variable is used to keep track of how many records to display, and when the page is first displayed, showRecords is set to 7 (a safe and reasonable default). On subsequent postbacks of the page, that value is set to whatever is in the text box:

private void Page_Load(object sender, System.EventArgs e)
{
   if (! IsPostBack )
   {
      showRecords = 7;
   }
   else
   {
      showRecords = Convert.ToInt32(txtShowRecords.Text);
   }

Continuing in the page-load event handler, the database connection is made and the database is searched, based on the “technology” (i.e., ASP.NET versus C# versus VB.NET):

string connectionString = 
   "server=localhost;Trusted_Connection=true;database=AmazonSalesRanks";

string commandString = 
   @"Select top " + showRecords + 
   " * from BookInfo where technology = 'ASPNET' order by rank";
SqlDataAdapter dataAdapter = 
   new SqlDataAdapter(commandString, connectionString);
DataSet dataSet = new DataSet( );
dataAdapter.Fill(dataSet,"aspBookInfo"); // first table

The same is done for each of the other queries. Once the tables in the data set are created, a data view is created for the first table. This represents a view of the ASP.NET results:

DataView aspDataView = 
   dataSet.Tables[0].DefaultView;

It is to this data view that the GridView is bound:

gvASP.DataSource = aspDataView;
 gvASP.DataBind();

Once this is done, the rowNumber member variable is set back to zero (we’ll discuss how it moves from zero in a bit), and the next data view is created and bound to its respective GridView:

rowNumber = 0;DataView csDataView = dataSet.Tables[1].DefaultView;
gvCSharp.DataSource = csDataView;
gvCSharp.DataBind( );

This is done one final time for the third table:

rowNumber = 0;DataView vbDataView = dataSet.Tables[2].DefaultView;
this.gvVBNet.DataSource = vbDataView;
gvVBNet.DataBind( );

Finally, the label lblLastUpdate is set from the lastUpdate field in the table:

lblLastUpdate.Text = "Last updated: " + dataSet.Tables[2].Rows[0]
                     ["lastUpdate"].ToString();

Handling the RowDataBound Event

You will remember that when you created the GridView, you bound the RowDataBound event to the RowDataBound method. As a matter of fact, you did this for all three GridViews. Whenever an item is bound on any grid, it is handled in this method. All the method does is to increment the row counter, rowNumber:

public void Item_Bound(Object sender, GridViewItemEventArgs e)
{
   rowNumber++;
}

The net effect is that each time an item is bound to the GridView, the rowNumber is incremented and then displayed in the first templated column, giving you a relative ranking within the GridView.

Searching by Category

Making a web service method call for each ISBN you want to check is by far not the most efficient approach possible. Not only does it involve multiple round trips to the Amazon service, but it is likely that the ISBNs you list will become out of date almost immediately as new competitors come on the market, and others go out of print.

Fortunately, Amazon can search by category. In the next (and final) iteration of this program, you’ll dispense with your XML files with ISBNs and instead simply ask Amazon for all the books in the ASP.NET, C#, and VB.NET categories.

To keep the example simple, you’ll use the default of retrieving just 10 books in each category. Amazon Web Services allow a great deal of tailoring of your request, but for this example, we’ll go with the minimal properties required.

Create a copy of the SalesRankDBWebServices project and name it AmazonWebServiceClientSearching. You’ll modify it as shown in Example 16-5.

Example 16-5. SalesRankDBWebServices02

#region Using directives

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Windows.Forms;

#endregion

namespace AmazonWebServiceClient
{
   partial class AmazonWebServiceClient : Form
   {
      private int timeRemaining;
      const int WaitTime = 900; // 15 minutes
      private string connectionString;
      private System.Data.SqlClient.SqlConnection connection;
      private System.Data.SqlClient.SqlCommand command;

      public AmazonWebServiceClient( )
      {
         InitializeComponent( );
      }

      private void AmazonWebServiceClient_Load( object sender, 
        EventArgs e )
      {
         // connection string to connect to the Sales Rank Database
         connectionString =
      "server=localhost;Trusted_Connection=true;database=AmazonSalesRanks";

         // Create connection object, initialize with 
         // connection string. 
         connection =
            new System.Data.SqlClient.SqlConnection( connectionString );

         // Create a SqlCommand object and assign the connection
         command =
            new System.Data.SqlClient.SqlCommand( );

         command.Connection = connection;
         timeRemaining = 1;  // when you first start up, get the info.
         UpdateButton( );

      }

      private void btnStart_Click( object sender, EventArgs e )
      {
         // toggle the timer
         updateTimer.Enabled = updateTimer.Enabled ? false : true;
         UpdateButton( );

      }

      private void btnNow_Click( object sender, EventArgs e )
      {
         timeRemaining = 2;
      }

      private void UpdateButton( )
      {
         btnStart.Text = updateTimer.Enabled ? "Stop" : "Start";
      }

      private void updateTimer_Tick( object sender, EventArgs e )
      {

         if ( updateTimer.Enabled )
            txtClock.Text = ( --timeRemaining ).ToString( ) + " seconds";
         else
            txtClock.Text = "Stopped";

         // hi ho, hi ho, it's off to work we go...
         if ( timeRemaining < 1 )
         {
            timeRemaining = WaitTime;  // reset the clock
            timeRemaining = WaitTime;
            GetInfoFromAmazon( "ASP.NET", "ASPNET" );
            GetInfoFromAmazon( "C#", "CSHARP" );
            GetInfoFromAmazon( "VB.NET", "VBNET" );
         }
      }

      private void GetInfoFromAmazon( string keyword, string technology )
      {
         
         AWSProductData productData = new AWSProductData( );

         ItemSearch srch = null;
         try
         {
            ItemSearchRequest req = new ItemSearchRequest( );
            req.Keywords = keyword;
            req.SearchIndex = "Books";

            srch = new ItemSearch( );
            srch.AssociateTag = "libertyassocia00A"; 
            srch.SubscriptionId = " Your Subscription ID ";
            srch.Request = new ItemSearchRequest[1];
            srch.Request[0] = req;

         }
         catch ( System.Exception e )
         {
            lblStatus.Text = e.Message;
         }

         ItemSearchResponse response;

         int salesRank = -1;
         string isbn = string.Empty;
         string author = string.Empty;
         string pubDate = string.Empty; 
         string publisher = string.Empty; 
         string title = string.Empty; 
         string strURL = string.Empty;

         Items[] responseItems = null;
         try
         {
            // get back ItemSearchResponse
            response = productData.ItemSearch( srch );

            // Items returns array of Items
            responseItems = response.Items;

            foreach ( Items items in responseItems )
            {
               // Item property of Items is an array of Item objects
               Item[] arrayOfItem = items.Item;     
     
               foreach ( Item item in arrayOfItem )
               {
                  isbn = FixQuotes( item.ItemAttributes.ISBN );
                  salesRank = item.SalesRank == 
                     null ? -1 : Convert.ToInt32( item.SalesRank );
                  author = FixQuotes( item.ItemAttributes.Author[0] );
                  pubDate = FixQuotes( 
                      item.ItemAttributes.PublicationDate);
                  publisher = FixQuotes( item.ItemAttributes.Publisher );
                  title = FixQuotes( item.ItemAttributes.Title );
                  strURL = item.DetailPageURL;
                  // update the list box
                  string results = title + " by " + author + ": " +
                     publisher + ", " + pubDate + ". Rank: " + salesRank;
                  lbOutput.Items.Add( results );
                  lbOutput.SelectedIndex = lbOutput.Items.Count - 1;

                  // update the database
                  string commandString = @"Update BookInfo set isbn = '" +
                     isbn + "', title = '" + title + "', publisher = '" +
                     publisher + "', pubDate = '" + 
                     pubDate + "', rank = " +
                     salesRank + ", link = '" + 
                     strURL + "', lastUpdate = '" +
                     System.DateTime.Now + "', technology = '" +
                     technology + "', author = '" +
                     author + "' where isbn = '" +
                     isbn + "'";

                  command.CommandText = commandString;
                  try
                  {
                     // if no rows were affected, this is a new record
                     connection.Open( );
                     int numRowsAffected = command.ExecuteNonQuery( );
                     if ( numRowsAffected == 0 )
                     {
                        commandString = @"Insert into BookInfo values ('" +
                           isbn + "', '" + title + "', '" + publisher + 
                           "', '" +
                           pubDate + "', '" + FixQuotes( strURL ) + "', " 
                           + salesRank + ", '" +
                           System.DateTime.Now +
                           "', '" + technology + "', '" + author + "')";

                        command.CommandText = commandString;
                        command.ExecuteNonQuery( );
                     }
                  }
                  catch ( Exception ex )
                  {
                     lblStatus.Text = ex.Message;
                     lbOutput.Items.Add( "Unable to update database!" );
                     lbOutput.SelectedIndex = lbOutput.Items.Count - 1;
                  }
                  finally
                  {
                     connection.Close( );      // clean up
                  }
                  Application.DoEvents( );      // update the UI
               }
            }
         }
         catch ( System.Exception ex)
         {
            lblStatus.Text = ex.Message;
         }

      }     // close for GetInfoFromAmazon

      private string FixQuotes( string s )
      {
         if ( s == null )
            return string.Empty;
         return s.Replace( "'", "''" );

      }
   } // end class
} // end name space

In this version, all the code to manipulate the XML files is removed. The method GetInfoFromISBN is replaced by GetInfoFromAmazon. Instead of creating an ItemLookupRequest object, you create a KeywordRequest object:

private void GetInfoFromAmazon( string keyword, string technology )
{
   
   AWSProductData productData = new AWSProductData( );ItemSearch srch = null;
   try
   {
      ItemSearchRequest req = new ItemSearchRequest( );
      req.Keywords = keyword;
      req.SearchIndex = "Books";

      srch = new ItemSearch( );
      srch.AssociateTag = "libertyassocia00A"; 
      srch.SubscriptionId = "Your Subscription ID";
      srch.Request = new ItemSearchRequest[1];
      srch.Request[0] = req;

   }

Notice that the keyword property has been assigned the keyword parameter passed in (i.e., C#, ASP.NET, or VB.NET). The SearchIndex property limits the search to books (rather than, for example, records).

Once the ItemSearchRequest object is created, you embed it in an ItemSearch object that holds the AssociateTag and the SubscriptionID.

What you get back is an ItemSearchResponse object. The Items property of this object is an array of Item objects. Each Item object has an Item property which, not surprisingly, is an array of Item objects. It is in these Item objects that you’ll find the information about the matching books.


[1] Content on Amazon is copyrighted and can’t be used without written permission. Such permission was graciously provided for this book and for use on my web site, http://www.LibertyAssociates.com.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.226.170.187