C# Dynamically Add Columns to table in Database

Question

Hello,

I am still learning quite a bit about programming so this may be an obvious question but I am in the process of writing a program where a user can import an excel file with no column constraints (meaning each excel file could have a different number of columns and so on). What I want to do is be able to convert the imported excel file into a database table using SQLite. As far as I have read the syntax for SQLite is the same as ADO syntax but I am unsure of how to dynamically create the tables in the database. I am looking for any form of suggestions, if someone felt like showing an example in code that would be cool.

Thanks! Nathan

Answer 1

You can just use ExecuteNonQuery to create your table, using the standard SQLite Query Language for CREATE TABLE.

Answer 2

Here is a piece of code for creating a table in sqlite:

SQLiteConnection mDBcon = new SQLiteConnection();
mDBcon.ConnectionString = "Data Source=" + DataSourcePath;
mDBcon.Open();
SQLiteCommand cmd = new SQLiteCommand(mDBcon);
cmd.CommandText = "CREATE TABLE IF NOT EXISTS tags (ISBN VARCHAR(15), Tag VARCHAR(15));";
cmd.ExecuteNonQuery();

note that in SQLite you can only add coloumn to existing tables, and only to the end of the table:

cmd.CommandText = "ALTER TABLE books ADD COLUMN PublishDate DateTime;";
cmd.ExecuteNonQuery();

Addition

Assuming you imported you data from the excel to a DataSet, you can now iterate through the DataSet's DataTables, create the corresponding tables and fill them: (disclaimer, haven't tested it)

foreach(DataTable table in dataSet.Tables)
{
 SQLiteCommand cmd = new SQLiteCommand(mDBcon);
 cmd.CommandText = "CREATE TABLE IF NOT EXISTS " + table.Name + "(";
 bool first = true;
 foreach (DataColumn column in table.Columns)
 {   
  cmd.CommandText += "@"+column.Name;
  if (!first) cmd.CommandText += ",";
  else first = false;
  cmd.Parameters.Add(new SQLiteParameter("@"+column.Name, column.Name));
 }
 cmd.CommandText += ");";
 cmd.ExecuteNonQuery();

 // Fill the new table:
 SQLiteDataAdapter da = new SQLiteDataAdapter("select * from " + table.Name, mDBcon);
 da.Fill(table);
}

Answer 3

So I have tried out what you suggested and I keep getting this error:

SQLite error near "@TAGID": syntax error

This is what the CommandText looks like : CREATE TABLE IF NOT EXISTS HLMS9126to9128(@TAGID);

This is how I changed the code:

public void CreateSQLiteTable(string tableName) { foreach (DataTable table in LocalDataSet.LocalDs.Tables) { SQLiteCommand cmd = new SQLiteCommand(SQLiteConn.Conn); cmd.CommandText = "CREATE TABLE IF NOT EXISTS " + tableName + "("; bool isfirst = true;

            foreach (DataColumn col in LocalDataSet.LocalDs.Tables[0].Columns)
            {
                cmd.CommandText += "@" + col.ColumnName;

                if (!isfirst) cmd.CommandText += ",";
                else isfirst = false;

                cmd.Parameters.Add(new SQLiteParameter("@" + col.ColumnName, DbType.String, col.ColumnName));
            }
            cmd.CommandText += ");";
            cmd.ExecuteNonQuery();
        }
    }

Any help would be great.