Major update to SQL Server Compact 3.5 SP2 available

A major update to SQL Server Compact 3.5 SP2 has just been released, disguised as a “Cumulative Update Package”. Microsoft knowledgebase article 2553608 describes the update. The update contains the following product enhancements:

Support for Windows Embedded CE 7.0

The update contains updated device components. This expand the supported device platforms to this impressive list: Pocket PC 2003 Software, Windows CE, Windows Mobile 5.0, Windows Mobile 6, Windows Mobile 6.1 , Windows Mobile 6.5 Professional, Windows Mobile 6.5 Standard, Windows Embedded CE 7.0

Support for Merge Replication with SQL Server “Denali” CTP3

The update contains new Server Tools, that support Merge Replication with the next version of SQL Server, codename “Denali”. The replication components also work with Windows Embedded CE 7.0.

For a list of fixes in the Cumulative Updates released for SQL Server Compact 3.5 SP2, see my blog post here.

It is nice to see that the 3.5 SP2 product, with it’s full range of device support and synchronization technologies is kept alive and kicking.

NOTE: Currently, the only download available is the desktop runtime, I will update this blog post and tweet (@ErikEJ) when the other downloads are available.

Viewing SQL statements created by Entity Framework with SQL Server Compact

Sometimes it can be useful to be able to inspect the SQL statements generated by Entity Framework against your SQL Server Compact database. This can easily be done for SELECT statements as noted here. But for INSERT/UPDATE/DELETE this method will not work. This is usually not a problem for SQL Server based applications, as you can use SQL Server Profiler to log all SQL statements executed by an application, but this is not possible with SQL Server Compact.

This forum thread contains an extension method, that allows you to log INSERT/UPDATE/DELETE statements before SaveChanges is called on the ObjectContext. I have updated and fixed the code to work with SQL Server Compact 4.0, and it is available in the updated Chinook sample available below in the ObjectQueryExtensions class in the Chinook.Data project.

You can now use code like the following to inspect an INSERT statement:

using (var context = new Chinook.Model.ChinookEntities())
{ 
    context.Artists.AddObject(new Chinook.Model.Artist { ArtistId = Int32.MaxValue, Name = "ErikEJ" });
    string sql = context.ToTraceString();
}

The “sql” string variable now contains the following text:

--=============== BEGIN COMMAND ===============

declare @0 NVarChar set @0 = 'ErikEJ'

insert [Artist]([Name])

values (@0)


; select [ArtistId]


from [Artist]


where [ArtistId] = @@IDENTITY

go

--=============== END COMMAND ===============

This statement reveals some of the magic behind the new support for “server generated” keys with SQL Server Compact 4.0 when used with Entity Framework 4.0. SQL Server Compact is “tricked” into executing multiple statements in a single call.

SQL Server Compact Toolbox 2.3–Visual Guide of new features

After more than 44.000 downloads, version 2.3 of my 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

Generate database documentation

This feature allows you to create documentation of all tables and columns in your database, in HTML or XML (raw) format, for use with product documentation etc. If you have added descriptions to database, table or column, these will also be included.

From the database context menu, select Create Database Documentation…

You will be prompted for a filename and can choose between HTML and XML format. The generated document will then open in the associated application (for example your browser).

The format of the HTML and XML file comes from the excellent DB>doc for Microsoft SQL Server CodePlex project. You can use the XML file as the data in your own documentation format.

By default, tables beginning with __ are not included in the documentation (this includes the table with object descriptions). They can optionally be included via a new option:

Please provide any feedback for this new feature to the CodePlex issue tracker

Handle password protected files better

When trying to open a password protected file, where the password is not saved with the connection string, you are now prompted to enter the database password, instead of being faced with an error.

Show result count in status bar

The query editor status bar now displays the number of rows returned.

Other fixes

Improvements to Windows Phone DataContext generation, improved error handling to prevent Visual Studio crashes, and the latest scripting libraries included.

Using PowerShell to manage SQL Server Compact database files

I noticed that someone asked if you could manage SQL Server Compact from PowerShell, and yes you can, based on the reply here I put together the following sample:

[Reflection.Assembly]::LoadFile(“C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Desktop\System.Data.SqlServerCe.dll”)

$connString = "Data Source=C:\data\sqlce\test\roads.sdf"
$cn = new-object "System.Data.SqlServerCe.SqlCeConnection" $connString

# create the command
$cmd = new-object "System.Data.SqlServerCe.SqlCeCommand"
$cmd.CommandType = [System.Data.CommandType]"Text"
$cmd.CommandText = "SELECT TOP (100) * FROM Road"
$cmd.Connection = $cn

#get the data
$dt = new-object "System.Data.DataTable"

$cn.Open()
$rdr = $cmd.ExecuteReader()

$dt.Load($rdr)
$cn.Close()

$dt | Out-Default | Format-Table

Windows Phone / SQL Server Compact resources

This blog post collects links to relevant blog posts and articles about Windows Phone support for SQL Server Compact. You can also follow me on Twitter (@ErikEJ) to get notified of any SQL Server Compact related news.

