SQL Server Compact Toolbox 3.7.2–Visual Guide of new features

After more than 260.000 downloads, version 3.7.2 of my SQL Server Compact Toolbox extension for Visual Studio 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. The focus for this release is a number of features that help improve the experience when using the new support for SQL Server Compact 3.5 (supplied by yours truly) in Entity Framework 6.1 and the related Entity Framework 6.1 Tools.

Run “Generate from Model” script without errors

When using the “Generate Database from Model” option in the Entity Data Model Tools, the tool generates a script to DROP and CREATE the generated objects in the database. If you have tried to run the script until now in the SQL Server Compact Toolbox, the script would fail. A new option “Ignore DDL errors” in the Toolbox SQL Editor will allow you to run the script without any errors:

clip_image001

DDEX install requires VS restart

The simple DDEX providers supplied by the Toolbox for VS 2012 and VS 2013 are installed when the Toolbox is activated, and therefore require VS to be restarted to be used. A notification is now available to notify you of this. You can read more about the DDEX providers in my previous blog post here and here.

clip_image002

(Also, notice the new, “modern” icons in the toolbar of the Toolbox.)

Preserve SQL Server date types

A new option is now available to control scripting of the SQL Server date, datetime2 and datetimeoffset data types. The default behaviour is to always convert these column types to datetime (SQL Server Compact does not have these datatypes). But this can cause data loss (as datetime has limited precision) or overflow errors, as datetime only supports dates from 1753 to 9999, unlike date, datetime2 and datetimeoffset, that all support dates from 0001 to 9999.

clip_image003

Improved About dialog

The About dialog has been improved for readability, and contains a counter for the current number of Toolbox downloads:

clip_image004

Bug fixes

EDMX generation feature failed if only VS 2012 and not VS 2013 was installed.

“Refresh” of tables, including refresh after schema changes, was broken

Scripting API improvements

– improved SQLCE runtime check

– datetimeoffset columns scripted as datetime by default now, not nvarchar()

– sql_variant and hierarchyid handling improved, to avoid getting wrong ordinal for identity and timestamp

– sqlite indexes are now always given unique names

The scripting API improvements are also included in the latest version of my SQL Compact command line export tool and Scripting API, available here.

Feedback

As usual, please provide feedback if you have any feature requests, ideas or encounter any issues (or even bugs!!) by using the CodePlex Issue tracker.

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.

SQL Server Compact Toolbox 3.7–Visual Guide of new features

After more than 235.000 downloads, version 3.7 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 new features included in this release, many suggested by users of the tool via the CodePlex issue tracker. A major “theme” for this release has been to improve the experience for Visual Studio 2013 users, which has no Server Explorer/DDEX support for SQL Server Compact at all.

Add Column, Index and Foreign Key to table

As Server Explorer support is completely missing in VS 2013, and only available for 4.0 in VS 2012, I have added dialogs for building script to add columns, indexes and foreign keys to a table. Right click any table, and select the desired dialog:

image

image

image

  image

Note that currently the Index and Foreign Key dialogs only support a single column, I am aware of this, so please vote here and/or here.

Generate Entity Framework 6 Entity Data Model with VS 2012 and SQL Compact 3.5

The code generation menu items have now been moved to a sub-menu:

image

In addition, as the Entity Framework Tools are now available for Visual Studio 2012 as well as Visual Studio 2013, it is now possible to generate a “database first” Entity Framework model (EDMX) based on a SQL Server Compact database in VS 2012. And I have just contributed an Entity Framework SQL Server Compact 3.5 provider (available as prerelease on NuGet), that allows you to use SQL Server Compact 3.5 with Entity Framework 6. 

Copy database file

It is now possible to copy a database file, and then for example paste it into your project, should you so desire, simply by pressing Ctrl+C on the selected database, or selecting the context menu item:

image

Data only export to SQL Server

The current data export feature has been enhanced to support SQL Server and IDENTITY columns by using DBCC CHECKINDENT instead of the unsupported ALTER TABLE statements currently used.

image

New “ALTER column” context menu item

To help you modify columns, a new context menu to script ALTER COLUMN statements has been added:

image

“Database designer”

I am now hosting a web based database designer based on http://code.google.com/p/wwwsqldesigner/ (which implements only the SQL Server Compact column data types). Current state is “alpha” (and there are known issues with IE 11)!

