public Table CreateTable()
{
FormCreateTable fct = new FormCreateTable();
Table tbl = null;
while (true)
{
if (fct.ShowDialog() == DialogResult.OK)
{
tbl = fct.GetCreatedTable();
try
{
if (string.IsNullOrEmpty(tbl.Name))
throw new Exception("Enter a valid name for the table.");
if (tbl.Columns == null || tbl.Columns.Count == 0)
throw new Exception("Add minimum one column to the table.");
if (Provider.Database.Tables[tbl.Name] != null)
throw new Exception("There is already a table named " + tbl.Name);
Provider.Database.Tables.Add(tbl);
if (!CreateTable(Provider.Database, tbl))
Provider.Database.Tables.Remove(tbl);
break;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Cinar Database Tools");
fct.DialogResult = DialogResult.None;
}
}
else
break;
}
return tbl;
}
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;
}
}