1

I have a C# winforms application with SQLite as database which have a few tables.

The application has a datagridview control that represents data from database.

I want a user to be able to change database table fields, which is easy by using SQL `ALTER query.

The problem comes when the user wants to insert some data to the database table because I need to know the table's field name in order to construct a valid INSERT query.

I usually just hard-coded field names, because I never had an option that allows user to change the table's field name in a database.

My idea is that my INSERT query uses datagridview's column names, so, when a user changes the table's field name, it also changes the grid's field names, and every time the application runs, grid's columns are named to the names same as database table has.

That way I always have an access to the right field name.

Or can I just use an index instead of string to reference a field?

Usually I use this code to insert data.

class SQLiteDatabase
{
    //...
    public bool Insert(String tableName, Dictionary<String, String> data)
        {
            String columns = "";
            String values = "";
            Boolean returnCode = true;
            foreach (KeyValuePair<String, String> val in data)
            {
                columns += String.Format(" {0},", val.Key.ToString());
                values += String.Format(" '{0}',", val.Value);
            }
            columns = columns.Substring(0, columns.Length - 1);
            values = values.Substring(0, values.Length - 1);
            try
            {
                this.ExecuteNonQuery(String.Format("insert into {0}({1}) values({2});", tableName, columns, values));
            }
            catch(Exception fail)
            {
                MessageBox.Show(fail.Message);
                returnCode = false;
            }
            return returnCode;
        }
     //...
}



 db = new SQLiteDatabase();
    Dictionary<String, String> myData = new Dictionary<String, String>();
    myData.Add("NAME", "something");
    myData.Add("LAST_NAME", "something");
    try
    {
        db.Insert("TABLE_NAME", myData);
    }
    catch(Exception ex)
    {
        MessageBox.Show(ex.Message);
    }

Edit: I just read that in SQLite it is not possible to rename a column, remove a column, or add or remove constraints from a table.... WTF? This is the solution to rename columns...

How do I rename a column in a SQLite database table?

Community
  • 1
  • 1
Boy
  • 1,182
  • 2
  • 11
  • 28
  • This sounds like you could model it in a way that wouldn't require `ALTER`s (which sounds like a nightmare and potentially a vulnerability). Can't you just do this with foreign keys and join tables? – Joe Sep 07 '13 at 15:27
  • How exactly should I achieve that? – Boy Sep 07 '13 at 15:59
  • I don't want to be rude, but read a book on SQL. You might model this as a table called `fieldName`, a table called `entity` (whatever that is), a table called `entityField` which has the structure (`FK to entity, FK to fieldName, field value`). You can then do a `join` on these tables to retrieve a set of 'fields' and values for a given entity. – Joe Sep 07 '13 at 16:09
  • @Joe, I don't get it... a FK to entity? Is there a way to reference a whole table instead of some row? – Boy Sep 08 '13 at 00:57
  • The idea would be to change your representation of various types 'entities' from a number of tables to a single table with a type field, `FK`ed to a `type` table. And instead of having a number of columns, have a `columns` table (or as I called it `field`). This means you are representing your schema as data rather than in SQL. – Joe Sep 08 '13 at 09:07

0 Answers0