Server DGML now has table selection
When generating a database diagram for a SQL Server database, you can now select which tables to include in the graph, which can be helpful when diagramming databases with many tables. I have already blogged about this earlier.
Other improvements and bug fixes

Missing database files indicated in tree view
No longer depends on the SQL Server Compact 3.5 runtime, and no longer depends on DbProviderFactory, simply requires either 3.5 or 4.0 in GAC
Each Query opens in new window
BUG: “Upgrade to 4.0” was blocked in Visual Studio 2013
BUG: Not ideal support for Dark Theme in VS 2012/2013 (improved slightly)
BUG: EF6 EDMX code generation with VB was broken

SQL Server Compact 4 desktop app with simple Private Deployment and LINQ to SQL

In this post I will describe a simplified approach to SQL Server Compact Private Deployment, for an overview blog post on Private Deployment with SQL Server Compact, see my blog post here.

By forcing your app to run using x86 always (Prefer 32-bit), which is the new default Platform target option  for apps targeting .NET Framework 4.5, deployment of SQL Server Compact with you app becomes simpler, but must follow different guidelines from what I have previously blogged about. (The same approach will also work with apps targeting .NET 4.0, just set the Platform target to x86 in the location shown below. And the same approach will also work with the SQL Server Compact 3.5 DLL files.)

image

To read more about the new default Platform target option introduced in .NET 4.5, see the MSDN documentation here, and the blog post here.

In addition, I will demonstrate how to use LINQ to SQL with SQL Server Compact 4.0, a low overhead, fast performing ORM.

For the sake of simplicity, and in order to focus attention on the private deployment aspects, I will demonstrate with a console application, but the same approach will also work for WinForms and WPF applications.

Before you get started, make sure you have the following installed:

1: Visual Studio 2010/2012/2013 Pro or higher 

2: SQL Server Compact Toolbox add-in (Install via Tools/Extensions in VS)

3: An existing SQL Server Compact database file, I will use Chinook, which you can download from here

4: The SQL Server Compact 4.0 SP1 runtime

(You could also use the free Visual Studio 2010/2012/2013 for Windows Desktop with the standalone SQL Server Compact Toolbox for 4.0, which also supports LINQ to SQL code generation)

With that in place, let us open Visual Studio and get started:

Create new console application

Go to File, New Project, and create a new Windows Console application. Make sure to set the target platform to 4.0 or newer.

image

Include the SQL Server Compact binaries in your project

Now include the SQL Server Compact 4.0 binaries and ADO.NET Provider as content in your app. Copy C:Program Files (x86)Microsoft SQL Server Compact Editionv4.0PrivateSystem.Data.SqlServerCe.dll to your project folder, and then copy all files and folders in C:Program Files (x86)Microsoft SQL Server Compact Editionv4.0Privatex86 also to your project folder.

In the Solution Explorer, select Show All Files, and include the new folder and the seven files just copied in the Project:

image

Now mark all the copied files (also the files in the Microsoft.VC9.CRT folder) and mark them as Content, Copy Always:

image

Finally, add a Reference to the System.Data.SqlServerCe.dll file in your project folder:

image

(Make sure to check the file location and the Version, should be 4.0.0.1)

Add your database file to the project

Make sure it is also Content, Copy Always – we use the”Database First” workflow here.

Generate the LINQ to SQL DataContext

Next,we will generate a LINQ to SQL DataContext class and related Table classes based on the database, so connect to the database in SQL Server Compact Toolbox, using the Add SQL Server Compact 4.0 Connection menu item:

image

Then right click the database and select “Add LINQ to SQL DataContext to current project”:

image

(I am just using ChinookContext as Context name)

Click OK, and a DataContext class file will be added to your project, and the required reference to System.Data.Linq will be added to the project.

Now let us add some test code to the Main method in order to verify that everything works so far, so the Program.cs code looks like this:

using System;
using System.Data.SqlServerCe;

namespace LinqToSqlCePrivateDeploy
{
class Program
{
private const string
dbFileName = "Chinook_SqlServerCompact_AutoIncrementPKs.sdf";

private static string dbConnectionString =
string.Format("Data Source=|DataDirectory|{0};Max Database Size=4091", dbFileName);
static void Main(string[] args)
{
using (var connection =
new SqlCeConnection(dbConnectionString))
{
using (var context = new ChinookContext(connection))
{
//To log SQL statements, use:
//context.Log = Console.Out;
foreach (var album in context.Album)
{
Console.WriteLine(album.Artist.Name);
Console.WriteLine(album.Title);
}
}
}
Console.Read();
}
}
}

