10 Database access and DepartmentDAO

1. If needed, retrieve the OOP Company project up to chapter 9 from DLO. It contains the classes as you left them at the end of chapter 9.

2. A file CreateInsertScriptCompany.sql is available on DLO. Download it and run it in MySQL Workbench. This creates a database with departments, persons and employees. A user with a password is also created.

3. Add the MySQL connector to your project (e.g. MySql.Data via NuGet):
   a) In Visual Studio: right-click the project → Manage NuGet Packages → search for MySql.Data.
   b) Install the package.

4. Create a new Database folder in your project.

5. A generic DatabaseAccess class is available on DLO. Download it and place it in the folder you just created. It will then be visible in your project.

6. Create a new DepartmentDAO class with the method SaveDepartment(Department department), which saves a (new) department to the database.

7. Test your code by creating a department named HR in Hilversum in the database from your launcher.
   a) Do not forget to set up database access first with the correct database, user and password. These can be found in the SQL script.
   b) Do not forget to create an instance of the DepartmentDAO class.

8. Create a new method GetDepartmentsByLocation(string location) that returns a list of departments for the given location.

9. Test your code by displaying all departments that are in Hilversum. Your output should look like:

   department HR at Hilversum
   department Operations at Hilversum
   

10. Finally, add a new employee to the database. This employee is named Lodewijk, lives in Zaandam, works in the Support department in Amsterdam and earns € 2500 per month.
    a) Because of subtyping you must update two tables (Person and Employee). You therefore need two DAOs.
    b) The primary key for the Person table is auto increment. You need the generated key to update the Employee table.