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

After more than 415.000 downloads, version 4.3 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 CodePlex issue tracker

This new version was released in July at the time that Visual Studio 2015 went live, and is of tested with Visual Studio 2015 – keep in mind that all extensions now work with the free Visual Studio Community Edition. This release has a couple of new features that improves on the SQLite support originally added in version 4.0

Scan solution for SQLite database files

image

The “Add Connections from Solution” feature has been enhanced to scan all projects in the current solution for any SQL Server Compact and SQLite files. If any are found (based on the file patterns defined in Options), the Toolbox will attempt to add them as new connections.

The current file patterns are defined as shown below, but you can change them to anything you like:

image

Rename Connection name

The names of connections currently defaults to simply the file name of the database file. This becomes an issue, if you have many same named files in different folders connected. Therefore, you can now rename the Connection name:

image 

SQLite-net DataAccess.cs improvements

The SQLIite-net code generation feature has received a couple of small updates based on user feedback:

image

The generated classes will also contain any views defined in the SQLite database, and nullable values will be mapped accordingly (for example int?).

NULL value display

image

NULL values are now displayed as ‘NULL’ in Query results text and grid. Remember that you can see query results both as text and in a grid, the default is text for performance reasons, but this can easily be changed in Options:

image

Also notice that if you prefer the previous behaviour, this can be changed here, via the ShowNullValuesAsNULL option.

Other improvements

Unique indexes now always scripted as UNIQUE constraints (a long standing omission, which hit me when I was creating the reverse engineering for the SQL Server Compact providers for Entity Framework 7)

As usual, any feedback and comments are welcome, and if you like my free tools, I am always very grateful for a review on Visual Studio Gallery

“SQL Server Compact & SQLite Toolbox” related news

This blog post contains three bits of news, that all relate to my SQL Server Compact / SQLite Toolbox Visual Studio add-in

Visual Studio 2013 Community Edition

Microsoft recently released Visual Studio 2013 Community, a free, full featured edition of Visual Studio, has essentially all features found in VS Professional, expect some Office/SharePoint project templates, and is free for many scenarios (please check the licensing requirements). Previously, users of the SQL Server Compact Toolbox with VS Express had to use the standalone edition of the Toolbox. If you replace your Express edition with Community, this is no longer the case, as it supports all VSIX extensions from the VS Gallery / Extensions and Updates dialog in Visual Studio. A very bold move from Microsoft, and a major  boost for open source/hobby developers. If you have any questions about this VS edition, start by reading the Q&A here

image

Visual Studio Auto Updater

Mads Kristensen, a Microsoft Program Manager (and fellow Dane), recently released the “one extension to rule them all”, the Visual Studio Auto Updater extension. It allows you to specify which of your Visual Studio extensions you want to automatically update when a new version is released. I have submitted a pull request to have the SQL Server Compact / SQLite Toolbox included in the list of extensions that are always updated. Highly recommended, ensures that you Visual Studio installation is always fresh.

image

SQLite Toolbox on the Channel 9 “Visual Studio Toolbox” show

In early November last year I had the opportunity to appear on the popular Channel 9 show “Visual Studio Toolbox” hosted by Robert Green. In the show, I present the new SQLite support in the Toolbox. A good intro if you want to get started using the Toolbox.

VSToolbox2

Version 4.2 preview

The next release of the Toolbox, version 4.2, is currently available in preview from CodePlex. The main focus for this release is a number of (overdue) improvements to the SQL query editor:

Editor2

1: The editor now has proper “file” handling, with Save and Save As buttons, and the saved file name appearing in the tab caption.

2: Keyboard shortcuts have been enabled, allowing you to use F5 to execute queries, and use Ctrl+O to open scripts, and Ctrl+S to save scripts.

3: A button to export the current result as CSV (Excel) format has been added.

Try out the preview, and let me know if you have any suggestions or find any issues via the CodePlex issue tracker.

SQL Server Compact Toolbox 3.7.3 – Visual Guide of new features

After nearly 290.000 downloads, version 3.7.3 of my SQL Server Compact Toolbox extension for Visual Studio  “14”, 2013, 2012 and 2010 is now available for download. This blog post is a visual guide to the new features included in this release, many suggested by users of the tool via the CodePlex issue tracker.

New features

Export SQL Server database to SQLite script (beta)

This feature enables you to create a script of all tables and data in an existing SQL Server database, that can be run using the SQLite3.exe tool as described in my blog post here, allowing you to quickly migrate to SQLite, for example for use in Windows Store/Phone Apps.