We can now access the database via the generated object model, and do not have to type SQL, but can use LINQ to query the database. In addition, we can update the database (INSERT, UPDATE, DELETE) via methods on the DataContext.

Notice that the DataContext must be constructed with a SqlCeConnection object, in order for LINQ to SQL to work with SQL Server Compact 4.0.

Deploy the database file

The final step will be done to ensure that the database file will be located in a writeable location on the users machine when deployed/installed. We will simply do this in code in order to not depend on any install actions and issues. In addition, we can do this without storing any connection strings in app.config, making the app more self-contained. We will use the same approach that I have already used in my blog post here, which takes advantage of the DataDirectory connection string macro.

private static void CreateIfNotExists(string fileName)
{
string path = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);
// Set the data directory to the users %AppData% folder
// So the database file will be placed in: C:\Users\<Username>\AppData\Roaming\
AppDomain.CurrentDomain.SetData("DataDirectory", path);

// Enure that the database file is present
if (!System.IO.File.Exists(System.IO.Path.Combine(path, fileName)))
{
//Get path to our .exe, which also has a copy of the database file
var exePath = System.IO.Path.GetDirectoryName(
new Uri(System.Reflection.Assembly.GetExecutingAssembly().CodeBase).LocalPath);
//Copy the file from the .exe location to the %AppData% folder
System.IO.File.Copy(
System.IO.Path.Combine(exePath, fileName),
System.IO.Path.Combine(path, fileName));
}
}

Remember to add a call to CreateIfNotExists as the first line in the Main method:

