/// <summary>Implements the Exec method of the IDTCommandTarget interface. This is called when the command is invoked.</summary>
/// <param term='commandName'>The name of the command to execute.</param>
/// <param term='executeOption'>Describes how the command should be run.</param>
/// <param term='varIn'>Parameters passed from the caller to the command handler.</param>
/// <param term='varOut'>Parameters passed from the command handler to the caller.</param>
/// <param term='handled'>Informs the caller if the command was handled or not.</param>
/// <seealso class='Exec' />
public void Exec(string commandName, vsCommandExecOption executeOption, ref object varIn, ref object varOut, ref bool handled)
{
handled = false;
if (executeOption == vsCommandExecOption.vsCommandExecOptionDoDefault)
{
DTE2 dte = ServiceCache.ExtensibilityModel.DTE as DTE2;
if (commandName == "MyAddin1.Connect.MyAddin1")
{
Document activeDocument = null;
IScriptFactory scriptFactory = ServiceCache.ScriptFactory;
if (scriptFactory != null)
{
scriptFactory.CreateNewBlankScript(ScriptType.Sql);
activeDocument = dte.ActiveDocument;
}
if (activeDocument != null)
{
TextSelection ts = activeDocument.Selection as TextSelection;
ts.Insert("This Query Window was created with our TestAddin.", (int)vsInsertFlags.vsInsertFlagsInsertAtStart);
}
handled = true;
}
if (commandName == "MyAddin1.Connect.MyAddin1ToolWindowForm")
{
Windows2 MyWindow = (Windows2)dte.Windows;
Assembly asm = System.Reflection.Assembly.GetExecutingAssembly();
object MyControl = null;
Window toolWindow = MyWindow.CreateToolWindow2(_addInInstance, asm.Location, "MyAddin1.MyAddinWindow", "MyAddin1.MyAddin1ToolWindowForm", "{5B7F8C1C-65B9-2aca-1Ac3-12AcBbAF21d5}", MyControl);
toolWindow.Visible = true;
handled = true;
/*Assembly a = Assembly.GetExecutingAssembly();
* object controlObject = null;
*
* Windows2 toolWindows = dte.Windows as Windows2;
* Window2 toolWindow;
*
*
* toolWindow = (Window2)toolWindows.CreateToolWindow2(_addInInstance,a.Location, "MyAddin1.MyAddin1ToolWindowForm ", "", Guid.NewGuid().ToString(), ref controlObject);
*
* toolWindow.WindowState = vsWindowState.vsWindowStateNormal;
* toolWindow.IsFloating = false;
* toolWindow.Visible = true;
*
* handled = true;*/
}
else
{
String s = varIn.ToString();
string s2 = varOut.ToString();
}
}
}
public void CreateNewScript(string content)
{
Document activeDocument = null;
_scriptFactory.CreateNewBlankScript(ScriptType.Sql);
var dte = _dte.DTE as DTE2;
if (dte != null)
{
activeDocument = dte.ActiveDocument;
}
if (activeDocument != null)
{
var ts = activeDocument.Selection as TextSelection;
if (ts != null)
{
ts.Insert(content, (int)vsInsertFlags.vsInsertFlagsCollapseToStart);
}
}
}
/// <summary>Implements the Exec method of the IDTCommandTarget interface. This is called when the command is invoked.</summary>
/// <param term='commandName'>The name of the command to execute.</param>
/// <param term='executeOption'>Describes how the command should be run.</param>
/// <param term='varIn'>Parameters passed from the caller to the command handler.</param>
/// <param term='varOut'>Parameters passed from the command handler to the caller.</param>
/// <param term='handled'>Informs the caller if the command was handled or not.</param>
/// <seealso class='Exec' />
public void Exec(string commandName, vsCommandExecOption executeOption, ref object varIn, ref object varOut, ref bool handled)
{
handled = false;
if (executeOption == vsCommandExecOption.vsCommandExecOptionDoDefault)
{
//ext.Documents.Add();
//ext.Documents.Add(ext.ActiveDocument.Kind.ToString().Replace("{", "").Replace("}", ""));
//ext.WindowConfigurations.Add("test window");
//ext.ExecuteCommand("QueryDesigner.Select");
//ext.ExecuteCommand("QueryDesigner.SQL");
//ext.ExecuteCommand("QueryDesigner.ExecuteSQL");
if (commandName == "SSMSAddin.Connect.ViewReferencedValue")
{
var sqlParser = new SimpleSqlParser();
var ext = ((DTE2)ServiceCache.ExtensibilityModel);
IScriptFactory scriptFactory = ServiceCache.ScriptFactory;
var window = (Window)ext.ActiveWindow;
var scriptWindow = _windowExtensions.ContainsKey(window) ? _windowExtensions[window] : null;
var values = scriptWindow?.GetSelectedValues();
foreach (var val in values.Where(v => v?.Values?.Count > 0))
{
var s = scriptFactory.CreateNewBlankScript(ScriptType.Sql, scriptFactory.CurrentlyActiveWndConnectionInfo.UIConnectionInfo, null);
Document document = ext.ActiveDocument;
if (document != null)
{
var sql = scriptWindow.GetQuery();
var table = sqlParser.GetTableFromSql(sql, val.ColumnName);
var split = table.Split('.');
var schema = "dbo";
if (split.Length == 3)
{
schema = split[1].Replace("[", "").Replace("]", "");
table = split[2].Replace("[", "").Replace("]", "");
}
else if (split.Length == 2)
{
schema = split[0].Replace("[", "").Replace("]", "");
table = split[1].Replace("[", "").Replace("]", "");
}
else if (split.Length == 1)
{
table = split[0].Replace("[", "").Replace("]", "");
}
var sqlToGetReferences = $@"
SELECT S2.name AS SchemaName, T2.name AS TableName, C2.name AS ColumnName
FROM sys.tables T
JOIN sys.schemas S ON S.schema_id = T.schema_id
JOIN sys.columns C ON C.object_id = T.object_id
JOIN sys.foreign_key_columns FK ON FK.parent_object_id = T.object_id AND FK.parent_column_id = C.column_id
JOIN sys.tables T2 ON T2.object_id = FK.referenced_object_id
JOIN sys.columns C2 ON C2.object_id = T2.object_id AND C2.column_id = FK.referenced_column_id
JOIN sys.schemas S2 ON S2.schema_id = T2.schema_id
WHERE T.name = '{table}' AND S.name = '{schema}' AND C.name = '{val.ColumnName}'
";
var conInfo = scriptFactory.CurrentlyActiveWndConnectionInfo.UIConnectionInfo;
SqlCommand cmd = new SqlCommand(sqlToGetReferences, new SqlConnection($"Server={conInfo.ServerNameNoDot};Database={conInfo.AdvancedOptions["DATABASE"]};Trusted_Connection=True;"));
cmd.Connection.Open();
var referencedTable = string.Empty;
var referencedSchema = string.Empty;
var referencedColumn = string.Empty;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
referencedSchema = (string)reader["SchemaName"];
referencedTable = (string)reader["TableName"];
referencedColumn = (string)reader["ColumnName"];
}
}
cmd.Connection.Close();
//replace currently selected text
if (!string.IsNullOrEmpty(referencedSchema) && !string.IsNullOrEmpty(referencedTable) && !string.IsNullOrEmpty(referencedColumn))
{
TextSelection selection = (TextSelection)document.Selection;
selection.Insert($"SELECT * FROM [{referencedSchema}].[{referencedTable}] WHERE [{referencedColumn}] {val?.WhereClause()}", (Int32)EnvDTE.vsInsertFlags.vsInsertFlagsContainNewText);
ext.ExecuteCommand("Query.Execute");
}
}
}
handled = true;
return;
}
}
}
