“SQL Compact Toolbox” Visual Studio 2010 add-in beta available

I have just published the beta of a new Visual Studio (Professional or higher) add-in, that provides a number of useful tools for SQL Compact developers, directly from the Visual Studio environment:

Scripting of schema and data, ad-hoc query editing and execution, database relationship diagrams, migration/import/export and more features to come in subsequent releases.

After investigating the difficulty of creating a Visual Studio add-in, I stumbled upon the SQL Azure Explorer add-in on CodePlex. This gave me hope that creating a Visual Studio add-in could actually be done within a finite and limited amount of effort and time. This project showcased how to create a Toolwindow, and hook this up with a treeview complete with context menus and even a SQL free-form editor. The add-in uses the new VSIX package format, which greatly simplifies end-user management of extensions to the Visual Studio IDE

To start creating your own add-ins, download the Visual Studio 2010 SDK, and go through the walkthrough on MSDN.

The add-in gets it’s data repository and script generation code on the “API” available in my ExportSqlCe CodePlex project. In the latest source for this project, the “scripting framework” is available a a single DLL file, SqlCeScripting.dll.

The add-in bases it’s SQL Server Compact database connections on the Data Connections that you have defined in the Server Explorer. Below is the code to return a list of SQL Server Compact Data Connection names and related connection strings, based on the connections in Server Explorer:

using Microsoft.VisualStudio.Data;
using Microsoft.VisualStudio.Data.AdoDotNet;
using Microsoft.VisualStudio.Data.Interop;

internal static Dictionary<string, string> GetDataConnections(SqlCe35ToolboxPackage package)
{
    // http://www.mztools.com/articles/2007/MZ2007018.aspx
    Dictionary<string, string> databaseList = new Dictionary<string, string>();

    var objIVsDataConnectionsService = package.GetServiceHelper(typeof(IVsDataConnectionsService)) as IVsDataConnectionsService;
    var objIVsDataConnectionManager = package.GetServiceHelper(typeof(IVsDataConnectionManager)) as IVsDataConnectionManager;
    if (objIVsDataConnectionsService != null)
    {
        for (int i = 0; i < objIVsDataConnectionsService.Count; i++)
        {
            var objProviderGuid = objIVsDataConnectionsService.GetProvider(i);
            if (objProviderGuid == package.SqlCompact35Provider)
            {
                var sConnectionName = objIVsDataConnectionsService.GetConnectionName(i);
                var sConnectionString = objIVsDataConnectionsService.GetConnectionString(i);
                var objIVsDataConnection = objIVsDataConnectionManager.GetDataConnection(objProviderGuid, sConnectionString, true);
                var conn = (DataConnection)objIVsDataConnection;
                var adoConn = (AdoDotNetConnectionSupport)conn.ConnectionSupport;
                //TODO test for Mobile Connection (using Microsoft.SqlServerCe.Client)
                //Hack to get the underlying connection string including password
                var ceconn = (System.Data.Common.DbConnection)adoConn.ProviderObject;
                databaseList.Add(ceconn.ConnectionString, sConnectionName);
            }
        }
    }
    return databaseList;
}

//In the package class:

public object GetServiceHelper(Type type)
{
    return GetService(type);
}

In addition to the script related features, the add-in allows you to create DGML documents, that visualize the table relationships of your database (see sample in my earlier blog post).

The following code shows how to open the DGML file from add-in code:

var dte = package.GetServiceHelper(typeof(EnvDTE.DTE)) as EnvDTE.DTE;

using (IRepository repository = new DBRepository(databaseInfo.Connectionstring))
{
    Generator generator = new Generator(repository, fileName);
    generator.GenerateSchemaGraph();
    dte.ItemOperations.OpenFile(fileName);
    dte.ActiveDocument.Activate();
}

Here are a few screenshots:

 

The table context menu, and the simple query editor.

 

The database context menu.

 

The root context menu, with SQL Server related menu items.

Please try out the beta, and provide feedback of any kind.