/// <summary>
/// Runs the search (hoTools SQL file, EA search or LINQ Search). It handles the exceptions.
/// It converts wild cards of the <Search Term>.
/// - First search for SQL-File
/// - Search the LINQ Search if LINQ is supported
/// - If no SQL file found run EA Search
/// </summary>
/// <param name="searchName">EA Search name or SQL file name (uses path to find absolute path)</param>
/// <param name="searchTerm"></param>
/// <param name="exportToExcel"></param>
public string SearchRun(string searchName, string searchTerm, bool exportToExcel = false)
{
searchName = searchName.Trim();
if (searchName == "")
{
return("");
}
// SQL file?
string sqlFile = _globalCfg.GetSqlFileName(searchName);
if (sqlFile != "")
{
// ---------------------SQL Search----------------------------
string sqlString = _globalCfg.ReadSqlFile(searchName);
// run sql search
searchTerm = UtilSql.ReplaceSqlWildCards(Repository, searchTerm, RepositoryType);
return(SqlRun(searchName, sqlString, searchTerm, exportToExcel));
}
// LINQPad
string linqPadFile = _globalCfg.GetLinqPadQueryFileName(searchName);
if (linqPadFile != "")
{
LinqPad linqPad = new LinqPad(Repository, _globalCfg.LprunPath, _globalCfg.TempFolder, @"html",
_globalCfg.UseLinqPadConnection)
{
UseLinqPadConnections = _globalCfg.UseLinqPadConnection,
LinqPadConnections = _globalCfg.LinqPadConnectionPath
};
Boolean result = linqPad.Run(linqPadFile, @"html", linqPad.GetArg(Repository, searchTerm));
if (!result)
{
return("");
}
// output target to browser
if (_globalCfg.LinqPadOutputHtml)
{
Process.Start(linqPad.TargetFile);
}
// HTML to DataTable
System.Data.DataTable dtHtml = linqPad.ReadHtml();
if (exportToExcel)
{
// DataTable to Excel
string excelFile = Path.Combine(_globalCfg.TempFolder,
$"{Path.GetFileNameWithoutExtension(linqPadFile)}");
Excel.SaveTableToExcel(ref excelFile, dtHtml);
}
// Make EA xml
string xml = Xml.MakeXmlFromDataTable(dtHtml);
// Output to EA
Repository.RunModelSearch("", "", "", xml);
return("");
}
else
{
// EA Search
try
{
// run SQL search and display in Search Window
Repository.RunModelSearch(searchName, searchTerm, "", "");
return("");
}
catch (Exception e)
{
MessageBox.Show($@"Can't find search!{Environment.NewLine}{Environment.NewLine}- MDG hoTools.. enabled?{Environment.NewLine}- SQL path in Settings correct?{Environment.NewLine}- LINQPad support enabled (Settings General)? :{Environment.NewLine}'{searchName}' '{searchTerm}'{Environment.NewLine}- *.sql in path '{_globalCfg.GetSqlPaths()}'{Environment.NewLine}- *.linq in path '{_globalCfg.GetLinqPaths()}'{Environment.NewLine}- EA Search{Environment.NewLine}{Environment.NewLine}Note:{Environment.NewLine}- Define path in File, Settings{Environment.NewLine}- LINQPad needs a license and has to be installed!{Environment.NewLine}{Environment.NewLine}{e}",
$@"Error start search.");
return("");
}
}
}
/// <summary>
/// Called when user makes a selection in the menu.
/// This is your main exit point to the rest of your Add-in
/// </summary>
/// <param name="repository">the repository</param>
/// <param name="location">the location of the menu</param>
/// <param name="menuName">the name of the menu</param>
/// <param name="itemName">the name of the selected menu item</param>
public override void EA_MenuClick(EA.Repository repository, string location, string menuName, string itemName)
{
string xml;
DataTable dt;
// for LINQ to SQL
IDataProvider provider; // the provider to connect to database like Access, ..
string connectionString; // The connection string to connect to database
string parametersToPassToQuery;
string linqQueryFileName;
string linqQueryFilePath;
bool result;
LinqPad linqPad;
DataTable dtHtml;
switch (itemName)
{
// user has clicked the menuHello menu option
case MenuHello:
this.SayHello();
break;
// user has clicked the menuGoodbye menu option
case MenuGoodbye:
this.SayGoodbye();
break;
case MenuOpenProperties:
this.TestPropertiesDialog(repository);
break;
// Test the Search and output the results to EA Search Window
case MenuRunDemoSearch:
// 1. Collect data
dt = SetTable();
// 2. Order, Filter, Join, Format to XML
xml = QueryAndMakeXmlFromTable(dt);
// 3. Out put to EA
repository.RunModelSearch("", "", "", xml);
break;
case MenuRunDemoPackageContent:
// 1. Collect data into a data table
dt = SetTableFromContext(repository);
// 2. Order, Filter, Join, Format to XML
xml = QueryAndMakeXmlFromTable(dt);
// 3. Out put to EA
repository.RunModelSearch("", "", "", xml);
break;
// Example to run SQL, convert to DataTable and output in EA Search Window
case MenuRunDemoSqlToDataTable:
// 1. Run SQL
string sql = "select ea_guid AS CLASSGUID, object_type AS CLASSTYPE, name, stereotype, object_type from t_object order by name";
xml = repository.SQLQuery(sql);
// 2. Convert to DataTable
dt = Xml.MakeDataTableFromSqlXml(xml);
// 2. Order, Filter, Join, Format to XML
xml = QueryAndMakeXmlFromTable(dt);
// 3. Out put to EA
repository.RunModelSearch("", "", "", xml);
break;
// Read connection string from EA and try an ADODB Connection
// Copy connection string to clipboard
case MenuShowConnectionString:
string eaConnectionString = repository.ConnectionString;
if (eaConnectionString != null)
{
connectionString = LinqUtil.GetConnectionString(repository, out provider);
string lConnectionString = $"{eaConnectionString}\r\n\r\nProvider for Linq for SQL:\r\n'{provider}\r\n{connectionString}";
Clipboard.SetText(lConnectionString);
MessageBox.Show($@"{lConnectionString}", @"Connection string (EA+LINQ + SQL) copied to clipboard");
if (connectionString == "")
{
return;
}
ADODB.Connection conn = new ADODB.Connection();
try
{
conn.Open(connectionString, "", "", -1); // connection Open synchronously
//conn.Open(connectionString, "", "", -1); // connection Open synchronously
MessageBox.Show($@"EA ConnectionString: '{eaConnectionString}'
ConnectionString:
- '{connectionString}'
Provider:
- '{provider}'
Mode:
- '{conn.Mode}'
State:
- '{conn.State}'", @"ODBC Connection established ");
}
catch (Exception e)
{
MessageBox.Show($@"EA ConnectionString: '{eaConnectionString}'
ConnectionString:
- '{connectionString}'
Mode:
- '{conn.Mode}'
State:
- '{conn.State}'
{ e}", @"ODBC Connection error ");
}
}
break;
// Basis LINQ to SQL example
case MenuShowRunLinq2Db:
// get connection string of repository
connectionString = LinqUtil.GetConnectionString(repository, out provider);
// Run LINQ query to dataTable
dt = LinqUtil.RunLinq2Db(provider, connectionString);
// Make EA xml
OrderedEnumerableRowCollection <DataRow> rows = from row in dt.AsEnumerable()
orderby row.Field <string>(dt.Columns[0].Caption)
select row;
xml = Xml.MakeXml(dt, rows);
// Output to EA
repository.RunModelSearch("", "", "", xml);
break;
// Advanced LINQ to SQL example
case MenuShowRunLinq2DbAdvanced:
// get connection string of repository
connectionString = LinqUtil.GetConnectionString(repository, out provider);
// Run LINQ query to dataTable
dt = LinqUtil.RunLinq2DbAdvanced(provider, connectionString);
// Make EA xml
xml = Xml.MakeXmlFromDataTable(dt);
// Output to EA
repository.RunModelSearch("", "", "", xml);
break;
// run LINQPad query to HTML,csv, text (uses lprun.exe)
// - !!!!You need a LINQPad license to run!!!!!!
// - lprun installed at standard location (c:\Program Files (x86)\LINQPad5\lprun.exe)
// - output to 'c:\temp\EaBasicQuery.html'
// - EA standard installation (used for EAExample database)
case MenuShowRunLinqPadToHtml:
// Run query with lprun.exe
parametersToPassToQuery = @"""Test query EaBasicQuery.linq""";
linqQueryFileName = "EaBasicQuery.linq";
linqQueryFilePath = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + @"\" + linqQueryFileName;
linqPad = new LinqPad(repository);
// Output as html with read back table and out put to EA Search Window
result = linqPad.Run(linqQueryFilePath, @"html", parametersToPassToQuery);
if (!result)
{
return;
}
dtHtml = linqPad.ReadHtml();
// Make EA xml
xml = Xml.MakeXmlFromDataTable(dtHtml);
// Output to EA
repository.RunModelSearch("", "", "", xml);
linqPad.Show();
// csv
result = linqPad.Run(linqQueryFilePath, @"csv", parametersToPassToQuery);
if (!result)
{
return;
}
linqPad.Show();
// text
result = linqPad.Run(linqQueryFilePath, @"text", parametersToPassToQuery);
if (!result)
{
return;
}
linqPad.Show();
break;
// Run LINQPad and output EA context information
case MenuShowRunLinqPadEaContext:
linqQueryFileName = "TestCallLinqWithParameter.linq";
linqQueryFilePath = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + @"\" + linqQueryFileName;
linqPad = new LinqPad(repository);
// Output as html with read back table and out put to EA Search Window
result = linqPad.Run(linqQueryFilePath, @"html", linqPad.GetArg(repository, "mySearchTerm"));
if (!result)
{
return;
}
dtHtml = linqPad.ReadHtml();
// Make EA xml
xml = Xml.MakeXmlFromDataTable(dtHtml);
// Output to EA
repository.RunModelSearch("", "", "", xml);
linqPad.Show();
break;
// run LINQ XML query for own EA queries which are stored in *.xml
case MenuShowRunLinqXml:
// Make DataTable with LINQ Search/Query
dt = EaSearches();
// Make
xml = Xml.MakeXmlFromDataTable(dt);
// Output to EA
repository.RunModelSearch("", "", "", xml);
//linqPad.Show();
break;
case MenuShowShowLinqPadConnections:
linqQueryFileName = "LinqPadConnections.linq";
linqQueryFilePath = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + @"\" + linqQueryFileName;
linqPad = new LinqPad(repository);
// Output as html with read back table and out put to EA Search Window, don't use a connection
result = linqPad.Run(linqQueryFilePath, @"html", linqPad.GetArg(repository, ""), noConnection: true);
if (!result)
{
return;
}
dtHtml = linqPad.ReadHtml();
// Make EA xml
xml = Xml.MakeXmlFromDataTable(dtHtml);
// Output to EA
repository.RunModelSearch("", "", "", xml);
linqPad.Show();
break;
// run LINQ XML query for own EA queries which are stored in *.xml
case MenuAbout:
// get product version
Assembly assembly = Assembly.GetExecutingAssembly();
FileVersionInfo fileVersionInfo = FileVersionInfo.GetVersionInfo(assembly.Location);
// Get file-version of dll
string pathRoot = Assembly.GetExecutingAssembly().Location;
pathRoot = Path.GetDirectoryName(pathRoot);
string productVersion = $"{fileVersionInfo.ProductVersion}{Environment.NewLine}";
string pathAddInSimpleVersion = Path.Combine(new[] { pathRoot, "AddInSimple.dll" });
string pathHoLinqToSql = Path.Combine(new[] { pathRoot, "hoLinqToSql.dll" });
string pathLinq2Db = Path.Combine(new[] { pathRoot, "linq2db.dll" });
MessageBox.Show($@"Product version: {productVersion}
AddInSimple.dll {FileVersionInfo.GetVersionInfo(pathAddInSimpleVersion).FileVersion}
hoLinqToSql.dll {FileVersionInfo.GetVersionInfo(pathHoLinqToSql).FileVersion}
linq2db.dll {FileVersionInfo.GetVersionInfo(pathLinq2Db).FileVersion}
hoTools
[email protected]
+49 172 51 79 16 7
", @"AddInSimple, the example Add-In");
break;
}
}