SQL Server Compact & SQLite Toolbox 4.4 & 4.5 – Visual Guide of new features

After more than 470.000 downloads, version 4.5 of my SQL Server Compact & SQLite Toolbox extension for Visual Studio 2010 and later is now available for download and available via Tools/Extensions and Updates in Visual Studio. This blog post is a visual guide to the new features, improvements and bug fixes included in this release, many suggested by users of the tool via the GitHub issue tracker

I have moved the source code and issue tracker to GitHub, and I am planning to move the remaining CodePlex content “soon”. I have also moved the source of the scripting API that the Toolbox uses extensively to the same GitHub repository. As always, and suggestion, bug reports and pull requests are welcome!

New icons

The icons of the extension have all been overhauled, and updated to use the latest VS 2015 icons/images. From users already trying out the latest bits, I have received positive feedback on this visual change.

Query editor toolbar redesign and more

I have also started using Azure AppInsights to track (anonymously) feature usage etc. This revealed (maybe not surprisingly) that the most use feature is the query editor. So for this release, the query editor has received a lot of attention. In addtion to the changes below, the general layout of the editor has been streamlined (no more rounded corners).

The first button is new, and will open a “New Qery” window against the same database file. An options dropdown have been added to expose some of the (maybe overlooked) options that are available for the query editor. Setting these only affects the current query window, use the general settings (which you can reach via the Setting button in the query editor now) to change the “default” value for a setting.

Lets have a quick look at each of the options:

“Show result in Grid”: By default, the query results (from a SELECT statement) will be displayed as text for performance reasons, but you can opt-in to have the results displayed in an Excel like grid instead.

“Show Binary Values in Result”: By default, binary values (from for example “image” or “rowversion” columns) will be displayed a “System.Byte[]”. Enabling this will instead display the actual value as a hexadecimal string – but may affect performance.

“Show null values as NULL”: Until recently, NULL values were displayed as an empty string, not as “NULL”. Disabling this reverts to the old behaviour.

“Ignore DDL errors”: Enabling this (per query window only) feature, allows you to run the database creation scripts generated by the Entity Framework “Generate Database from Model” feature, and ignore errors caused by for example pre-existing tables.

“Use classic (plain) grid”: This will revert to using the “old” grid for grid results – about the “new” grid, see below!

New result grid in query editor

A new grid that allows you to further drill into the results of a query without writing any SQL is now the “default” result grid in the query editor. The grid uses http://wpfextendeddatagrid.codeplex.com/

“Autofilter” :The grid has built in filters, in the example above to filter on invoices for a particular customer (or even set of customers).

“Grouping”: Add columns you want to group by to the surface above the grid. In the example above, Invoices are grouped by Customer Id.

Options now part of Visual Studio options dialog

The options for the Toolbox have finally been integrated in the standard Visual Studio Tools/Options pages.

Improved SQL Server support

SQL Server Connections defined in Server Explorer now appear in the Toolbox, in order to better expose the SQL Server releated features of the Toolbox:

And scripting of a SQLite schema (only) based on a SQL Server database is now possible:

Improvements

A number of smaller improvements are also included in this release:

Improve format of INSERT statements generated by CSV import (to make the more readable)
Added latest System.Data.SQLite version (1.0.99)
Added link to blog post to DataAccess.cs to better explain how to use it.
Support for row-returning SQLite statements beginning with "WITH"
Added "Feedback" button
Using Azure Application Insights for metrics and crash reports
Ability to remove password from password protected SQL Compact file (by leaving password box blank)
Progress messages during export now displayed in Output window
Enable scripting of hierarchyid as varbinary(892)
Now able to script Adventureworks2014 schema and data to both SQL Compact and SQLite without errors
Table Data script (INSERTs) now have "RESEED" command at end always
Option to Opt out of Telemetry added

Bug fixes

In addition to the above improvements and features, and number of bug fixes are included in this release:

API:
Fix bug in SQLMetal.exe detection (on some configurations)
SQLite CREATE TABLE datetime default should be current_timestamp, not GETDATE()
Improved SQLite datetime handling in SELECT and Edit in Grid

User Interface:
CSV export from the SQL editor included one separator too many in the heading
Editor keyboard bugs fixed
Scroll issues in treeview fixed
Autohide of Toolbox bug fixed

Table builder:
Table builder was not scripting default values
Table builder should not script default value for IDENTITY columns