MSDN

Local Database Overview for Windows Phone

How to: Create a Basic Local Database Application for Windows Phone

How to: Create a Local Database Application with MVVM for Windows Phone

How to: Deploy a Reference Database with a Windows Phone Application

Walkthrough: Updating a Local Database Application for Windows Phone

Local Database Best Practices for Windows Phone

Local Database Connection Strings for Windows Phone

Local Database Migration Overview for Windows Phone

LINQ to SQL Support for Windows Phone

Video: SQL Server Compact and User Data Access in Mango

PowerPoint slides: SQL Server Compact and User Data Access in Mango

Video + PPT: New Data Access Features Coming to Windows Phone

Windows Phone Mango Application Storage Jumpstart PDF

Get to Mango #8 - Using a Local Database (Screencast)

ErikEJ

Populating a Windows Phone “Mango” SQL Server Compact database on desktop

SQL Server Compact Toolbox 2.2–Visual Guide of new features

Windows Phone Local Database tip: Viewing the SQL generated by LINQ to SQL

Windows Phone Local Database tip: Batch INSERT performance

Windows Phone Local Database tip: Exploring INSERT performance–5 power tweaks

Generating a LINQ to SQL DataContext for VS Express for Windows Phone

Windows Phone Local Database tip: Initializing the database

SQL Server Compact Toolbox 2.6.1–Visual Guide of new features

Windows Phone Local Database tip: Exploring multiple UPDATEs and rowversion impact

Windows Phone Local Database tip: Working with encrypted database files

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

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

Jesse Liberty

Coming in Mango–Sql Server CE

Coming In Mango–Local DB Part 2- Relationships

Best Practices For Local Databases

Yet Another Podcast #43–Sean McKenna and Windows Phone Data

Sean McKenna and Windows Phone "Mango" Database Support

Rob Tiffany

New Windows Phone Mango Data Access Features @ Tech Ed North America 2011

Alex Golesh

Windows Phone Mango–What’s New? (“Local Database” - Part 1 of 8)

Andy Wigley

Windows Phone LINQ to SQL and Data Virtualization: How to work with large tables in LINQ to SQL on Windows Phone

Windows Phone Geek

Windows Phone Mango Local Database- mapping and database operations

Using SqlMetal to generate Windows Phone Mango Local Database classes

Performance Best Practices: Windows Phone Mango Local Database

Windows Phone Mango Local Database(SQL CE): Introduction

Windows Phone Mango Local Database(SQL CE): Linq to SQL

Windows Phone Mango Local Database(SQL CE): [Table] attribute

Windows Phone Mango Local Database(SQL CE): [Column] attribute

Windows Phone Mango Local Database(SQL CE): [Association] attribute

Windows Phone Mango Local Database(SQL CE): Database mapping

Windows Phone Mango Local Database(SQL CE): DataContext

Windows Phone Mango Local Database(SQL CE): Connection Strings

Windows Phone Mango Local Database(SQL CE): Creating the Database

Windows Phone Mango Local Database(SQL CE): Database Queries with LINQ

Windows Phone Mango Local Database(SQL CE): How to Insert data

Windows Phone Mango Local Database(SQL CE): How to Update data

Windows Phone Mango Local Database(SQL CE): How to Delete data

C# Corner

Getting Started With Local Database Operations in Windows Phone 7

Arsahnt

Distributing a SQL CE database in a WP7 Mango application

Arsanth Daily – May 25th

Arsanth Daily – May 27th

Windows Phone 7 SQL CE – Column inheritance

Windows Phone 7 SQL CE – DataContext Tables

Working with pre-populated SQL CE databases in WP7

LINQ to SQL CE performance tips for WP7

Arsanth Daily – June 6th

Arsanth – August 16th

Arsanth – August 18th

How To: Log LINQ to SQL activity on WP7

Kunal Chowdhury

Windows Phone 7 (Mango) Tutorial - 22 - Local Database Support, Create DataContext

Windows Phone 7 (Mango) Tutorial - 23 - Local Database Support, Configuring Project

Windows Phone 7 (Mango) Tutorial - 24 - Local Database Support, CRUD operation with Demo

Windows Phone 7 (Mango) Tutorial - 25 - Learn about Database Connection String

Sergey Barskiy

SQL CE on Windows Phone 7.1 (Mango)

SQL CE in Mango–Updating the Schema

Derik Whittaker

Using SQL CE on WP7 Mango–Getting Started

Using SQL CE On WP7 Mango–Working with Associations

Using SQL CE On WP7 Mango–Working with Indexes 

Mark Artega

Windows Phone and Database Support

Rabeb

Mango- Baby Steps: Creating an application with a local Database

JeffCren

WP7 App First Run Logic

Matt Lacey

Simplifying use of SQL CE in Mango

Corrado

Using Local Database in WP7-Mango

Max Paulousky

Windows Phone (Mango) DB Engines Performance Testing

Nick Randolph

Change Tracking with SQL Server Compact (LINQ to SQL) on Windows Phone

