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();
}
private QuerySetting GetQuerySetting()
{
string strName = "ReportQuery";
string strDescription = UFIDA.U8.UAP.Services.ReportResource.U8ResService.GetResStringEx("U8.UAP.Services.ReportData.ConvertDataToBO.报表数据源查询");
QuerySetting querySetting = null;
if (this._IsSqlQuerist)
{
SQLScript sQLScript = new SQLScript();
sQLScript.SelectPart = this._SqlScript;
querySetting = new SQLQuerySetting(strName, sQLScript, strDescription);
}
else
{
string dllType = string.Empty;
if (this._bVB)
{
dllType = "Com";
}
else
{
dllType = "DotNet";
}
querySetting = new CustomQuerySetting(strName, dllType, this._dllInfor, strDescription);
}
FillColumnData(querySetting);
return(querySetting);
}
public DBRefList <T> Retrieve <T>(Clause clause = null) where T : TableEntity
{
var script = new SQLScript(Accessor, Commons.Objects.Enums.SQLTYPE.SELECT);
var from = new FromItem <T>(Accessor);
var select = new SelectItem(Accessor, from);
script.AddItems(select, from);
if (clause != null)
{
var where = new WhereItem(Accessor, clause, from);
script.AddItems(where);
}
var list = new DBRefList <T>();
var table = Accessor.Retrieve(script.ExportCommand());
if (table != null && table.Rows.Count > 0)
{
foreach (var row in table.Rows)
{
list.NewEntity().LoadFrom(row as DataRow, 0);
}
}
return(list);
}
public void Run()
{
try
{
foreach (DictionaryEntry item in this._scriptsToExecute)
{
try
{
SQLScript currentScript = default(SQLScript);
currentScript = (SQLScript)item.Value;
ExecuteScript(currentScript);
//WriteScript(item.Key, currentScript)
}
catch (System.Data.SqlClient.SqlException ex)
{
// This line must record the failure for a later notification to user
throw (ex);
}
}
}
catch (Exception ex)
{
throw (ex);
}
}
private void button1_Click(object sender, EventArgs e)
{
try
{
if (tbFolderName.Text.Length == 0)
{
throw new Exception("SQL 文件保存目录不能为空");
}
string sql = "";
if (tabControl1.SelectedTab.Name == "tabPage1")
{
tbName = cbTableName.SelectedItem.ToString().Split('|')[0].Trim();
foreach (ListViewItem item in lvFields.Items)
{
if (item.Checked)
{
sql = sql + item.Text + ",";
}
}
if (sql.Trim().Length > 0)
{
sql = sql.Substring(0, sql.Length - 1);
}
else
{
throw new Exception("字段没有选择");
}
sql = "Select " + sql + " From " + tbName;
}
else
{
if (tbSQLScript.Text.Trim().Length == 0)
{
throw new Exception("SQL script is null");
}
if (tbTableName.Text.Trim().Length == 0)
{
tbName = DateTime.Now.ToString("yyyyMMDDHHmmss");
}
else
{
tbName = tbTableName.Text.Trim();
}
sql = tbSQLScript.Text;
}
DataTable dt = SQLScript.GetRecordSet(sql);
string txtFileName = tbFolderName.Text + "\\" + tbName + ".txt";
ExportToTxt(dt, txtFileName);
throw new Exception("数据文件已生成：" + txtFileName);
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
}
private dynamic ExecuteScript(SQLScript script)
{
System.Data.SqlClient.SqlConnection dbConn = new System.Data.SqlClient.SqlConnection();
System.Data.SqlClient.SqlCommand dbComm = new System.Data.SqlClient.SqlCommand();
string scriptBatchContentsText = "";
while (script.ScriptBatchContents.Count > 0)
{
scriptBatchContentsText = System.Convert.ToString(script.ScriptBatchContents.Dequeue());
try
{
dbConn.ConnectionString = this._connectionString;
dbComm.Connection = dbConn;
dbComm.CommandType = CommandType.Text;
dbComm.CommandText = scriptBatchContentsText;
dbConn.Open();
dbComm.ExecuteNonQuery();
}
catch (Exception ex)
{
throw (ex);
}
finally
{
dbConn.Close();
}
}
return(default(dynamic));
}
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 Add(SQLScript item, int deep)
{
if (list == null) list = new List<SQLScript>();
if (item != null)
{
item.Deep = deep;
list.Add(item);
}
}
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();
}
private void SQLGenerator_Load(object sender, EventArgs e)
{
DataTable dt = SQLScript.GetTableList();
foreach (DataRow row in dt.Rows)
{
cbTableName.Items.Add(row["name"].ToString() + " |" + row["id"].ToString());
}
lvFields.View = View.Details;//设置视图
lvFields.Columns.Add("Field Name", lvFields.Width / 2, HorizontalAlignment.Center);
lvFields.Columns.Add("DataType", lvFields.Width / 2 - 10, HorizontalAlignment.Center);
}
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 static void OnGUI(ModEntry entry)
{
if (Settings.DeveloperMode)
{
GUILayout.Label("### Create Key (key, eng, kor)");
GUILayout.BeginHorizontal();
for (int i = 0; i < 3; i++)
{
values[i] = GUILayout.TextField(values[i]);
}
if (GUILayout.Button("Apply"))
{
SQLValue value = new SQLValue(values[0], values[1], values[2]);
SQLScript.InsertKey(value);
}
GUILayout.EndHorizontal();
GUILayout.Label("### Remove Key (key)");
GUILayout.BeginHorizontal();
values[3] = GUILayout.TextField(values[3]);
if (GUILayout.Button("Apply"))
{
SQLScript.DeleteKey(values[3]);
}
GUILayout.EndHorizontal();
GUILayout.Label("### Get Key Result: ");
GUILayout.Label(BEString.Get(values[4], SystemLanguage.English));
GUILayout.Label(BEString.Get(values[4], SystemLanguage.Korean));
values[4] = GUILayout.TextField(values[4]);
GUILayout.Space(20f);
Settings.TestValue = GUILayout.TextField(Settings.TestValue);
GUILayout.Space(20f);
}
}
private void WriteScript(string ScriptName, SQLScript script)
{
try
{
IEnumerator ienum = script.ScriptBatchContents.GetEnumerator();
StringBuilder strBldr = default(StringBuilder);
string testPath = System.AppDomain.CurrentDomain.BaseDirectory + ScriptName + ".txt";
while (ienum.MoveNext())
{
strBldr = (System.Text.StringBuilder)ienum.Current;
StreamWriter wr = new StreamWriter(testPath, true);
wr.Write(strBldr.ToString());
wr.Close();
}
}
catch (Exception ex)
{
throw (ex);
}
}
private void cbTableName_SelectedValueChanged(object sender, EventArgs e)
{
lvFields.Items.Clear();
if (cbTableName.SelectedItem == null)
{
return;
}
string tbID = cbTableName.SelectedItem.ToString().Split('|')[1].Trim();
DataTable dt = SQLScript.GetTableFiedList(tbID);
foreach (DataRow row in dt.Rows)
{
var item = new ListViewItem();
item.Text = row["name"].ToString();
string result = row["xtype"].ToString();
item.Checked = true;
item.SubItems.Add(result);
lvFields.Items.Add(item);
}
}
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);
}
private void GetScriptsFromFileList()
{
try
{
if (this._scriptsToExecute == null)
{
_scriptsToExecute = new SortedList();
}
foreach (DictionaryEntry item in this._fileList)
{
try
{
SQLScript currentscript = new SQLScript(System.Convert.ToString(item.Value));
_scriptsToExecute.Add(currentscript.FileName, currentscript);
}
catch (Exception)
{
}
}
}
catch (Exception)
{
}
}
private void PrepareScripts(ScriptingOptions scriptingOptions)
{
try
{
List <Action> actions = new List <Action>();
Server myServer = new Server(DatabaseServer.Server);
myServer.ConnectionContext.ConnectionString = SQLHelper.CreateNewConnection(DatabaseServer).ConnectionString;
myServer.ConnectionContext.Connect();
if (myServer.ConnectionContext.IsOpen)
{
Database myDatabase = myServer.Databases[DatabaseServer.Database];
Scripter scripter = new Scripter(myServer);
ScriptingOptions createOptions = scriptingOptions;
ScriptingOptions rollBackOptions = scriptingOptions;
rollBackOptions.ScriptDrops = true;
string rootDirectory = Path.Combine(Path.GetDirectoryName(createOptions.FileName), myDatabase.Name);
string rollbackFileName = string.Format("{0}_Rollback{1}", Path.GetFileNameWithoutExtension(scriptingOptions.FileName), Path.GetExtension(scriptingOptions.FileName));
rollBackOptions.FileName = Path.Combine(rootDirectory, rollbackFileName);
actions.Clear();
foreach (var sqlObject in SqlObjects)
{
actions.Add(() =>
{
if (SingleFilePerObject)
{
string directory = Path.Combine(rootDirectory, sqlObject.Type);
if (!Directory.Exists(directory))
{
Directory.CreateDirectory(directory);
}
createOptions.FileName = Path.Combine(
directory,
sqlObject.ToString() + ".sql"
);
}
createOptions.FileName = createOptions.FileName.NextAvailableFilename();
rollBackOptions.FileName = rollBackOptions.FileName.NextAvailableFilename();
// Update the status of scripting
lblScriptingStatus.SynchronizedInvoke(() =>
{
lblScriptingStatus.Text = "Scripting : (" + (SqlObjects.IndexOf(sqlObject) + 1) + "/" + SqlObjects.Count + ") " + sqlObject.ToString();
lblScriptingStatus.Refresh();
});
//Insert Comments
//AppendTextToFile(createOptions.FileName, "-- ***********************");
//AppendTextToFile(createOptions.FileName, "-- CREATE Script for : " + sqlObject.ToString());
//AppendTextToFile(createOptions.FileName, "-- ***********************");
//AppendTextToFile(rollBackOptions.FileName, "-- ***********************");
//AppendTextToFile(rollBackOptions.FileName, "-- DROP Script for : " + sqlObject.ToString());
//AppendTextToFile(rollBackOptions.FileName, "-- ***********************");
switch (sqlObject.Type)
{
case "TABLEWTIRGGERS":
SQLScript.ScriptTables(myDatabase, createOptions, rollBackOptions, sqlObject.Name, sqlObject.Schema);
SQLScript.ScriptTriggers(myDatabase, createOptions, rollBackOptions, sqlObject.Name, null, sqlObject.Schema);
break;
case "USER_TABLE":
SQLScript.ScriptTables(myDatabase, createOptions, rollBackOptions, sqlObject.Name, sqlObject.Schema);
break;
case "SQL_TRIGGER":
SQLScript.ScriptTriggers(myDatabase, createOptions, rollBackOptions, sqlObject.Name, sqlObject.Name,
sqlObject.Schema);
break;
case "SQL_STORED_PROCEDURE":
SQLScript.ScriptStoredProcedure(myDatabase, createOptions, rollBackOptions, sqlObject.Name, sqlObject.Schema);
break;
case "SQL_TABLE_VALUED_FUNCTION":
SQLScript.ScriptUserDefinedFunctions(myDatabase, createOptions, rollBackOptions, sqlObject.Name, sqlObject.Schema);
break;
case "SQL_SCALAR_FUNCTION":
SQLScript.ScriptUserDefinedFunctions(myDatabase, createOptions, rollBackOptions, sqlObject.Name, sqlObject.Schema);
break;
}
});
}
actions.Add(() =>
{
lblScriptingStatus.Text = "Success : Successfully completed scripting.";
});
((EzScriptForm)Application.OpenForms["EzScriptForm"]).helper.SetActionsTodo(actions);
((EzScriptForm)Application.OpenForms["EzScriptForm"]).backgroundWorkerExample.RunWorkerAsync();
}
}
catch (Exception ex)
{
lblScriptingStatus.Text = "Error : " + ex.Message;
lblScriptingStatus.Refresh();
}
}
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();
}
public static void Setup(ModEntry entry)
{
SQLScript.Setup(entry.Path); //ok
}
public void Add(SQLScript item)
{
if (list == null) list = new List<SQLScript>();
if (item != null) list.Add(item);
}
public static string Get(string key, SystemLanguage language)
{
return(SQLScript.GetReaderResult(key, ToSupportedLanguage(language)) ?? $"no such key '{key}'");
}
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();
}
