How to pass several variables in MySqlCommand statement and insert into database table in C#?

What I want to do is get the PKs of two tables and pass them to the corresponding variables and then add them to the data insertion in the FKs columns. When I execute the following code I don't get the desired result what am I doing wrong?

public void Create_data()
        {            
            string max_id = "";
            string max_region_id = "";
            conn.Open();
            string query1 = "SELECT MAX(id) FROM countries";
            MySqlCommand cmd1 = new MySqlCommand(query1, conn);
            max_id = Convert.ToString(cmd1.ExecuteScalar());

            string query2 = "SELECT MAX(id) FROM regions";
            MySqlCommand cmd2 = new MySqlCommand(query2, conn);
            max_region_id = Convert.ToString(cmd2.ExecuteScalar());


            using (MySqlCommand cmd = new MySqlCommand())
            {
                cmd.CommandText = "INSERT INTO countries(country, code) VALUES (@country, @code);" + "INSERT INTO regions(region, country_id) VALUES (@region," + max_id+1 + ");" + "INSERT INTO ports(port,region_id) VALUES (@port," + max_region_id+1 + ");";
                cmd.CommandType = CommandType.Text;
                cmd.Connection = conn;

                cmd.Parameters.Add("@country", MySqlDbType.VarChar).Value = country;
                cmd.Parameters.Add("@code", MySqlDbType.VarChar).Value = code;
                cmd.Parameters.Add("@region", MySqlDbType.VarChar).Value = region;
                cmd.Parameters.Add("@port", MySqlDbType.VarChar).Value = port;

                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }

Solution 1:

Your approach will break as soon as you have multiple users inserting data at the same time.

Use the LAST_INSERT_ID function instead:

using (MySqlCommand cmd = new MySqlCommand())
{
    cmd.CommandText = @"INSERT INTO countries(country, code) VALUES (@country, @code);" 
    + "INSERT INTO regions(region, country_id) VALUES (@region, LAST_INSERT_ID());" 
    + "INSERT INTO ports(port,region_id) VALUES (@port, LAST_INSERT_ID());";
    
    cmd.CommandType = CommandType.Text;
    cmd.Connection = conn;

    cmd.Parameters.Add("@country", MySqlDbType.VarChar).Value = country;
    cmd.Parameters.Add("@code", MySqlDbType.VarChar).Value = code;
    cmd.Parameters.Add("@region", MySqlDbType.VarChar).Value = region;
    cmd.Parameters.Add("@port", MySqlDbType.VarChar).Value = port;

    cmd.ExecuteNonQuery();
    conn.Close();
}

NB: You seem to be storing a single connection object in a field of your class. That never ends well. Instead, you should be creating the connection at the point where it's needed, and wrapping it in a using block to ensure it's disposed of as soon as you've finished with it.