SAI430—iLab Week 4: Using XML
TCOs Satisfied
2 Given a business case study, produce a set of technical documents using one of the guidelines identified in TCO #1 to address the planning, programming, and testing specifications for a new transaction processing system.
4 Given a business system application that needs to retrieve from and write to a relational database using an external XML data file format, the student will design, code and document the business application using a high-level language.
7 Given an application that needs to be written in a language (or environment) with which you are not familiar, compare the new language to a language with which you are familiar in order to implement the given methods in the new language.
Introduction and Rationale
A very popular method of storing data in computer files is XML (extensible markup language). One of the benefits of XML is that the file can be formatted to the needs of the company. In the same manner that many programs and devices created CSV files, they might also create XML files. Whereas a CSV file might have data stored as:
11775, 894, S, Khaki, 24.95, 99
an XML file might instead use:
While the second line is clearly longer, it is far more descriptive and can be easily searched for values of specific attributes. In Week 4 you will learn to read in a simple XML file, extract the data from it, and then use this data to add, update, or delete entries in the “item” table.
Lab Setup
Resetting the database before and during testing
Before you begin this lab, you should reset the database to its original condition in Week 1. You might also find that you want to reset it many times during the testing of your program. To reset your database back to its starting state—rerun the SQL script provided in Week 1: Omni_InventoryDB.sql. You can do this as often as you need. The script has also been provided in the zip: SAI430_W4_iLab_Extras.zip located in Document Sharing. This script is run from within the Omnymbus MySQL server. See the database setup file from Week 1: SAI430_Online_Database_setup.docx. Refer to Figure 1.
Figure 1—Resetting original database state by rerunning setup script
Lab Description
For Week 4 you will create a Visual Studio C#.NET program that reads an XML file which will contain items labeled with one of three commands: ADD, UPDATE, and DELETE. You will perform an associated add, update, or delete in the Inventory database that we originally created in Week 1 (see Figures 2 and 3).
Figure 2—Transaction Processing Workflow
Figure 3—Inventory Database ERD
The XML file will be entitled “Update.xml”. You can find this file in Document Sharing within the “SAI430_W4_iLab_Extras.zip” ZIP archive. Unzip this file to a location easy to find. It is suggested that you add it to the same location in which you stored the output and input CSV files from the Week 2 and Week 3 iLab. The location “F:\SAI430\” was suggested where “F” is your personal data drive on the CITRIX server. You may need to review the CITRIX tutorial on moving files between your local PC and the CITRIX environment. See the Student Resources tab for CITRIX information.
The Update.xml file will look like this.
This is a very simple use of XML where the NODES will be one of three types (ADD, UPDATE, or DELETE). These nodes will have attributes associated with the items that they process. Read these lines one at a time and use them to ADD, UPDATE or DELETE items from the Item table. Print a message to the console explaining if these tasks were successful or not. See Figure 4 for an example of the first run. A second run with the same XML file produces errors (see Figure 5).
Figure 4— Sample console output
Figure 5—Result when trying to run the XML file a second time without resetting the database
Assumption 1: Items in this XML file are already in the Inventory table (see Table 1), they just need to be added to the item table. These items include:
Table 1—Inventory table from the Inventory database
invent_id invent_desc
559 Mens Expedition Parka
786 3-Season Tent
894 Womens Hiking Shorts
897 Womens Fleece Pullover
995 Childrens Beachcomber Sandals
Assumption 2: Only the size, color, price, and quantity can be updated in an UPDATE statement.
Assumption 3: Only the item_id is required to do a DELETE.
Techniques
We will add a few new methods to the Item class from Week 3. The first things that are needed are the Update and Delete methods. The AddRow method was created last week.
//Update a row to the database passed in as db
public bool UpdateRow(OdbcConnection db)
{
String sql = “UPDATE item ”
+ “SET itemsize=?, ”
+ “color=?, ”
+ “curr_price=?, ”
+ “qoh=? ”
+ “WHERE item_id=?”;
OdbcCommand Command = new OdbcCommand(sql, db);
Command.Parameters.Add(“@SZ”, OdbcType.VarChar).Value = this.Itemsize.Trim();
Command.Parameters.Add(“@COL”, OdbcType.VarChar).Value = this.Color.Trim();
Command.Parameters.Add(“@PR”, OdbcType.Double).Value = (double)this.Curr_price;
Command.Parameters.Add(“@QOH”, OdbcType.Int).Value = this.Qoh;
Command.Parameters.Add(“@ID”, OdbcType.Int).Value = this.Item_ID;
int result = Command.ExecuteNonQuery(); //Returns 1 if successful
if (result > 0)
return true; //Was successful in updating
else
return false; //failed to update
}
//Delete a row from the database passed in as db
public bool DeleteRow(OdbcConnection db)
{
String sql = “DELETE FROM item WHERE item_id=?”;
OdbcCommand Command = new OdbcCommand(sql, db);
Command.Parameters.Add(“@ID”, OdbcType.Int).Value = this.Item_ID;
int result = Command.ExecuteNonQuery(); //Returns 1 if successful
if (result > 0)
return true; //Was successful in deleting
else
return false; //failed to delete
}
You will notice that these two methods are not unlike the AddRow method. The primary difference is that the SQL statements are different. While this is not a database course, you should be familiar with these simple SQL commands and their use.
The next method is similar to the ParseCSVline method that parsed lines from a CSV file into individual items. In this case, the parseXML method will extract the attributes from an XML file pointed to by the XmlReader (named simply “f” in this method). The XmlReader is opened in the Main method and used with a while loop to move through the XML file (see the Main method skeleton).
Remember that each line of a command looks like this:
The XmlReader can extract these attributes directly with the GetAttribute method. After parseXML is executed, the object will be full of data from the XML file.
//Used to parse an XML file pointed to by XmlReader
public bool parseXML(XmlReader f)
{
try
{
this.Item_ID = int.Parse(f.GetAttribute(“item_id”));
this.Invent_id = int.Parse(f.GetAttribute(“invent_id”));
this.Itemsize = f.GetAttribute(“itemsize”);
this.Color = f.GetAttribute(“color”);
this.Curr_price = decimal.Parse(f.GetAttribute(“curr_price”));
this.Qoh = int.Parse(f.GetAttribute(“qoh”));
}
catch (Exception ex)
{
return false;
}
return true;
}
You then need THREE more methods in the Item class. One to handle an ADD, another for the UPDATE, and finally one for the DELETE. The ADD method looks like this:
//Get this item from the XML file and
//add this item to the database passed in as db
public bool XMLAdd(XmlReader f, OdbcConnection db)
{
if (!this.parseXML(f)) //parse the item from “f”
return false; //Leave if the parse failed
//Is it in database? Check that it is NOT.
if (!this.IsInDatabase(db))
{
//if not, add it
if (this.AddRow(db))
return true;
else
return false; //if something went wrong
}
else
return false; //already in DB
}
You should write both the XMLUpdate and XMLDelete methods and add them to the Item class. They are both based on the XMLAdd method above, but rather than look for the absence of an item in the database, they count on one being there.
Because the DELETE command in XML only has an item_id, the parseXML method will fail. Instead, use this line to parse just the item_id.
//Only need ID to delete
this.Item_ID = int.Parse(f.GetAttribute(“item_id”));
The Main Method
Here is the skeleton of the Main method:
//Add namespace to already existing ones from previous Weeks.
//This is for XML.
using System.Xml;
namespace ProjectWeek4
{
class Program
{
static void Main(string[] args)
{
//Step 1 – Connect to database
//Connection string needed to talk to Omnymbus
[Connect to database here]
//Step 2 – Open input file
//Set where the file comes from
string filepath = @”f:\sai430\”;
string filename = @”Update.xml”;
//Open XML reader – name it “theFile”
XmlReader theFile = XmlReader.Create(filepath + filename);
//Step 3 – Loop through file and add to database. Read will
//return FALSE when there are no more lines to read.
while (theFile.Read())
{
//Step 4 – Create an object to use each time through the loop
Item theItem = new Item();
//Step 5 – Check each node in the XML file
//to see what it is: ADD, UPDATE, or DELETE
if (theFile.Name.Equals(“ADD”))
{
if ([CALL the instantiated item’s XMLAdd method here])
[Print success message to console]
else
[Print failure message to console]
}
else if… [Do the same for Update and Delete]
} //end of while loop
connection.Close();
theFile.Close();
Console.WriteLine(“\n\n\nPress ENTER to continue”);
Console.ReadLine();
} //end of main
} //end of class
} //close namespace
Notice again that the class does more of the work—the Main only controls the order in which things happen. This is the goal of OOP—the objects take care of themselves.
Deliverables
For iLab 4, you will submit the following deliverables to the course drop box in a single ZIP file:
1. Name your ZIP archive LastName_FirstName_W4_iLab.zip
2. Copy and paste your entire C#.NET program into a Word document. Add this document to the ZIP file. Your teacher may request that you include your entire Week 4 project in the ZIP file. If so, you will need to revisit the Student Resources tab to learn about transferring files from CITRIX to your local PC.
3. Run your program from WEEK 2. As you will recall, the Week 2 iLab will generate a CSV file with all of your items in it—including the new items added, the items that were updated, and of course the lack of items which were deleted. Insert this newly generated CSV file into your archive. It should be named Items.csv. See the CSV file sample displayed in Excel below in Figure 6.
Make sure you close and save your documents before adding them to the ZIP file. This will ensure that the latest version of the files are uploaded.
Figure 6—CSV File displayed in Excel
Grading Rubric
iLab Deliverables Description Points
Item 1 Document header and comments in the code. 10
Item 2 Well written and functional C#.NET program Main. 15
Item 3 Well written and functional Item class. 15
Item 4 CSV file with the new data that was added, updated, and deleted to the Item table. Create using the Week 2 program. 10
Total Points 50
Hints and Tips
TIP: The XML namespace
You should read about the classes available in the System.XML namespace added in this iLab. Search for it on your favorite search engine—reading not only Microsoft’s websites, but several of the third-party sites as well. The XmlReader is a good place to start. Look for tutorials.
TIP: Variation of XML and variations of methods to read XML within programs
XML formats vary widely as the formats are created as needed. There are no rules to what nodes and tags you can create in a format—only that you obey the syntactical and grammatical rules of the XML format itself (for example, there must be a closing tag for each opening tag). There are also advanced techniques for accessing this data—for example there is a technique called LINQ. LINQ allows the programmer to write statements that look similar to SQL, but that are directly in the code. These statements can access data easily from XML, databases, and even CSV files. However, it is important that you learn the basic ways of performing these tasks first so that you have a fundamental understanding of the foundations of data handling.
We will look at an alternate way to format and read data in an XML file in Week 5.
TIP: Adding new inventory types
You can very easily add new inventory types by creating a new class called Inventory whose attributes and properties would be defined as:
namespace ProjectWeek4
{
class Inventory
{
public int Invent_id { get; set; }
public string Desc { get; set; }
Notice that this class represents the inventory descriptions from the Inventory table. This new class can contain the same types of methods as used by the Item class to add rows to the Item table—except of course it adds to the Inventory table. It is doubtful that you would ever delete from this table as any referenced descriptions deleted would violate referential integrity—or at the very least leave items without descriptions. You would also need to add a new node command rather than ADD, UPDATE, or DELETE since they are already used by the item commands. Perhaps you might use NEW.
TIP: Look at some EDITORS like NotePad++ and Sublime for doing XML work. These types of editors understand XML files and will color-code them to make it easy to see problems. These are much better than Notepad!
Course Project Tie-in
What is XML and why is it beneficial for defining data in this project? What are advantages of XML over a CSV file? Go into detail about the various makeup of the XML format. Are there alternatives to XML? How do computer programs input and process XML files? Use Week 4 to explain XML and how it will be used in the project. Are there any third-party tools that might help?
There is nothing to turn in for the Course Project until Week 8, but your team must not get behind. The final paper is fairly long. To stay on track, complete the Week 4 portion of the Course Project.
