Wednesday, December 19, 2012

Integrating Red Gate SmartAssembly in the SQL Server Compact Toolbox

In the next release of the SQL Server Compact Toolbox, which is currently available in an alpha release, I will start using Red Gate SmartAssembly for Error Reporting and quality improvement. In this blog post I will describe the few steps required to integrate SmartAssembly with the Visual Studio VISX build process and in code. Some of these steps are not well documented on the SmartAssembly support site, as in this case we are protecting a DLL file, not an .exe (the more common case), so I thought I would share my findings.

SmartAssembly is a .NET instrumentation tool, that offers centralised error reporting and feature usage tracking (it also offers various obfuscation features, but I am not using these), and includes a nice desktop client, that integrates all the features of the product in a single UI, including viewing your Error Reports and Feature Usage statistics.

image

MSBuild integration

Once you have downloaded SmartAssembly, you can create a new SmartAssembly project (.saproj file) – do this for your add-in DLL, and save the file. Then look at the useful instructions on this support page. You will need to make a change to the instructions on that page, and possibly also your .saproj file:

In your .csproj file (VISX Add-In project), change the SmartAssembly build task to run AfterCompile, not AfterBuild, like this, and add OverwriteAssembly="True" :

<Target Name="AfterCompile" Condition=" '$(Configuration)' == 'Release' ">   

<SmartAssembly.MSBuild.Tasks.Build OverwriteAssembly="True" ProjectFile="C:\Data\SQLCE\CodePlexTFS\TFS07\SqlCeToolbox\SqlCe35Toolbox\SqlCeToolbox.saproj" />

</Target>

Change the source file in you .saproj file to point to the DLL file in the obj folder, not the bin folder, like so:

<MainAssemblyFileName>.\obj\Release\SqlCeToolbox.dll</MainAssemblyFileName>

This will allow SmartAssembly to instrument your DLL after it has been built, but before it is added to the .VSIX file (which is a .zip file)

 

Invoking Error Reporting in Code

As the Toolbox is an add-in, I prefer not to catch any unhandled Visual Studio exceptions, but would still like to be able to report any errors occurring in the Toolbox, in order to be able to improve it. SmartAssembly easily allows you to to this.

Start by adding references to  SmartAssembly.ReportException.dll and SmartAssembly.ReportUsage.dll in the C:\Program Files\Red Gate\SmartAssembly 6\SDK\bin folder from your project.

Then in order to invoke Error Reporting, use:

SmartAssembly.ReportException.ExceptionReporting.Report(ex);




Then when a handled exception occurs, the user will see this dialog:

image

And to report usage use:

SmartAssembly.ReportUsage.UsageCounter.ReportUsage(feature);

Where feature is the name of the feature in question.


Hope you found it useful.

Thursday, November 29, 2012

SQL Server Compact Merge Replication Library alpha released

I have just published a new Codeplex project, that contains a library to help with SQL Server Merge Replication using SQL Server Compact 3.5 SP2.

This library simplifies the code and configuration to do Merge Replication from a SQL Server Compact 3.5 SP2 desktop client, with a number of useful helper methods.
Features:

  • Is intended for use from a WinForms or WPF application, and the Synchronize method runs async.
  • Implements best practices for optimal performance, and attempt to properly detect expired subscriptions, by throwing a PublicationMayHaveExpiredException.
  • Will create the database file for you as required, so an existing database file is not required.
  • Optionally logs sync status to a SyncLog table (which is a part of the publication)
  • Generate INSERT script in order to rescue local data in case of a disaster (for example publication expiry)
  • Validate a Publication, for example after initial Sync
  • Properly format a SqlCeException as a string to get all available error information
  • Source includes a demo form to test app.config parameters and see the library in action
using ErikEJ.SqlCeMergeLib;
using System.Data.SqlServerCe;
...
string sdfFile = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "MergeTest.sdf");
conn = new SqlCeConnection(string.Format("Data Source={0}", sdfFile));

DateTime syncDate = sync.GetLastSuccessfulSyncTime(conn);
textBox1.Text = "Last Sync: " + syncDate.ToString();

