C# - Add variables to specific points in a string

Question

I'm trying to set up a MySQL connection on a C# program I'm working on. I'm to the point where I'm building the queries. My basic premise is that you have a function in a class that you'd call that accepts the name of a table and a hashtable with column names and their respective values (for the insert command).

Ex:

Hashtable hash = new Hashtable();
hash.Add("title", title);
hash.Add("contents", content);

db.Insert(stories, hash);

So, my question is, how could I iterate over the hashtable received by the Insert method, each time adding the key and the value in specific, changing positions.

A possible query would be "Insert into TABLE (key1, key2) VALUES ('value1', 'value2')"

My dilemma is trying to get the keys and values to match up in the string.

Use parameters. For things that aren't SQL, use a templating engine. — SLaks, Jul 30 '13 at 23:17
You do **NOT** want to do this. Use parameterized queries instead of building them from text — Kevin, Jul 30 '13 at 23:18
Check out this SO answer to get you going. http://stackoverflow.com/a/652999/2270839 — Kevin, Jul 30 '13 at 23:21

score 0 · Answer 1 · answered Jul 31 '13 at 04:03

You can use List to store the column names and values from the Hashtable then join them into the command text. The parameters for the command is added as you iterate through the Hashtable.

private void Insert(string tableName, Hashtable hash)
{
    MySqlCommand command = new MySqlCommand();

    List<string> columnList = new List<string>();
    List<string> valueList = new List<string>();

    foreach (DictionaryEntry entry in hash)
    {
        columnList.Add(entry.Key.ToString());
        valueList.Add("@" + entry.Key.ToString());

        command.Parameters.AddWithValue("@" + entry.Key.ToString(), entry.Value);
    }

    command.CommandText = "INSERT INTO " + tableName + "(" + string.Join(", ", columnList.ToArray()) + ") ";
    command.CommandText += "VALUES (" + string.Join(", ", valueList.ToArray()) + ")";

    command.ExecuteScalar();

}

C# - Add variables to specific points in a string

1 Answers1