/// <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("");
}
}
/// <summary>
/// Output the last from hoTools Query sent sql string to EA
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void lastSqlStringSentToEAToolStripMenuItem_Click(object sender, EventArgs e)
{
Util.StartFile(SqlError.GetHoToolsLastSqlFilePath());
}