Looping to Insert Records in MySQL

Question

This is a single question in two parts. My C# project does some structural calculations and then writes a BOM to a MySQL database. The two problems I need to resolve is first, how to assemble the data into an appropriate array so I can loop through it. And then how do I loop through it to write it to the MySQL database.

The fields of data I want to assemble into the array will be fairly uniform, in that each record will contain six fields. But of course each time the data will be different. Here is what I have so far:

MySqlCommand cmd1 = new MySqlCommand("INSERT INTO BOM_Details (BOM_No, Part_No, Descr, Qty, Length, Width) VALUES (" + result + ", vPArtNo, '" + vTemp1 + "', 2," + Program.vdSpanLength.SpanLength(Program.vdWidth) + ", 0);", DBFactory.conn);
cmd1.ExecuteNonQuery();

MySqlCommand cmd2 = new MySqlCommand("INSERT INTO BOM_Details (BOM_No, Part_No, Descr, Qty, Length, Width) VALUES (" + result + ", vPArtNo, '" + vTemp1 + "', 2," + Program.vdSpanLength.SpanLength(Program.vdWidth) + ", 0);", DBFactory.conn);
cmd2.ExecuteNonQuery();

and so forth, and so forth. Some of the fields will be static, others will be filled with data from a variable. I guess my confusion is also around the MySQLCommand objects I'm naming cmd1, cmd2, etc... I would think that I could use the same object name but when I try to reassign it I get an error saying it's already in use.

Anyway, back to my two steps. I'd like to assemble the data into an array first. Fields 1 thru Fields 6 and sometimes there will be 20 lines in the array, sometimes 30 or more. The number of records for each pass through the program will always vary.  I think I can figure this out but I'm still new to building arrays. Any advice would be appreciated. Then once I have the array filled I want to perform a simple loop to populate the database and then close it and exit the program. I'll be hacking at this today but any advice would be appreciated.

A little further reading tells me that what I probably need is a multidimensional array. And then it begs the question can an array contain strings and numbers or will they all have to be of one type. If so I guess I could Convert.Double, etc... the strings which I need to be numbers.

Answer 1

The values are a CSV string so you can use something like the code below.

            object[] objects = {1,"a",2,"b",3};

            string csv = string.Join(",",objects.Select(x => x.ToString()).ToArray());

---

jdweng

Answer 2

Another variation

            string[] headers = { "BOM_No", "Part_No", "Descr", "Qty", "Length", "Width" };
            string names = string.Join(",", headers);
            object[] objects = { 1, "a", 2, "b", 3 };
            string values = string.Join(",",objects.Select(x => x.ToString()).ToArray());

            string SQL = string.Format("INSERT INTO BOM_Details ({0}) VALUES ({1});", names, values);

Answer 3

The first comment that needs to be made about your code snippet: This is the text-book example of how to make your code vulnerable to Sql Injection Attacks. Never, ever, concatenate string values from external source to create a sql command.

The good news is that the answer to your question also conveniently addresses the code injection issue above. You should make use of parameters in your Sql command, something like this:

MySqlCommand cmd1 = new MySqlCommand("INSERT INTO BOM_Details (BOM_No, Part_No, Descr, Qty, Length, Width) VALUES (@result, vPArtNo, @vTemp1, 2, @length, 0);", DBFactory.conn);

cmd1.Parameters.Add("@result", MySqlDbType.Int32, 0, someValue);
cmd1.Parameters.Add("@VTemp1", MySqlDbType.Int32, 0, someOtherValue);
cmd1.ExecuteNonQuery();

Note that here the Sql comment is static text string, and containers placeholders or parameters for each of the variable parts of your insert, each start with an '@'. The following code then properly defines these, and provides a value. No sql injection worries any more.

Also note that you can now re-use your command as many times as you need. You can change the values of the parameters between each call to ExecuteNonQuery;

cmd1.Parameters["@result"].Value = someNewValue;

So, you can read through your CSV file in a loop, and just set the parameters to the correct values for each row. Only one MySqlCommand is need (but will be executed many time with different data).

HTH,

Nick.

Answer 4

Joel!

Never build Transact-SQL statements directly from user input.

Answer 5

I agree.  But you can allow user to select fields from a control like check boxes where the user can choose one or more predefined fields.

---

jdweng

Answer 6

Thanks everyone. Those are some great tips. And we want to be as security conscious as possible with this project. The user input will initially come from an internal form also written in .NET so I'll be able to tightly control what gets sent to the database. But eventually it will become part of a web based form which will run from an Apache+php app so users can access it from their browser or even their cell phones.

I'm studying these replies in depth and will go through more coding today. I am going to be looping through a multidimensional array theArray[,], not a text file.