SQLIteExport

“Set Password” option added to Maintenance menu

This feature enables you to set or change the password of an existing database.

setpw

Enable entry of multiline text in DataGridView

This feature allows you to enter multi line text in the data edit grid, by pressing Shift+Enter to get to a new line.

Support for VS “14” CTP

Support for VS “14” was actually already enabled, but there was a minor issue with registration of the simple DDEX providers, that has now been fixed.

UPDATE: Microsoft has blocked add-ins that claim to support VS 14 from VS 2013, so in the meantime you can download a build for VS “14” from here:

https://sqlcetoolbox.codeplex.com/releases/view/123666

image

 

Bug Fixes

Result Grid column headers were displayed without the first _ (underscore) character if they had one

image

Download Count now fetched async, so the About dialog opens faster.

image

Add Column – defaults to Allow Null

image

“Migrate” and “Export” features broken for large databases with multiple script files

image

image

Entity Framework ”reboot” – EF7 – Get a sneak peek via TechEd US live stream

The next version of Entity Framework has just been named “EF7” (code name EF EveryWhere). Before the presentation, which will be streamed live on Channel 9 during TechEd US on Wednesday May 14 at 1:30 PM CDT (Houston, Texas), allow me to summarize what we know so far about the next version of Entity Framework.

The Entity Framework team has already published some thoughts about the new Framework here, and based on this and other sources, we can summarize the following (some or maybe most of which is me guessing, of course):

– A completely new codebase, will not be based on the 1 million+ line codebase of EF6

– Will be open source, and accept pull requests and other community feedback

– Will support Windows Phone, Windows Store, Windows Desktop/Server/Cloud, and also support Mono/Xamarin platforms

– Will be based on a provider model, so SQL and NoSQL data sources can “plug in”. Will initially support SQLite on Phone and Store apps. Will also support SQL Server and Azure Table Storage

– Will only include a productive subset of the current, huge API – Code First Mappings, DbContext, POCO classes, and less mapping schemes. So current investments in DbContext, Code First/Second and POCO will be forward reusable.

– Will not contain ObjectContext, Entity SQL, EntityConnection, EntityCommand etc. Phew.

Sounds ambitious and great to me. Can’t wait to see some code and start playing!

UPDATE: The source code is now available on Github as part of ASP.NET vNext. And Rowan Miller, Program Manager on the EF team speaks about and demonstrates EF7 in this TechEd session available online.

“Shop Talk” with Eric Sink, Zumero for SQL Server founder

In my last blog post I introduced you to Zumero, an Enterprise solution, that allow you to replicate and sync SQL Server data across all your team’s mobile devices.

I had the pleasure of meeting up with Zumero founder, Eric Sink, @eric_sink at the recent SQL Saturday event in Copenhagen.

image

We agreed to do a short interview by mail, so I could share my Zumero questions with my blog readers. So without further ado, let’s get started.

I know you have been involved with other software products (SourceGear). What is the story behind Zumero?

We think of Zumero as a new start-up that is incubating inside SourceGear, so there is much shared history.

We started SourceGear in 1997, initially as a contracting shop building custom software for mobile devices.  We did a lot of work with Blackberry devices and some very early Motorola smart phones.

Then we got into version control tools, which has been a great business for us over the last 15 years.  We are still serving a large customer base with on-going active development of SourceGear Vault.

But in the last few years, the world of mobile has been drawing us back in.  🙂

And from a certain point of view, our work on version control means that we have been doing “sync” for 15 years.

And SourceGear Vault has always been built on SQL Server.

So that’s how we got the three main ingredients of Zumero:  SQL Server, mobile, and sync.

Some of the features that Merge Replication provide are: – Minimal client code, secure, compressed transfer over https, server side filtering, read-only tables, conflict handling. How does Zumero stack up?

I /think/ we stack up pretty well, although we still have many ideas for how to make our product better.

“minimal client code”  — The Zumero Client SDK tries to make everything easy.  Just call the Zumero sync function and Zumero will synchronize changes in both directions.

“secure” — Zumero is designed to work over SSL/https.  The protocol includes authentication.  And on the client, Zumero is compatible with bulk encryption libraries such as SQLCipher.

“compressed transfer over https” — All packages exchanged between Zumero client and server are compressed.  In fact, the packages from server to client are often “deltified” to make them very small indeed.

