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

Question

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();
            }
        }

can you please add the schema of your tables (countries, region)? I believe you could join them as right now you might be fetching unordered data from both tables. — Yousha Arif
– Yousha Arif, Commented Jan 10, 2022 at 17:29
Not like that! Concatenating values directly into your query leaves you vulnerable to SQL Injection. Use parameters instead. — Richard Deeming
– Richard Deeming, Commented Jan 10, 2022 at 17:30
If this is going to be a multiuser environment rethink. What is going to happen when multiple users execute that code in parallel? — Ralf
– Ralf, Commented Jan 10, 2022 at 17:33
@Richard yes it's an odd mix - can't work out why the initial query is parameterized and the subsequent aren't.. tho I think this particular example would struggle to be an attack risk.. (Not that it's an excuse) — Caius Jard
– Caius Jard, Commented Jan 10, 2022 at 20:19

Charlieface · Accepted Answer · 2022-01-11 13:06:18Z

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.

edited Jan 11, 2022 at 13:06

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

answered Jan 10, 2022 at 17:34

Richard Deeming

31.5k11 gold badges86 silver badges167 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Charlieface Over a year ago

You should get used to using verbatim strings it means you can put a newline directly in your string without escaping, and no need for + either. I can see you used it on the first string, but you didn't actually put a newline in

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related