How to insert list of data into mysql table

Question

I have a table which contains a single column "CODE_CAR". I want to populate this table with list of string as below :

_Connection.Open();
var lst_code_cars = new List<string> { "YH_21", "NM_00", "BAR_N178" };
string cmdText = "INSERT INTO Y157.CARS_IDENDITY(CODE_CAR) VALUES ("+lst_code_cars+")";
MySqlCommand cmd = new MySqlCommand(cmdText, _Connection);
foreach (string cars_code in lst_code_cars )
{
   cmd.Parameters.AddWithValue("@"+cars_code , cars_code );
   cmd.ExecuteNonQuery();
}
_Connection.Close();

When i try this code, i get error : MySqlException : Parameter '@NM_00' must be defined.

Side note, you can send these values all at one time to insert into your table (will require a few changes to the query), fewer trips to the DB is always better. Also when adding a parameter, please define the length and the actual type of the parameter, when not doing so, the DB has to infer the DB type and it may not be what *it should be*. — Trevor, Mar 23 '21 at 14:07
As well as the obvious SQL injection, given your connection object is called `_Connection`, I suspect you are caching it. **Don't cache connection objects**, create when you need them and dispose with `using`, see https://stackoverflow.com/questions/17552829/c-sharp-data-connections-best-practice — Charlieface, Mar 23 '21 at 14:19

J... · Accepted Answer · 2021-03-23T14:44:33.080

The error tells you exactly what is wrong. You did not define a parameter, you concatenated the value directly into the query string. So don't do that.

_Connection.Open();
var lst_code_cars = new List<string> { "YH_21", "NM_00", "BAR_N178" };
string cmdText = "INSERT INTO Y157.CARS_IDENDITY(CODE_CAR) VALUES (@CarCode)";
MySqlCommand cmd = new MySqlCommand(cmdText, _Connection);
foreach (string cars_code in lst_code_cars )
{
   cmd.Parameters.Clear();
   cmd.Parameters.AddWithValue("@CarCode" , cars_code );
   cmd.ExecuteNonQuery();
}
_Connection.Close();

You'll also need to clear the parameters if you mean to re-add them each loop. Either that or add the parameter once and then edit the value in the loop instead of adding a new parameter each time.

Ideally, though, you could write this as a batch insert.

How to insert list of data into mysql table

1 Answers1