/// <summary>
/// Run sql query and execute Script for found rows. This function is intended to use from Dialog.
/// </summary>
/// <param name="isWithAsk">True: Ask for each found rows if to execute</param>
void RunScriptWithAskGui(bool isWithAsk = false)
{
if (tabControlSql.SelectedIndex == -1)
{
return;
}
Cursor.Current = Cursors.WaitCursor;
// get TabPage
TabPage tabPage = tabControlSql.TabPages[tabControlSql.SelectedIndex];
// get TextBox
TextBox textBox = (TextBox)tabPage.Controls[0];
// get Script and its parameter to run
DataGridViewRow rowToRun = dataGridViewScripts.Rows[_rowScriptsIndex];
DataRowView row = rowToRun.DataBoundItem as DataRowView;
var scriptFunction = row["FunctionObj"] as ScriptFunction;
// replace templates, search term and more
string sql = SqlTemplates.ReplaceMacro(Repository, textBox.Text, GetSearchTerm());
if (sql == "")
{
return;
}
// run SQL, Script and ask whether to execute, skip script or break all together
GuiFunction.RunScriptWithAsk(Model, sql, scriptFunction, isWithAsk: isWithAsk);
Cursor.Current = Cursors.Default;
}
/// <summary>
/// Run an SQL string and if query output the result in EA Search Window. If update, insert, delete execute SQL.
/// It return "" for nothing found or the SQL result string.
/// <para/>- replacement of macros
/// <para/>- run query
/// <para/>- format to output
/// </summary>
/// <param name="sqlName"></param>
/// <param name="sql"></param>
/// <param name="searchText">Search Text to replace 'Search Term' macro</param>
/// <param name="exportToExcel"></param>
/// <returns>"" for nothing found or the EA SQL XML string with the found information</returns>
public string SqlRun(string sqlName, string sql, string searchText, bool exportToExcel = false)
{
// replace templates
sql = SqlTemplates.ReplaceMacro(Repository, sql, searchText);
if (String.IsNullOrWhiteSpace(sql))
{
return("");
}
// normalize according to linefeed
sql = Regex.Replace(sql, @"\r\n |\r\n|\n\r|\n|\r", "\r\n ");
// check whether select or update, delete, insert sql
if (Regex.IsMatch(sql, @"^\s*select ", RegexOptions.IgnoreCase | RegexOptions.Multiline))
{
// run the SQL select query
var xmlSqlQueryResult = SqlQueryWithException(sql) ?? "";
// output the query in EA Search Window format
string xmlEaOutput = MakeEaXmlOutput(xmlSqlQueryResult);
if (exportToExcel)
{
Excel.MakeExcelFileFromSqlResult(xmlSqlQueryResult,
@"d:\temp\sql\" + Path.GetFileNameWithoutExtension(sqlName) + ".xlsx");
}
Repository.RunModelSearch("", "", "", xmlEaOutput);
return(xmlSqlQueryResult);
}
else
{
// run the update, delete, insert sql
bool ret = SqlExecuteWithException(sql);
// if ok output the SQL
if (ret)
{
string sqlText =
$"Path SQL:\r\n{SqlError.GetHoToolsLastSqlFilePath()}\r\n\r\n{SqlError.ReadHoToolsLastSql()}";
MessageBox.Show(sqlText, @"SQL executed!\r\n\r\nCtrl+C to copy it to clipboard (ignore beep).");
}
return("");
}
}