sync.Completed += SyncCompletedEvent;
sync.Progress += SyncProgressEvent;
sync.Synchronize(conn, 1002);

Other useful methods:
Generate INSERT script for the local database (for disaster recovery):

public string GenerateInsertScripts (
SqlCeConnection connection,
List<string> tableNames
)

Format a SqlCeException as a String:

public string ShowErrors (
SqlCeException e
)

Validate that the local database is properly Merge Replicated;

public bool Validate (
SqlCeConnection connection
)

Configuration:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="InternetLogin" value=""/>
<add key="InternetPassword" value=""/>
<add key="InternetUrl" value="http://erik-pc/ssce35sync/sqlcesa35.dll"/>
<add key="Publication" value="PubPostCodes"/>
<add key="Publisher" value="Erik-PC\SQL2008R2"/>
<add key="PublisherDatabase" value="PostCodes"/>
<add key="PublisherLogin" value="sa"/>
<add key="PublisherPassword" value="pw"/>
<add key="UseNT" value="false"/>
</appSettings>
</configuration>

repl.jpg

Hope you will find it useful, and please post any bugs and suggestion via the Issue Tracker on CodePlex.


Note, that it appears that Merge Replication against SQL Server 2012 with SP1 or later is currently broken (but works with SQL Server 2012 RTM).

Tuesday, November 27, 2012

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.

Tuesday, October 16, 2012

Tips and tricks for using SQL Server Compact with VB/VBA/VBScript/ASP

This may sound a bit old-school, and it probably is, but some developers of Visual Basic still find that SQL Server Compact is a compelling local database solution.

To use SQL Server Compact with VB, you must have the SQL Server Compact runtime MSI installed on the computer, as the only way to access SQL Server Compact from VB is via the OLEDB provider, that must be registered on the machine by the MSI installer.

To use the OLEDB provider, you need the provider name, and for SQL Server Compact, this is:

Version 3.0/3.1:  Microsoft.SQLSERVER.MOBILE.OLEDB.3.0

Version 3.5: Microsoft.SQLSERVER.CE.OLEDB.3.5

Version 4.0: Microsoft.SQLSERVER.CE.OLEDB.4.0

It my blog post here I describe how you can access and iterate a table from VBA.

XL Dennis has also blogged about the same subject.

Notice that you cannot access columns of type “image” using the OLEDB provider, let alone INSERT into these columns – I describe a workaround for the INSERT case in my blog post here.

As the database access from takes place via the OLEDB provider, for some connection string properties, make sure to use the ssce: variant, if available. So for example to access a password protected database file from VB/VBA, use this connection string format:

Data Source=mydb.sdf;ssce:database password=123

XL Dennis has also blogged about creating a SQL Server Compact database from VB/VBA.

In order to do UPDATE, INSERT and DELETE, you must construct the required SQL statements as strings, and simply execute them, using ADO.Connection .Execute method.

Finally, in my blog post here, I demonstrate that it is possible to use SQL Server Compact 4.0 from Classic ASP.

Hopefully, this collection of tips and tricks is enough to get you started with VBA/VB and SQL Server Compact.

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)

Monday, July 30, 2012

The state and (near) future of SQL Server Compact

I recently got asked about the future of SQL Server Compact, and in this blog post I will elaborate a little on this and the present state of SQL Server Compact.

Version 4.0 is the default database in WebMatrix ASP.NET based projects, and version 2 of this product has just been released.

There is full tooling support for version 4.0 in Visual Studio 2012, and the “Local Database” project item is a version 4.0 database (not LocalDB). In addition, Visual Studio 2012, coming in august, will include 4.0 SP1, so 4.0 is being actively maintained currently. Entity Framework version 6.0 is now open source, and includes full support for SQL Server Compact 4.0. (Entity Framework 6.0 will release “out of band” after the release of Visual Studio 2012).

The latest release (build 8088) of version 3.5 SP2 is fully supported for Merge Replication with SQL Server 2012 (note that "LocalDB" cannot act as a Merge Replication subscriber), and Merge Replication with Windows Embedded CE 7.0 is also enabled.

