public void MakeSQLNew() { SQLScript.Clear(); string GenName = txtGenName.Text.Trim(); if (!string.IsNullOrEmpty(GenName)) { int?NewValue = StaticFunctionsClass.ToIntDef(txtGenNewValue.Text.Trim(), null); int?IncrementValue = StaticFunctionsClass.ToIntDef(txtIncrementValue.Text.Trim(), null); var sb = new StringBuilder(); sb.Append($@"CREATE SEQUENCE {GenName};{Environment.NewLine}"); if (NewValue != null) { string cmd = ((IncrementValue != null) && (IncrementValue.Value > 0)) ?$@"ALTER SEQUENCE {txtGenName.Text.Trim()} RESTART WITH {NewValue.Value} INCREMENT BY {IncrementValue.Value};{Environment.NewLine}" :$@"ALTER SEQUENCE {txtGenName.Text.Trim()} RESTART WITH {NewValue.Value};{Environment.NewLine}"; sb.Append(cmd); } sb.Append($@"{SQLPatterns.Commit}{Environment.NewLine}{Environment.NewLine}"); sb.Append($@"COMMENT ON GENERATOR {GenName} IS '{fctGenDescription.Text}';{Environment.NewLine}"); sb.Append($@"{SQLPatterns.Commit}{Environment.NewLine}{Environment.NewLine}"); SQLScript.Add(sb.ToString()); } else { SQLScript.Add("/* Generator name is not defined */"); } SQLToUI(); }
public void MakeSQOAlter() { SQLScript.Clear(); cbCollate.Enabled = false; var sb = new StringBuilder(); if (DomainObject.Name != txtName.Text) { sb.Append($@"ALTER DOMAIN {DomainObject.Name} TO {txtName.Text};"); sb.Append($@"{SQLPatterns.Commit}{Environment.NewLine}"); } sb.Append($@"ALTER DOMAIN {txtName.Text} TYPE {StaticVariablesClass.CompleteRawType(cbTypes.Text.Trim(), StaticFunctionsClass.ToIntDef(txtLength.Text.Trim(),0))}"); if (cbCharSet.Enabled) { if (cbCharSet.Text != "NONE") { sb.Append($@" CHARACTER SET {cbCharSet.Text}"); } } sb.AppendLine($@";"); sb.AppendLine($@"{SQLPatterns.Commit}{Environment.NewLine}"); if (txtDefault.Text != DomainObject._defaultValue) { string defstr = (cbTypes.Text.IndexOf("CHAR") >= 0) ? $@"'{txtDefault.Text}'" : txtDefault.Text; string cmd = string.IsNullOrEmpty(txtDefault.Text) ? $@"ALTER DOMAIN {txtName.Text} SET DEFAULT NULL;" : $@"ALTER DOMAIN {txtName.Text} SET DEFAULT {defstr};"; sb.AppendLine(cmd); sb.AppendLine($@"{SQLPatterns.Commit}{Environment.NewLine}"); } if (txtCheck.Text.Length > 0) { sb.AppendLine($@"ALTER DOMAIN {txtName.Text} DROP CONSTRAINT; /* drop check constraint */"); if (ckCheck.Checked) { sb.AppendLine($@"ALTER DOMAIN {txtName.Text} ADD CONSTRAINT CHECK ({txtCheck.Text}); /* adds not null flag to domain */"); } sb.AppendLine($@"{SQLPatterns.Commit}{Environment.NewLine}"); } else if (ckCheck.Checked != DomainObject.NotNull) { sb.AppendLine($@"ALTER DOMAIN {txtName.Text} DROP CONSTRAINT; /* drop check constraint */"); if (ckCheck.Checked) { sb.AppendLine($@"ALTER DOMAIN {txtName.Text} ADD CONSTRAINT CHECK (VALUE IS NOT NULL); /* adds not null flag to domain */"); } sb.AppendLine($@"{SQLPatterns.Commit}{Environment.NewLine}"); } if (fctDescription.Text != DomainObject.Description) { sb.AppendLine($@"COMMENT ON DOMAIN {txtName.Text} IS '{fctDescription.Text}';"); sb.AppendLine($@"{SQLPatterns.Commit}{Environment.NewLine}"); } SQLScript.Add(sb.ToString()); SQLToUI(); }
public void MakeSQLNew() { SQLScript.Clear(); //ckPrimary.Visible = true; StringBuilder sb = new StringBuilder(); /* * CREATE UNIQUE INDEX TLAGERDEF_IDX1 * ON TLAGERDEF(TLAGER_ID, POSX, POSY, POSZ) */ if (ckPrimary.Checked) { sb.Append($@"ALTER TABLE {TableName} ADD PRIMARY KEY"); string fieldStr = GetFields(); if (rbSortAscending.Checked) { fieldStr += $@" {StaticVariablesClass.ASCStr}"; } if (rbSortDescending.Checked) { fieldStr += $@" {StaticVariablesClass.DECStr}"; } fieldStr += ";"; sb.Append(fieldStr); } else { sb.Append($@"{StaticVariablesClass.CREATEStr} "); if (cbUnique.Checked) { sb.Append($@"{StaticVariablesClass.UNIQUEStr} "); } if (rbSortAscending.Checked) { sb.Append($@"{StaticVariablesClass.ASCStr} "); } else if (rbSortDescending.Checked) { sb.Append($@"{StaticVariablesClass.DECStr} "); } sb.Append("INDEX "); sb.Append(txtIndexName.Text.Trim()); sb.Append(" ON "); sb.Append(TableName); string fieldStr = GetFields(); sb.Append(fieldStr); } SQLScript.Add(sb.ToString()); SQLScript.Add($@"{SQLPatterns.Commit}"); SQLToUI(); }
public void MakeNotNullAlter(bool isPrimary) { // ALTER TABLE TADRESSEN ALTER POSTFACH TO POSTFACHX // COMMENT ON COLUMN TADRESSEN.POSTFACHX IS '123' // ALTER TABLE Customer CHANGE Address Addr char(50); feldnamen ändern // ALTER TABLE t1 ALTER c_temp TO c1; // ALTER TABLE t1 ALTER c1 TYPE char(90); fctSQL.BackColor = SystemColors.Info; SQLScript.Clear(); gbCollate.Enabled = false; gbNULLDefault.Enabled = true; bool FieldNameChanged = (OrgFieldObject.Name != FieldObject.Name); bool FieldTypeChanged = ((OrgFieldObject.Domain.FieldType != FieldObject.Domain.FieldType) || (OrgFieldObject.Domain.Precision != FieldObject.Domain.Precision) || (OrgFieldObject.Domain.Scale != FieldObject.Domain.Scale) || (OrgFieldObject.Domain.Length != FieldObject.Domain.Length) || (OrgFieldObject.Domain.CharSet != FieldObject.Domain.CharSet) || (OrgFieldObject.Domain.Collate != FieldObject.Domain.Collate)); bool PrimaryKeyChanged = (OrgFieldObject.IsPrimary != FieldObject.IsPrimary); bool IsNUllChanged = (OrgFieldObject.Domain.NotNull != FieldObject.Domain.NotNull); bool PositionChanged = ((OrgFieldObject.Position != FieldObject.Position) && (FieldObject.Position > 0)); bool DefaultChanged = (OrgFieldObject.Domain.DefaultValue != FieldObject.Domain.DefaultValue); bool DescriptionChanged = (OrgFieldObject.Description != FieldObject.Description); if (!string.IsNullOrEmpty(txtNULLDefault.Text)) { SQLScript.Add(Environment.NewLine); SQLScript.Add(SQLPatterns.UpdateFieldData.Replace(SQLPatterns.TableKey, TableObject.Name).Replace(SQLPatterns.ColumnKey, FieldObject.Name).Replace(SQLPatterns.ValueKey, txtNULLDefault.Text)); SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}{Environment.NewLine}"); } // if (IsNUllChanged && (tabControlFieldTypes.SelectedTab == tabPageFieldType)) // && (TableObject.IsNotNull(FieldObject.Name) != cbNotNull.Checked)) { // alter table tartikel alter datum set not null; SQLScript.Add(Environment.NewLine); if (!isPrimary) { string cmd1 = SQLPatterns.AlterTableFieldSetNull.Replace(SQLPatterns.TableKey, TableObject.Name).Replace(SQLPatterns.ColumnKey, FieldObject.Name); SQLScript.Add(cmd1); SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}{Environment.NewLine}"); } string cmd2 = SQLPatterns.AlterTableFieldSetNotNull.Replace(SQLPatterns.TableKey, TableObject.Name).Replace(SQLPatterns.ColumnKey, FieldObject.Name); SQLScript.Add(cmd2); SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}{Environment.NewLine}"); } SQLToUI(); }
public void MakeSQLNew() { SQLScript.Clear(); //ckPrimary.Visible = true; StringBuilder sb = new StringBuilder(); /* * CREATE UNIQUE INDEX TLAGERDEF_IDX1 * ON TLAGERDEF(TLAGER_ID, POSX, POSY, POSZ) */ if (ckPrimary.Checked) { sb.Append($@"ALTER TABLE {_tableObject.Name} ADD PRIMARY KEY"); string fieldStr = GetFields(); if (((eSort)cbSorting.SelectedItem) != eSort.NONE) { fieldStr += $@" {EnumHelper.GetDescription((eSort)cbSorting.SelectedItem)}"; } fieldStr += ";"; sb.Append(fieldStr); } else { sb.Append($@"{StaticVariablesClass.CREATEStr} "); if (cbUnique.Checked) { sb.Append($@"{StaticVariablesClass.UNIQUEStr} "); } if (((eSort)cbSorting.SelectedItem) != eSort.NONE) { sb.Append($@"{EnumHelper.GetDescription((eSort)cbSorting.SelectedItem)} "); } sb.Append("INDEX "); sb.Append(txtIndexName.Text.Trim()); sb.Append(" ON "); sb.Append(_tableObject.Name); string fieldStr = GetFields(); sb.Append(fieldStr); sb.Append(";"); } SQLScript.Add(sb.ToString()); SQLScript.Add($@"{SQLPatterns.Commit}"); SQLToUI(); }
public void MakeSQLNew() { SQLScript.Clear(); cbCollate.Enabled = true; var sb = new StringBuilder(); sb.Append($@"CREATE DOMAIN {txtName.Text} AS "); sb.Append($@"{StaticVariablesClass.CompleteRawType(cbTypes.Text.Trim(), StaticFunctionsClass.ToIntDef(txtLength.Text.Trim(), 0))}"); if (cbCharSet.Enabled) { if (cbCharSet.Text != "NONE") { sb.Append($@" CHARACTER SET {cbCharSet.Text}"); } } if (cbCollate.Enabled) { if (cbCollate.Text != "NONE") { sb.Append($@" COLLATE {cbCollate.Text}"); } } if (!string.IsNullOrEmpty(txtDefault.Text)) { sb.Append($@" DEFAULT '{txtDefault.Text}'"); } sb.AppendLine($@";"); sb.Append($@"{SQLPatterns.Commit}{Environment.NewLine}"); if (cbNotNull.Checked) { sb.AppendLine($@"ALTER DOMAIN {txtName.Text} ADD CONSTRAINT CHECK (VALUE IS NOT NULL); /* adds not null flag to domain */"); sb.AppendLine($@"{SQLPatterns.Commit}{Environment.NewLine}"); } if (!string.IsNullOrEmpty(fctDescription.Text)) { sb.AppendLine($@"COMMENT ON DOMAIN {txtName.Text} IS '{fctDescription.Text}';"); sb.Append($@"{SQLPatterns.Commit}{Environment.NewLine}"); } SQLScript.Add(sb.ToString()); SQLToUI(); }
public bool MakeSQLNew() { string active = TriggerObject.Active ? "ACTIVE" : "DEACTIVE"; string triggerTypeStr = EnumHelper.GetDescription(TriggerObject.Type); // StaticVariablesClass.GetTriggerTypeStr(TriggerObject.Type); SQLScript.Clear(); StringBuilder sb = new StringBuilder(); sb.Append($@"{SQLPatterns.SetTermStart}{Environment.NewLine}"); sb.Append($@"CREATE TRIGGER {TriggerObject.Name} "); sb.Append($@"FOR {TriggerObject.RelationName}"); sb.Append($@"{Environment.NewLine}{active} "); sb.Append($@"{triggerTypeStr} "); sb.Append($@"POSITION {TriggerObject.Sequence}"); sb.Append($@"{Environment.NewLine}{TriggerObject.Source}{Environment.NewLine}"); sb.Append($@"^{Environment.NewLine}"); sb.Append($@"{SQLPatterns.SetTermEnd}{Environment.NewLine}"); SQLScript.Add(sb.ToString()); SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}"); SQLToUI(); return(true); }
public void MakeSQLNew(bool force = false) { DomainClass Domain = null; if ((cbDOMAIN.SelectedItem != null) && (cbDOMAIN.Text.Length > 0)) { Domain = cbDOMAIN.SelectedItem as DomainClass; } if (Domain == null) { tabControlFld.SelectedTab = tabPageField; } bool usefieldtype = tabControlFld.SelectedTab == tabPageField; SQLScript.Clear(); var sb = new StringBuilder(); sb.Append($@"CREATE TABLE {_tableObject.Name}{Environment.NewLine}({Environment.NewLine} {txtFieldName.Text} "); if (!usefieldtype) { string cmd = (Domain != null) ? $@"{Domain.Name}" : $@"{cbDOMAIN.Text}"; sb.Append(cmd); //Anlegen mit domain -> keine collation und charset... if ((!usefieldtype) && (Domain.FieldType == "VARYING")) { sb.Append(" /* domain */ "); } if (cbPrimaryKey.Checked) { sb.Append(" PRIMARY KEY"); } cmd = (Domain != null) ? $@"{Environment.NewLine} /* raw:{Domain.RawType} intern:[{Domain.FieldType}] */" : $@"{Environment.NewLine} /* {GetTypeString(cbTypes.Text, txtLength.Text, txtPrecisionLength.Text, txtScale.Text)} */"; sb.Append(cmd); } else { //Keine Domain //sb.Append(GetTypeString(cbTypes.Text, txtLength.Text, txtPrecisionLength.Text, txtScale.Text)); sb.Append(GetTypeString(cbTypes.Text, txtLength.Text, txtPrecisionLength.Text, txtScale.Text)); if (cbTypes.Text == "VARCHAR") { //Lege Column mit Datentyp an if (cbCharSet.Text.Length > 0) { sb.Append($@" CHARACTER SET {cbCharSet.Text}"); } if (cbCollate.Text.Length > 0) { sb.Append($@" COLLATE {cbCollate.Text}"); } } if (txtDefault.Text.Length > 0) { sb.Append($@" DEFAULT {txtDefault.Text.Trim()}"); } if (cbPrimaryKey.Checked) { sb.Append(" PRIMARY KEY"); } if (cbNotNull.Checked) { sb.Append(" NOT NULL"); } } sb.Append($@"{Environment.NewLine});"); SQLScript.Add(sb.ToString()); sb.Clear(); //COMMENT ON COLUMN TADRESSEN.TTT IS ' rrrrrrrrrrrrrrrr' if (fctDescription.Text.Length > 0) { sb.Clear(); sb.Append($@"COMMENT ON COLUMN {_tableObject.Name}.{txtFieldName.Text.Trim()} IS '{fctDescription.Text}';"); SQLScript.Add(sb.ToString()); SQLScript.Add(SQLPatterns.Commit); } SQLToUI(); }
public void MakeSQLNew() { SQLScript.Clear(); //ckPrimary.Visible = true; StringBuilder sb = new StringBuilder(); /* * CREATE UNIQUE INDEX TLAGERDEF_IDX1 * ON TLAGERDEF(TLAGER_ID, POSX, POSY, POSZ) */ if (ckPrimary.Checked) { sb.Append($@"ALTER TABLE {TableName} ADD PRIMARY KEY "); bool firstdone = false; string fieldStr = string.Empty; fieldStr += "("; foreach (ListViewItem lvi in lvFields.Items) { if (firstdone) { fieldStr += ","; } string st = lvi.Text; fieldStr += st; firstdone = true; } fieldStr += ")"; fieldStr += ";"; sb.Append(fieldStr); } else { sb.Append("CREATE "); if (cbUnique.Checked) { sb.Append("UNIQUE "); } sb.Append("INDEX "); sb.Append(txtIndexName.Text.Trim()); sb.Append(" ON "); sb.Append(TableName); sb.Append("("); bool firstdone = false; foreach (ListViewItem lvi in lvFields.Items) { if (firstdone) { sb.Append(","); } string st = lvi.Text; sb.Append(st); firstdone = true; } sb.Append(");"); } SQLScript.Add(sb.ToString()); SQLScript.Add("COMMIT;"); SQLToUI(); }
public void MakeSQOAlter() { // ALTER TABLE TADRESSEN ALTER POSTFACH TO POSTFACHX // COMMENT ON COLUMN TADRESSEN.POSTFACHX IS '123' // ALTER TABLE Customer CHANGE Address Addr char(50); feldnamen ändern // ALTER TABLE t1 ALTER c_temp TO c1; // ALTER TABLE t1 ALTER c1 TYPE char(90); fctSQL.BackColor = SystemColors.Info; SQLScript.Clear(); gbCollate.Enabled = false; gbNULLDefault.Enabled = true; bool FieldNameChanged = (OrgFieldObject.Name != FieldObject.Name); bool FieldTypeChanged = ((OrgFieldObject.Domain.FieldType != FieldObject.Domain.FieldType) || (OrgFieldObject.Domain.Precision != FieldObject.Domain.Precision) || (OrgFieldObject.Domain.Scale != FieldObject.Domain.Scale) || (OrgFieldObject.Domain.Length != FieldObject.Domain.Length) || (OrgFieldObject.Domain.CharSet != FieldObject.Domain.CharSet) || (OrgFieldObject.Domain.Collate != FieldObject.Domain.Collate)); bool PrimaryKeyChanged = (OrgFieldObject.IsPrimary != FieldObject.IsPrimary); bool IsNUllChanged = (OrgFieldObject.Domain.NotNull != FieldObject.Domain.NotNull); bool PositionChanged = ((OrgFieldObject.Position != FieldObject.Position) && (FieldObject.Position > 0)); bool DefaultChanged = (OrgFieldObject.Domain.DefaultValue != FieldObject.Domain.DefaultValue); bool DescriptionChanged = (OrgFieldObject.Description != FieldObject.Description); if (FieldNameChanged) { SQLScript.Add(SQLPatterns.AlterTableRenameField.Replace(SQLPatterns.TableKey, TableObject.Name).Replace(SQLPatterns.ColumnKey, OrgFieldObject.Name).Replace(SQLPatterns.ColumnKey2, FieldObject.Name)); SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}{Environment.NewLine}{Environment.NewLine}"); } if (FieldTypeChanged) { var sb = new StringBuilder($@"ALTER TABLE {TableObject.Name} ALTER {FieldObject.Name}"); if (tabControlFieldTypes.SelectedTab == tabPageFieldType) { var FieldType = GetTypeString((DBDataTypes)cbFieldTypes.SelectedItem, txtLength.Text, txtPrecisionLength.Text, txtScale.Text); sb.Append($@" TYPE {FieldType}"); //Fieldinfo for domain Type tp = cbFieldTypes.SelectedItem.GetType(); if (tp == typeof(DBVarchar)) { int len = StaticFunctionsClass.ToIntDef(txtLength.Text, 0); string btstr = (len > 0) ? $@" CHARACTER SET {cbCharSet.Text}" : $@" COLLATE {cbCollate.Text}"; sb.Append(btstr); } else if (tp == typeof(DBBlob)) { int len = StaticFunctionsClass.ToIntDef(txtBlobSize.Text, 0); string btstr = (cbBlobType.Text == "TEXT") ? $@" SUB_TYPE 1" : $@" SUB_TYPE 0"; sb.Append(btstr); if (len > 0) { sb.Append($@" SEGMENT SIZE {len}"); } } if (!sb.ToString().EndsWith(";")) { sb.Append(";"); } SQLScript.Add(sb.ToString()); SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}{Environment.NewLine}"); } else { var Domain = ((cbDOMAIN.SelectedItem != null) && (cbDOMAIN.Text.Length > 0)) ? cbDOMAIN.SelectedItem as DomainClass : null; if (Domain != null) { var FieldType = (Domain != null) ? Domain.Name : cbDOMAIN.Text; string cmd = (Domain != null) ? $@" {FieldType} /* raw:{Domain.RawType} intern:[{Domain.FieldType}] */" : $@" {FieldType} /* {GetTypeString((DBDataTypes) cbFieldTypes.SelectedItem, txtLength.Text, txtPrecisionLength.Text, txtScale.Text)} */"; sb.Append(cmd); //Fieldinfo for domain if (Domain.FieldType == "VARYING") { if ((Domain.CharSet.Length > 0) || (Domain.Collate.Length > 0)) { sb.Append(" /*"); } if (Domain.CharSet.Length > 0) { sb.Append($@" CHARACTER SET {Domain.CharSet}"); } if (Domain.Collate.Length > 0) { sb.Append($@" COLLATE {Domain.Collate}"); } if ((Domain.CharSet.Length > 0) || (Domain.Collate.Length > 0)) { sb.Append(" */"); } } SQLScript.Add(sb.ToString()); SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}{Environment.NewLine}"); } } } if (!string.IsNullOrEmpty(txtNULLDefault.Text)) { SQLScript.Add(Environment.NewLine); SQLScript.Add(SQLPatterns.UpdateFieldData.Replace(SQLPatterns.TableKey, TableObject.Name).Replace(SQLPatterns.ColumnKey, FieldObject.Name).Replace(SQLPatterns.ValueKey, txtNULLDefault.Text)); SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}{Environment.NewLine}"); } if (IsNUllChanged && (tabControlFieldTypes.SelectedTab == tabPageFieldType)) // && (TableObject.IsNotNull(FieldObject.Name) != cbNotNull.Checked)) { // alter table tartikel alter datum set not null; SQLScript.Add(Environment.NewLine); string cmd = (FieldObject.Domain.NotNull) ? SQLPatterns.AlterTableFieldSetNotNull.Replace(SQLPatterns.TableKey, TableObject.Name).Replace(SQLPatterns.ColumnKey, FieldObject.Name) : SQLPatterns.AlterTableFieldSetNull.Replace(SQLPatterns.TableKey, TableObject.Name).Replace(SQLPatterns.ColumnKey, FieldObject.Name); SQLScript.Add(cmd); SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}{Environment.NewLine}"); } // ALTER TABLE EMPLOYEE ADD PRIMARY KEY(SSN) if (PrimaryKeyChanged) { if (FieldObject.IsPrimary) { SQLScript.Add(Environment.NewLine); string cmd = SQLPatterns.AlterTableAddPK.Replace(SQLPatterns.TableKey, TableObject.Name).Replace(SQLPatterns.ColumnKey, FieldObject.Name).Replace(SQLPatterns.PrimaryKey, FieldObject.PK_ConstraintName); SQLScript.Add(cmd); SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}{Environment.NewLine}"); } else if (!string.IsNullOrEmpty(FieldObject.PK_ConstraintName)) { SQLScript.Add(Environment.NewLine); string cmd = SQLPatterns.AlterTableDropPK.Replace(SQLPatterns.TableKey, TableObject.Name).Replace(SQLPatterns.PrimaryKey, FieldObject.PK_ConstraintName); SQLScript.Add(cmd); SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}{Environment.NewLine}"); } } if (PositionChanged) { string sql = SQLPatterns.PositionSchablone.Replace(SQLPatterns.TableKey, TableObject.Name).Replace(SQLPatterns.ColumnKey, FieldObject.Name).Replace(SQLPatterns.PositionKey, $@"{FieldObject.Position}"); SQLScript.Add(Environment.NewLine); SQLScript.Add(sql); SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}{Environment.NewLine}"); } if (DefaultChanged) { // alter table tartikel alter id set default 1; string cmd = (txtDefault.Text.Length > 0) ? $@"{SQLPatterns.AlterTableSetDefault.Replace(SQLPatterns.TableKey,TableObject.Name).Replace(SQLPatterns.ColumnKey,FieldObject.Name).Replace(SQLPatterns.DefaultKey,txtDefault.Text)}" : $@"{SQLPatterns.AlterTableDropDefault.Replace(SQLPatterns.TableKey,TableObject.Name).Replace(SQLPatterns.ColumnKey,FieldObject.Name)}"; SQLScript.Add(Environment.NewLine); SQLScript.Add(cmd); SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}{Environment.NewLine}"); } if ((DescriptionChanged) && (fctDescription.Text != FieldObject.Description)) { SQLScript.Add(Environment.NewLine); SQLScript.Add($@"COMMENT ON COLUMN {TableObject.Name}.{txtFieldName.Text.Trim()} IS '{fctDescription.Text}';"); SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}{Environment.NewLine}"); } SQLToUI(); }
public void MakeSQLNew() { fctSQL.BackColor = Color.Bisque; SQLScript.Clear(); gbCollate.Enabled = true; gbNULLDefault.Enabled = false; if (string.IsNullOrEmpty(txtFieldName.Text)) { SQLScript.Add("/* FieldName is undefined */"); SQLToUI(); return; } bool FieldTypeChaned = (OrgFieldObject.Name != FieldObject.Name); bool PrimaryKeyChanged = (OrgFieldObject.IsPrimary != FieldObject.IsPrimary); bool IsNUllChanged = (OrgFieldObject.Domain.NotNull != FieldObject.Domain.NotNull); bool PositionChanged = ((OrgFieldObject.Position != FieldObject.Position) && (FieldObject.Position > 0)); bool DefaultChanged = (OrgFieldObject.Domain.DefaultValue != FieldObject.Domain.DefaultValue); bool DescriptionChanged = (OrgFieldObject.Description != FieldObject.Description); var sb = new StringBuilder(); string FieldName = txtFieldName.Text; sb.Append($@"ALTER TABLE {TableObject.Name} ADD {FieldName} "); if (tabControlFieldTypes.SelectedTab == tabPageFieldType) { var FieldType = GetTypeString((DBDataTypes)cbFieldTypes.SelectedItem, txtLength.Text, txtPrecisionLength.Text, txtScale.Text); sb.Append($@" {FieldType}"); Type tp = cbFieldTypes.SelectedItem.GetType(); if (tp == typeof(DBVarchar)) { //Lege Column mit Datentyp an if (cbCharSet.Text.Length > 0) { sb.Append($@" CHARACTER SET {cbCharSet.Text}"); } if (cbCollate.Text.Length > 0) { sb.Append($@" COLLATE {cbCollate.Text}"); } } else if (cbFieldTypes.Text == "BLOB") { // BLOB SUB_TYPE 0 SEGMENT SIZE 80 int st = 0; if (cbBlobType.Text == "TEXT") { st = 1; } if (cbBlobType.Text == "BINARY") { st = 0; } sb.Append($@" SUB_TYPE {st}"); int len = StaticFunctionsClass.ToIntDef(txtBlobSize.Text, 0); if (len > 0) { sb.Append($@" SEGMENT SIZE {len}"); } } } else //Domain { var Domain = ((cbDOMAIN.SelectedItem != null) && (cbDOMAIN.Text.Length > 0)) ? cbDOMAIN.SelectedItem as DomainClass : null; FieldName = (Domain != null) ? Domain.Name : cbDOMAIN.Text; string cmd = (Domain != null) ? $@" {FieldName} /* raw:{Domain.RawType} intern:[{Domain.FieldType}] */" : $@" {FieldName} /* {GetTypeString((DBDataTypes) cbFieldTypes.SelectedItem, txtLength.Text, txtPrecisionLength.Text, txtScale.Text)} */"; sb.Append(cmd); if (Domain != null) { if (Domain.FieldType == "VARYING") { sb.Append(" /* domain */ "); } } } if (txtDefault.Text.Length > 0) { sb.Append($@" DEFAULT '{txtDefault.Text.Trim()}'"); } if (cbNotNull.Checked) { sb.Append(" NOT NULL"); } sb.Append(";"); SQLScript.Add(sb.ToString()); SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}{Environment.NewLine}"); if (cbPrimaryKey.Checked) { SQLScript.Add($@"{Environment.NewLine}"); string cmd = SQLPatterns.AlterTableAddPK.Replace(SQLPatterns.TableKey, TableObject.Name).Replace(SQLPatterns.ColumnKey, FieldName).Replace(SQLPatterns.PrimaryKey, txtPK.Text); //SQLScript.Add($@"ALTER TABLE INVOICE ADD CONSTRAINT {txtPK.Text} PRIMARY KEY ({FieldName})"); SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}{Environment.NewLine}"); } //COMMENT ON COLUMN TADRESSEN.TTT IS ' rrrrrrrrrrrrrrrr' if (fctDescription.Text.Length > 0) { SQLScript.Add($@"{Environment.NewLine}"); SQLScript.Add(SQLPatterns.AlterTableColumnComment.Replace(SQLPatterns.TableKey, TableObject.Name).Replace(SQLPatterns.ColumnKey, FieldName).Replace(SQLPatterns.DescriptionKey, fctDescription.Text)); SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}{Environment.NewLine}"); } int pos = StaticFunctionsClass.ToIntDef(txtPosition.Text, 0); if (pos > 0) { string sql = SQLPatterns.PositionSchablone.Replace(SQLPatterns.TableKey, TableObject.Name).Replace(SQLPatterns.ColumnKey, FieldName).Replace(SQLPatterns.PositionKey, $@"{pos-1}"); SQLScript.Add($@"{Environment.NewLine}"); SQLScript.Add(sql); SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}{Environment.NewLine}"); } SQLToUI(); }