SAI430—iLab Week 2: A Sequential Write Program
TCOs Satisfied
1 Given a set of guidelines and tools, explore how project work and development can be enhanced and improved with proper structure and formality.
5 Given a business system application that needs to retrieve from and write to a relational database using an external sequential or CSV 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
In Week 2, you will expand upon the knowledge gained in Week 1. Here you will learn about and create a comma separated values (CSV) file. CSV files are extremely common when processing, storing, and transferring data. These files essentially store data in simple text files (often called flat files) with the data separated by commas. Each line in the CSV file generally represents one row from a table or one row from a query (see more information in the Hints and Tips section). You will write a program that takes data from the inventory database created in Week one and store it to a CSV file.
Lab Description
For Week 2 you will create a Visual Studio C#.NET program based upon your Week 1 lab assignment. Recall that the database is defined by the ERD shown in Figure 1.
Figure 1— Inventory Database ERD
You will use the StreamWriter class to generate a CSV file on disk (see Hints and Tips). In the same manner that you wrote multiple lines to the console from an SQL query in Week 1, you will now write multiple lines to a file in Week 2. The query will be similar, joining the data from both tables. The primary difference this time is that the data will not be formatted to display on a console, but instead to be stored in a CSV file. For example, the price of each item will not be formatted as currency. Once it has been changed to contain dollar signs and commas, it can no longer be used in mathematical operations. The data we will store to the CSV file might later be used for calculations.
The CSV file will contain all of the items in the item table with the following attributes:
• Item ID
• Item Description
• Item Quantity
• Item Size
• Item Color
• Item Price
Each row in the file will contain the attributes for one item. You should place quotes around the item description, size, and color data when writing them to the file (see the Hints and Tips section for more detail on this). Name this file:
Items.csv
If you were to open the resulting file in Notepad, it should look like Figure 2.
Figure 2—Sample CSV file viewed in Notepad
If you double-click the file, it should open in Excel (assuming you have Excel installed and another program hasn’t taken over the .csv extension). When opened by Excel, it should be formatted something like Figure 3.
Figure 3—Sample CSV file opened in Excel
The lab is relatively simple, but it takes a lot of trial-and-error to get the file created properly and the data formatted in proper CSV format.
Deliverables
For iLab 2, you will submit the following deliverables to the course Dropbox in a single ZIP file:
1. Name your ZIP archive LastName_FirstName_W2_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 2 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. Include the generated CSV file in your archive. It should be named Items.csv
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.
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. 20
Item 3 Proper values are output to a CSV file. 10
Item 4 The CSV file has quotes around data that contains strings. 10
Total Points 50
Hints and Tips
HINT 1: StreamWriter
To add the StreamWriter class to your program, you will need to add the following namespace at the top of the program:
//Add namespace for read/write to disk
using System.IO;
HINT 2: Seeing if the output directory exists
This code is useful when you’re not sure if the destination directory exists where you want to write a file. You can, of course, pick any file path you need.
//Set where the file goes
string filepath = @”c:\sai430\”;
string filename = @”Items.csv”;
//Check to see if directory exists, if not create it.
if (! Directory.Exists(filepath))
Directory.CreateDirectory(filepath);
As a side note, notice the “@” sign used prior to the file path and filename. This tells the system to treat the string as a “literal” string—therefore ignoring the back slash systems as possible commands. We know that \n causes a new line. What might the computer think \s does? Without the “@” we would have had to write the line as:
string filepath = “c:\\sai430\\”;
On CITRIX, your personal drive will have your D# associated with it—and it should be the F: drive. Therefore, you will probably use:
string filepath = @”f:\sai430\”;
The name of the subdirectory does not have to be “sai430”, you can create any that you want, such as:
string filepath = @”f:\sai430\Week2\output\”;
HINT 3: To open and write to a file, you will need to create a StreamWriter object (search for this class online and become familiar with its use and methods). This takes the place of the console and is then used in the same manner. The StreamWriter that you create will need a variable name. The example below uses “sw” as the name. Notice that the file path and filename from HINT 2 are used. The first line of the foreach statement is also given to you as an example of how you write to the StreamWriter. Notice the comma following the {0}. This will be the first value separation comma in the line. All of the following write commands (except the last one) will also have a comma.
//Open file for output
StreamWriter sw = new StreamWriter(filepath+filename, false);
//Loop through all data results
foreach (DataRow dataRow in theTable.Rows)
{
sw.Write(“{0},”, dataRow[“item_ID”].ToString());
At the bottom of your file, about where you close the database, you will need to close the StreamWriter. You should be able to find this command in your research (or possibly even guess it).
TIP 1: Just what is a CSV file?
CSV files are not necessarily generated by databases. Any program might create them for a variety of reasons. For example, a temperature sensor in an industrial oven might add one line to the file Ovencycle.csv every minute.
10/13/2014, 14:31, 327.5
10/13/2014, 14:32, 327.7
10/13/2014, 14:33, 328.0
In this case, the computer has recorded three lines of data that includes the date, time, and temperature in degrees Fahrenheit. Later, this data can be imported into the main database system, or it could simply be opened in a program like Excel. In fact, Excel understands CSV files and will usually open them automatically when you double-click a CSV file (see Figure 4). Once imported into Excel, the data can be charted, analyzed, and otherwise processed. Many other programs such as statistical analysis tools can also directly import CSV files with a minimum of effort.
Figure 4—Example CSV file opened in Excel
There are also cases where the data may itself contain commas, therefore causing a formatting issue. Consider the following five lines of data that contain a restaurant, phone, rating, and average meal price. See what happens when it is imported into Excel in Figure 5.
Joe’s Diner, 816-555-2838, 2.7, 5.33
Bill & Jane’s, 816-555-8882, 3.5, 9.10
Sub, Surf, and Fun, 816-555-3817, 4.1, 8.22
McDoogles, 816-555-9388, 2.1, 3.77
Steak, Inc., 816-555-7732, 4.7, 15.33
Figure 5— Bad CSV file leads to a bad import
What’s wrong? Upon closer examination, we can see that the commas in the name confused Excel. Excel thought those commas were value separators and therefore split the name up into several columns. To fix this, quotes are often placed around the data that might have a problem (see the result in Figure 6).
“Joe’s Diner”, 816-555-2838, 2.7, 5.33
“Bill & Jane’s”, 816-555-8882, 3.5, 9.10
“Sub, Surf, and Fun”, 816-555-3817, 4.1, 8.22
“McDoogles”, 816-555-9388, 2.1, 3.77
“Steak, Inc.”, 816-555-7732, 4.7, 15.33
Figure 6—Names are properly handled
HINT 4: Add quotes around string data
The problem with adding quotes to your StreamWriter write command is that you also use quotes to identify what is being printed. Look at how the inventory description is printed without using quotes. The string “{0},” defines the place where the description will be printed along with the following comma. As an example, Joe’s Diner is printed with the comma after it.
sw.Write(“{0},”, dataRow[“invent_desc”].ToString());
Joe’s Diner,
But if we want to add quotes around the description, we’d need to add quotes around the {0}. This would create a line that looked like this:
sw.Write(“”{0},””, dataRow[“invent_desc”].ToString());
But the compiler would not understand all the quotes as it considers them to be string delimiters for use at the beginning and end of strings. Fortunately, there is a backslash command \” that will tell a string that we really want a single quote, and that it shouldn’t treat it as a string delimiter. We use it just like a \n. In summary, to use a quote in a string, put a backslash in front of it.
sw.Write(“\”{0}\”,”, dataRow[“invent_desc”].ToString());
Give it a try in your program. The above line of code should write a description and comma to the CSV file as shown here:
“Joe’s Diner”,
Course Project Tie-in
Week 2 lab gives you more insight into how C#.NET deals with files on disk by introducing the concept of Streams. Other languages use this same concept, such as Java and C++. What are the languages used in today’s business development environment, and where does C#.NET fit in? Use Week 2 to compare and contrast the major development languages in use today. What would each be used for? For large business or small? Web applications, console, or back-end? You should pick at least three major languages. Explain why C# is a good choice for this project.
In your course project, you are “pretending” to be developing these Weekly iLabs for a fictional company. You will be combining these iLabs into a major project in Week 8. Write your course project from the point of view of developers in this fictional company, explaining to management what the options were, and why you chose C#.NET.
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 2 portion of the Course Project.