On Windows Phone, version 3.5 is alive and well, and will of course also be included with the upcoming Windows Phone 8 platform. Windows Phone 8 will also include support for SQLite, mainly to make it easier to reuse projects between Windows Phone 8 and Windows 8 Metro.

On WinRT (Windows 8 Metro Style Apps), there is no SQL Server Compact support, and Microsoft is currently (doubt that will change) offering SQLite as an alternative. See Matteo Paganis blog post also: http://wp.qmatteoq.com/using-sqlite-in-your-windows-8-metro-style-applications

So, currently SQL Server Compact is available of the following Microsoft platforms: Windows XP and later, including ASP.NET, Windows Phone, Windows Mobile/Embedded CE.

On the other hand, SQL Server Compact is not supported with: Silverlight (with exceptions), WinRT (Windows 8 Metro Style Apps).

So I think it is fair to conclude that SQL Compact is alive and well. In some scenarios, SQL Server "LocalDB" is a very viable alternative, notice that currently LocalDB requires administrator access to be installed (so no "private deployment"). See my comparison here.

Tuesday, June 19, 2012

HOW TO: Connect to SQL Server Compact from F#

I have previously blogged about connection to SQL Server Compact from other technologies than pure ADO.NET:

ASP Classic

VBA (Excel)

Silverlight

PowerShell

F# version 3.0 in Visual Studio 2012 makes it relatively easy to connect to a SQL Server Compact database (I am NOT a F# programmer, and even I could connect, so it must be very easy!).

In this blog post I will show the required steps to connect to a SQL Server Compact 4.0 database file and start using it via Entity Framework with F#.

In VS 2012 (currently RC), create a new F# application:

image

