internal void cmdTableDrop(string arg)
{
if (!checkConnection()) return;
Table table = SelectedObject as Table;
SQLInputDialog sid = new SQLInputDialog(Provider.Database.GetSQLTableDrop(table, true), true, string.Format("Table \"{0}\" will be dropped and ALL DATA LOST.\n\nContinue?", table.Name));
if(sid.ShowDialog()==DialogResult.OK)
{
try
{
int i = Provider.Database.ExecuteNonQuery(sid.SQL);
Provider.Database.Tables.Remove(table);
findNode(table).Remove();
if (ObjectRemoved != null)
ObjectRemoved(this, new DbObjectRemovedArgs { Object = table });
if (CurrSQLEditor != null)
CurrSQLEditor.ShowInfoText("Table " + table.Name + " dropped succesfully.");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Cinar Database Tools", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
}
private void propertyGrid_PropertyValueChanged(object s, PropertyValueChangedEventArgs e)
{
bool cancel = true;
string sql = null, message = "";
Action afterExecute = null, afterError = null;
try
{
if (propertyGrid.SelectedObject is IMetadata)
{
if (propertyGrid.SelectedObject is Column)
{
Column column = propertyGrid.SelectedObject as Column;
switch (e.ChangedItem.Label)
{
case "Name":
sql = Provider.Database.GetSQLColumnRename((string)e.OldValue, column);
message = "Do you really want to rename this column?";
afterExecute = () => { findNode(column).Text = column.Name + " (" + column.ColumnType + ")"; };
break;
case "ColumnType":
case "Length":
string oldOriginalColumnType = column.ColumnTypeOriginal;
column.ColumnTypeOriginal = Provider.Database.DbTypeToString(column.ColumnType);
sql = Provider.Database.GetSQLColumnChangeDataType(column);
message = "Do you really want to change the type of this column?";
afterError = () => { column.ColumnTypeOriginal = oldOriginalColumnType; };
break;
case "IsNullable":
sql = column.IsNullable ?
Provider.Database.GetSQLColumnRemoveNotNull(column) : Provider.Database.GetSQLColumnAddNotNull(column);
message = "Do you really want to change nullability of this column?";
break;
case "DefaultValue":
sql = Provider.Database.GetSQLColumnChangeDefault(column);
message = "Do you really want to change default value of this column?";
break;
case "IsAutoIncrement":
sql = column.IsAutoIncrement ?
Provider.Database.GetSQLColumnSetAutoIncrement(column) : Provider.Database.GetSQLColumnRemoveAutoIncrement(column);
message = "Do you really want to change auto incrementing of this column?";
break;
}
}
if (propertyGrid.SelectedObject is Table)
{
Table table = propertyGrid.SelectedObject as Table;
switch (e.ChangedItem.Label)
{
case "Name":
sql = Provider.Database.GetSQLTableRename((string)e.OldValue, table.Name);
message = "Do you really want to rename this table?";
afterExecute = () => { findNode(table).Text = table.Name; };
break;
}
}
if (sql != null)
{
SQLInputDialog sid = new SQLInputDialog(sql, true, message);
if (sid.ShowDialog() == DialogResult.OK)
{
Provider.Database.ExecuteNonQuery(sid.SQL);
if (afterExecute != null) afterExecute();
if (ObjectChanged != null)
ObjectChanged(this, new DbObjectChangedArgs() { Object = propertyGrid.SelectedObject, NewValue = e.ChangedItem.Value, OldValue = e.OldValue, PropertyName = e.ChangedItem.Label });
cancel = false;
}
}
}
if (propertyGrid.SelectedObject is Item || propertyGrid.SelectedObject is Diagram)
{
cancel = false;
switch (e.ChangedItem.Label)
{
case "Name":
findNode(propertyGrid.SelectedObject).Text = e.ChangedItem.Value.ToString();
break;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Cinar Database Tools", MessageBoxButtons.OK, MessageBoxIcon.Error);
if (afterError != null) afterError();
cancel = true;
}
try
{
if (cancel)
e.ChangedItem.PropertyDescriptor.SetValue(propertyGrid.SelectedObject, e.OldValue);
}
catch { }
}
public bool CreateTable(Database.Database db, Table tbl)
{
string sql = tbl.ToDDL();
SQLInputDialog sid = new SQLInputDialog(sql, false);
if (sid.ShowDialog() == DialogResult.OK)
{
db.ExecuteNonQuery(sid.SQL);
try
{
populateTreeNodesFor(null, tbl);
if (ObjectAdded != null)
ObjectAdded(this, new DbObjectAddedArgs { Object = tbl });
}
catch { }
return true;
}
return false;
}
private void cmdEditIndex(string arg)
{
Table table = findSelectedTable();
if (table == null) return;
FormCreateIndex fct = new FormCreateIndex(table);
BaseIndexConstraint oldIndex = SelectedObject as BaseIndexConstraint;
fct.SetKey(oldIndex);
while (true)
{
if (fct.ShowDialog() == DialogResult.OK)
{
BaseIndexConstraint index = fct.GetCreatedKey();
try
{
if (index is Index)
table.Indices.Add((Index)index);
else
table.Constraints.Add((Constraint)index);
if (string.IsNullOrEmpty(index.Name))
throw new Exception("Enter a valid name.");
if (index.ColumnNames == null || index.ColumnNames.Count == 0)
throw new Exception("Select minimum one column.");
string sql = Provider.Database.GetSQLBaseIndexConstraintRemove(oldIndex) + ";" + Environment.NewLine;
sql += Provider.Database.GetSQLBaseIndexConstraintAdd(index);
SQLInputDialog sid = new SQLInputDialog(sql, false);
if (sid.ShowDialog() == DialogResult.OK)
{
Provider.Database.ExecuteNonQuery(sid.SQL);
if (oldIndex is Index)
table.Indices.Remove((Index)oldIndex);
else
table.Constraints.Remove((Constraint)oldIndex);
findNode(oldIndex).Remove();
if (ObjectRemoved != null)
ObjectRemoved(this, new DbObjectRemovedArgs { Object = oldIndex });
populateTreeNodesFor(null, index);
if (ObjectAdded != null)
ObjectAdded(this, new DbObjectAddedArgs { Object = index });
}
break;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Cinar Database Tools");
if (index is Index)
table.Indices.Remove((Index)index);
else
table.Constraints.Remove((Constraint)index);
fct.DialogResult = DialogResult.None;
}
}
else
break;
}
}
private void cmdDropIndex(string arg)
{
if (!checkConnection()) return;
Table table = findSelectedTable();
BaseIndexConstraint index = SelectedObject as BaseIndexConstraint;
try
{
string sql = Provider.Database.GetSQLBaseIndexConstraintRemove(index);
SQLInputDialog sid = new SQLInputDialog(sql, false, string.Format("Index \"{0}\" on \"{1}\" will be dropped.\n\nContinue?", index.Name, table.Name));
if (sid.ShowDialog() == DialogResult.OK)
{
Provider.Database.ExecuteNonQuery(sid.SQL);
if (index is Index)
table.Indices.Remove((Index)index);
else
table.Constraints.Remove((Constraint)index);
findNode(index).Remove();
if (ObjectRemoved != null)
ObjectRemoved(this, new DbObjectRemovedArgs { Object = index });
CurrSQLEditor.ShowInfoText(string.Format("Index {0} on {1} dropped successfully.", index.Name, table.Name));
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Cinar Database Tools");
if (index is Index)
table.Indices.Add((Index)index);
else
table.Constraints.Add((Constraint)index);
}
}
private void cmdDoDatabaseOperation(string arg)
{
switch (arg)
{
case "Drop":
if (MessageBox.Show("Database will be dropped and ALL DATA LOST.\n\nContinue?", "Cinar Database Tools", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == DialogResult.Yes)
{
Provider.Database.ExecuteNonQuery("drop database " + Provider.Database.Name);
TreeNode tn = findSelectedDBNode();
Provider.Connections.Remove(Provider.ActiveConnection);
tn.Remove();
if (ObjectRemoved != null)
ObjectRemoved(this, new DbObjectRemovedArgs { Object = tn.Tag });
CurrSQLEditor.ShowInfoText(string.Format("Database {0} dropped successfully.", tn.Text));
}
break;
case "Create":
cmdNewConnection("create");
break;
case "Truncate":
{
StringBuilder sb = new StringBuilder();
foreach (Table t in Provider.Database.Tables)
sb.AppendLine("truncate table [" + t.Name + "];");
SQLInputDialog sid = new SQLInputDialog(sb.ToString(), false);
if (sid.ShowDialog() == DialogResult.OK)
Provider.Database.ExecuteNonQuery(sid.SQL);
break;
}
case "Empty":
{
StringBuilder sb = new StringBuilder();
foreach (Table t in Provider.Database.Tables)
foreach (Constraint c in t.Constraints)
if (c is Cinar.Database.ForeignKeyConstraint)
sb.AppendLine(Provider.Database.GetSQLConstraintRemove(c) + ";");
foreach (Table t in Provider.Database.Tables)
sb.AppendLine(Provider.Database.GetSQLTableDrop(t, true) + ";");
SQLInputDialog sid = new SQLInputDialog(sb.ToString(), false);
if (sid.ShowDialog() == DialogResult.OK)
{
Provider.Database.ExecuteNonQuery(sid.SQL);
cmdRefreshMetadata("nowarn");
}
break;
}
case "Transfer":
cmdShowForm(typeof(FormDBTransfer).FullName);
break;
case "Backup":
cmdShowForm(typeof(FormSQLDump).FullName);
break;
}
}
private void cmdColumnDrop(string arg)
{
Column column = SelectedObject as Column;
SQLInputDialog sid = new SQLInputDialog(Provider.Database.GetSQLColumnRemove(column), true, string.Format("Column \"{0}.{1}\" will be dropped and ALL DATA LOST.\n\nContinue?", column.Table.Name, column.Name));
if (sid.ShowDialog() == DialogResult.OK)
{
try
{
int i = Provider.Database.ExecuteNonQuery(sid.SQL);
column.Table.Columns.Remove(column);
findNode(column).Remove();
if (ObjectRemoved != null)
ObjectRemoved(this, new DbObjectRemovedArgs { Object = column });
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Cinar Database Tools", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
}
private void cmdAlterTable(string arg)
{
FormCreateTable fct = new FormCreateTable();
Table tbl = SelectedObject as Table;
if (tbl == null) {
MessageBox.Show("Select table first", "Cinar Database Tools");
return;
}
fct.SetTable(tbl);
while (true)
{
if (fct.ShowDialog() == DialogResult.OK)
{
TableDef tblNew = fct.GetAlteredTable();
try
{
StringBuilder sb = new StringBuilder();
foreach (ColumnDef colNew in tblNew.Columns)
{
// field yeni eklenmişse
if (colNew.OriginalColumn == null)
{
Column f = new Column()
{
Name = colNew.Name,
ColumnTypeOriginal = colNew.ColumnType,
ColumnType = Provider.Database.StringToDbType(colNew.ColumnType),
Length = colNew.Length,
DefaultValue = colNew.DefaultValue,
IsNullable = colNew.IsNullable,
IsAutoIncrement = colNew.IsAutoIncrement
};
tbl.Columns.Add(f);
sb.AppendLine(Provider.Database.GetSQLColumnAdd(tbl.Name, f) + ";");
if (colNew.IsPrimaryKey)
{
PrimaryKeyConstraint k = new PrimaryKeyConstraint();
tbl.Constraints.Add(k);
k.ColumnNames.Add(f.Name);
k.Name = "PK_" + tbl.Name;
sb.AppendLine(Provider.Database.GetSQLConstraintAdd(k) + ";");
}
continue;
}
// field'ın adı değiştirilmişse
if (colNew.OriginalColumn.Name != colNew.Name)
{
string oldName = colNew.OriginalColumn.Name;
colNew.OriginalColumn.Name = colNew.Name;
sb.AppendLine(Provider.Database.GetSQLColumnRename(oldName, colNew.OriginalColumn) + ";");
}
// default value'su değiştirilmişse
if (colNew.OriginalColumn.DefaultValue != colNew.DefaultValue)
{
colNew.OriginalColumn.DefaultValue = colNew.DefaultValue;
sb.AppendLine(Provider.Database.GetSQLColumnChangeDefault(colNew.OriginalColumn) + ";");
}
// field type, length veya nullable değiştirilmişse
if (colNew.OriginalColumn.ColumnTypeOriginal != colNew.ColumnType || colNew.OriginalColumn.Length != colNew.Length || colNew.OriginalColumn.IsNullable != colNew.IsNullable)
{
var dependentConstraints = colNew.OriginalColumn.Table.Constraints.Where(cons => cons.ColumnNames.Contains(colNew.OriginalColumn.Name)).ToList();
foreach(var cons in dependentConstraints)
sb.AppendLine(Provider.Database.GetSQLConstraintRemove(cons) + ";");
colNew.OriginalColumn.ColumnTypeOriginal = colNew.ColumnType;
colNew.OriginalColumn.ColumnType = Provider.Database.StringToDbType(colNew.ColumnType);
colNew.OriginalColumn.Length = colNew.Length;
colNew.OriginalColumn.IsNullable = colNew.IsNullable;
sb.AppendLine(Provider.Database.GetSQLColumnChangeDataType(colNew.OriginalColumn) + ";");
foreach (var cons in dependentConstraints)
sb.AppendLine(Provider.Database.GetSQLConstraintAdd(cons) + ";");
}
// primary key kaldırılmışsa
if (colNew.OriginalColumn.IsPrimaryKey == true && colNew.IsPrimaryKey == false)
{
var k = colNew.OriginalColumn.Table.Constraints.FirstOrDefault(cons => cons is PrimaryKeyConstraint);
if (k != null)
{
colNew.OriginalColumn.Table.Constraints.Remove(k);
sb.AppendLine(Provider.Database.GetSQLConstraintRemove(k) + ";");
}
}
// primary key yapılmışsa
if (colNew.OriginalColumn.IsPrimaryKey == false && colNew.IsPrimaryKey == true)
{
PrimaryKeyConstraint k = new PrimaryKeyConstraint();
tbl.Constraints.Add(k);
k.ColumnNames.Add(colNew.Name);
k.Name = "PK_" + tbl.Name;
sb.AppendLine(Provider.Database.GetSQLConstraintAdd(k) + ";");
}
}
List<Column> deletedColumns = new List<Column>();
foreach (Column c in tbl.Columns)
if (!tblNew.Columns.Any(nc => nc.Name == c.Name))
deletedColumns.Add(c);
foreach (Column c in deletedColumns)
sb.AppendLine(Provider.Database.GetSQLColumnRemove(c) + ";");
if (tbl.Name != tblNew.Name)
{
sb.AppendLine(Provider.Database.GetSQLTableRename(tbl.Name, tblNew.Name) + ";");
tbl.Name = tblNew.Name;
}
string sql = sb.ToString();
SQLInputDialog sid = new SQLInputDialog(sql, false);
if (sid.ShowDialog() == DialogResult.OK)
{
Provider.Database.ExecuteNonQuery(sid.SQL);
try
{
findNode(tbl).Remove();
populateTreeNodesFor(null, tbl);
if (ObjectChanged != null)
ObjectChanged(this, new DbObjectChangedArgs { Object = tbl });
}
catch { }
}
else
{
tblNew.UndoChanges();
}
break;
}
catch (Exception ex)
{
tblNew.UndoChanges();
MessageBox.Show(ex.Message, "Cinar Database Tools");
fct.DialogResult = DialogResult.None;
}
}
else
break;
}
}