First Visual Studio 2013 C# Workbench Exercise


In this assignment you will create an employee database in Visual Studio 2013 with C#. When you are through with this project you will have specified the following items:
  1. Database named WPC.
  2. Table named EMPLOYEE.
  3. Form1.cs [Design] containing (a) a form for entering an employee and (b) a ListView table of employees.
  4. Employee.cs class file containing the object specification for holding an employee record.
  5. WPCDB.cs class file containing methods for accessing the database: GetConnection(), AddEmployee(), GetEmployee().
  6. Form1.cs containing the Form1 event handlers: Form1_Load() and btnSubmit_Click().

Complete the following steps:

Click for a playlist of six videos showing you how to complete this project.

  1. Start Visual Studio 2013
  2. Create Project - From the File menu, create a New Project that uses Visual C# and is a Windows Form Application. Name the project WPC. Click for a video(1) of creating the project, database and employee table.
  3. Create Database - In the Solution Explorer on the right, right click on the WPC project and Add - New Item - Service Based Database - named WPC.
  4. Create Employee Table - In the Server Explorer on the left, open the WPC database and open the Tables folder. Right click and create a new table with the fields described below. Note that the video shows the FirstName, LastName, Department, Phone and Email fields defined as nchar( ) instead of varchar( ). Using varchar( ) will save having to convert them from nchar( ) to varchar( ) in the next exercise.

    1. In the lower panel, replace [Table] name: CREATE TABLE [dbo].[EMPLOYEE]
    2. EmployeeNumber - int - identity(1,1)
    3. FirstName - varchar(25), not null
    4. LastName - varchar(25), not null
    5. Department - varchar(35), not null
    6. Phone - varchar(12), null OK
    7. Email - varchar(100), not null
  5. Click the Update button at the top of the table and when the Preview Database Updates window appears, click the Update Database button.
  6. Add Employee Records - In the Server Explorer, refresh the Tables, right click EMPLOYEE and Show Table Data. Add the first record from the EMPLOYEE DATA worksheet in this spreadsheet manually. IMPORTANT: Do not enter a value for EmployeeNumber. It will be auto-filled once you finish entering the record.
  7. Create Add Employee Form - Click to open the Form1.cs [Design] file and using the Toolox, Add TextBoxes and a Submit button for entering the FirstName, LastName, Department, Phone and Email. Add an appropriate Label before each TextBox. Click for a video(2) of creating the add employee form.
  8. Name the TextBox for FirstName: txtFirstName, etc. Name the Button: btnSubmit.
  9. Create Employees Display - Also on Form1.cs [Design], add a ListView control for displaying Employee records. Click Smart Tag menu on the upper right corner of the control and do the following: Click for a video(3) of adding a ListView to the form.
    1. Set View to Details.
    2. Click Edit Columns. On the left side (Members) click Add 6 times to add 6 columns.
    3. Change the columnHeader1 Text property to "ID" and Width to 30. You can leave (Name) as columnHeader1.
    4. Change the columnHeader2 Text property to "First Name" and Width to 100. You can leave (Name) as columnHeader2.
    5. Change the columnHeader3 Text property to "Last Name" and Width to 100. You can leave (Name) as columnHeader3.
    6. Change the columnHeader4 Text property to "Department" and Width to 120. You can leave (Name) as columnHeader4.
    7. Change the columnHeader5 Text property to "Phone" and Width to 80. You can leave (Name) as columnHeader5.
    8. Change the columnHeader6 Text property to "Email" and Width to 140. You can leave (Name) as columnHeader6.
  10. Create Employee Object - In the Solution Explorer on the right, right click on the WPC project and Add - New Class - named Employee, and do the following: Click for a video(4) showing creating the Employee.cs class file.
    1. Make the class public.
    2. Add variables:
      private int employeeNumber;
      private string firstName;
      private string lastName;
      private string department;
      private string phone;
      private string email;
    3. Add a line:
      public Employee() { }
    4. Add:
      public int EmployeeNumber
      {
        get { return employeeNumber; }
        set { employeeNumber = value; }
      }
    5. Add:
      public string FirstName
      {
        get { return firstName; }
        set { firstName = value; }
      }
    6. Add similar public string methods for LastName, Department, Phone and Email.
  11. Create Database Access Methods - In the Solution Explorer on the right, right click on the WPC project and Add - New Class - named WPCDB, and do the following: Click for a video(5) showing creating the WPBDB.cs database class file.
    1. Add using System.Data.SqlClient; at the top.
    2. Make the class public static.
    3. Add GetConnection() method:
      public static SqlConnection GetConnection()
      {
         string connStr = @"<connection_string>";
         SqlConnection conn = new SqlConnection(connStr);
         return conn;
      }
      Where <connection_string> is a WPC database property.
    4. Add AddEmployee() method:
      public static void AddEmployee(string firstName, ...)
      {
         string insStmt = "INSERT INTO EMPLOYEE (FirstName, ...)" +
           " VALUES (@firsName, ...)";
         SqlConnection conn = GetConnection();
         SqlCommand insCmd = new SqlCommand(insStmt, conn);
         insCmd.Parameters.AddWithValue("@firstName", firstName);
         ...
         try { conn.Open(); insCmd.ExecuteNonQuery(); }
         catch (SqlException ex) { throw ex; }
         finally { conn.Close(); }
      }
    5. Add GetEmployee() method:
      public static List<Employee> GetEmployee()
      {
         List<Employee> employeeList = new List<Employee>();
         SqlConnection conn = GetConnection();
         string selStmt = "SELECT * FROM EMPLOYEE ORDER BY LastName,FirstName";
         SqlCommand selCmd = new SqlCommand(selStmt, conn);
         try
         {
           conn.Open();
           SqlDataReader reader = selCmd.ExecuteReader();
           while (reader.Read())
           {
             Employee employee = new Employee();
             employee.EmployeeNumber = (int)reader["EmployeeNumber"];
             employee.FirstName = reader["FirstName"].ToString();
             ...
             employeeList.Add(employee);
           }
           reader.Close();
         }
         catch (SqlException ex) { throw ex; }
         finally { conn.Close(); }
         return employeeList;
      }
  12. Specify Event Handlers - Go to the Form1.cs [Design] form and double-click on the form background(and not on a control). The Form1.cs code page should open and the cursor should be in the Form1_Load() method. Add the following code: Click for a video(6) showing how to finish the event handlers.
       listView1.Items.Clear();
       List<Employee> employeeList;
       try
       {
         employeeList = WPCDB.GetEmployee();
         if (employeeList.Count > 0)
         {
           Employee employee;
           for (int i = 0; i < employeeList.Count; i++)
           {
             employee = employeeList[i];
             listView1.Items.Add(employee.EmployeeNumber.ToString());
             listView1.Items[i].SubItems.Add(employee.FirstName);
             ...
           }
         }
         else
         {
           MessageBox.Show("There are no employees.", "Alert");
         }
       }
       catch (Exception ex)
       {
         MessageBox.Show(ex.Message, ex.GetType().ToString());
       }
  13. Return to the Form1.cs [Design] form and double-click on the Submit button for entering a new employee. The Form1.cs code page should open again and the cursor should be in the btnSubmit_Click() method. Add the following code:
       WPCDB.AddEmployee(txtFirstName.Text,...);
       txtFirstName.Text = "";
       ...
       MessageBox.Show("Employee Added", "Alert");
       this.Form1_Load(this,null);
  14. Click on the Start button and test your program. Enter the rest of the employees using your form.
  15. When you are through, save and close the project. Zip the project folder and upload it to your passworded assignments directory.

VS2013_Ch_1.php