Coding Platform: ASP.NET 2.0 WebForms with C# with MySQL as backend
Background
I am currently working on a bug fixing a website.
One of the table "registrations" have 80 columns.
The Insert/ Update is done through simple sql statements without any parameterized queries.
Problem
At registration, the user can vary many parameters resulting in atleast 15 kinds of INSERT query. My problem is how to ensure all fields are inserted with their correct value.
So, I created a
Dictionary<string, string> fields = new Dictionary<string, string>();
fields.Add("LoginEmail", MySQL.SingleQuoteSQL(txtLoginEmail.Text));
fields.Add("Password", MySQL.SingleQuoteSQL(txtLoginPassword.Text));
fields.Add("ContactName", MySQL.SingleQuoteSQL(txtContactName.Text));
fields.Add("City", MySQL.SingleQuoteSQL(txtCity.Text));
My idea was to make a simple insert query like this
INSERT INTO registrations("all keys as comma separated string") VALUES ("all keys as comma separated string")
My questions are
And also, what are the other better approaches?
P.S: I am maintaining the code and Making an Entity Class mapping the columns to properties and storing the values is not an option in this.
string list<T>(IEnumerable<T> enumerable)
{
List<T> list = new List<T>(enumerable);
return string.Join(",", list.ToArray());
}
//...
string sql= String.Format("INSERT INTO registrations({0}) VALUES({1})",
list(fields.Keys),
list(fields.Values));