“server side filtering” — Zumero does support the ability to exclude tables, or columns from tables, or to filter rows on a custom WHERE clause.

“read only tables” — This can be accomplished with Zumero’s authentication and permissions.

“conflict handling” — Zumero handle conflict resolution on the server.  The default rules seem to be appropriate for most cases, but those rules can be customized to do whatever is needed for a given situation.

In addition, Zumero adds a few things that SQL CE merge replication does not, most notably, support for iOS and Android devices.

OTOH, Zumero is built on SQLite, which does not resemble SQL Server as closely as SQL CE does.  We are constantly working to provide Zumero customers with a smooth experience by improving features, tooling and documentation.  My blog series on the differences between SQL Server and SQLite is a part of those efforts:

Rob Tiffany has demonstrated scaling Merge Replication. How does Zumero scale?

This is one area where Zumero’s youth is evident, and I shall not pretend.  There is much more experiential knowledge about scaling with merge replication.  Rob Tiffany’s “cheat sheet” is a gold mine of information:

And some of his advice would be applicable to Zumero as well.

However, we are quite happy with the scalability results we have achieved so far and we continue to push further.

(a)  We do a lot of “crowd testing”, using lots of clients to abuse the Zumero server so we can tune it for reliability and performance.

(b) We are currently working with some customers who are integrating Zumero into an environment where merge replication is already in use (as a scaling solution for the SQL Server backend, not as a mobile sync solution).  We have some additional testing to do here, but we hope to able to publish some guidelines about the compatibility of Zumero with this kind of situation.

(c) Finally, we have tested and verified another way of scaling out, by having multiple Zumero servers talking to a single instance of SQL Server.  This can make a significant difference and is not difficult to set up behind a round robin DNS.

You are using Triggers and tracking tables on SQL Server, and not the built-in Change Tracking. Why?

The real reason is that Zumero’s core sync code existed before we made the decision to make SQL Server the primary focus of the product.

That said, the documentation for Change Tracking raises questions for us.  We wonder if it would require changes to our sync algorithm.  We wonder if our support for tracking schema changes would still work.  We wonder about the apparent dependence on snapshot isolation.

We may explore this further in the future, but right now, we’ve got a solution which is robust, so we don’t feel much pressure to change it.

Do you provide a Windows Mobile (.NET Compact Framework) client library?

Currently, no, but we are planning to do so.  The implementation work for Windows Mobile 6 support is done, but it has not yet been released as part of the product.  If a Zumero customer needs this, we would be ready to discuss and coordinate making it available.

What do you recommend for use as data access API towards SQLite on Windows platforms?

Right now, there is no API we can recommend without caveats.  Or rather, there are several good choices, but no clear winner.

For desktop Windows only, the core SQLite team products System.Data.SQLite, an ADO.NET provider which is fully compatible with EF6.  If you’re not using mobile, this is almost certainly the best choice.

Frank Krueger’s sqlite-net wrapper is quite popular, and for some very good reasons.  But last I knew, none of the various PCL forks have been folded back in, and that has been an issue for some.  Nonetheless, this is the wrapper we encounter most often.

WinRT without System.Data is a story with a big hole in it.  We remain hopeful that Microsoft has some more good stuff in the pipeline.  

Looking beyond Windows to include iOS and Android as well, things can get complicated.  Both of these other platforms include a [different] version of SQLite as part of the mobile OS.  Many apps use those.  Some bundle their own.  Some replace SQLite with SQLCipher.  Things can get tricky.  In addressing this set of problems with our customers, we’ve built some stuff that we are planning to make available to the community as open source (to be announced on my blog, soon, I hope). [Ed: Available as source code on Github, and NuGet packages coming soon]

SQL Server Compact Toolbox 3.7.1–Visual Guide of new features

After 250.000 (!) downloads, version 3.7.1 of my SQL Server Compact Toolbox extension for Visual Studio 2013, 2012 and 2010 is now available for download (and available via Tools/Extensions in Visual Studio). This blog post is a visual guide to the few new features included in this minor release, which all centre around Server Explorer (DDEX) , including support for Visual Studio 2012 and 2013.

 

Simple DDEX providers for Visual Studio 2012 and 2013

image

I already blogged about this feature here, it has been extended to also support SQL Compact 3.5. Follow these steps to use the providers with Entity Framework Tools:

