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