Answer 7

I've gotten a little further with this but I'm still having trouble. First, I am attempting to do this with an ArrayList now in lieu of a multidimensional array. As each step through the calculations is completed it add more stuff to the array like this:

vaMySqlArrayList.Add(Convert.ToString(BOM_No));
vaMySqlArrayList.Add("20140009");
vaMySqlArrayList.Add("PART #12");
vaMySqlArrayList.Add("1");
vaMySqlArrayList.Add("0");
vaMySqlArrayList.Add("0");

vaMySqlArrayList.Add(Convert.ToString(BOM_No));
vaMySqlArrayList.Add("20140011");
vaMySqlArrayList.Add("PART #32");
vaMySqlArrayList.Add("1");
vaMySqlArrayList.Add("0");
vaMySqlArrayList.Add("0");

And so forth. I end up with about 25 of these blocks for this particular assembly. Notice that each block contains six (6) lines, the number of columns in the mySQL table I'm writing to. I attempt to write to the database using a loop like this:

MySqlCommand vcSqlcmd = new MySqlCommand("INSERT INTO BOM_Details (BOM_No, Baan_No, Descr, Qty, Length, Width) VALUES (@BOM_No, @BAAN_No, @Description, @Qty, @Length, @Width);", DBFactory.conn);

for (int i = 0; i <= vaMySqlArrayList.Count / 6; i +=6)
{
   vcSqlcmd.Parameters.AddWithValue("@BOM_No",vaMySqlArrayList[i]);
   vcSqlcmd.Parameters.AddWithValue("@BAAN_No", vcMySqlArrayList[i + 1]);
   vcSqlcmd.Parameters.AddWithValue("@Description", vaMySqlArrayList[i + 2]);
   vcSqlcmd.Parameters.AddWithValue("@Qty", vaMySqlArrayList[i + 3]);
   vcSqlcmd.Parameters.AddWithValue("@Length", vaMySqlArrayList[i + 4]);
   vcSqlcmd.Parameters.AddWithValue("@Width", vaMySqlArrayList[i + 5]);
   vcSqlcmd.ExecuteNonQuery();
}
DBFactory.CloseDB();

This works,  but it only inserts one (1) line into the table then throws an exception:

Additional information: Parameter '@BOM_No' has already been defined.

Answer 8

Okay, a quick trip to lunch to clear the mind and I think I have it working. Well I know it's working but I'd like to get any suggestions on how it might be improved or strengthened on the security end. This will initially run from an internal Windows Application Form but one day soon it will be moved to the company's website.

MySqlCommand SQLCommand = new MySqlCommand("INSERT INTO BOM_Details (BOM_No, Part_No, Descr, Qty, Length, Width) VALUES (@BOM_No, @BAAN_No, @Description, @Qty, @Length, @Width);", DBFactory.conn);
           
            for (int i = 0; i < vaMySqlArrayList.Count; i +=6)
            {
                SQLCommand.Parameters.AddWithValue("@BOM_No", vaMySqlArrayList[i]);
                SQLCommand.Parameters.AddWithValue("@Part_No", vaMySqlArrayList[i + 1]);
                SQLCommand.Parameters.AddWithValue("@Description", vaMySqlArrayList[i + 2]);
                SQLCommand.Parameters.AddWithValue("@Qty", vaMySqlArrayList[i + 3]);
                SQLCommand.Parameters.AddWithValue("@Length", vaMySqlArrayList[i + 4]);
                SQLCommand.Parameters.AddWithValue("@Width", vaMySqlArrayList[i + 5]);
                SQLCommand.ExecuteNonQuery();
                SQLCommand.Parameters.Clear();
            }
            DBFactory.CloseDB();

I ended up not needing to divide the vaMySqlArrayList.Count by six, although I didn't quite follow that one just yet. Some more studying is in order. And by adding the Parameter.Clear method it took care of the reassignment of the variable each time it went through the loop.

Answer 9

