.NET Framework Type Conversion and Casting

Question

There was such a problem. I'm making a form in Windows Forms to work with tables in a database. There is a dict<string, Object> dictionary containing pairs of column name - data type in the column. There is an array string[] my_values, where the values ​​that I want to add to the table are passed from the TextBox, all with the string type, respectively.

Problem: I need to add values ​​with already cast type to mysql query. For such values, I created a List, but I don’t understand how to bring these values ​​and add them to this List. All the ways I've tried have failed, do you have any ideas?

        string str = textBox1.Text;
        my_values = str.Split(new char[] {','});
        string addQuery = $"insert into " + name_of_table + " (";

        for (int i = 0; i < dict.Count - 1; i++)
        {
            if (dict.ElementAt(i).Key == "To")
            {
                addQuery = addQuery + "[" + dict.ElementAt(i).Key + "]" + ", ";
            }
            else
            {
                addQuery = addQuery + dict.ElementAt(i).Key + ", ";
            }
        }
        addQuery = addQuery + dict.ElementAt(dict.Count-1).Key + ") values (";
        for (int i = 0; i < my_values.Length - 1; i++)
        {
            addQuery = addQuery + $"'{my_values[i]}', ";
        }
        addQuery = addQuery + $"'{my_values[my_values.Length - 1]}'" + ")";

Answer 1

I suspect you asking how to determine if you require the string delimiters or other when dynamically constructing your insert statement please also read the inline comments.

The same could be achieved with a switch statement with pattern matching (not the if..else.. ) but I am showing you here the principle

        string str = textBox1.Text;
        my_values = str.Split(new char[] { ',' });
        string addQuery = $"insert into " + name_of_table + " (";

        for (int i = 0; i < dict.Count - 1; i++) {

            if (dict.ElementAt(i).Key == "To") {
                // id recommend always adding the [ ] brackets, does no harm and you may encounter yet another reserved word as a column name
                addQuery = addQuery + "[" + dict.ElementAt(i).Key + "]" + ", ";
            }
            else {
                addQuery = addQuery + dict.ElementAt(i).Key + ", ";
            }
        }
        addQuery = addQuery + dict.ElementAt(dict.Count - 1).Key + ") values (";


        for (int i = 0; i < my_values.Length - 1; i++) {

            // factor this out into a seperate method so that you can reuse it
            //  public static string FormatValue(object input) { ... }
            string formatted = string.Empty;
            if (my_values[i] is string s) {
                formatted = $"'{s}'";
            }
            else if (my_values[i] is int i) {
                formatted = $"{i}";
            }

            addQuery = addQuery + $"{formatted}, ";
        }

        // here then call the FormatValue method too 
        addQuery = addQuery + $"'{my_values[my_values.Length - 1]}'" + ")";