Now add references to the required libraries (Entity Framework and F# Type Providers):

FSharp.Data.TypeProviders
System.Data.Entity
System.Data.Linq

image

I order to access SQL Server Compact via EF 4 from F#, and Entity Data Model file (.EDMX) file is required. And the EDMX Wizard and Designer only works with C# and VB.NET projects, so to create this, add a Visual C# Console project to the solution:

image

Now add the EDMX file to this project, right click the project, select Add, New Item…, Data,

image

Select generate from database, and point to the desired SQL Server Compact 4.0 database.

Just say no:

image

The Console project now contains an EDMX file, and a useful connection string in app.config.

Now go back to the F# project clear program.fs, and add the following “using” statements:

open System
open System.Data.Linq
open System.Data.EntityClient
open Microsoft.FSharp.Data.TypeProviders



Grab the connectionString value from the console project app config, and add this line:

let internal connectionString = "metadata=res://*/;provider=System.Data.SqlServerCe.4.0;provider connection string='data source=C:\\Data\\SQLCE\\Test\\nw40.sdf';"



Notice the changes to the provider connection string - =&quot; has been change to single quote, and backslashes have been escaped.


Now add the EDMX file from the Console project to the F# project as Content:


image


You can now initialize the EF type provider like so:

type internal edmx = EdmxFile<"NWModel.edmx">



And start using the context like this:

let internal context = new edmx.nw40Model.nw40Entities(connectionString)

let internal res = query { for supplier in context.Suppliers do select supplier }

res|> Seq.iter (fun supplier -> printfn "%s" supplier.Company_Name)

let wait = Console.ReadKey()

Friday, May 25, 2012

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 Files\Microsoft SQL Server Compact Edition\v3.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 Edition\v3.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 Files\Microsoft SQL Server Compact Edition\v3.5\Private (!) 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 Files\Microsoft SQL Server Compact Edition\v3.5\Private\System.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!

Wednesday, May 16, 2012

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

Monday, May 7, 2012

Visual Studio 11 beta - Tooling for SQL Server Compact

Visual Studio 11 beta includes SQL Server Compact 4.0 SP1 CTP1, as I blogged about here. In this post, I will describe in greater detail the tooling support included with Visual Studio 11 beta. Notice the Visual Studio 11 is in beta, and things can change before release.

image

Visual Studio 11 beta only supports SQL Server Compact 4.0 in Server Explorer and other tools, so no longer support for 3.5 SP2, for development with that, you can still reference the 3.5 DLL files, but will not get any built-in tooling support.

This also means, that the EDM Wizard now only supports 4.0, so no longer the confusing mix of 4.0 support for Web projects, and 3.5 support for other project types.

image

The Transact-SQL Editor in Premium and Ultimate, that previously supported SQL Server Compact 3.5 and 4.0 now only support SQL Server, sadly. So in order to analyse SQL Server Compact queries, you must have SQL Server 2008 R2 Management Studio Express  (which is a free product) installed.

image

The Server Explorer is “missing colours”, but otherwise looks familiar:

image

The Server Explorer dialogs for SQL Server Compact, that were previously available, look very much the same!

image

And you can still add a “Local Database”, which creates an empty SQL Server Compact 4.0 database in your project:

image

The SQL Server Compact Toolbox also works with Visual Studio 11 beta, notice that is also support connections to 3.5 database files!

image

Monday, April 23, 2012

Preview of SQL Server Compact Toolbox version 3.0 now available

This short blog post lists the main new features in version 3, with pointers to the menu location of the new features in the upcoming version 3.0 of my SQL Server Compact Toolbox add-in for Visual Studio. Please go ahead and download the preview, and let us know what you think.

New features

Extensive support for Sync Framework 2.1, including Provisioning, Deprovisioning, Code Generation, Local Database Cache Code Generation and Explorer tree integration - thanks to great effort from fellow MVP JuneT (blog | twitter)

image
(Root context menu)

image
(Database context menu)

Generate desktop LINQ to SQL classes both for 3.5 and 4.0 databases (see this blog post for more info)

image
(Database context menu)

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

image
(Database context menu)

Script only data (with "correct" table ordering, using QuickGraph DataSetGraph with Topological Sort)
image
(Database context menu)

Add own Compact 3.5 based connections when using Visual Studio 11 beta

WP DataContext - option to include a ConnectionStringBuilder class

image

Go and try it out, and let us know what you think!

Thursday, April 12, 2012

Using LINQ to SQL with SQL Server Compact 4.0 (yes, you can!)

This question on StackOverflow more than hinted at the fact, that it was possible to use LINQ to SQL with SQL Server Compact 4.0. (Despite “rumours” of the opposite). I decided to find out, if this would work, and what it required tooling wise. (Please be aware that this is not supported by Microsoft). The SQLMetal.exe command line utility, that is used to generate the LINQ to SQL DataContext and related table classes is hardcoded via a string constant to work with 3.5. But using my command line utilities in combination with SQLMetal would allow you to create the classes based on a 4.0 database file. Using a batch (cmd) file similar to the following:

   1:  set sdf=C:\projects\ChinookPart2\Chinook40.sdf
   2:  set class=Chinook
   3:   
   4:  c:\data\sqlce\bin\exportsqlce40 "Data Source=%sdf%" c:\temp\temp.sqlce schemaonly
   5:  del c:\temp\%class%.sdf
   6:   
   7:  c:\data\sqlce\sqlcecmd -d"Data Source=C:\temp\%class%.sdf" -e create -n
   8:  c:\data\sqlce\sqlcecmd -d"Data Source=C:\temp\%class%.sdf" -i c:\temp\temp.sqlce –n
   9:   
  10:  sqlmetal /code:%class%.cs C:\temp\%class%.sdf



In line 4, the exportsqlce40 utility is used to export the schema (table definitions) from the 4.0 based file.


In line 7, and empty 3.5 file is created, and in line 8 it is populated with the generated schema.


Finally, in line 10, sqlmetal is run against this newly created 3.5 file, and generates the required classes.


To test that this worked, I created a Console project, added a reference to System.Data.Linq and System.Data.SqlServerCe.dll version 4.0.0.0. I also included the Chinook.cs file generated above in the project.


Then I used the code below to test that I could access the database via LINQ to SQL:

using System;
using System.Data.SqlServerCe;
using System.Linq;

namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{

using (SqlCeConnection conn = new SqlCeConnection(@"Data Source=C:\projects\Chinook\Chinook40.sdf"))
{
using (Chinook db = new Chinook(conn))
{
db.Log = Console.Out;
var list = db.Album.ToList();
if (list.Count > 0)
System.Diagnostics.Debug.Print("It works!");
}
}

}
}
}



