SAI430 – Week 5 Lab
SAI 430 – iLab Week 5: Data Validation
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.
3
Given a set of business requirements to support a business case study like the one stated in TCO #2, identify and implement proper and adequate data validation processes for both data entry and file processing within an information system before inserting the data into a relational database.
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
In week five we will study several data validation techniques while also learning additional features of C#.NET. TCO#3 references the importance of validating data PRIOR to being inserted into a database. This, of course, helps to guarantee the integrity and trustworthiness of the data. There are multiple ways to check the validity of the data using both server-side and client-side methods.
Server-side methods can include database programs such as stored procedures and triggers, while client-side methods generally employee validation logic within the applications themselves. An entire course could be taught just on data validation techniques, but we only have one week. We will focus on the client-side by adding validation code into our existing program.
Additionally, we will focus on managing exceptions within our program. Many of you may remember using try/catch statements to trap exceptions within your code, but here we will also learn to write our own exceptions and throw them as needed to handle errors and data violations. We’ll catch all such irregularities and place them into an “errorlog” table within our database.
Lab Setup
Resetting the database before and during testing (New Database Table)
Before you begin this lab, you should reset the database to a new week five staring condition. You might also find that you want to reset it many times during the testing of your program. To reset your database back to the week five state – rerun the SQL script provided in week 5: Omni_InventoryDBWk5.sql. You can do this as often as you need. The script has been provided in the zip: SAI430_W5_iLab_Extras.zip located in document sharing. This script is run from within the Omnymbus MySQL server. Refer to Figure 1.
SAI 430 – iLab Week 5
Figure 1 – Resetting original database state by rerunning setup script
Lab Description – PART 1
For week five 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 one (see Figure 2 and Figure 3). The new “errorlog” table has been added.
XML file provided to the system from an external sourceComputer program written for XML processing and data validation.System Database
Figure 2 – Transaction Processing and Validation Workflow
SAI 430 – iLab Week 5
Itemitem_id: int (PK)invent_id: int (FK)itemsize: varchar(10)color: varchar(10)curr_price: decimalqoh: intinventoryinvent_id: int (PK)invent_desc: varchar(100)errorlogError_id: int (PK)item_id: interrorTime: datetimeerrorMsg: varchar(255)
Figure 3 – Inventory Database ERD with new errorlog table
The new errorlog table has been created so that any irregularities can be logged for future examination. Each row of the errorlog contains a unique error_id which will auto increment whenever a new row is entered. It also records the item_id which produced the error, the time and date of the error, and a brief description of what the error was. This error log will be populated by your program. The script that creates it is contained in the Omni_InventoryDBWk5.sql script.
CREATE TABLE `errorlog` (
`error_id` int(5) NOT NULL AUTO_INCREMENT,
`item_id` int(5) NOT NULL DEFAULT ‘0’,
`errorTime` datetime,
`errorMsg` varchar(255) DEFAULT NULL,
PRIMARY KEY (`error_id`)
);
The XML file will be entitled “UpdateW5.xml”. You can find this file in document sharing within the “SAI430_W5_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 have been using on the F drive. 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 UpdateW5.xml file will look like this.








