SAI430—iLab Week 1: The Database Programming Environment

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.

Introduction and Rationale
During Week 1, the primary learning activity is to set up the programming and database environments that you will use in completing the course labs. We will investigate TCO 5 by learning how to use Visual Studio to connect to a MySQL database. This setup will be tested by a short C#.NET program that will query and display data from a MySQL table. You will then be asked to modify this program to display additional information.
TCO 1 will be addressed as you begin to design your project requirements document that constitutes the class project. Teams of two or three should be assigned by your instructor so that you can begin collaborating on this project.
Lab Steps
Step 1—Setting up MySQL
For Week 1, you will create a MySQL database that is typical of an inventory management system. The entity relationship diagram (ERD) is shown in Figure 1.

Figure 1 – Inventory Database ERD
To use MySQL in this course, a third-party service called Omnymbus has been employed to provide virtual MySQL servers. You should have been assigned a user ID and password for this service. Omnymbus is a typical online service like those that many companies use for application and website development. Learning to use Omnymbus is an important activity that can serve you well both academically and professionally. This being said, some students may find that they want to install MySQL on their personal computer. The community edition of MySQL is free.
With the permission of the instructor, the savvy student could setup and use MySQL on their personal computer system fairly easily—however, this is beyond the scope of the course. Technical support WILL NOT be provided by DeVry or your professor if you choose to set up MySQL on your personal system. Additionally, all of the examples given in the course use the Omnymbus version of MySQL server.
The steps for setting up your Week 1 iLab MySQL database on Omnymbus can be found in Document Sharing in the document entitled:
SAI430_Online_Database_setup.docx

Step 2—Visual Studio connectivity and testing
This course will use Microsoft’s C#.NET, which is part of the Visual Studio package. Using C# will provide a relatively easy method for connecting your programs to the data. Setting up such a connection in the real world is always a complicated task the first time, but most businesses have established a repeatable method which they use over and over again in their projects.
You will be provided with a method of connecting C#.NET with MySQL using ODBC, but you should be aware that there are several other techniques that work equally well. ODBC is a Microsoft middleware technology that works well with products like C# and Java.
The steps for setting up and testing you connectivity between C#.NET and MySQL can be found in Document Sharing in the document entitled:
SAI430_CITRIX_Programming_setup.docx

Step 3—Reading and displaying data from a relational database.
During Step 2, you created and tested a program that displayed several item ID numbers from the Item table. Not only did this test your ability to copy and run a C# program, but also proved that you successfully connected to the MySQL database on the Omnymbus server. For your next step, you will create a typical relational query that is somewhat more complex, yet based upon these concepts.
Modify the program provided in Step 2 to display the following information on the console with a simple column header:
• Item Description
• quantity in inventory
• size of item
• color of item
• price of item
See Figure 1 above for the database ERD. You will need to “join” the item and inventory tables with your embedded SQL query. Figure 2 shows the expected console output:

Figure 2— Console output from iLab 1
Console Output:
ITEM QUANT SIZE COLOR PRICE
—————————————————————–
3-Season Tent 200 Sienna $259.99
3-Season Tent 200 Forest $259.99
Womens Hiking Shorts 200 S Khaki $29.95
Womens Hiking Shorts 200 M Khaki $29.95
Womens Hiking Shorts 200 L Khaki $29.95
Womens Hiking Shorts 200 S Olive $29.95
Womens Hiking Shorts 200 M Olive $29.95
Womens Hiking Shorts 200 L Olive $29.95
Womens Fleece Pullover 200 S Teal $59.95
Womens Fleece Pullover 200 M Teal $59.95
Childrens Beachcomber Sandals 200 10 Blue $15.99
Childrens Beachcomber Sandals 200 12 Blue $15.99
Childrens Beachcomber Sandals 200 11 Red $15.99
Mens Expedition Parka 200 S Navy $199.95
Mens Expedition Parka 200 M Navy $199.95
Mens Expedition Parka 200 XL Navy $209.95