static void Main(string[] args)
{
CreateIfNotExists(dbFileName);

You can now use ClickOnce, XCopy or an Installer to deploy your app, with no other requirements than the target .NET Framework version.

What we have achieved:

– Simple, self contained deployment of a single user desktop app of any type to any .NET 4.0 or higher platform (not ARM, though)

– No need for special incantations in app.config

– RAD (Rapid App Development) “Database First” access to a well-performing, well-documented and simple ORM.

You can download the complete solution from here; http://sdrv.ms/179QBaa

Generate a Windows Phone 8 Local Database DataContext from an existing database

Despite my tooling for this having been available for more than 18 months, I have never posted a dedicated blog post for this subject. This post intends to remedy this.

The Local Database (a SQL Server Compact database accessed via LINQ to SQL) is a data access API available on Windows Phone 7.5 and 8.

The Microsoft documentation (listed here on my blog, together with many other useful Windows Phone Local Database links) always describes a Code First workflow, which makes it cumbersome to reuse existing effort in creating a SQL Server Compact database schema for Windows Mobile or desktop, and  also makes it hard to distribute a database prepopulated with data together with your app. My tooling, which is available with the SQL Server Toolbox Visual Studio add-in, and also available in a simpler form with the new standalone edition of the Toolbox for SQL Server Compact 3.5 (currently in alpha), enables both scenarios. The standalone edition is useful for VS Express users and when you do not wish to install Visual Studio on a PC (it is a single .exe file, so very simple to distribute)

In the following walkthrough, using Visual Studio 2012, I will demonstrate how to use the SQL Server Compact Toolbox to take an existing SQL Server database and use it as an included Windows Phone database in an new (or existing) Windows Phone 8 App. The process to do this requires these steps:

– Create the SQL Server Compact database from the server database and add it to the Windows Phone project
– Generate the LINQ to SQL DataContext and releated classes.
– Use the database from code

I assume you have Visual Studio 2012 Pro or higher with the Windows Phone 8 SDK installed.

Create the SQL Server Compact database

I have created a new Windows Phone Databound App for this sample, and selected Windows Phone OS 8.0 as the target OS.

image

I then use the Toolbox to create a new SQL Server Compact 3.5 database in the folder where the Phone project resides, (you can determine the folder from by using the “Open Folder in File Explorer” context menu item).

image

I then click Create, navigate to the project folder, and type PostCodes.sdf, press OK.

image

Click OK, and a new, empty database will be added to the database list in the Toolbox:

image

Now we need to connect to the SQL Server database, and script it, then run the script against the new, empty database.

image

Create and save the database script using the Toolbox menu item above, and then open the SQL Editor against the PostCodes.sdf database file:

image

Use the Open button in the editor to load the script, and the press the Execute button to run the script.

image

Now the database contains a PostCode table (the script is available here), which has all Danish postcodes.

The final step is adding the database file to the Phone project. In Solution Explorer, select “Show all files”, and include PostCodes.sdf. In this sample scenario, we would like the database to become writable on the Phone, so include it a “Embedded Resource” – it could also be included as Content, if it was just a read-only database, read more here.

image

 

Generate the LINQ to SQL DataContext

In order to generate the DataContext based on the database, right click it in the Toolbox, and select “Add Windows Phone DataContext to current project”.
If this menu item is disabled, verify that the database file is in 3.5 format, and that the SQL Server Compact 3.5 SP2 runtime is properly installed, you can check this via the About dialog. “Yes” is required in both places, if that is not the case, repair/re-install.

image

image

image

Let’s walk through the various options on this dialog:

Context name: The name of the generated DataContext class

Namespace: Allows you to specify another namespace for the generated code

Language: You can generate C# or VB code.

Pluralize: If checked, will rename tables (Person => People) etc.

Create a file per table: Normally, just a single file is created

Advanced options:

Add schema version table: If you would like to include the database file a a writeable file, and allow use of the DatabaseSchemaUpdater class in a future app version select this option .

Add rowversion column to all tables: Checking this will ensure that all tables have a rowversion column (formerly timestamp), which enhances performance when doing UPDATE and DELETE (see my blog posts here and here)

Include ConnectionStringBuilder: Will add a LocalDatabaseConnectionStringBuilder class to the project, to help with building connection strings in a strongly typed fashion.

For this sample project, just click OK, and a PostCodesContext.cs file will be added to the project, and we are done.

image

Use the database from code

Finally, to demonstrate that we are able to include data with the app, alter the DataTemplate in MainPage.xaml as follows:

<DataTemplate>
  <StackPanel Margin=”0,0,0,17″>
      <TextBlock Text=”{Binding Zip}” TextWrapping=”Wrap” Style=”{StaticResource PhoneTextExtraLargeStyle}”/>
      <TextBlock Text=”{Binding City}” TextWrapping=”Wrap” Margin=”12,-6,12,0″ Style=”{StaticResource PhoneTextSubtleStyle}”/>
  </StackPanel>
</DataTemplate>

Replace the OnNavigatedTo event handler in MainPage.xaml.cs with the following code:

        protected override void OnNavigatedTo(NavigationEventArgs e)
{
using (PostCodesContext ctx = new PostCodesContext(PostCodesContext.ConnectionString))
{
ctx.CreateIfNotExists();
ctx.LogDebug = true;
MainLongListSelector.ItemsSource = ctx.PostCode.ToList();
}
}

This code initialize a new PostCodesContext instance (embraced in “using”, as it is Disposable). The CreateIfNotExists method extracts the PostCodes.sdf embedded resource from the project, and copies it to isolated storage (feel free to look at the code). Setting LogDebug to true will show all SQL statements as text in the Debug window while debugging:

SELECT [t0].[Id], [t0].[Zip], [t0].[City], [t0].[Street], [t0].[Company], [t0].[IsProvince], [t0].[rowguid] AS [Rowguid], [t0].[ts] AS [Ts]
FROM [PostCode] AS [t0]

And finally, calling ToList() will execute the SELECT and return a list of PostCode objects, that is the bound to the ItemsSource property of the LongListSelector.

Result:

pc

Summary

Let us finish with a summary of advantages of this approach:
– Use desktop database tools for data population and schema design
– Saves time doing 1:1 mapping between database tables and DataContext classes
– DataContext class and entity classes are partial and can be extended
– The generated DataContext contains Index definitions (which SqlMetal does not support, as this is a Windows Phone extension)
– The generated DataContext contains the CreateIfNotExists method, that optionally extracts an included database (prepopulated with data) to Isolated Storage
– The generated DataContext includes the LogDebug property, that allows you to see all SQL statements generated on the device in the debug window
– Optionally split the generated Data Context classes into multiple files
– Optionally add a Version table if you include the table with your app, and want to enable use of the schema updater functionality.
– Optionally add rowversion columns to improve UPDATE and DELETE performance
– Optionally include a ConnectionStringBuilder class to build a valid connection string in a strongly typed way,  using advanced connection string options (see some of my Phone blog posts for candidates)

Hope you find it useful.

UPDATE: Comments have now been closed, please contact me for any issues via the Codeplex issue tracker here: http://sqlcetoolbox.codeplex.com/issues/list

SQL Server Compact Toolbox 3.1.1 with support for Windows Phone 8 and VS 2012 released

Just a short note to let you know, that the SQL Server Compact Toolbox add-in has been updated beginning of this month to support the following new features:

Generation of a LINQ to SQL DataContext for Windows Phone 8 projects in Visual Studio 2012. (Like Windows Phone 7.5, Windows Phone 8 supports a so-called “Local Database”, which is a SQL Server Compact Database accessible only via LINQ to SQL). This feature will allow you to generate a Phone specific DataContext, I have blogged about this earlier as you can see under the heading ErikEJ on this page. 

image

In addition, the Toolbox is now able to use sqlmetal.exe on “clean” Windows 8 systems, with only Visual Studio 2012 Pro or higher installed.

Also note, that the Toolbox supports SQL Server Compact 3.5 database files even under VS 2012, despite the fact, that the VS 2012 Server Explorer no longer supports SQL Server Compact 3.5 (this requires the SQL Server Compact 3.5 SP2 desktop MSI to be installed, of course – download from here.

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

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

Private deployment of SQL Server Compact 3.5 SP2

The information found in the official documentation is not very extensive, and this blog post hopes to extend on the information found there. I have already blogged about private deployment with SQL Server Compact 4.0, and have an overview post here. 

SQL Server Compact 3.5 SP2 requires the following software:

The OS must be Windows XP SP3 or higher:

For applications targeting .NET 3.5 SP1, no additional software is required.

For applications targeting .NET 4.0, either .NET Framework 3.5 SP1 or the VC++ 2005 SP1 redistributable (for x86 and/or x64) is required.

Make sure the 3.5 SP2 runtime is properly installed, on x64 machines you must install both the x86 and x64 runtimes.

Let us assume that the requirements above are fulfilled (notice that Windows 7 includes .NET 3.5 SP1). So what else is required – let’s make a Console app and find out! Our goal is to create an application, that runs without SQL Server Compact 3.5 SP2 already installed, on both x64 and x86 systems. Notice that the instructions below works, no matter if your application targets “x86” (the 32 bit .NET Framework on all platforms, “Any CPU” (either the 32 or 64 bit .NET Framework), or x64 (the 64 bit Framework exclusively).

In Visual Studio, create a new Console project:

image

Now we must include the unmanaged SQL Server Compact C++ runtime files, each set of files in their own folder, which are platform specific. So create 2 folders in the project, one named x86 for the 32 bit files, and one named AMD64 (not x64!) for the 64 bit files.
NOTE: This convention, based on the value of the PROCESSOR_ARCHITECTURE environment variable is a special SQL Server Compact feature.

image 

Now we must locate the required files. If you are using a 32 bit machine, only the 32 bit files are installed on your machine, and you must manually extract the 64 bit files to a folder as described here. I am using (like most these days) a x64 machine, and it has the  most recent files for both platforms already installed. Make sure that all files you include have the exact same file version, or you will fail. The 3.5 SP2 file version is 3.5.8080.0, you can view the file version in Windows Explorer.

The files in the “C:Program Files” folder are all 64 bit files, and the files in the “C:Program Files (x86)” folder are all 32 bit files (on x64 systems)

The files you need to add are:
sqlceca35.dll
sqlcecompact35.dll
sqlceer35EN.dll
sqlceme35.dll
sqlceoledb35.dll
sqlceqp35.dll
sqlcese35.dll

So, add the files from C:Program FilesMicrosoft SQL Server Compact Editionv3.5 to the AMD64 project folder, using Add, Existing Item (make sure to change the filter to “All files”):

image

Make sure all files are included with Build Action = Content, and Copy to Output Directory = Copy Always:

image

Then add files from C:Program Files (x86)Microsoft SQL Server Compact Editionv3.5 to the x86 project folder, in the same way:

image

Finally, add the ADO.NET provider (System.Data.SqlServerCe.dll) to the project root, add this file form the C:Program FilesMicrosoft SQL Server Compact Editionv3.5Private (!) folder. Also set this file as Content, Copy Always:

image

Now add a reference to the ADO.NET provider in the root project folder:

image

Make sure the Version (Assembly Version) is 3.5.1.50, that indicates that it is the correct file:

image

Now build the project, and look in the bin/debug folder, to make sure all files are copied with the project output. You can now test that private deployment works either by uninstalling the 3.5 SP2 runtimes or on a PC without the runtimes installed.

If you are using only ADO.NET “Classic” (no LINQ to SQL or Entity Framework), this is all you need for private deployment. If you initialize a LINQ to SQL DataContext with a SqlCeConnection object, as I describe here, no additional configuration is required.

If you depend on the DbProvider API (LINQ to SQL and Entity Framework does), you must add the following to your project’s app.config:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
<system.data>
<DbProviderFactories>
<remove invariant="System.Data.SqlServerCe.3.5" />
<add
name="Microsoft SQL Server Compact Data Provider 3.5"
invariant="System.Data.SqlServerCe.3.5"
description=".NET Framework Data Provider for Microsoft SQL Server Compact"
type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=3.5.1.50, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
/>
</DbProviderFactories>
</system.data>
</configuration>

UPDATE Feb 2013: Entity Framework private deployment is ONLY supported with Entity Framework 1.0, so below will not work in VS 2010/VS 2012 (EF 4.0 and EF 5.0)

If you use Entity Framework, you must add the C:Program FilesMicrosoft SQL Server Compact Editionv3.5PrivateSystem.Data.SqlServerCe.Entity.dll to your project root as content, and have a configuration like the following (as described by the SQL Compact Team here)

<?xml version="1.0" encoding="utf-8"?>
<configuration>
<system.data>
<DbProviderFactories>
<remove invariant="System.Data.SqlServerCe.3.5" />
<add name="Microsoft SQL Server Compact Data Provider 3.5" invariant="System.Data.SqlServerCe.3.5" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=3.5.1.50, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
</DbProviderFactories>
</system.data>
<runtime>
<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
<dependentAssembly xmlns="">
<assemblyIdentity name="System.Data.SqlServerCe" publicKeyToken="89845dcd8080cc91" culture="neutral" />
<bindingRedirect oldVersion="3.5.1.0-3.5.1.50" newVersion="3.5.1.50" />
</dependentAssembly>
</assemblyBinding>
</runtime>
</configuration>

Hope this was useful!

SQL Server Compact Toolbox 3.0–Visual Guide of new features

After more than 110.000 downloads, version 3.0 of my free, open source 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, many suggested by users of the tool via the CodePlex issue tracker

Extensive support for Sync Framework 2.1

Thanks to a fantastic effort from fellow MVP June Tabadero (blog | twitter), extensive support for Sync Framework 2.1 has been added to the Toolbox, including Provisioning, Deprovisioning, Code Generation, Local Database Cache Code Generation and Explorer tree integration. You can read a nice walkthrough of the features in June’s blog post here. Notice that you will need to install the Sync Framework 2.1 bits for any of these features to work, you can download from here. June also recently blogged about using SQL Server Compact 4.0 with Sync Framework here.

clip_image002

Generate desktop LINQ to SQL classes both for 3.5 and 4.0 databases

I  decided to add the option to generate LINQ to SQL desktop classes, as I recently discovered that you can actually use LINQ to SQL with SQL Server Compact 4.0 (though NOT supported in any way by Microsoft). Read the blog post before you start using LINQ to SQL with 4.0.

clip_image003

clip_image005

Migrate a SQL Server Compact database directly to SQL Server (LocalDB/Express)

As you may know, the Toolbox already has features that allow you to generate a script of a SQL Server Compact database, and run it against a SQL Server database. But this release includes a feature to simplify this process, by not only generating a script, but also immediately executing it against a SQL Server database connected via Server Explorer.

clip_image006

clip_image007

Script only data (with “correct” table ordering)

Due to the increasing number of Database context menu items, I have moved all the script options to a separate “Script Database” sub-menu:

clip_image009

I have also added “Script Database Data”, which scripts only the data (!), sorted correctly by using the QuickGraph topological sorting of a DataSet.

WP DataContext – option to include ConnectionStringBuilder class

I have added the option to also have a ConnectionStringBuilder class generated for Windows Phone, to help constructing valid Windows Phone connection strings, with the limited amount of advanced options available on Windows Phone.

Other improvements

Scripting API fixes:
Tables are now ordered by topological sort when scripting entire database.
Data scripting now uses DbDataReader (or speed and to avoid some OOM issues)
“date” and “datetime2” SQL Server values are converted to “datetime” by default.
SQL scripts with DGML no longer generated.
Server based DGML now includes schema
Duplicate Execution plans fixed.
Improved script execution