– Install this version of the toolbox and launch Visual Studio 2012/2013.
– Open the Toolbox (this will silently run the “installation” of the DDEX provider if required).
Restart VS 2012/2013.
– Add new Toolbox connection to a new or existing SQL Server Compact 4.0 or 3.5 database file.
– Add EntityFramework.SqlServerCompact or EntityFramework.SqlServerCompact.Legacy NuGet package to project and build project.
– You can now use this connection for Generate Model from Database, Generate Database from Model and Update Model from Database, and with the Power Tools.

With this release, the DDEX support matrix looks like this:

  SQL Server Compact 3.5 SQL Server Compact 4.0
Visual Studio 2010 Microsoft None
Visual Studio 2010 SP1 + SQL CE 4.0 Tools Microsoft Microsoft
Visual Studio 2012 ErikEJ Microsoft
Visual Studio 2013 ErikEJ ErikEJ

“Microsoft” means full featured, Microsoft supplied provider
ErikEJ” means simple, basic support, enough to enable Entity Framework Tools, provided by this version of SQL Server Compact Toolbox.

Currently, the 3.5 DDEX provider does not fully work with the Entity Framework Tools, due to a bug (my bug!) in the EntityFramework.SqlServerCompact.Legacy Entity Framework provider; I am working on getting it fixed.

Scan Solution for sdf files

This new menu item will scan the active Solution for any sdf files, and add those to the Toolbox:

image

Prefer DDEX

image

This new option allows you to always use the Toolbox supplied dialogs for connecting to a SQL Server Compact database file, which will allow you to connect to any file, and does not require an .sdf file type. (By choosing not to prefer!)

Bug fixes and enhancements

Add connection will now always use DDEX provider if available
Handle apostrophe in file and folder names when saving connections
Fixed error: The specified table does not exist. (Databases)
Make it clearer that sqlmetal dependent features requires 3.5 to be installed

Please report any issues you encounter and provide feature suggestions and requests here.

Getting started with SQLite in Windows Store / WinRT apps

In this blog post I will expand the blog post by Tim Heuer  to include information on how to include and access a pre-populated SQLite database file, maybe even a file created by migrating from a SQL Server Compact database file, as I blogged about recently.

First, download the “SQLite for Windows Runtime” Extension via Tools/Extensions and Updates/Online. Restart Visual Studio.

Then add references to the SQLite and C++ extensions as described by Tim Heuer. Remember to change the Build Configuration to either x64 or x86 in Configuration Manager.

Now add the sqlite-net nuget package to the project, from the References node, select “Manage NuGet Packages” and search online for “sqlite-net”:

clip_image002

This will add SQLite.cs and SQLiteAsync.cs to your project.

Now add the SQLite database file to your project as Content:

+clip_image003

If you want the database file to be writeable, you will have to copy it to your local appdata folder. Keep in mind, that when your app is uninstalled, the file will be removed.

You can use code like the following to ensure that the file has been copied:

private string dbName = "chinook.db";
private async void LoadData()
{
await CreateIfNotExists(dbName);
}
private async Task CreateIfNotExists(string dbName)
{
if (await GetIfFileExistsAsync(dbName) == null)
{
StorageFile seedFile = await StorageFile.GetFileFromPathAsync(
Path.Combine(Windows.ApplicationModel.Package.Current.InstalledLocation.Path,
dbName));
await seedFile.CopyAsync(Windows.Storage.ApplicationData.Current.LocalFolder);
}
}

private async Task<StorageFile> GetIfFileExistsAsync(string key)
{
try
{
return await ApplicationData.Current.LocalFolder.GetFileAsync(key);
}
catch (FileNotFoundException) { return default(StorageFile); }
}

And code like this to access data (see the sqlit-net site for more samples) https://github.com/praeclarum/sqlite-net

protected override void OnNavigatedTo(NavigationEventArgs e)
{
var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, dbName);
using (var db = new SQLite.SQLiteConnection(dbPath))
{
var list = db.Table<Artist>().OrderBy(a => a.Name).ToList();
}
}
//This would reside in another file or even project
public class Artist
{
[SQLite.PrimaryKey, SQLite.AutoIncrement]
public int ArtistID { get; set; }
public string Name { get; set; }
}

public class Album
{
[SQLite.PrimaryKey, SQLite.AutoIncrement]
public int AlbumID { get; set; }
public string Name { get; set; }
public int ArtistID { get; set; }
}

clip_image004

Hope this will be able to get you started using SQLite with your Windows Store app.

You can download the complete sample with a database file from this link (all code above is in MainPage.xaml.cs): http://sdrv.ms/Pd1xeL