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.
6 Given a business system application that needs to apply a file of add, update or delete transactions to a database design, the student will code and document the business application using a high-level language.
Introduction and Rationale
In Week 3, you will expand upon the knowledge gained in Weeks 1 and 2. During Week 2, you learned how to create a CSV file with queried data from your database. This Week you will create a program that reads a CSV file on disk and then loads that information into the database “Item” table.
Various software and hardware produce flat files that contain data which must be added to company databases. For example, a cash register at the grocery store might accumulate all of the transactions that take place over the day—then at regular intervals it will transmit this information all at once to a program that then adds the data to the database. Refer to the topic of “transaction processing” in your course material.
Lab Description
For Week 3 you will create a Visual Studio C#.NET program that reads a flat file of new inventory items (which is in CSV format) and then adds that data to the ITEM table in our inventory database (see Figure 1 and 2).
Figure 1—Transaction Processing Workflow
Figure 2—Inventory Database ERD
The flat file will be entitled “NewInventory.csv”. You can find this file in Document Sharing within the “SAI430_W3_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 CSV file from the Week 2 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 NewInventory.csv file will look like this. Notice line 2 does not have a size.
11775, 894, S, Khaki, 24.95, 10
11665, 786, , White, 259.95, 200
11779, 894, M, Olive, 29.95, -10
11849, 559, XXL, Royal Blue, 30.00, 219
11797, 897, L, Teal, 59.95, 200
11798, 897, XL, Teal, 64.95, 100
These lines use the format:
item_id, invent_id, itemsize, color, curr_price, quantity
Read these lines one at a time and use them to ADD items to the database. Upon successfully adding an item, print a success message to the console. If an item is already in the database, then an error message should be printed to console. See Figure 3 for an example.
Figure 3—Sample console output
Assumption: Items in this flat 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
We will learn how to add new items to the inventory table in Week 4, but for now, these items are the only ones that our store sells. This means that the invent_id in each row of the CSV file is one of the five choices listed above. For now, we will assume that there will be no erroneous entries. Later we will learn to validate our data and catch errors.
Additionally, we will learn to handle updates and deletes in Week 4 as we study the XML file format.
Techniques
By this time in your academic career, you should be familiar with the concept of Object-oriented Programming (OOP). The use of classes and objects in program design is prevalent in the real world, and you are expected to master these techniques before graduation. Not only is it a good practice, but the use of OOP makes programs easier to write and understand. For this program, we will be creating an Item class to store our item data rather than to place each bit of data into lots of individual variables. One of the primary purposes of an object is to encapsulate data.
Encapsulating objects in C# is a little different than what you may have learned in Java or C++. Typically, you create several private attributes which each has a public accessor (“getter”) and mutator (“setter”). So an attribute in Java with its accompanying methods might look like this:
private int age;
public void setAge(int value) {
age = value;
}
public int getAge() {
return age;
}
However, in C# a slightly simpler format is allowed which creates properties:
public int Age { get; set; }
Using properties, the data can be stored or accessed using the equal sign (assume age is in a class called Person):
Person Employee = new Person();
Employee.Age = 25;
Console.WriteLine(Employee.Age);
Whereas in Java we would have written:
Person Employee = new Person();
Employee.setAge(25);
System.out.println(Employee.getAge());
The idea is that the use of properties is more natural and that
Employee.Age = 25;
is easier to read than:
Employee.setAge(25);
To add a class to your project, go to the project pulldown menu and select “Add Class…”
Then name your class and click ADD.
Then write the class:
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;
namespace ProjectWeek3
{
class Item
{
//Set and Get properties
public int Item_ID { get; set; }
public int Invent_id { get; set; }
public string Itemsize { get; set; }
public string Color { get; set; }
public decimal Curr_price { get; set; }
public int Qoh { get; set; }
Next, we will add a method that can parse a CSV line from the input file. Remember that each line may look like this:
11798, 897, XL, Teal, 64.95, 100
These lines use the format:
item_id, invent_id, itemsize, color, curr_price, qoh
Adding this method will break up a line from the CSV file into the individual attributes. Notice the use of the “Split” method in the string class. Read about Split and find out how it creates a string array; understand what this method does.
//Parse CSV line
public bool ParseCSVline(string aLine)
{
try
{
string[] fields = aLine.Split(‘,’);
this.Item_ID = int.Parse(fields[0]);
this.Invent_id = int.Parse(fields[1]);
this.Itemsize = fields[2];
this.Color = fields[3];
this.Curr_price = decimal.Parse(fields[4]);
this.Qoh = int.Parse(fields[5]);
return true; //if everything parsed, return true
}
catch (Exception ex)
{
return false; //if a parse failed, return false
}
} //end of barseCSVline
There are a few things to notice about this method:
1. It returns true or false depending upon whether or not it was successful.
2. The word “this” is used to specify “this object we are currently in”. Remember, objects don’t know their own name—they only know that they exists. Therefore, the only name they know themselves by is “this”. In the example above, the use of the word “this.” is optional, but is good programming practice to clearly show that each listed property is a class property.
The next method checks to see if the current object exists in the database. Remember that once the above method runs, the object will have an item_ID. We can look for that in the database. The only thing our item needs to know is the connection to the database. We’ll pass that in from the Main method once we write it.
//See if an item is in the database.
//Pass in the database connection as db.
public bool IsInDatabase(OdbcConnection db)
{
String sql = “SELECT * FROM item WHERE item_ID=?”;
OdbcCommand Command = new OdbcCommand(sql, db);
Command.Parameters.Add(“@ID”, OdbcType.Int).Value = this.Item_ID;
//If query returns a row – there is already an item in the table.
if (Command.ExecuteReader().HasRows)
return true;
else
return false;
} //end of IsInDatabase
There are several things to notice about this method.
1. This method returns a true or false depending upon whether or not the object exists in the Item table.
2. Parameters can be used to embed data in an SQL statement. In this case, the “?” in the SQL statement is replaced by the value of “this.Item_ID” two lines lower in the Parameters statement. In this manner, the SQL statement looks for a particular item by its ID. Note that the item’s type was specified in the parenthesis as “OdbcType.Int”. The “@ID” is unused in ODBC, but can serve as a visual cue to the programmer.
3. The parameter that is passed in, “OdbcConnection db”, contains the connection to the database which was already set up in the main.
And finally, the last method in this class will ADD all the attributes of the current object to the database.
//Add a row to the database passed in as db
public bool 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 > 0)
return true; //Was successful in adding
else
return false; //failed to add
} //end of AddRow
} //end of Class Item
} //end of namespace
Once again, there are a few things to note:
1. The SQL statement is a standard INSERT for which you should be familiar.
2. Each “?” in the SQL statement is replaced by a parameter statement further down in the code.
3. The curr-price attribute is handled a little differently. ODBC has difficulty matching up the “decimal” datatypes in MySQL and C#.NET. To simplify matters, they were changed to the “Double” datatype for this INSERT. Unfortunately the technology world is not perfect, nor is it consistant. Little problems like this pop up all the time.
4. The use of the Trim() method at the end of Itemsize and Color make sure that any spaces left on the front or back of the string are “trimmed” off.
SUMMARY: This class has been provided for your reference and to teach you further techniques in C#.NET, which include database communication and the creation of a class that is smart enough to interact with the database. There is no one way to do this, or even a best way. Actual techniques usually vary from programer to programmer and company to company. We will be expanding this Item class as we continue through the semester. Next week we will add XML handling.
Next, you still need to write the Main method that will use this class. A skeleton is provided to you with comments. This skeleton is not the only possible solution, just one of many.
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;
//Add namespace for read/write to disk
using System.IO;
namespace ProjectWeek3
{
class Program
{
static void Main(string[] args)
{
//Step 1 – Connect to database
//Connection string needed to talk to Omnymbus
[You will add the connection code used in the previous two iLabs here]
//Step 2 – Open input file
//Set where the file comes from
string filepath = @”F:\sai430\”;
string filename = @”NewInventory.csv”;
//Open reader
[Create a StreamReader object here with the name “theFile”. Read and learn about how to use the StreamReader.]
//Step 3 – Create an object to use
[Instantiate the Item class here. Name it theItem.]
//Step 4 – Loop through file and add to database
while (theFile.Peek() >= 0)
{
//Get one line and parse it inside the object
[Get a line from the StreamReader object “theFile” and pass it on to theItem object. Remember, we wrote a method to parse this line and load it into theItem’s attributes.]
//Check to see if item is already there
[Do an IF statement that checks to see if theItem is already in the database. Remember, we wrote a method that will answer this question with true or false. If false, you can use the “continue” statement to go back up to the top of the while loop and read the next line (read about “continue” in C#.NET). Don’t forget to print your error message.]
//Add the new item to the database if it wasn’t already there
[Finally, add the item to the database here. Again, we have a method for doing this.]
} //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
Deliverables
For iLab 3, you will submit the following deliverables to the course Dropbox in a single ZIP file:
1. Name your ZIP archive LastName_FirstName_W3_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 3 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 this week. 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 4.
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 4—CSV File displayed in Excel (new items highlighted)
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 to the Item table. Create using the Week 2 program. 10
Total Points 50
Hints and Tips
HINT 1: Resetting the database during testing
You may find that you want to test your program multiple times, but that this will create changes to the database. 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_W3_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 5.
Figure 5—Resetting original database state by rerunning setup script
HINT 2: Using Peek
while (theFile.Peek() >= 0)
The Peek property of the StreamReader class will look at the next line in a file WITHOUT actually reading it. In this case, it tells us whether or not we need to read the next line. It will return a value less than zero if there’s nothing to read. Essentially, we’re asking it if there are any more lines to read.
TIP: OdbcCommand
You should look at and become familiar with the OdbcCommand methods ExecuteNonQuery and ExecuteReader. What is the difference?
Course Project Tie-in
This class will use ODBC to connect Visual Studio to MySQL, but what other methods exist for connecting programming languages to databases? Use Week 3 to identify connection techniques or other technologies that get data to or from the database and into the hands of stakeholders. For example, examine Microsoft’s OLE and the concept of “Native Drivers”. Are some languages better than others for this purpose? What must be taken into consideration when the program and database are not located on the same server? How does security play into this?
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 3 portion of the Course Project.
