Add all columns from database automatic to a INSERT INTO query

Question

I have a WPF window, where I can add a Customer to the database. Labels and Textboxes are programmaticaly added by the amount of Columns in the database table.

Now if I put some text in the textboxes, i want to save all these texts in the Database.

My Code until now is:

try {
    for (int i = 0; i < textboxes.Count; i++)
    {
        var value = textboxes[i];
        var column = labels[i];
        MySqlCommand cmd = connection.CreateCommand();
        cmd.CommandText = "INSERT INTO firmenkunden ("+column.Name+") VALUES ('"+value.Text+"')";
        cmd.ExecuteNonQuery();
    }
    MessageBox.Show("Der Firmenkunde wurde erfolgreich erstellt.");
    this.Close();
}
catch (MySqlException ex) {
    MessageBox.Show(ex.Message);
}

But this don't work. It looks like this now:

Does someone has a idea how I can do this?

it **works**, but not in the way you expect. in a loop you make *many* `insert` queries with one column, eg. `INSERT INTO firmenkunden (City) VALUES ('M')`, `INSERT INTO firmenkunden (Fax) VALUES (123)`, instead of *one* query with many columns, like `INSERT INTO firmenkunden (City, Fax) VALUES ('M', 123)`. also: inserting `value.Text` into a query string you make it open for sql-injections — ASh, Feb 27 '17 at 13:50
I am new to C# so i have no much knowledge. Any idea how I can do it like u said? I guess I need something like a array. All Columns should be in `INSERT INTO firmenkunden (:::)`. and all Text from textbox should be in `VALUES (::::)`. U know what I mean? — O Jean, Feb 27 '17 at 13:53
Possible duplicate of [C# with MySQL INSERT parameters](http://stackoverflow.com/questions/16167924/c-sharp-with-mysql-insert-parameters) — ASh, Feb 27 '17 at 13:59
Seeing MySqlCommand and MessageBox.Show in the same code block makes my eyes hurt. https://msdn.microsoft.com/en-us/library/ee658109.aspx?f=255&MSPPError=-2147217396 http://stackoverflow.com/questions/13786549/how-to-implement-3-tiers-architecture-in-c-sharp — granadaCoder, Feb 27 '17 at 14:02
@granadaCoder Mvvm is a bit hard for me to understand (every tutorial is different and makes my brain crashin ^^). — O Jean, Feb 27 '17 at 14:13
@OJean I would ignore those articles for now, just try to improve and learn every day. Eventually MVVM and other architectures will make sense. Incremental improvement is the goal. — Derrick Moeller, Feb 27 '17 at 14:17
Thing is that im at my education. (4months school 4 months work for a firm). Now im in firm and i have just ~1month left and my boss got no results (i was sick and i tried to learn etc.) so i want to work now and dont learn. The last 3 months was timewaste. — O Jean, Feb 27 '17 at 14:32

score 3 · Answer 1 · edited May 23 '17 at 12:25

The problem is how you are create your query to execute on the database. See, for each column you are executing an Insert, but you need send all the columns to the command, so try do it:

   var columns = new System.Text.StringBuilder();
   var values = new System.Text.StringBuilder();
   MySqlCommand cmd = connection.CreateCommand();
   for (int i = 0; i < textboxes.Count; i++)
   {
       var value = textboxes[i];
       var column = labels[i];
       cmd.Parameters.AddWithValue("@" + column.Name, value.Text);
       string complement = (i == 0 ? string.Empty : ",");
       columns.Append(complement + column.Name);
       values.Append(complement + "@" + column.Name);
   }
   cmd.CommandText = "INSERT INTO firmenkunden (" + columns.ToString() + ") VALUES (" + values.ToString() + ")";

Another thing you have to do is prefer uses MySqlParameters instead concat values on the queries. Here have an explanation why, and here how you can use that.

I hope it can help you.

score 0 · Answer 2 · answered Feb 27 '17 at 13:53

You are generating one query per textbox. You need to add all columns and values to one query.

Something like this should work (untested), but depending on the datatype some fields might need escaping and/or different handling of quotes.

 List<String> columns = new List<String>();
 List<String> values = new List<String>();

 for (int i = 0; i < textboxes.Count; i++)
 {
    var value = textboxes[i];
    var column = labels[i];

    columns.add(column);
    values.add(value);
 }

 MySqlCommand cmd = connection.CreateCommand();
 cmd.CommandText = "INSERT INTO firmenkunden (`"+String.Join("`,`", columns)+"`) VALUES ('"+String.Join("'", values)+"')";
 cmd.ExecuteNonQuery();

Derrick Moeller · Accepted Answer · 2017-02-27T14:11:41.440

The main issue you have is that you are performing multiple inserts, you could solve this by using your loop to build a single INSERT command.

string queryText = "INSERT INTO firmenkunden (";

for (int i = 0; i < textboxes.Count; i++)
{
    var column = labels[i];
    if (i < textboxes.Count - 1)
        queryText += column.Name + ",";
    else
        queryText += column.Name;
}

queryText += ") VALUES (";

for (int i = 0; i < textboxes.Count; i++)
{
    var value = textboxes[i];
    if (i < textboxes.Count - 1)
        queryText += "'" + value.Text + "',";
    else
        queryText += "'" + value.Text + "')";
}

MySqlCommand cmd = connection.CreateCommand();
cmd.CommandText = queryText;
cmd.ExecuteNonQuery();

There are also many other ways to build a string. I would recommend spending some time reading up on string concatenation, I would use StringBuilder over what I have provided. String also provides a number of static methods that could be useful.

All of that said there are better ways to do this, your implementation and the implementation above are not a best practice. You may want to investigate Linq to SQL, or even better Entity Framework. There are other options as well but they aren't as accessible. If you wish to continue building your commands, you will want to at the very least look into parameterized SQL.

Add all columns from database automatic to a INSERT INTO query

3 Answers3