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); }
protected string BuildSelectSql(TreeNode tblNode, int count = 0) { return(SqlFactory.SelectSql(sd, tblNode.Text, count)); }