I recently found an interesting social networking web site where you can list all the books you’ve read so you can connect with other people based on your reading habits. This is very appealing to me because I have read over 2000 books. I’ve also kept a list of all the books I’ve read. I maintain the list in a simple Access database with just three columns; book number (sequential number), book title, and book author(s). Unfortunately I do not record the ISBN number and the web site www.librarything.com only offers bulk import of ISBN numbers.
I found a web service isbndb.com/data-intro.html that allows you to look up the ISBN of a book based on its title. The web service provides an opportunity to automate the process of populating a new database column for the ISBN number.
I created a project in Visual Studio 2005 with C# as the programming language. The first thing I had to do was import the NET Framework classes that the project would need. These include the classes for XML, Diagnostics, IO, Threading, Network, and SQL Server client because I imported my Access database into SQL Server for this project. It is easier to deal with a SQL Server database when working with the NET Framework, at least most of my sample code is written for that.
Next I added a trace listener to the project’s application configuration file. Trace listeners were added in NET Framework 2.0 to make it easier to log debugging information. Using a trace listener, my application can log the response from the web service and the SQL statements used to update a record. This provides a written record of the program execution so I can see where a failure occurred and what data caused the problem. The web service places a limit on how many ISBN look ups you can perform in a day and my log file shows me where I was in the database updating when I reached my limit for the day.
The first thing my application does is establish a database connection to the SQL Server and run a query to get all the records for a data reader. Then it loops through the records and calls the web service with the book title in the web address. The web service is called using a web request object and gets an asynchronous callback. The response callback method reads the web service response string and creates an XML document because the response is string data in the form of XML. An XML node reader is then required to find the BookList node to see if the total_results is greater than 0. In other words, I must check to see if any books were found and do nothing if we come up empty. The ISBN number is actually an attribute of the BookData element so this project required some sophisticated knowledge of how to find data in a complex XML document. You should know the difference between elements (aka nodes) and attributes in XML and how to extract data from each.
Finally, my application updates the database record by adding the ISBN value to the new ISBN column. The SQL statement to do the update is logged in case some unexpected value causes an error. Logging your SQL statements is always a good idea and often the key to troubleshooting a problem. I also look at the entire XML response in case something in that causes the application to error. If there is an error and my application needs to pick up where it left off, I just change the query for all the records to get just the records where the book number is greater than the last successful update.
Unfortunately, the web service only allows approximately 500 queries a day and I’ve read almost 2500 books so this application must run for 5 days to complete the job. Also, looking up ISBN numbers by book title is not very accurate so I won’t be getting the exact copy of the book I read or own. I’m just using the first book result so it may not even be the right title in some cases.
Later on I may turn this into a web part so I can perform ISBN look ups as part of a web portal.
