protected void AddColumn()
{
string sql = SqlFactory.BuildAddColumnSQL(tablename, txtColumn.Text, BuildColumnLayout());
var result = DataAccess.ExecuteNonQuery(MainForm.mInstance.CurrentDB, sql, out SQLiteErrorCode returnCode);
if (result == -1 || returnCode != SQLiteErrorCode.Ok)
{
Common.ShowMsg(string.Format("Add Column Failed.\r\n{0}", DataAccess.LastError));
return;
}
toolStripStatusLabel1.Text = "Column Added.";
MainForm.mInstance.AddTable(tablename);
}
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);
}
protected string BuildEditTriggerSql(TreeNode tblNode)
{
return(SqlFactory.EditTriggerSql(sd, tblNode.Text));
}
protected string BuildCreateTriggerSqlTemplate()
{
return(SqlFactory.TriggerTemplate());
}
protected string BuildDeleteSqlTemplate(TreeNode tblNode)
{
return(SqlFactory.DeleteTemplate(tblNode.Text));
}
protected string BuildCreateViewSqlTemplate()
{
return(SqlFactory.ViewTemplate());
}
protected string BuildUpdateSqlTemplate(TreeNode tblNode)
{
return(SqlFactory.UpdateTemplate(sd, tblNode.Text));
}
protected string BuildInsertSqlTemplate(TreeNode tblNode)
{
return(SqlFactory.InsertTemplate(sd, tblNode.Text));
}
protected string BuildSelectViewSql(TreeNode tblNode, int count = 0)
{
return(SqlFactory.SelectViewSql(sd, tblNode.Text, count));
}
protected string BuildIntegrityCheckSql()
{
return(SqlFactory.IntegrityCheckSql());
}
protected string BuildDropSql(TreeNode tblNode)
{
return(SqlFactory.DropSql(tblNode.Text));
}