Notice that the last two ADD statements will always produce errors as item 11111 has no other data, and item 11999 has a negative quantity on hand. 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 only when these tasks fail. In addition, you will write the erroneous results to the error log table. See Figure 4 for an example of
SAI 430 – iLab Week 5
the first run on console. A second run with the same XML file produces more errors (see
Figure 5) since data has already been added or deleted from the table.
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:
SAI 430 – iLab Week 5
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.
Validation
We will try to validate the input data by catching the following irregularities and write them to the errorlog table:
1. Could not parse the XML data from the file.
2. Item id was less than 1.
3. Current price was negative
4. Quantity on hand was negative
5. Tried to do an ADD, but the item already was in the database.
6. Tried to do an UPDATE, but it wasn’t in the database – or was already updated.
7. Tried to do a DELETE, but the item wasn’t there.
8. Tried to send an SQL command, but it failed for some reason.
SAI 430 – iLab Week 5
Lab Description – PART 2
You will write a separate program that will save the error log to a CSV file, in the same manner as the week 2 assignment did for the Item table. The CSV file should be named errorlog.csv. After two runs of the XML file for part 1 of the lab, the CSV file should look like THIS when imported into Excel (see Table 2).
Table 2 – CSV file from errorlog displayed in Excel
You will reset the database, then run the XML import program twice. Similar results to those above should be produced.
SAI 430 – iLab Week 5
Techniques
The first thing you need to do is review the errorlog table in the database (the ERD and the script used to create it are shown earlier in this document). Notice that the error_id will auto increment each time a new error is added.
ItemException Class
Next, you should create the ItemException class. This class inherits the Exception class so that it can be thrown to a try/catch block if an error state is determined. By inheriting the Exception class, ItemException becomes an exception itself – plus it gains any properties or methods that we create.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ProjectWeek5
{
class ItemException : Exception
{
public int Item_id { get; set; }
public string ErrorMsg { get; set; }
public ItemException(int id, string msg)
{
Item_id = id;
ErrorMsg = msg;
}
public override string ToString()
{
return ErrorMsg;
}
}
}
Things to note:
1. It inherits Exception
2. Its records can store the item_id of whichever item it is in when something went wrong. But that information will have to be passed into it.
3. Likewise, an error message is handed to it.
4. The ItemException constructor will be used to pass in the item_id and ErrorMsg.
5. The ToString method has been overloaded so that the error message can be printed.
You should comment this code explaining what each piece does. This class should be stored in a file called ItemException.cs that is within your project.
SAI 430 – iLab Week 5
Error Class
The error class is used to write errors to the database. See the relevant notes following the listing.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
//Add namespaces needed for database connection
using System.Data;
using System.Data.Odbc;
using System.Xml;
namespace ProjectWeek5
{
class Error
{
public int Item_id { get; set; }
public DateTime ErrorTime { get; set; }
public string ErrorMsg { get; set; }
//Class Constructor
//Pass in database connection, item id, and error message.
public Error(OdbcConnection db, int id, string msg)
{
Item_id = id;
ErrorMsg = msg;
ErrorTime = DateTime.Now; //set time to right now
AddRow(db);
}
//Add error to error log.
public void AddRow(OdbcConnection db)
{
string sql = “INSERT INTO errorlog ”
+ “(item_id, errortime, errormsg) ”
+ “VALUES(?, ?, ?)”;
OdbcCommand Command = new OdbcCommand(sql, db);
Command.Parameters.Add(“@ID”, OdbcType.Int).Value = this.Item_id;
Command.Parameters.Add(“@TM”, OdbcType.DateTime).Value = this.ErrorTime;
Command.Parameters.Add(“@msg”, OdbcType.VarChar).Value = this.ErrorMsg;
Command.ExecuteNonQuery();
}
}
}
Things to NOTE:
1. Item_id is used to hold the id of the item where things went wrong.
2. ErrorTime stores the time and date of when the error happened. “Datetime” is a datatype used both by C# and MySQL. They are generally compatible.
3. ErrorMsg holds the actual error that the programmer chooses to send to the error table.
SAI 430 – iLab Week 5
4. The constructor Error is used to pass in the database connection, the item_id, and the errorMSG. It stores them in the properties along with the datetime.Now – which is the current date and time. The constructor then asks AddRow to update the table.
5. Using a simple INSERT statement (like we have been using for the last two weeks), the Item_id, ErrorTime, and ErrorMsg are sent to the errorlog table. The primary key, error_id, will be updated automatically to the next highest value.
This class should be stored within the project in a file named Error.cs.
Changes to the Item class
This will change several methods within the Item class as we remove the simple Booleans used to indicate success or failure and instead replace them with exceptions that can hold far more detailed error messages. For example, look at the change in AddRow:
//Add a row to the database passed in as db
public void AddRow(OdbcConnection db)
{
String sql = “INSERT INTO item ”
+ “(item_id, invent_id, itemsize, color, curr_price, qoh) ”
+ “VALUES( ?, ?, ?, ?, ?, ?)”;
OdbcCommand Command = new OdbcCommand(sql, db);
Command.Parameters.Add(“@ID”, OdbcType.Int).Value = this.Item_ID;
Command.Parameters.Add(“@INVID”, OdbcType.Int).Value = this.Invent_id;
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;
int result = Command.ExecuteNonQuery(); //Returns 1 if successful
if (result < 1) throw new ItemException(this.Item_ID, "Failed to add item"); } Things to NOTE: 1. AddRow no longer returns a Boolean indicating success or failure. 2. Instead, AddRow must be placed in a Try/Catch block to see if the ItemException occurs on the last line. 3. The ItemException will occur if there is no result. We instantiate this exception using “new”, load it up with the Item_ID and an error message, then we throw it to whoever called it. SAI 430 – iLab Week 5 Another updated method is the parseXML method. It too must now be within a try/catch. //Used to parse an XML file pointed to by XmlReader public void 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) { throw new ItemException(this.Item_ID, "An input was an improper datatype"); } if (this.Item_ID < 1) throw new ItemException(this.Item_ID, "The item id must be positive"); if (this.Curr_price < 0) throw new ItemException(this.Item_ID, "The item price must be positive"); if (this.Qoh < 0) throw new ItemException(this.Item_ID, "The quantity must be positive"); } Things to NOTE: 1. If any Parse method fails, the above try/catch will “catch” the failure – in turn throwing its own exception back to the calling method. 2. Validation is also done here: We check to make sure the Item_ID is not less than 1. 3. We check to make sure the Curr_price and Qoh are positive values. 4. Validation errors also generate thrown exceptions. SAI 430 – iLab Week 5 So where do we use these thrown exceptions that we created in the previous two methods? Well, XMLAdd is one that uses them: //add this item to the database passed in as db public void XMLAdd(XmlReader f, OdbcConnection db) { try { this.parseXML(f); //Will get an exception if didn’t parse right //Is it in database? Check that it is NOT. if (this.IsInDatabase(db)) { //don't add it new Error(db, this.Item_ID, "Already in database"); Console.WriteLine(this.Item_ID + " already in database, can't insert."); } else //if not, add it this.AddRow(db); //Will get an exception if Add failes } catch (ItemException ex) //Catch bad parse or bad ADD { new Error(db, ex.Item_id, ex.ErrorMsg); Console.WriteLine("Item {0} not added: {1}", ex.Item_id, ex.ErrorMsg); } } Things to NOTE: 1. We start the TRY statement as soon as we enter the XMLAdd method. We begin by parseXML(f). We know that if the parse fails, the parseXML will throw back an ItemException that takes us to the catch. Likewise, later when we call AddRow(db), another ItemException might be thrown if something goes wrong (like a bad value that we didn’t check for). 2. If we end up in the catch statement, we then instantiate the Error class and send it the database connection, the Item_ID from the exception (named “ex”), and the ErrorMsg. Earlier we learned that Error accepts these values in its constructor and uses them to add a row to the errorlog table in the database. 3. We also use Error earlier in the XMLAdd method if the Item_ID is already in the database. 4. Notice that the Console.WriteLines have been moved out of the Main and added here in the Item class. The Item class is taking on more responsibility. You must also update the XMLUpdate and XMLDelete methods to use these new techniques. Note that in XMLDelete, the Item_ID parsing needs to be done without the parseXML method, but it can still be within the try/catch: //Only need ID to delete this.Item_ID = int.Parse(f.GetAttribute("item_id")); SAI 430 – iLab Week 5 The New Main Method Skeleton Look how fit and trim Main() looks now! All the responsibility is being moved to the methods. Note, the “using” statements were omitted for brevity (they remain the same as last week). namespace ProjectWeek5 { 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 = @"UpdateW5.xml"; //Open XML reader XmlReader theFile = XmlReader.Create(filepath + filename); //Step 3 - Loop through file and add to database while (theFile.Read()) { //Step 4 - Create an object to use Item theItem = new Item(); //Step 5 – Determine which command to perform if (theFile.Name.Equals("ADD")) theItem.XMLAdd(theFile, connection); else if (theFile.Name.Equals("UPDATE")) theItem.XMLUpdate(theFile, connection); else if (theFile.Name.Equals("DELETE")) theItem.XMLDelete(theFile, connection); } //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 SAI 430 – iLab Week 5 Deliverables For iLab #5, you will submit the following deliverables to the course drop box in a single ZIP file: 1. Name your ZIP archive LastName_FirstName_W5_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 5 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. You should have at least five C# files including: Your main program, your Item class, your Error class, your Item Exception class, and your program to write the errorlog to CSV. 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. 4. Run your errorlog CSV output program. It should generate a file named errorlog.csv. See the CSV file sample displayed above in Excel (see Table 2). 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 SAI 430 – iLab Week 5 Grading Rubric iLab Deliverables Description Points Item #1 Document header and comments in the code. 5 Item #2 Well written and functional C#.NET program Main. 5 Item #3 Well written and functional Item class. 10 Item #4 Well written and functional Error:Exception class. 5 Item #5 Well written and functional Error class for writing errors to the errorlog table. 5 Item #6 Well written and functional errorlog output program. 5 Item #7 CSV file with the new data that was added, updated, and deleted to the Item table. Create using the week 2 program. 5 Item #8 CSV file with error log with the same basic data as shown in Table 2 above. 10 Total Points 50 Course Project Tie-in For the week 5 section of the course project document, describe the different ways in which data validation can occur to ensure the integrity of the database. Part of this explanation should include a definition of what data integrity means. What is a false positive? What is a false negative? When would you want one over the other? Do some research on Type I and Type II errors. How do validation considerations change when the data comes from a file, like an XML file, versus when it comes from interaction from a user, such as on a web page? What is the difference between client-side and server-side validation? Remember to talk about this topic in regards to the project being created in the iLab exercises. 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 5 portion of the Course Project.