Skip to content

10.4 DbAccess class

  1. Create a new folder Data (or Database) in your project.

  2. Create a class DbAccess: 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
    namespace Geometry.Data
{
    public class DbAccess
    {
        private readonly string _connectionString;
        private MySqlConnection? _connection;

        public DbAccess(string connectionString)
        {
            _connectionString = connectionString;
        }

        public void OpenConnection()
        {
            _connection = new MySqlConnection(_connectionString);
            _connection.Open();
        }

        public MySqlConnection GetConnection()
        {
            if (_connection == null)
                throw new InvalidOperationException("Connection not opened.");
            return _connection;
        }

        public void CloseConnection()
        {
            _connection?.Close();
        }
    }
}

    All code related to creating a connection to a database is placed in a separate class. We can then create objects that handle the connection. The code in Main is greatly simplified. The code in the DbAccess class becomes reusable. The class diagram of the DbAccess class is as follows:

    Mermaid diagram Mermaid diagram

    • The attribute _connection holds the active connection to the DBMS.
    • The constructor needs the connection string, for example:
      Server=localhost;Database=Shapes;User=userShapes;Password=userShapesPW;
    • In that connection string: Server is the MySQL host, Database is the schema to use,
      User is the login name, and Password is the login password.
    • The three methods OpenConnection(), GetConnection(), and CloseConnection() are now placed in the DbAccess class.

  3. Remove the constant you added above the Main method.

  4. Change the code in your Main method to use DbAccess: 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
    DbAccess dbAccess = new DbAccess("Server=localhost;Database=Shapes;User=userShapes;Password=userShapesPW;");
dbAccess.OpenConnection();
var connection = dbAccess.GetConnection();

string sql = "SELECT * FROM Point;";
try
{
    MySqlCommand command = new MySqlCommand(sql, connection);
    using MySqlDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        double x = reader.GetDouble("xcoordinate");
        double y = reader.GetDouble("ycoordinate");
        Console.WriteLine($"({x:F2}, {y:F2})");
    }
}
catch (MySqlException ex)
{
    Console.WriteLine(ex);
}
dbAccess.CloseConnection();
    • You create a new instance of the DbAccess class with the correct parameters to access the database.
    • The dbAccess object is then used to open, provide, and close the connection.

  5. Run your program. The output is the same as in 10.3. step 1.