Inserting values to a table using generic dictionary

naveen picture naveen · Apr 17, 2011 · Viewed 9.9k times · Source

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

  1. Is Dictionary the best data structure to implement this?
  2. Does the sorting of keys by generic Dictionary changes key-value indices at the query?
  3. Best method to fetch all keys into an array and the corresponding values to another matching array.

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.

Answer

Mark Cidade picture Mark Cidade · Apr 17, 2011
 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));