private string BuildCreateSQL() { Dictionary <string, ColumnLayout> Columns = new Dictionary <string, ColumnLayout>(); foreach (DataGridViewRow dr in dgvTableDef.Rows) { // Assign to local variables for readability // ColName and ColType have already been tested to insure values are not null ColumnRow cr = MoveGridRow(dr); if (string.IsNullOrEmpty(cr.ColName)) { break; } ColumnLayout column = new ColumnLayout(); column.Check = cr.ColCheck; column.Collation = cr.ColCollation; column.ColumnType = cr.ColType; column.DefaultValue = cr.ColDefault; column.ForeignKey = new ForeignKeyLayout(); column.ForeignKey.Table = cr.FK_Table; column.ForeignKey.To = cr.FK_Column; column.ForeignKey.OnUpdate = cr.FK_OnUpdate; column.ForeignKey.OnDelete = cr.FK_OnDelete; column.NullType = cr.ColAllowNulls ? 0 : 1; column.PrimaryKey = cr.ColPrimaryKey; column.Unique = cr.ColUnique; Columns.Add(cr.ColName, column); } return(SqlFactory.CreateSQL(txtTableName.Text, Columns)); }
protected string BuildCreateSql(string TableName, Dictionary <string, DBColumn> columns) { // Remap foreign column layout to internal SQLite Column Layout Dictionary <string, ColumnLayout> SQColumns = new Dictionary <string, ColumnLayout>(); foreach (var col in columns) { if (col.Value.IncludeInImport) { ColumnLayout SQCol = new ColumnLayout(); SQCol.Check = string.Empty; SQCol.Collation = string.Empty; SQCol.ColumnType = col.Value.Type; SQCol.DefaultValue = col.Value.HasDefault ? col.Value.DefaultValue : string.Empty; // Foreign key will not be used during Import SQCol.ForeignKey = new ForeignKeyLayout(); SQCol.NullType = col.Value.IsNullable ? 0 : 1; SQCol.PrimaryKey = col.Value.PrimaryKey; SQCol.Unique = col.Value.IsUnique; SQColumns.Add(col.Value.Name, SQCol); } } return(SqlFactory.CreateSQL(TableName, SQColumns)); }
protected bool RebuildTable() { bool bNoWarning = false; bool.TryParse(MainForm.cfg.appsetting(Config.CFG_COLUMNEDITWARN), out bNoWarning); if (!bNoWarning) { if (!ShowWarning()) { return(false); } } string sql; SQLiteErrorCode returnCode; bool foreign_key_enabled; string CurrentDB = MainForm.mInstance.CurrentDB; sql = "Pragma foreign_keys"; var data = DataAccess.ExecuteScalar(CurrentDB, sql, out returnCode); foreign_key_enabled = (long)data == 0 ? false : true; sql = string.Format("Select * From sqlite_master Where type = \"index\" AND tbl_name = \"{0}\"", tablename); DataTable idxDT = DataAccess.ExecuteDataTable(CurrentDB, sql, out returnCode); sql = string.Format("Select * From sqlite_master Where type = \"trigger\" AND tbl_name = \"{0}\"", tablename); DataTable trigDT = DataAccess.ExecuteDataTable(CurrentDB, sql, out returnCode); SQLiteConnection conn = null; SQLiteCommand cmd = null; Dictionary <string, ColumnLayout> columns = DataAccess.SchemaDefinitions[CurrentDB].Tables[tablename].Columns; Dictionary <string, ColumnLayout> newcolumns = new Dictionary <string, ColumnLayout>(); switch (ExecType) { case SQLType.SQLRenameColumn: foreach (var col in columns) { newcolumns.Add(col.Key == txtColumn.Text ? txtNewColumn.Text : col.Key, col.Value); } break; case SQLType.SQLModifyColumn: foreach (var col in columns) { newcolumns.Add(col.Key, col.Key == txtColumn.Text ? BuildColumnLayout() : col.Value); } break; case SQLType.SQLDeleteColumn: columns.Remove(txtColumn.Text); newcolumns = columns; break; default: return(false); } string tmptablename = Common.TempTableName(); if (string.IsNullOrEmpty(tmptablename)) { Common.ShowMsg("Cannot build temporary table - terminating."); return(false); } string CreateSQL = SqlFactory.CreateSQL(tmptablename, newcolumns); string SelectSQL = SqlFactory.SelectSql(tablename, columns); if (foreign_key_enabled) { DataAccess.ExecuteNonQuery(CurrentDB, "Pragma foreign_keys=false", out returnCode); } bool rCode = DataAccess.OpenDB(CurrentDB, ref conn, ref cmd, out returnCode, false); if (!rCode || returnCode != SQLiteErrorCode.Ok) { Common.ShowMsg(String.Format(Common.ERR_SQL, DataAccess.LastError, returnCode)); return(false); } SQLiteTransaction sqlT; sqlT = conn.BeginTransaction(); try { //Create the temp table cmd.CommandText = CreateSQL; var createRtnCode = DataAccess.ExecuteNonQuery(cmd, out returnCode); if (createRtnCode != 0) { throw new Exception(String.Format("Cannot create Temp Table.\r\n{0}", DataAccess.LastError)); } //Copy data from the current table to the temp table string insertSQL = string.Format("Insert Into {0} {1}", tmptablename, SelectSQL); cmd.CommandText = insertSQL; var insertRtnCode = DataAccess.ExecuteNonQuery(cmd, out returnCode); if (insertRtnCode != 0) { throw new Exception(String.Format("Cannot Copy Rows into Temp Table.\r\n{0}", DataAccess.LastError)); } //delete the current table cmd.CommandText = SqlFactory.DropSql(tablename); var deleteRtnCode = DataAccess.ExecuteNonQuery(cmd, out returnCode); if (deleteRtnCode != 0) { throw new Exception(String.Format("Cannot Delete Original Table.\r\n{0}", DataAccess.LastError)); } //rename the temp table to the old table name cmd.CommandText = string.Format("Alter Table {0} Rename To {1}", tmptablename, tablename); var renameRtnCode = DataAccess.ExecuteNonQuery(cmd, out returnCode); if (renameRtnCode != 0) { throw new Exception(String.Format("Cannot Rename Temp Table.\r\n{0}", DataAccess.LastError)); } //Rebuild Indexes foreach (DataRow dr in idxDT.Rows) { cmd.CommandText = dr["sql"].ToString(); var indexRtnCode = DataAccess.ExecuteNonQuery(cmd, out returnCode); if (indexRtnCode != 0) { throw new Exception(String.Format("Cannot Rebuild Indexes.\r\n{0}", DataAccess.LastError)); } } //Rebuild Triggers foreach (DataRow dr in trigDT.Rows) { cmd.CommandText = dr["sql"].ToString(); var triggerRtnCode = DataAccess.ExecuteNonQuery(cmd, out returnCode); if (triggerRtnCode != 0) { throw new Exception(String.Format("Cannot Create Triggers.\r\n{0}", DataAccess.LastError)); } } sqlT.Commit(); } catch (Exception ex) { sqlT.Rollback(); Common.ShowMsg(String.Format(Common.ERR_SQL, ex.Message, returnCode)); return(false); } finally { DataAccess.CloseDB(conn); if (foreign_key_enabled) { DataAccess.ExecuteNonQuery(CurrentDB, "Pragma foreign_keys=true", out returnCode); } } MainForm.mInstance.AddTable(tablename); return(true); }