CODEWITHSUNDEEP
c#sqlite
Nathan
Nathan

Reputation: 5243

How do I dynamically add columns to tables?

I want to import an Excel file with no column constraints (each file could have a different number of columns) into an SQLite database table. How do I dynamically create the tables in the database?

Upvotes: 0

Views: 17529

Answers (3)

Weol
Weol

Reputation: 1

public void AddNewColumn(string columnName)
    {
        string query = $"ALTER TABLE YourTable ADD COLUMN {columnName} TEXT";
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            SQLiteCommand command = new SQLiteCommand(query, connection);
            connection.Open();
            command.ExecuteNonQuery();
        }
    }

Upvotes: 0

Amirshk
Amirshk

Reputation: 8258

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);
}

Upvotes: 7

Reed Copsey
Reed Copsey

Reputation: 564651

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

Upvotes: 2

Related Questions