My preference is to use the code below where you can specify each field type in the database to the code doesn't have to guess the type of each field. 

            SqlCommand vcSqlcmd = new MySqlCommand("INSERT INTO BOM_Details (BOM_No, Baan_No, Descr," +
               "Qty, Length, Width) VALUES (@BOM_No, @BAAN_No, @Description, @Qty, @Length, @Width);",DBFactory.conn);

                vcSqlcmd.Parameters.Add("@BOM_No", SqlDbType.Int);
                vcSqlcmd.Parameters.Add("@Part_No", SqlDbType.Int);
                vcSqlcmd.Parameters.Add("@Description", SqlDbType.VarChar);
                vcSqlcmd.Parameters.Add("@Qty", SqlDbType.Int);
                vcSqlcmd.Parameters.Add("@Length", SqlDbType.Int);
                vcSqlcmd.Parameters.Add("@Width", SqlDbType.Int);
           
            for (int i = 0; i < vaMySqlArrayList.Count; i +=6)
            {
                vcSqlcmd.Parameters["@BOM_No"].Value =  vaMySqlArrayList[i];
                vcSqlcmd.Parameters["@Part_No"].Value = vaMySqlArrayList[i + 1];
                vcSqlcmd.Parameters["@Description"].Value = vaMySqlArrayList[i + 2];
                vcSqlcmd.Parameters["@Qty"].Value = vaMySqlArrayList[i + 3];
                vcSqlcmd.Parameters["@Length"].Value = vaMySqlArrayList[i + 4];
                vcSqlcmd.Parameters["@Width"].Value = vaMySqlArrayList[i + 5];
                vcSqlcmd.ExecuteNonQuery();
                vcSqlcmd.Parameters.Clear();
            }
            DBFactory.CloseDB();

---

jdweng

Answer 10

Okay, will give that a shot. I think I read that the Add method is deprecated...maybe not in this case. But it said to use AddWithValue ???

Answer 11

Add isn't depreciated.  AddWithValue often gives errors.  AddWithValues is almost as bad as using General Fromating in an excel spreadsheet which turns numbers to dates and dates to numbers.

---

jdweng

Answer 12

Many thanks again. This is where I'm at with the suggestions from above:

 MySqlCommand SQLCommand = new MySqlCommand("INSERT INTO BOM_Details (BOM_No, Part_No, Descr, Qty, Length, Width) VALUES (@BOM_No, @Part_No, @Description, @Qty, @Length, @Width);", DBFactory.conn);

            SQLCommand.Parameters.Add("@BOM_No", MySqlDbType.VarChar);
            SQLCommand.Parameters.Add("@Part_No", MySqlDbType.Int32);
            SQLCommand.Parameters.Add("@Description", MySqlDbType.VarChar);
            SQLCommand.Parameters.Add("@Qty", MySqlDbType.Int32);
            SQLCommand.Parameters.Add("@Length", MySqlDbType.Double);
            SQLCommand.Parameters.Add("@Width", MySqlDbType.Double);

            for (int i = 0; i < vaMySqlArrayList.Count; i +=6)
            {
                SQLCommand.Parameters["@BOM_No"].Value = vaMySqlArrayList[i];
                SQLCommand.Parameters["@Part_No"].Value= vaMySqlArrayList[i + 1];
                SQLCommand.Parameters["@Description"].Value = vaMySqlArrayList[i + 2];
                SQLCommand.Parameters["@Qty"].Value = vaMySqlArrayList[i + 3];
                SQLCommand.Parameters["@Length"].Value = vaMySqlArrayList[i + 4];
                SQLCommand.Parameters["@Width"].Value = vaMySqlArrayList[i + 5];
                SQLCommand.ExecuteNonQuery();
                SQLCommand.Parameters.Clear();
            }
            DBFactory.CloseDB();

But when I run the debugger it fails on the first line in the loop:

Additional information: Parameter '@BOM_No' not found in the collection.

When I check there is at least one row added to the BOM_Details table and it contains the correct data. But apparently the second time through the loop it fails so I commented out the Parameter.Clear line and it works properly. That makes sense with your method although I'm still very new to most of this. Learning more all the time. Thanks.

Answer 13

Thanks for all the helpful replies. I got this working pretty good....but then they suddenly decided they want to write the same information to an Oracle database. So I'm hacking through that now. Looks like most of the same code will work, but I need information on how to use parameterized input with Oracle and that means my loop changes.

So the first thing I'm going to try is to just write to the Oracle table without parameters.

for (int i = 0; i < vaMySqlArrayList.Count; i += 6)
{
OracleCommand SQLCommand = new OracleCommand("INSERT INTO BAAN.tdfab100 (eitm, dsca, dscb, dscc, seak, seab, ctyp, engi,) VALUES ('1', 'TPS HATCH', 'ALUM','" + Program.vdSpan + "X" + Program.vdWidth + "','TPS" + Program.vdSpan + "X" + Program.vdWidth + "ACDN', 'SOMETHING FOR seab', 'HATCH', 'UsersName');", connection);
SQLCommand.ExecuteNonQuery();
}

But this obviously will not work. So how can I loop through this and reassign the variable SQLCommand each time?

Answer 14

What's the issue here? OracleCommand has exactly the same Parameters collection. Basically you just need to change the class of the sql command from "MySqlCommand" to "OracleSqlCommand", but the approach remains the same. I don't see why the loop would need to be changed.