Assignment 3

In this assignment we are going to create tables in a database and perform some queries. We are not going to handle all kinds of SQL statements.

We are using a database system called MariaDB, a clone of MySQL.

The tables we will use are described below.

The input files we will use are described below. You can find links to them on the web site.

What to do

Write code to do the following in a Form application:

Your form should include:

• a TextBox for the user to type an SQL statement
• a Label to tell the user what the TextBox is for
• a Button for "Execute this command"
• a Listbox for printing the results of queries
• a Button for clearing the ListBox and TextBox
• a Button for closing the form
• a Label at the top saying something like "Department Data".

You might need more Labels as well.

In the form Load event handler, do the following:

• Use a MySqlConnection object to start a session with the database.

• Open the connection.

• Use a MySqlCommand ("Create") to create the RoomTable.

• Use a MySqlCommand ("Create") to create the OfficeTable.

• Use a MySqlCommand ("Create") to create the ClassTable.

• Open the file Room.txt. Read each line in it. For each line, use a MySqlCommand ("Insert") to create a row in RoomTable. Close the file.

• Open the file Office.txt. Read each line in it. For each line, use a MySqlCommand ("Insert") to create a row in OfficeTable. Close the file.

• Open the file Class.txt. Read each line in it. For each line, use a SMyqlCommand ("Insert") to create a row in ClassTable. Close the file.

• Now use the GetSchema method (GetSchema("Tables")) to obtain information about the tables in your database. You should then print the names of the tables in the ListBox.

• Close the connection.

When a user types in a SQL command and clicks the Execute button, the string in the TextBox needs to be executed. In the button handler:

• Open the connection.

• If the first part of the SQL command is "Select Count", the result of the query will be just one integer. Use the ExecuteScalar method to execute the SQL command. Print the result in the ListBox.

• If the first part of the SQL command is "Select", you should use a MySqlDataReader and the ExecuteReader method to obtain the results of the query, format each row of the results as a string and print it in the ListBox.

• If the the first part of the SQL command is "Insert", you should use the ExecuteNonQuery method to execute the SQL command and print a message saying you did so in the ListBox.

• If the first part of the SQL command is something else, print a message in the ListBox saying other SQL commands are not yet implemented.

• Close the connection.

In the event that the user-supplied SQL command is not valid, we will have an error. You need to handle this.

If the user clicks the Clear button, delete the contents of the TextBox and the ListBox.

If the user clicks the Exit button, end the form application.

Other notes

As MariaDb and MySql are not Microsoft products, support for them are not built into C#. To use it, we need to download and install the appropriate files into the project (separately for different projects). To do this:

• In Visual Studio, having opened your project, go to the Tools Menu.
• Select "NuGet Package Manager".
• Select "Package Manager Console".
• In a window at the bottom of the main Visual Studio window, you will have a command line with a prompt: "PM>". You need to type several lines:

       PM> install-package MySql
       PM> install-package MySql.Data
       PM> install-package MySql.ConnectorNet.Data

  Past experience indicates we may not need all three of these. If one of them seems to fail, it may be that we didn't need it.

Also, to use the MySql library, we will need some using statements:

     using System.Data;
     using System.Data.SqlClient;
     using MySql.Data;
     using MySql.Data.MySqlClient;

To connect to your database, you need values for three keywords in the connection string: uid, pwd and database.

If you are in group 1, your string should be:

     "server=10.158.56.53;uid=csci473g01;pwd=wordpass01;database=csci473g01;"

For the other groups, the same pattern applies. For group M, use the string:

     "server=10.158.56.53;uid=csci473gM;pwd=wordpassM;database=csci473gM;"

replacing M by its value as 2 digits. Notice the user ID is the same as the database name.

You can find the group number to use on Blackboard as a column in the Gradebook.

Whenever you are going to open a connection, do so with a "using" block to ensure that the connection will be closed at the end of the block.

Pick a foreground color and a background color for your form, and also choose at least one font and font size. (There is no obligation to use the default.)

To make the output easier to read, skip a line in the ListBox between queries.

Some of the steps described above should be done using the ExecuteNonQuery method of the MySqlCommand class.

Although the GetSchema method can do other things as well, all we want is the list of names of tables.

Description of the input files

We have three input files.

Office.txt contains the information for OfficeTable. Each line in the file corresponds to one row in the table. The fields are separated by commas.

Room.txt contains the information for RoomTable. Each line in the file corresponds to one row in the table. The fields are separated by commas.

Class.txt contains the information for ClassTable. Each line in the file corresponds to one row in the table. The fields are separated by commas.

When you read the file, use ReadLine to read each line of data as a string. Now use the Split method of the String class to divide the original string into substrings. (Look for the MSDN page on the Split method and look at the example.) In some cases you will need to convert a string to an integer; you can use one of the static methods of the Convert class. You will then need to concatenate strings to create the SQL string for the Insert command.

As with Assignments 1 and 2, you should copy these files into the bin/debug subdirectory of whatever directory contains your Assignment 3 project.

Description of the tables

We have 3 tables.

OfficeTable has 2 columns:

• the name of a teacher (character data, length no more than 20) (primary key)
• the number of an office (integer)

RoomTable has 3 columns:

• the number of a room (integer) (primary key)
• the seating capacity of the room (integer)
• a flag to say it is a smart classroom ('Y') or not ('N') (1 character)

ClassTable has 6 columns:

• the number of a class (string) (primary key)
• the name of a teacher (character data, length no more than 20) (foreign key)
• the number of a room (integer) (foreign key)
• the time when the class begins (character data, length 5)
• which days the class meets (character data, length no more than 5)
• the current enrollment (integer)

Use the following names for columns in your tables: Teacher, Office, Room, Capacity, Smart, Class, Time, Days, Enrollment.

While the user might type various queries, here are a few to use in testing. You will need to rewrite them in SQL.

Who is teaching course 311-1?

Which classrooms are smart classrooms?

How many classrooms are smart?

Which classes meet on TTh?

What is Green's office number?