Skip to content

10.7 Auto-increment values

  1. Add the following code to the SaveCircle method in CircleDao: 

     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
    public void SaveCircle(Circle circle)
{
    string sql = "INSERT INTO Shape(color) VALUES (@color);";
    int primaryKey = 0;
    try
    {
        var cmd = new MySqlCommand(sql, _dbAccess.GetConnection());
        cmd.Parameters.AddWithValue("@color", circle.Color);
        cmd.ExecuteNonQuery();

        cmd.CommandText = "SELECT LAST_INSERT_ID();";
        primaryKey = Convert.ToInt32(cmd.ExecuteScalar());
        Console.WriteLine($"The added key is {primaryKey}");
    }
    catch (MySqlException ex)
    {
        Console.WriteLine(ex);
        return;
    }

    sql = "INSERT INTO Circle VALUES (@id, @radius, @x, @y);";
    try
    {
        var cmd = new MySqlCommand(sql, _dbAccess.GetConnection());
        cmd.Parameters.AddWithValue("@id", primaryKey);
        cmd.Parameters.AddWithValue("@radius", circle.Radius);
        cmd.Parameters.AddWithValue("@x", circle.Center.X);
        cmd.Parameters.AddWithValue("@y", circle.Center.Y);
        cmd.ExecuteNonQuery();
    }
    catch (MySqlException ex)
    {
        Console.WriteLine(ex);
    }

    The color of a circle is stored in the Shape table. A primary key is generated (auto-increment). This code inserts a shape and retrieves the generated primary key.

    • First, a row is inserted into Shape. LAST_INSERT_ID() returns the auto-increment value.
    • Then a row is inserted into Circle with that primary key and the circle data.

  2. Change the code in your Main method: 

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

CircleDao circleDao = new CircleDao(dbAccess);
circleDao.SaveCircle(new Circle(5, new Point(3, 7), "orange"));

dbAccess.CloseConnection();

  3. Run your program and verify in Workbench that a shape with color orange and a circle with the corresponding shapenumber were added.