Skip to content

10.5 PointDao class

  1. Create a class PointDao in the Data folder: 

     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
32
33
34
    namespace Geometry.Data
{
    public class PointDao
    {
        private readonly DbAccess _dbAccess;

        public PointDao(DbAccess dbAccess)
        {
            _dbAccess = dbAccess;
        }

        public void SavePoint(Point point)
        {
            string sql = "INSERT INTO Point VALUES (@x, @y)";
            var cmd = new MySqlCommand(sql, _dbAccess.GetConnection());
            cmd.Parameters.AddWithValue("@x", point.X);
            cmd.Parameters.AddWithValue("@y", point.Y);
            cmd.ExecuteNonQuery();
        }

        public List<Point> GetPoints()
        {
            List<Point> points = new List<Point>();
            string sql = "SELECT * FROM Point;";
            var cmd = new MySqlCommand(sql, _dbAccess.GetConnection());
            using var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                points.Add(new Point(reader.GetDouble("xcoordinate"), reader.GetDouble("ycoordinate")));
            }
            return points;
        }
    }
}
    • The method can save any point.
    • In the SQL query, parameters are used (@x, @y) instead of question marks. This prevents SQL injection.
    • The PointDao has an attribute of type DbAccess so it can get the connection.

  2. Change the code in your Main method: 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
    DbAccess dbAccess = new DbAccess("Server=localhost;Database=Shapes;User=userShapes;Password=userShapesPW;");
dbAccess.OpenConnection();

PointDao pointDao = new PointDao(dbAccess);
pointDao.SavePoint(new Point(-4, -2));

foreach (Point point in pointDao.GetPoints())
{
    Console.WriteLine(point);
}

dbAccess.CloseConnection();

    In Main, a DbAccess object is created and a connection is opened.

    • Next, a PointDao object is created, and the DbAccess object is passed to its constructor.
    • The PointDao object can now store a point via the SavePoint() method.
    • The PointDao object also has the GetPoints() method, which returns all stored points from the table as a List<Point>.

  3. Run your program. You have added an extra point to the database.