コード例 #1
0
        /// <summary>
        /// Add a column to the table named sourceTable at position columnNumber using the provided columnDefinition
        /// </summary>
        public void AddColumnToTable(string tableName, int columnNumber, ColumnDefinition columnDefinition)
        {
            using (SQLiteConnection connection = new SQLiteConnection(this.connectionString))
            {
                connection.Open();

                List<string> existingColumnNames = this.GetColumnNamesAsList(connection, tableName);
                if (existingColumnNames.Contains(columnDefinition.Name))
                {
                    throw new ArgumentException(String.Format("Column '{0}' is already present in table '{1}'.", columnDefinition.Name, tableName), "columnDefinition");
                }

                // if columnNumber would result in the column being inserted at the end of the table, then use the more efficient method to do so.
                if (columnNumber >= existingColumnNames.Count)
                {
                    this.AddColumnToEndOfTable(tableName, columnDefinition);
                    return;
                }

                // addding a column in a specific location requires
                // - creating a new schema
                // - creating a new table from that schema
                // - copying data to the new table
                // - removing the old table
                // - renaming the new table to the name of the old one

                // clone current schema and insert new column
                string newSchema = this.InsertColumnInSchema(connection, tableName, columnNumber, columnDefinition);

                // copy the existing table to a new table with the new schema
                string destTable = tableName + "NEW";
                string sql = "CREATE TABLE " + destTable + " (" + newSchema + ")";
                using (SQLiteCommand command = new SQLiteCommand(sql, connection))
                {
                    command.ExecuteNonQuery();
                }
                this.CopyAllValuesFromTable(connection, tableName, tableName, destTable);

                // remove the existing table and rename the new table
                this.DropTable(connection, tableName);
                this.RenameTable(connection, destTable, tableName);
            }
        }
コード例 #2
0
 /// <summary>
 /// Add a column definition into the provided schema at the given column location
 /// </summary>
 private string InsertColumnInSchema(SQLiteConnection connection, string tableName, int newColumnNumber, ColumnDefinition newColumn)
 {
     List<string> columnDefinitions = this.GetColumnDefinitions(connection, tableName);
     columnDefinitions.Insert(newColumnNumber, newColumn.ToString());
     return String.Join(", ", columnDefinitions);
 }
コード例 #3
0
 // This method will create a column in a table of type TEXT, where it is added to its end
 // It assumes that the value, if not empty, should be treated as the default value for that column
 public void AddColumnToEndOfTable(string tableName, ColumnDefinition columnDefinition)
 {
     this.ExecuteNonQuery("ALTER TABLE " + tableName + " ADD COLUMN " + columnDefinition.ToString());
 }