Tuesday, August 28, 2012

SQL Server Compact Toolbox 3.1–Visual Guide of new features

After more than 130.000 downloads, version 3.1 of my SQL Server Compact Toolbox extension for Visual Studio 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

Export to SQLite

As I have blogged about earlier, the Toolbox now has a feature that enables migration to SQLite, for use for example for migrating Windows Phone solutions to Windows 8 Apps.

clip_image002

Upgrade a SQL Server Compact 3.5 database directly to 4.0

The Toolbox already supports scripting a 3.5 database and running the generated script against a version 4.0 database, but this release contains a feature that does a direct upgrade, using the SqlCeEngine Upgrade method. In addition, the new database will be connected to Server Explorer and the Toolbox automagically.

clip_image003

Option to ignore IDENTITY columns when scripting table data

Some users have requested the option to script table data without SET IDENTITY_INSERT statements, basically ignoring the IDENTITY column. This is now possible via a new user option:

clip_image004

New SQL Server Connection dialog

When connecting to SQL Server to generate scripts, the dialog used in previous version of the Toolbox only worked with Integrated Security, not connections using SQL Server authentication. This is the new dialog:

clip_image005

Other improvements

Improved DataGrid results performance – when enabling showing query results in Grid, performance was not ideal – this has been improved:

clip_image006

Support for VS 2012:

clip_image007

Notice that both 4.0 and 3.5 database file versions are supported by the Toolbox in VS 2012, but that 3.5 SP2 must be installed separately, as it is no longer installed by Visual Studio. Toolbox support for 4.0 in VS 2012 does not require 3.5 SP2 to be installed.

Bug fixes

All SyncFx menu items now only available with SyncFx 2.1 installed
Updated scripting API
Fix of 2 bugs related to "correct" table sorting

Wednesday, August 22, 2012

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

Friday, August 17, 2012

SQL Server Compact 4.0 SP1 Released To Web

SQL Server Compact 4.0 SP1 has now been released as a web download (it is also included and installed with Visual Studio 2012). The release version is 4.0.8876.1

This release adds support for Windows 8 and Visual Studio 2012. In addition, it also includes some bug fixes and a new feature (so far undocumented), as I have described here

I would say it is a recommended upgrade for anyone using version 4.0.

Friday, August 10, 2012

Exporting SQL Server Compact to SQLite

The current available local relational database storage options on WinRT (Windows 8) are limited to SQLite (and maybe some others). Also on Windows Phone 8, both SQL Server Compact and SQLite will be available. So a natural path solutions currently based on SQL Server Compact will be to migrate to SQLite, and the first step would be to move the schema and data to a SQLite database.

I have therefore “bitten the bullet”, and the next version of the SQL Server Compact Toolbox, currently available for download in an alpha version, includes a feature to “dump” a SQL Server Compact database in SQLite .dump format.

image

In this post I will give you some insight in what was required to work with SQLite from the perspective of a SQL Server Compact developer.

The “home”of SQLite is http://www.sqlite.org and all required information is available there.The Downloads page contains a command line utility called sqlite3.exe, which can perform various operations against a SQLite database.The command line utility includes everything required to work with SQLite databases, as the “engine” code is embedded in the .exe – nice.

On of the features included is the ability to “dump” (export) an entire SQLite database to a .sql file, using the following command:

sqlite3 chinook.db .dump > chinook.sql

(Note that the Chinook sample database is also available in SQLIte format)

And the command to create a database file and load a dump file (import) is:

sqlite3 chinook.db < chinook.sql

The dump file has the following format:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE [Shippers] (
  [Shipper ID] INTEGER NOT NULL
, [Company Name] nvarchar(40) NOT NULL
, CONSTRAINT [Shippers_PK] PRIMARY KEY ([Shipper ID])
);
INSERT INTO [Shippers] ([Shipper ID],[Company Name]) VALUES (1,'Speedy Express');
INSERT INTO [Shippers] ([Shipper ID],[Company Name]) VALUES (2,'United Package');
INSERT INTO [Shippers] ([Shipper ID],[Company Name]) VALUES (3,'Federal Shipping');
… More table and data
… Indexes
COMMIT;


The PRAGMA command disables foreign keys constraint checking. And the BEGIN TRANSACTION command makes the process run fast!

So I order to create the SQLite export feature, I decided to create a script in dump file format for SQLite. During the implementation of this, I discovered the following, which you may (or may not) find helpful in your work with SQLite:

1: The dump file must be in UTF8 format, and an UTF8 BOM should not be included. This poses a challenge if you want to edit the file (using Notepad anyway, there are workarounds). I have therefore added the dummy SELECT 1; command to the to of the generated file, to allow you to edit it in notepad.

In order to create a UTF8 file without the 3 byte identifier, use code similar to the following:

            using (FileStream fs = new FileStream(fileLocation, FileMode.Create, FileAccess.Write, FileShare.Read))
{
System.Text.Encoding encoder = new System.Text.UTF8Encoding(false);
if (!sqlite)
encoder = new System.Text.UnicodeEncoding();

using (StreamWriter sw = new StreamWriter(fs, encoder))
{
sw.WriteLine(script);
sw.Flush();
}
}



2: ALTER TABLE is limited, so constraints must be included in the CREATE TABLE statement


3: GO separator is not used, all statements must simply end with semicolon.


4: IDENTITY is implicitly supported, as described here: http://www.sqlite.org/lang_createtable.html#rowid


5: The “N” unicode prefix is not used in SQLite.


6: SQLite datetime data format: '2007-01-01 00:00:00'


7: SQLite binary data format: X’FFAABB’


8: I have decided to not include rowversion columns in the dump file, they do not appear to be supported by SQLite (let me know if I am wrong)

You can view the few code changes I had to make here:
http://exportsqlce.codeplex.com/SourceControl/changeset/93614
http://exportsqlce.codeplex.com/SourceControl/changeset/93619

If you know anything about SQLite, please try the tool/script and let me know what I have done wrong.

The Visual Studio 2010 ADO.NET based tooling for SQLite is available here. Go to the downloads page, and install sqlite-netFx40-setup-bundle-x86-2010-1.0.81.0.exe 


Once you have moved the database to SQLite format you can start using it with for example WinRT, there are a couple of good starter blog posts here:


Tim Heuer: http://timheuer.com/blog/archive/2012/08/07/updated-how-to-using-sqlite-from-windows-store-apps.aspx


Matteo Pagani: http://wp.qmatteoq.com/using-sqlite-in-your-windows-8-metro-style-applications


(Keep in mind that the tooling for this is currently evolving)