Windows Phone LINQ to SQL and the INotifyPropertyChanged and INotifyPropertyChanging Interfaces

Understanding LINQ to SQL on Windows Phone 7.5

Chris Sainty

WP7.5 Mango–Compiled Queries

Anton Swanvelder

Writing an Image to SQL CE / Linq to SQL

Rafa Serna

Performance Improvements in SQL CE environments – I

Debug mode

Local Database application for windows Phone

Shazaml Design, LLC

More Complete WP7 Mango Database Update Walkthrough

Jerry Nixon

Mango Sample: Database Part 1:2

Mango Sample: Database Part 2:2

Mango Sample: SQL Toolbox

Mango- Database (screencast)

Bhaskar Shrestha

Using SQL Server Compact in Windows Phone 7.1 apps – Part 1

Using SQL Server Compact in Windows Phone 7.1 apps – Part 2

Using SQL Server Compact in Windows Phone 7.1 apps – Part 3

Using SQL Server Compact in Windows Phone 7.1 apps – Part 4

SQL Server Compact Private Deployment tweaks

As a follow up to my previous post about Private Deployment (the concept that you can simply include the SQL Server Compact DLL files with your application as content, so to speak), I will show you a couple of tweaks that the .NET Framework enables.

Forcing an existing application to use the private DLL files

If you have an existing application, that is compiled against the centrally deployed DLL files, for example assembly version 3.5.1.0 or 4.0.0.0, you can force the application to use the private assembly version files instead (3.5.1.50 and 4.0.0.1), via an application configuration file. Lets take ExportSqlCe40.exe as an example. This application is complied against assembly version 4.0.0.0, so it will not work unless SQL Server Compact 4.0 runtime is centrally installed.

To force this application to use Private Deployment only, create a .config file named ExportSqlCe40.exe.config, with the following contents:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="System.Data.SqlServerCe" publicKeyToken="89845dcd8080cc91" />
        <bindingRedirect oldVersion="4.0.0.0" newVersion="4.0.0.1" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
</configuration>

If you run the application now, you will get this error:

Now copy all files from the C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\Private folder to the folder where the Exportsqlce40.exe file resides:

Now the application runs, and uses only the private DLL files.

Isolating the SQL Server Compact runtime files in a separate folder

Continuing the sample above, to be neater, it would be nice to have the SQL Server Compact DLL files in a subfolder below the .exe file location. This can be done by moving the files to a separate folder, for example named SqlCe4. Now I have moved all the SQL Server Compact files and folders to that folder:

Now modify the .config file as follows:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <probing privatePath="SqlCe4"/>
      <dependentAssembly>
        <assemblyIdentity name="System.Data.SqlServerCe" publicKeyToken="89845dcd8080cc91" />
        <bindingRedirect oldVersion="4.0.0.0" newVersion="4.0.0.1" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
</configuration>

Notice the Probing setting above, that has been added. Also notice that this comes before the bindingRedirect.

Hop you find this useful.

SQL Server Compact Toolbox 2.2–Visual Guide of new features

After more that 32.000 downloads, version 2.2 of my 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

Generate an Windows Phone DataContext (.cs) in the current project (beta feature)

This feature allows you to create a DataContext for use with Windows Phone “Mango” projects, based on an existing SQL Server Compact 3.5 database.

This will be a huge timesaver, and allows you to reuse the effort you may already have put in creating your database schema, including foreign keys and the required indexes. It will also save you much repetitive code, as the generated classes reflect your tables 1:1.

This technique even works with Northwind.sdf, despite object names with spaces. None of this is (of course) supported in any way by Microsoft.

Under the covers, I am using SQLMetal to generate a desktop DataContext class, and then this class is enhanced to work well in a Windows Phone project.

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 classe and entity classes are partial and can be expanded
- Invalid DataContext constructors removed
- The generated DataContext contains Index definitions (which SqlMetal does not support, as this is a Windpows Phone extension)
- The generated DataContext contains the CreateIfNotExists method, that optionally extracts an included database (prepopulated with data) to Isolated Storage

If you also would like to include your desktop database with your XAP, the generated DataContext contains the code from this blog post and supports the procedure described in the blog post.

Please provide any feedback for this beta feature to the CodePlex issue tracker.

Add Description to tables and columns

On Database, Table and Column level, there is a new menu item: Edit Description, that allows you to enter a object description. The object description are stored in a table named __ExtendedProperties in the current database.

The description is then shown as a tooltip for the object:

In addition, you can reuse the data for documentation etc. (I am planning a documentation feature in a future release)

Options dialog for saving options from session to session

A new options dialog is available from the main toolbar:

SQL Editor improvements

The SQL Editor (again) allows you to display the results in a Grid, rather than text. This can be selected via the Options above. (Using grid will be slower and requires more memory). Also, a Save Script button has been added.

Other minor improvements

Latest scripting libraries included, with improvements to Db Diff and primary keys with multiple columns

Thanks to all Toolbox users for your continued encouragement and feedback!