VSIX Get query results in extension for SQL Server Management Studio

user_2972 0
Viewed 556

I want to visualize the query results in SQL Server Management Studio.

For that I need access to the data of a query result (the underlying data, that is being displayed).

I have created an extension for SSMS, using this guide: https://stackoverflow.com/a/55661807/2972

But, the documentation for extension development is quite poor. I have tried to discover classes in some Interop namespaces, e.g.: https://docs.microsoft.com/en-us/dotnet/api/microsoft.visualstudio.textmanager.interop?view=visualstudiosdk-2019

But didn't find something useful.

How do I get a reference to the query results themselves in SSMS from a Command extension?

And in general, how do I discover which "service type" is behind an "element" of the IDE?

visual-studio-extensions vsix ssms visual-studio
2 Answers

In case someone else wants to do this, it can be done in a rather hacky way, by reading private fields etc.

My code is here: https://github.com/martinnormark/DataDive/blob/f1faa0621a7438cbe4f8d8b3455f7b3a16635be6/src/DataDive/FacetsDiveCommand.cs#L136-L192

Copied here for reference:

var objType = ServiceCache.ScriptFactory.GetType();
var method1 = objType.GetMethod("GetCurrentlyActiveFrameDocView", BindingFlags.NonPublic | BindingFlags.Instance);
var Result = method1.Invoke(ServiceCache.ScriptFactory, new object[] { ServiceCache.VSMonitorSelection, false, null });


var objType2 = Result.GetType();
var field = objType2.GetField("m_sqlResultsControl", BindingFlags.NonPublic | BindingFlags.Instance);
var SQLResultsControl = field.GetValue(Result);


var m_gridResultsPage = GetNonPublicField(SQLResultsControl, "m_gridResultsPage");
CollectionBase gridContainers = GetNonPublicField(m_gridResultsPage, "m_gridContainers") as CollectionBase;


foreach (var gridContainer in gridContainers)
{
    var grid = GetNonPublicField(gridContainer, "m_grid") as GridControl;
    var gridStorage = grid.GridStorage;
    var schemaTable = GetNonPublicField(gridStorage, "m_schemaTable") as DataTable;


    var data = new DataTable();


    for (long i = 0; i < gridStorage.NumRows(); i++)
    {
        var rowItems = new List<object>();


        for (int c = 0; c < schemaTable.Rows.Count; c++)
        {
            var columnName = schemaTable.Rows[c][0].ToString();
            var columnType = schemaTable.Rows[c][12] as Type;


            if (!data.Columns.Contains(columnName))
            {
                data.Columns.Add(columnName, columnType);
            }


            var cellData = gridStorage.GetCellDataAsString(i, c + 1);


            if (cellData == "NULL")
            {
                rowItems.Add(null);


                continue;
            }


            if (columnType == typeof(bool))
            {
                cellData = cellData == "0" ? "False" : "True";
            }


            Console.WriteLine($"Parsing {columnName} with '{cellData}'");
            var typedValue = Convert.ChangeType(cellData, columnType, CultureInfo.InvariantCulture);


            rowItems.Add(typedValue);
        }


        data.Rows.Add(rowItems.ToArray());
    }


    data.AcceptChanges();
}

public object GetNonPublicField(object obj, string field)
{
    FieldInfo f = obj.GetType().GetField(field, BindingFlags.NonPublic | BindingFlags.Instance);

    return f.GetValue(obj);
}
user_29720

In fact, SSMS vs ide have some similarities, for example, they use part of the same menu control.

If you want to create a menu command on ssms, you could refer to this document about GUIDs and IDs.

Also, refer to this document about how to create a menu.

Besides, the SSMS sdk apis are here:

user_124507470
Related