Basics 3

Basics exercises revised, Part 3

part of Internet Application Workbook by Eve Andersson, Philip Greenspun, and Andrew Grumet
revisions by Michael Olan for CSIS 4135 at Stockton College

Exercise 1: Publishing data in XML

As you learned from querying bookstores, data on the Web have not traditionally been formatted for convenient use by computer programs. In theory, people who wish to exchange data over the Web can cooperate using XML, a 1997 standard from the Web Consortium (http://www.w3.org/XML/). In practice, you'll be hard-pressed to get any XML-based cooperation from the average web site. Fortunately for your sake in completing this problem set, you can cooperate with your fellow students: the overall goal is to make quotations in your database exportable in a structured format so that other students' applications can read them.

Here's what we need in order to cooperate:

an agreed-upon URL at everyone's server where the Quotes database may be obtained: "/Basics3/quotations-xml.aspx"
an agreed-upon format for the quotations (this is important!).

[In point of fact, we could avoid the need for prior agreement by setting up infrastructures for service discovery and by employing techniques for self-describing data -- both of which we'll deal with later in the semester -- but we'll keep things "simple" for now.]

We'll format the quotations using XML, a conventional notation for describing structured data. XML structures consist of data strings enclosed in HTML-like tags of the form <foo> and </foo>, describing what kind of thing the data is supposed to be.

Here's an informal example, showing the structure we'll use for our quotations:

<Quotes> 
   <Quote> 
      <Quotation>I shop, go to movies, soak up the sun when possible and go out to eat.</Quotation> 
      <Author>Britney Spears</Author> 
      <Name>Pop Musician Leisure Activities</Name> 
      <Creation_date>2001-04-02</Creation_date> </Quote> 
   <Quote> 
      .. another row from the Quotes table ... 
   </Quote>
 ... some more rows 
</Quotes>

Notice that there's a separate tag for each column in our SQL data model:

<Quote_ID> <Quotation> 
<Author> <Category> <Creation_date>

There's also a "wrapper" tag that identifies each row as a <Quote> structure, and an outer wrapper that identifies a sequence of <Quote> stuctures as a <Quotes> document.

Building a DTD

We could give a formal description of our XML structure, rather than an informal example, by means of an XML Document Type Definition (DTD).

Our DTD would start with a definition of the Quotes tag:

<!ELEMENT 
Quotes (Quote+)>

This says that the Quotes element must contain at least one occurrence of Quote but may contain more than one. Now we have to say what constitutes a legal Quote element:

<!ELEMENT Quote (Quote_ID,Quotation,Author,Category,Creation_date)>

This says that the sub-elements, such as Quote_ID must each appear exactly once and in the specified order. Now we have to define an XML element that actually contains something other than other XML elements:

<!ELEMENT Quote_ID (#PCDATA)>

This says that whatever falls between <Quote_ID> and </Quote_ID> is to be interpreted as raw characters rather than as containing further tags (PCDATA stands for "parsed character data").

Hey, actually you won't find this DTD stuff useful at all for completing this part of the problem set. The only reasons that DTDs are ever useful is for feeding to XML parsers because then they can then automatically tokenize an XML document. For implementing your quotations-xml page, you can just look at the informal example.

The meat of this exercise: Write a script that queries the Quotes table, produces XML documents in the preceding form, and returns them to the client with a MIME type of "application/xml". Place this in your site at quotations-xml.aspx, so that other users can retrieve the data by visiting that agreed-upon URL. This part is pretty easy. There is no GUI. When the page loads, select all the quotations from the Quotes table, and put them in a DataSet. This requires making an SqlCommand for the SELECT statement, and an SqlDataAdapter associated with the SqlCommand. The SqlDataAdapter is used to fill the DataSet. There's an example similar to this on p. 434 but using a SqlDataAdapter is slightly simpler.

 // First make a connection and SqlDataAdapter with the appropriate sql command
//  and then ...
 DataSet ds = new DataSet("Quotes"); 
 adapter.Fill(ds, "Quote");

NOTE: Never hard-code the connection string like they did in Listing 10-14! This belongs in web.config and can be retrieved with something like: ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString

The name given in the DataSet constructor will be used as the XML root tag. The name used in the Fill command is the tag attached to each record in the XML document (like in the example above).

Then write the data set as XML to the client.

Response.ContentType = "application/xml";
ds.WriteXml(Response.Output);
Response.End();

NOTE: Be sure to include the last statement above. Listing 10-14 does not include this which will not produce the desired results.

At this point, anyone requesting the URL for quotations-xml.aspx should get an XML document returned to them. Depending on the browser, this will probably show up as tags surrounding content.

You can see samples of your instructors tables XML here: Quotes and Categories

Use this URL to import the complete XML to your database: http://indra.stockton.edu/basics/basics2/quotations-xml.aspx

As always, there is a little catch. We have two tables and the client will need both, so this needs to be repeated for both the Categories and Quotes tables. The client will need these, one at a time. A simple way to do this is to require a URL parameter to be used. The convention to be used for this exercise is:

table=Categories or
table=Quotes

So when the client requests the Categories, send the Categories data (just the Name column is sufficient). When they request the Quotes, send the same data that gets displayed on the main quotations page - this will include the category name, not the category ID which might not coincide with the client's category IDs.

Exercise 2: Importing XML

Write a program to import the quotations from another student's XML output document. Your program must

Grab quotations-xml.aspx from another student's server
Parse the resulting XML structure into records and then parse the records into fields.
If a quote from the foreign server has identical author and content as a quote in your own database, ignore it; otherwise, insert it into your database with a new Quote_ID. (You don't want keys from the foreign server conflicting with what is already in your database.)

Hint: You can set up a temporary table using select * into Quotes_Temp from Quotes and then drop it after you're done debugging, so that you don't mess up your production quotations database.

In an effort to quickly move past this exercise, I'll provide much of what is needed to complete it. This page should have a very simple GUI. Just a textbox to enter the URL of the site you want to contact to get their quotes, and a submit button -- and perhaps a label indicating if the operation was successful when its done.

Exercise 2a: Importing the Catetories

Read it into an XDocument which is then used to fill a DataSet with catagory data similar to this:

       XDocument xDoc = XDocument.Load( url );
      xDoc.CreateReader();
      DataSet ds = new DataSet();
      ds.ReadXml(xDoc.CreateReader());

Create a temporary table in SQL Server to hold the Category names with something like this:

      string sql = "CREATE TABLE #Dummy (Name varchar (25) NOT NULL)"; 
     SqlCommand command = new SqlCommand(sql , conn ); 
     command.ExecuteNonQuery();

Note the # in front of the table name (#Dummy). This identifies a temporary table, which SQL Server will automatically delete as soon as the session ends, so you won't have to worry about old or corrupt data cluttering up the database.

Fill the tempory table from the DataSet with something like this:

      SqlDataAdapter myAdapter = new SqlDataAdapter();
     myAdapter.InsertCommand = new SqlCommand("INSERT INTO #Dummy1 VALUES (@Name)", conn);
     myAdapter.InsertCommand.Parameters.Add("@Name", SqlDbType.VarChar, 25, "Name");  
     myAdapter.Update(doc.DataSet.Tables[0]);

Merge the new categories in the temporary table with the existing ones, but avoiding duplicates:

This is the point where you should use a copy of the Categories table during debugging!

 string sqlMerge = "INSERT INTO Cat_bak (Name) SELECT Name FROM #Dummy WHERE NOT EXISTS (SELECT Name FROM Cat_bak WHERE Cat_bak.Name = #Dummy.Name) "; 
command = new SqlCommand(sqlMerge, Conn); 
command.ExecuteNonQuery();

Exercise 2b: Importing the Quotes

Fill a DataSet with the imported XML Quotes data similar to the previous steps.
Again make a #Dummy table but this time it should match the structure of the Quotes table (without a Quote_ID).

Fill this table similar to above. Note that the parameter for the creation date will be similar to:

myAdapter.InsertCommand.Parameters.Add("@Creation_date", SqlDbType.SmallDateTime, 4, "Creation_date");

Now finally merge the new quotes into the existing Quotes table, avoiding duplicates with this simple little SQL statement:

String sqlMerge = "INSERT INTO quotes_temp (Quotation, Author, Category_ID, Creation_Date) " + 
                  "SELECT Quotation, Author, Cat_bak.id, creation_date from #Dummy " + 
                  "INNER JOIN Cat_bak on #Dummy.Name = Cat_bak.Name WHERE NOT EXISTS " +
                  "(SELECT * FROM quotes_temp WHERE #Dummy.Quotation = quotes_temp.Quotation AND #Dummy.Author = quotes_temp.Author)";

Well, that should do it!

There may be/probably are better ways to do this, and if you find them, please share your expertise with the rest of us! All of you are poring over the help files in VS .NET to learn all you can, and searching Google, visiting .NET web sites, etc. to gather up all the latest info, right? Of course you are, since anyone in a 4000 level CS course would be experienced and motivated enough to do their own research and not sit back expecting their professor to hold their hands and do it for them. Good, I knew it all along!

Go back to the quotations pages and observe the fruits of your labor (assuming it all works that is).

Exercise 3: Taking Credit

Please go through your source code files. Make sure that there is a header at the top explaining (1) who wrote the code, (2) on what date it was written, and (3) what problem it is trying to solve.