Deliverables
For iLab 1, you will submit the following deliverables to the course Dropbox in a single Microsoft Word for Windows document:
1. Name your file LastName_FirstName_W1_iLab.docx. If your instructor requests additional files, you can ZIP this into a file entitled: LastName_FirstName_W1_iLab.zip.
2. In the document, include your full name, course number, professor’s name, date, assignment number, and email address as a header.
3. Include a screen capture of the console output. You can capture the console window by holding down the ALT key and pressing Print Screen. This will place the console window on the clipboard. You can then paste it into Word.
4. Copy and paste the program code into Word after the screen capture. Your instructor may ask you to include your entire project as a ZIP file. If so, she or he will give you specific instructions.
Make sure you close and save your Word document before uploading it to the Dropbox. This will ensure that the latest version of the file is 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 in output screenshot. 10
Item 4 Item 3 is well formatted as in the sample output from Figure 2 above. 10
Total Points 50

Hints and Tips
HINT 1: Be sure to use the program in the Visio Setup document SAI430_CITRIX_Programming_setup.docx as a starting point for your program.
HINT 2: Here is an example embedded SQL query that joins two tables dealing with employees and their department name. Your query will be similar but deal with the item and inventory tables.
string theQuery = “SELECT lastname, firstname, empID, department_desc ”
+ “FROM employee, department ”
+ “WHERE employee.department_ID = department.department_id”;

There are several things to notice here. The first is that the department description is contained within the department table which must be joined to the employee table which only has the department ID. The join is accomplished by matching the IDs within the WHERE clause.
The next thing to notice is that there is a space at the end of the first two lines before the quotation closes. Why? Failure to include this space would cause the SQL statement to contain “department_descFROM employee”, which is obviously going to create an error. Some students find that it is a good habit to print out theQuery to console while they are developing the program so that they can “eyeball” the embedded SQL and check for errors.
Console.WriteLine(theQuery);

This SQL can certainly be written in different ways. An alternative might be:
string theQuery = “SELECT a.lastname, a.firstname, a.empID, b.department_desc ”
+ “FROM employee a, department b ”
+ “WHERE a.department_ID = b.department_id”;

Using the table.column format with table labels often clears up arbitrary assignments, but in the previous example it was only needed for the WHERE clause.
HINT 3: The “PadRight()” method from the String class can come in handy:
Console.Write(“ITEM”.PadRight(30));
Console.WriteLine(“-“.PadLeft(65, ‘-‘));
Console.Write(“{0,10}”, dataRow[“itemsize”].ToString());

The first line prints “ITEM” left justified in a field of 30 total characters (the remaining characters are spaces). The second line prints 65 dashes. The third line would print the item size result RIGHT justified in a field of 10. Note that these three lines are examples and DO NOT go next to each other in your final solution. They are just hints. Review the C# Write and WriteLine methods as needed for data formatting.
HINT 4: When you extract the current price from the MySQL database, it comes from the DataAdapter as an object. In the line below, the object is then “cast” into a decimal which matches the type in the database. This freshly-cast object is then converted to a string with the addition of the “C” (or currency) format specifier. While your earlier classes probably addressed casting (often called typecasting), you should refresh your knowledge of this term and familiarize yourself with why it is used.
Console.WriteLine(“{0, 10}”, ((decimal)dataRow[“curr_price”]).ToString(“C”));

TIP: What do all these ODBC things do?
While there are several ways of doing this program, the method selected uses the ODBCConnection. Briefly discussed are the various classes used for this task.
The ODBCConnection provides your link to the database. Think of it as a two-way subway tunnel that connects your program to the database.
OdbcConnection connection = new OdbcConnection(conString);
connection.Open();

If the ODBCConnection is the subway tunnel, then the ODBCDataAdapter is the subway car. It carries the SQL to the database from the program. It also brings back any query results.
OdbcDataAdapter DataAdapter = new OdbcDataAdapter(theQuery, connection);

The DataSet is an empty shelf when it is first created. However, the DataAdapter will fill it with data brought back form the database.
DataSet theData = new DataSet();
DataAdapter.Fill(theData);

So that the data in the DataSet can easily be accessed, we then put it in a DataTable—kind of like an easy-to-read bulliten board. Using a foreach statement, it is easy to go through the DataTable row-by-row. There is a special class called the DataRow which can hold one row of data from the DataTable. The foreach loop therefore goes through the DataTable one row at a time, extracting a row from our query and allowing us to extract the results and print them.
DataTable theTable = theData.Tables[0];
foreach (DataRow dataRow in theTable.Rows)

Course Project Tie-in
Following your work on the Week 1 iLab, your team should get together to research some of the various databases available to businesses (see the Week 1 section of the Course Project description located in Course Home). Here we have used MySQL. Get to know the different databases, their capabilities, prices, and for what they are normally used.
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 1 portion of the Course Project.