Client Staffing VB.Net Project

The main objective of this progress report is to start your Visual Basic project for the Client Staffing Optimization problem, create classes for the required objects, build the database file and create the Database class to read the data into objects, design the form to display the data and build the code to manage the form.

1. PROJECT FOLDER AND CLASS FILES

Create a new project named Clientstaffing with an empty form. Rename your form frmMain (right-click on Forml in the Solution Explorer, click on Rename, type frmMain, press Enter). Add four classes: Staff, Client, Task and Assignment, together with their properties and their data types listed under each class as shown in the following table and a default constructor within each class.

Staff

StaffID (Integer)

First (String)

Last (String)

StaffClass (String)

StaffClassID (Integer)

Salary (Decimal)

Branch (Integer)

AssignmentList (New List(Of Assignment)

Client

ClientID (Integer)

Name (String)

Type (String)

Revenue (Double)

Location (Double)

AssignmentList (New List(Of Assignment))

Task

ClientID (Integer)

Week (Integer)

Hours (Integer)

StaffClassID (Integer)

Assignment

Staff (Staff)

Client (Client)

Rate (Double)

Assigned (Boolean)

DATABASE FILE

Create an Access database named ClientStaffDatabase.accdb in the project folder. Import all worksheets from the Excel file ClientStaffDatabase.xIsx into this database as tables with the same name as the worksheets. Set the following primary keys for each table as follows (note that Task and Assignment tables have composite primary keys):

Table Primary Key

Staff StaffID

Client ClientID

Task ClientID + StaffClassID + Week

Assignment StaffID + ClientID

Finally, include this file in your project by clicking on Project - Add Existing Item, selecting database files from the file type filter and selecting the file ClientStaffDatabase.accdb.

DATABASE CLASS

Create a class called Database, which first imports the library System.Data.OleDb (above the class header). Include a public property called MyDataSet as a new DataSet type object. Include a default constructor and create the following functions:

a. GetDataAdapter: This function takes a SQL statement as a String argument and returns an OleDbDataAdapter

object. Make sure to use the filename ClientStaffDatabase.accdb in the connection string.

b. GetStaff: This function returns a sorted list of String type keys and Staff type objects as values. In this function:

- Declare a new sorted list of string type keys and Staff type objects as values.

- Declare an OleDbDataAdapter type variable and use GetDataAdapter to create and assign the data adapter to this variable. The SQL statement as the input for GetDataAdapter should select everything from the Staff table in the database.

- Use the Fill function of the data adapter to create a table named “Staff” in MyDataSet.

- Create a for-loop to go through the rows of this table. In this loop:

- Create a new Staff type object.

- Then, for each column of the "Staff” table, get the value and assign it to the respective property of the Staff object (with one line of code for each column).

- Create a key string by concatenating the First and Last properties of the Staff object together with the string “, “ (a comma and a space).

- Add this key and the Staff object as its value to the sorted list declared at the beginning of the function.

- Finally, return the sorted list declared at the beginning of the function

GetClients: This function returns a list of Task type objects. In this function:

- Declare a new list of Task type objects.

- Use the Fill function of the data adapter to create a table named "Client” in MyDataSet.

- Create a for-loop to go through the rows of this table. In this loop:

- Create a new Client type object.

- Then, for each column of the “Client” table, get the value and assign it to the respective property of the Client object (with one line of code for each column).

- Add the Name property of the Client object as the key and the Client object

itself as its value to the sorted list declared at the beginning of the function.

- Finally, return the sorted list declared at the beginning of the function.

c. GetTasks: This function returns a list of Task type objects. In this function:

- Declare a new list of Task type objects.

- Use the Fill function of the data adapter to create a table named “Task” in MyDataSet.

- Create a for-loop to go through the rows of this table. In this loop:

- Create a new Task type object.

- Then, for each column of the “Client” table, get the value and assign it to the respective property of the Task object (with one line of code for each column).

- Add the Task object to the list declared at the beginning of the function.

- Finally, return the sorted list declared at the beginning of the function.

e. GetAssignment: This function takes in two arguments, the first argument being a sorted list of string type keys and Staff type objects as values, and the second argument being a sorted list of string type keys and Client type objects as values. This function returns a list of Assignment type objects. In this function:

- Declare a new list of Assignment type of objects.

- Create a for-loop to go through the rows of this table. In this loop:

- Create a new Assignment type object.

- Get the value from “StaffID” column of this table and assign it to an integer

Continued...