Scenario/Summary
The simple process of reading a datafile containing update data that needs to be used to update the records in a database is a very integral part of data and information systems in industry today. For the system being developed in this course, this process is no less important. The focus of this lab will be on the actions that take the output file from a transaction processor that contains data to be used to update inventory data in the database.
For this third lab we will use the Microsoft Visual Studio suite of applications and build our lab project in C#.net. The application will be very simple in nature, but at the same time it will process all of the required functionality to accomplish the actions needed. Some of the processes will be a little different than those used in Lab 1 and we will need to manipulate the C# code a little more than in Lab 1.
You will be using the same directory you did for Lab 1, only we will be creating a different project name. You will also need to download the Lab3TestFile.ZIP file from Doc Sharing. This file contains the INV_UPDATE.TXT file you will need to use to test your application.
Deliverables
The main deliverables for this lab will be the C#.NET application files and verification that the item table in the Inventory.MDB Access database was successfully updated. This verification will be provided by taking a screen shot of the table before running the application and then a second screen shot after. The second screen shot should show the changes made to the database table. Because a C#.NET application has a number of files associated with it contained in a directory folder named after the project name, as well as two sub directories (“bin” and “obj”) with sub-folders under those, it is important that you submit all of the files needed for the application. By right clicking on the file folder that bears the application name (inv_update) and then selecting to send the folder to a Zip file, you will get all of the files as well as folder structure needed to submit for grading. You will also want to add the screen shots saved to an MS Word document to the Zip file.
Submit only the one Zip file to the week 3 iLab drop box for grading.
iLAB STEPS
STEP 1: Creating a New Project
To begin the lab, first log in to the iLab environment through Citrix and select the folder labeled SAI430. In this folder you will find icons for Windows Explorer, SQL*Plus, and Visual Studio 2005. For this lab you will want to open up Visual Studio 2005. Be sure that you select “full access” from the security window box so that you will be to be able to access the C: on your current computer if you need to.
Once the Application has started up and you are at the Start Page, select the create a new project option. When presented with the New Project window like the one below, be sure that you have highlighted Console Application under Templates window. Now give the new project the name INV_GRAB in the Name field, and have the location field pointing to the F:SAI430 folder you have on the F: drive. The diagram below depicts what your New Project window should look similar to.
Once you have done this, select OK to complete this operation. You may get a “Microsoft Development Environment” message box stating that the project location is not a fully trusted .NET runtime location. You can ignore this and just select OK. You should now see your new project listed in the Solution Explorer window on the upper right hand corner of the editor window. You are now ready to begin setting up your form.
STEP 2: Adding New Namespace Components
- To begin, you need to add the following three namespaces to the top of your application code:
using System.IO; using System.Data; using System.Data.OleDb;
STEP 3: Defining New Variables
Under the section of code titled Public Form1(), add the following new variable:
- A string variable named line, and initialize it to a null
- A INT type variable named v_item_id, and initialize it to zero
- A INT type variable named v_qoh
STEP 4: Setting Up the Data Set and Getting Data
In Lab 1 we set up a simple data set and used a select statement to populate it. In this lab we are going to deal with this process a little differently, since we will need to have both a SELECT statement command as well as an UPDATE command to work with.
- The first thing we will do is set up the code that will define the data set and the data adapter (very similar to Lab 1). The name of the data set will be dataSet1, and the data adapter name will be dataAdapter1. Your code should look similar to the following:
DataSet dataSet1 = new DataSet(); OleDbDataAdapter dataAdapter1 = new OleDbDataAdapter();
- Next we need to define the select command as a new OleDbCommand that will be used to get the data for the data set. The name of the select command should be selectCommand1. Your code should look similar to the following:
OleDbCommand selectCommand1 = new OleDbCommand();
- Next we need to define the way we will use the select command when the database is opened and connected to it. The idea here is that, when we connect to the database, the data set will be ready to be populated and we will have defined in the connection string how to populate it. Your code for this section should look similar to the following;
OleDbConnection conn = new OleDbConnection(); dataAdapter1.SelectCommand = selectCommand1; selectCommand1.CommandText = "SELECT item_id, qoh FROM item"; selectCommand1.Connection = conn;
STEP 5: Setting Up a Database Connection
The creation of the database connection with Access and the associated data set that can be used to update the data in the database for this lab will be virtually identical to what was done in Lab 1. For the purposes of this lab, and your project, you will only need data from two columns in the ITEMS table of the INVENTORY database, but we will control that with the code written later. The following steps will lead you through the process of setting up the connection.
- Now you can set up the connection to your Access database that you downloaded and put in your folder. The actual connection string is @”Provider=Microsoft.JET.OLEDB.4.0; data source=F:inventory.mdb”. This is a standard connection string for MS Access. In the previous step, we defined the OleDbConnection conn, which we can now use to define the connection string that will be used for the connection.
conn.ConnectionString = @"Provider=Microsoft.JET.OLEDB.4.0; data source=F:SAI430inventory.mdb";
This is simply defining a string variable named conString and assigning the connection string to it. We will use this variable later.
- Now you can connect and open the database with the following command, entered right below the line above.
conn.Open();
STEP 6: Filling the Data Set and Defining the Update Command
The process of setting up the Update command is a bit more involved than defining a select statement, because we must define what columns in the data set we are going to change and we must create a reference between the variables defined at the beginning and the table columns they will relate to.
- The first thing is to generate the code to fill the data set based on the item table in the database that was just connected to it. The code is similar to what was used in Lab 1, as follows.
dataAdapter1.Fill(dataSet1, "item");
- Now we must deal with the update command. First we must associate the new command with the data adapter we created, then associate that with the database connection, and then finally add the column parameters to the command so we can find the data in the data set. The code should be similar to the following.
dataAdapter1.UpdateCommand = new OleDbCommand(); dataAdapter1.UpdateCommand.Connection = conn; dataAdapter1.UpdateCommand.Parameters.Add("@qoh", OleDbType.Integer, 4, "qoh"); dataAdapter1.UpdateCommand.Parameters.Add("@item_id", OleDbType.Integer, 5, "item_id");
STEP 7: Getting the Data from the Update File
In Lab 1 we used the StreamWriter class to write the date out to a file. In this lab we will use the StreamReader class to read the data from the input file.
- First you need to define an object named sReader as an object of StreamRead and initialize it to the path where the input file will be (this will be the F:SAI430 folder, or just the F drive if you did not create a folder). The name of the file produced by the transaction processing application is INV_UPDATE.TXT.
StreamReader sReader = new StreamReader(@"F:sai430inv_update.txt");
- Now use the ReadLine method to assign the sReader object with an empty set to the line variable that was defined up in step 3.
line = sReader.ReadLine();
- Next we need to define an object for the DataRow class that will hold the rows of data from the item table in our database. Name the new object itemRow and initialize it using dataSet1.Tables[“item”].Rows[0]
DataRow itemRow = dataSet1.Tables["item"].Rows[0];
STEP 8: Processing the Input File
Now we are ready to set up the loop to process the input data. Download the file from Doc Sharing titled Lab_3_loop.txt and insert the code under what you have just created.
The code for the loop is (was) created using the naming conventions that have been used in the previous steps for this lab, so if you used different names for your objects and such, you will need to adjust the code accordingly.
Now you are ready to rebuild the project and test it.
STEP 9: Building the Application
VERY IMPORTANT: Please refer to Lab 1 regarding how to build the application for 32-bit, and also set the security settings for a trusted application. Failing to do this for Lab 3 will cause errors when you try to run the application.
STEP 10: Testing the Application
You first want to make sure that you have the Inventory.MDB database in the same SAI430 folder on the F: of your directory structure in Citrix. This should also be the same location of your source code project and where you have pointed the program to write the output file to. In order to test the application and generate the documentation to verify that it works, you will need to do the following:
- Again, make sure the application, inventory.mdb, and the input path in the application are all using the SAI430 directory on the F: drive in Citrix, or at least are in the root of the F drive if you did not create a directory for this course. Also make sure that the input test file you downloaded from Doc Sharing is in the same location.
- Open up the Inventory.MDB database and item table, take a screen shot of the table, and then close the table. Paste the screen shot into an MS Word document and save it.
- Run the application using the INV_UPDATE.TXT input file you downloaded from Doc Sharing.
- Now open the database table again. You should see a difference in the totals for several of the inventory items (if you do not, then you need to determine what is not working). Take a second screen shot showing the new inventory totals and paste this one into the same MS Word document as the first.
- Submit the screen shots along with your code to be graded.