10.2 Setting up database and writing points

1. If needed, start from the project up to chapter 9. This contains the classes as you left them at the end of chapter 9.
2. The SQL script CreateInsertScriptShapes.sql is available in the Data folder. Run it in MySQL Workbench. You will have a database Shapes with tables Circle, Shape, Point, and Rectangle, plus a user and password.
3. Add the MySql.Data NuGet package to your project: dotnet add package MySql.Data

4. Add the following constant to your Program class (above the Main method):

   private const string ConnectionString =
   "Server=localhost;Database=Shapes;User=userShapes;Password=userShapesPW;";
   

   This connection string tells MySQL how to connect:
   - Server=localhost: connect to a MySQL server running on your own machine.
   - Database=Shapes: use the Shapes database that you created with the SQL script.
   - User=userShapes: log in with the application user userShapes.
   - Password=userShapesPW: use the password that was created for this user.
   Together these parts must match the server, database, user, and password you actually have in MySQL Workbench; if any part is different, the connection will fail.

5. Add the following code to your Main method:

   using MySql.Data.MySqlClient;
   
   MySqlConnection? connection = null;
   try
   {
       connection = new MySqlConnection(ConnectionString);
       connection.Open();
   }
   catch (MySqlException ex)
   {
       Console.WriteLine("SQL Exception: " + ex.Message);
   }
   
   if (connection != null)
   {
       Console.WriteLine("Connection established!");
       string sql = "INSERT INTO Point VALUES (3, 5);";
       try
       {
           MySqlCommand command = new MySqlCommand(sql, connection);
           command.ExecuteNonQuery();
           connection.Close();
       }
       catch (MySqlException ex)
       {
           Console.WriteLine(ex);
       }
   }
   

   • The query is in the string sql. In this case, a point with coordinates (3, 5) is added to the Point table.
   • The command is executed via the connection.
   • After execution, the connection can be closed.

6. Run your program and verify in Workbench that the point was created.