Analyzing SQL Server Compact queries using Visual Studio 2010 Premium/Ultimate

f you are the happy owner of Visual Studio 2010 Premium or Ultimate, there is a hidden tool that allows you to run and analyze queries against SQL Server Compact 3.5 and 4.0 databases. (Support for 4.0 requires Visual Studio 2010 SP1 + the SQL Server Compact Tools update). This blog post will walk through how to access and use this “hidden” tool.

NOTE: If you only have Visual Studio Professional, you can use my SQL Server Compact Toolbox in combination with the free SQL Server 2008 R2 Management Studio Express to perform similar query analysis.

To access the tool, go to the Data menu, and select Transact-SQL Editor, New Query Connection… (The tool is part of the so-called “Data Dude” features)

In the Connect to Server dialog, select SQL Server Compact:

You can select an existing database, or even create a new one. This dialog will automatically detect if the specified file is a version 3.5 or 4.0 file.

Once connected, you can perform functions similar to what you may know from SQL Server Management Studio:

SQL Server Compact Toolbox available for Visual Studio 11

Visual Studio 11 Developer Preview is now available for testing. As one of the first third party add-ins, a build of the SQL Server Compact Toolbox version 2.4 that supports this Visual Studio Preview version is available via Extension Manager or in the Visual Studio Gallery.

In order to add support for Visual Studio version 11 in an existing add-in, all you need to do is modify the source.extension.vsixmanifest file as shown below:

    <SupportedProducts>
      <VisualStudio Version="10.0">
        <Edition>Pro</Edition>
      </VisualStudio>
      <VisualStudio Version="11.0">
        <Edition>Pro</Edition>
      </VisualStudio>
    </SupportedProducts>

The result of this change is that the add-in can now be installed for several versions of Visual Studio.

I have had to make some changes, as the Toolbox currently depends on SQL Server Compact 3.5 SP2 to store it’s connections, and only SQL Server Compact 4.0 is included with Visual Studio 11. In the Developer Preview the version of SQL Server Compact included is the 4.0 RTM version, so no changes there for now.

To detect which version of Visual Studio you are running, you can use the following code in your Package.cs class:

public Version VisualStudioVersion
{
    get
    {
        var dte = this.GetServiceHelper(typeof(EnvDTE.DTE)) as EnvDTE.DTE;
        string root = dte.RegistryRoot;

        if (root.Contains("10.0"))
        {
            return new Version(10, 0);
        }
        else if (root.Contains("11.0"))
        {
            return new Version(11, 0);
        }
        else
        {
            return new Version(0, 0);
        }
    }
    set
    {
        this.VisualStudioVersion = value;
    }
}

I am currently not bringing forward any 4.0 connections defined in the VS 2010 edition of the add-in. Please let me know if a feature to import these connections to the VS 11 Server Explorer would be useful.

Also, would it be of interest to be able to manage 3.5 databases in VS 11, even though they are not supported in Server Explorer?

As always, please provide any feedback in the comments or via the Codeplex issue tracker.

SQL Server Compact Toolbox 2.4–Visual Guide of new features

After more than 50.000 downloads, version 2.4 of my SQL Server Compact Toolbox extension for Visual Studio 2010 is now available for download. This blog post is a visual guide to the new features included in this release.

Edit Table Data (beta)

The tools that are included with Server Explorer for SQL Server Compact 3.5 and 4.0 already include a feature to edit table data, called Show Table Data:

But the grid has some limitations that I have lifted on the new “Edit Table Data” feature:

- Ability to sort data by clicking a column heading

- Ability to Import/Export/Delete content of image columns

- Ability to locate data in a column (QuickSearch)

Split Windows Phone DataContext into multiple files

You can now select to have the Windows Phone DataContext generated as a DataContext class file, and a class file per table in your database.

Select tables to include in Entity Data Model

You can now select which tables to include in the Entity Data Model created from your SQL Server Compact database. (Thanks to the Extended WPF Toolkit Project)

Explore and script primary and foreign keys

The tree view now lists the primary and foreign keys belonging to a table, and it is also possible to script these individually.

Other fixes

Improved Add-in Update detection (for users behind proxies)
Improved handling of password protected files for non-English SQL Compact runtime
Some icons were not transparent
CreateDataIfExists (Windows Phone DataContext) now returns bool if database was created
Latest scripting library, with fix for missing SET IDENTITY INSERT with multiple files (from large tables)

Useful Windows Phone advice from Nick Randolph

Fellow MVP Nick Randolph (@BTRoam) publishes an excellent blog, Nick’s .NET Travels, often with articles that relate to SQL Server Compact and synchronization technologies, but also very useful articles for any Windows Phone developer, with a practical, hands-on approach. Highly recommended.

He has recently published the following articles:

Windows Phone LINQ to SQL and the INotifyPropertyChanged and INotifyPropertyChanging Interfaces

This article demonstrates the importance of implementing the INotifyPropertyChanging interface on your DataContext classes to improve memory management. As he points out, you should use SQLMetal to generate your DataContext, as this will avoid missing to implement these interfaces. Or even better use the SQL Server Compact Toolbox Visual Studio add-in, as it adds the following features on top of SQLMetal:

1. Removes unneeded/unsupported constructors

2. Adds any [Index] attributes to each table

3. Adds the CreateDatabaseIfExists method

4. Optionally splits the generated files into a file per table (in next version (2.4), currently available in beta)

Change Tracking with SQL Server Compact (LINQ to SQL) on Windows Phone

This article demonstrates how to get started using SQL Server Compact Change Tracking with a Windows Phone SQL Server Compact database, despite the fact that the Change Tracking APIs are not available on Windows Phone. He also gets thrown in how to use the Windows Phone SDK ISETool to move the database from the Phone (Emulator) to your local disk. Good stuff.

SqlCeBulkCopy, a library for fast SQL Server Compact INSERTS released

Version 2.1 of my SQL Server Compact Bulk Insert Library has now been released. This library exposes an API similar to the SqlBulkCopy API implemented for the SqlClient (working against SQL Server). The library allows you to quickly load data inot a SQL Server Compact database.

New features in this release include:

3 editions of the library:
One for .NET Compact Framework for version 3.5 databases - ErikEJ.SqlCe.NetCF.dll
One for full .NET Framework for version 3.5 databases - ErikEJ.SqlCe.dll
One for full .NET Framework for version 4.0 databases - ErikEJ.SqlCe40.dll

- New overloads of the WriteToServer method allows you to load any .NET list that implements IEnumerable or IEnumerable<T>

- API Documentation is now available in HTML format here. The API documentation was created very easily using the open source ImmDoc.NET utility. All this command line utility requires is XML comments file and the DLL file(s).

- NuGet package available

A NuGet package, that includes the SQL Server Compact 4.0 library is now available via the NuGet Package Manager.

If you need to load data fast from a DataTable, DataReader or .NET List to a SQL Server Compact database file, this is the library for you.