How to use Column Names from list into MySql insert

Question

I have 2 lists:

List<string> ColumnNames = new List<string>();
List<string> ValueNames = new List<string>();
ColumnNames = names.Keys.ToList();
ValueNames = names.Values.ToList();

I'm trying to use this lists into MySql statement:

...
MySqlCommand command = conDataBase.CreateCommand();
command.CommandText = string.Format("insert into MyTable {0} values (?parameter)", string.Join(",", ColumnNames));
command.Parameters.AddWithValue("parameter", string.Join(",", ValueNames));
...

Here I take error in syntax

...
command.CommandText = "insert into MyTable (" + string.Join(",", ColumnNames) + ") values ('" + string.Join(",", ValueNames) + "')";
...

and

...
command.CommandText = "insert into MyTable (" + string.Join(",", ColumnNames) + ") values (?parameter)";
command.Parameters.AddWithValue("parameter", string.Join(",", ValueNames));
...

In last 2 Codes I'm taking error "Column count doesn't match value count at row 1, but

ColumnNames.Count = ValueNames.Count

Possible duplicate of [Executing query with parameters](https://stackoverflow.com/questions/11905185/executing-query-with-parameters) — Patrick Artner, Jan 15 '18 at 12:58
You need to add all parameter names and each parameter-mapping on its own. See the answer https://stackoverflow.com/a/11905249/7505395 on how it should look. Variablenames in your command-text should be prefixed by `@` - you use the same name when adding the parameter with the value to the query. — Patrick Artner, Jan 15 '18 at 13:00
I tryed this too and I have same error: Column count doesn't match value count at row 1 — David Mamulashvili, Jan 15 '18 at 13:04
I think there is a problem in variable ColumnNames, not in Values, and the list.count will be different in different gridviews, so the sample you give me not answering the problem (( — David Mamulashvili, Jan 15 '18 at 13:05

score 0 · Answer 1 · answered Jan 15 '18 at 13:47

This is, what you could "script" using the answer provided in the other question:

string tableName = "Person";

// the names of the columns in your table    
var columnNames = new List<string> { "Name", "Age", "BirthDate" };

// the data you want to store in each column
var data = new List<string> { "Klaus", "22", "Before 2010" };

var myColumns = string.Join (", ", columnNames.Select (n => string.Format("[{0}]",n)));
var myParamColumns = string.Join (", ", columnNames.Select (n => string.Format("@{0}",n)));

using (var dbCommand = new SqlCommand() /*conDataBase.CreateCommand ()*/)
{ 
    dbCommand.CommandText = string.Format ("insert into {0}  ( {1} ) values ( {2} );", tableName, myColumns, myParamColumns);

    // your insert now looks like this:
    // insert into Person  ( [Name], [Age], [BirthDate] ) values ( @Name, @Age, @BirthDate );

    Console.WriteLine (dbCommand.CommandText);

    // then you add each single piece of data to each used @....    

    for (int i = 0; i < columnNames.Count; i++)
      dbCommand.Parameters.AddWithValue (columnNames[i], data[i]);

    dbCommand.ExecuteNonQuery ();
}

Thank you, I think it's better to use: List RevValueNames = new List(); myParamColumns.ForEach(x => RevValueNames.Add("'" + x + "'")); — David Mamulashvili, Jan 15 '18 at 13:53

score -1 · Accepted Answer · answered Jan 15 '18 at 13:48

-1

I found solution: https://www.codeproject.com/Questions/1225444/How-to-use-column-names-from-list-into-mysql-inser

List<string> ColumnNames = names.Keys.ToList()
List<string> ValueNames = names.Values.ToList();
List<string> RevValueNames = new List<string>();
ValueNames.ForEach(x => RevValueNames.Add("'" + x + "'"));
string Query = string.Format("insert into MyTable ({0}) values ({1})", string.Join(",", ColumnNames), string.Join(",", RevValueNames));
                command.CommandText = Query;

answered Jan 15 '18 at 13:48

David Mamulashvili

54
6

You should _never_ use user-input strings and string-concatenate them into your sql. Google Sql injection. Or think about what happens if I input this into one of your Textboxes: `'); TRUNCATE MyTable; DROP TABLE MyTable;` Using `SqlParameter` prevents this. – Patrick Artner Jan 15 '18 at 14:07
but I have no choice, I have many tables and many gridviews and I wont to write 1 module fro all of them, and column names and quantity are different – David Mamulashvili Jan 15 '18 at 15:20

How to use Column Names from list into MySql insert

2 Answers2