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.
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.
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.
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).
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
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>
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:
Declare a new instance of the AWSProductData
,
which acts as the proxy to the Amazon web service.
Call the ItemLookup
method on the
AWSProductData
instance, passing in a properly
initialized instance of ItemLookup
.
Get back an ItemLookupResponse
object.
Extract the Items
array and from that get the
first object (offset 0), an object of type Items
.
Ask that Items
object for its
Item
property, which is an array of
Item
s objects.
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;
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.
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.
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 /> <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
GridView
s. 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>
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>
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"/>
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();
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
GridView
s. 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
.
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.
3.139.70.21