NOTE: Notice that the Chinook DataContext class is initialized with a SqlCeConnection object, I could not make it work with a full connection string, or the name of an existing connection string setting in app.config.


In the next release of the SQL Server Compact Toolbox, the code generation process has been incorporated, allowing you to very easily add a the LINQ to SQL classes to your project, both based on 3.5 AND 4.0 database files.

New menu item on the database context menu:


image

New dialog:
image

Friday, April 6, 2012

Windows Phone Local Database tip: Exploring DELETE performance and a “Bug Alert”

In the previous instalments in this series of Windows Phone Local Database tips, I have been exploring SELECT, INSERT, UPDATE, CREATE DATABASE and Encryption. Now the time has come to have a closer look at DELETE, and investigate how they can be optimized (if possible). I will look at deleting multiple rows in a single call to SubmitChanges.

To test this, I will be using the code below, and as usual use the Chinook sample database.

//New database from embedded resource
using (ChinookContext db = new ChinookContext(ChinookContext.ConnectionString))
{
if (db.DatabaseExists())
db.DeleteDatabase();

db.CreateIfNotExists();
}

using (ChinookContext db = new ChinookContext(ChinookContext.ConnectionString))
{

try
{
//db.LogDebug = true;

var list = db.InvoiceLine.Take(100);

db.InvoiceLine.DeleteAllOnSubmit(list);

System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
sw.Start();

db.SubmitChanges();

sw.Stop();
System.Diagnostics.Debug.WriteLine(sw.ElapsedMilliseconds);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
}
}



Running this code using the InvoiceLines table from the Chinook sample database will result in 100 calls to a DELETE statement like this:


DELETE FROM [InvoiceLine] WHERE ([InvoiceLineId] = @p0) AND ([InvoiceId] = @p1) AND ([TrackId] = @p2) AND ([UnitPrice] = @p3) AND ([Quantity] = @p4)
-- @p0: Input Int32 (Size = 4; Prec = 0; Scale = 0) [100]
-- @p1: Input Int32 (Size = 4; Prec = 0; Scale = 0) [19]
-- @p2: Input Int32 (Size = 4; Prec = 0; Scale = 0) [581]
-- @p3: Input Decimal (Size = 19; Prec = 10; Scale = 2) [0.99]
-- @p4: Input Int32 (Size = 4; Prec = 0; Scale = 0) [1]


Notice that all columns are included in the WHERE statement, to check for concurrency conflicts (you can argue if this is really required when deleting). The 100 deletes runs in 320 ms on the emulator.


Now let us try to add a rowversion column to the InvoiceLines table, like we did for the UPDATE testing, and see if this has any effect.


You can also use the new feature in the SQL Server Compact Toolbox, which will allow you to add rowversion columns to all your tables, for the benefit of UPDATE and maybe also DELETE statements.


image


Let us try to run the same test code again.


Now the SQL statement looks like this, indicating that the query processor is bypassed, unlike in the statements above.


-- CURSOR BASED INDEX DELETE [InvoiceLine].[PK_InvoiceLine] (
--     InvoiceLineId: [1]
--    )
-- EQUALITY CHECK [versioncolumn], [System.Byte[]] = [System.Byte[]]


The time to perform the 100 DELETE statements is now down from 320 ms to under 70 ms, a significant improvement.


BUG ALERT!


If you attempt to take advantage of rowversion columns in an existing database, make sure that the table in question does not have more indexes that cover the primary key columns. If you have that, your SubmitChanges statement will “bomb out” your app.


image


For example the Album table in Chinook has a PK_Album index and PFK_Album index, that both are indexes on the AlbumId column. DROP the PFK_Album index in this case (you can use the new Index context menu option in the Toolbox to do that).


Hope you found this useful.