“SQL Server Compact & SQLite Toolbox” related news

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

Visual Studio 2013 Community Edition

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

image

Visual Studio Auto Updater

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

image

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

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

VSToolbox2

Version 4.2 preview

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

Editor2

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

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

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

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

SQL Server Compact Toolbox 3.7.3 – Visual Guide of new features

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

New features

Export SQL Server database to SQLite script (beta)

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

SQLIteExport

“Set Password” option added to Maintenance menu

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

setpw

Enable entry of multiline text in DataGridView

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

Support for VS “14” CTP

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

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

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

image

 

Bug Fixes

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

image

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

image

Add Column – defaults to Allow Null

image

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

image

image

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

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

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

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

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

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

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

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

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

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

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

An alternative to Merge Replication with SQL Server and SQL Server Compact – Zumero for SQL Server

While looking for a migration path for a customer currently using Merge Replication with SQL Server and Windows Mobile, I came across the recently released solution from Zumero, Zumero for SQL Server. As mentioned in my previous blog post, Merge Replication between SQL Server Compact and SQL Server 2014 is no longer possible, and in addition, SQL Server Compact 3.5 SP2 only supports a limited number of client platforms (Windows Mobile/CE and Windows desktop). Microsoft is promoting Azure Mobile Services with Offline support, but for obvious reasons, this solution does not work for on premise databases.

Zumero for SQL Server enables you to synchronize any mobile device with tables on a central SQL Server, using essentially the same components that we know from Merge Replication:

1: Configuration of tables to be synchronized, and added metadata/tracking to those. Before: Using SQL Server Management Studio to create a Merge Publication with articles (tables)
Now: Using ZSS Manager to create a DBFile with Tables

2: An IIS based agent, that exposes a http(s) endpoint for performing the sync process.
Before: Configure Web Synchronization Wizard
Now: ZSS Server

3: Client library for performing sync.
Before: System.Data.SqlServerCe.SqlCeReplication class, part of the SQL Server Compact ADO.NET provider
Now: Zumero Client SDK and SQLite.

using Zumero;

ZumeroClient.Sync(
"/path/to/sqlite.db", // full path to local db file
null,
"http://MY_ZUMERO_SERVER:8080",
"test", // remote DBFile name
null,
null,
null);

To get started testing out Zumero, simply follow the instructions here: http://zumero.com/get-started/ or start by watching the short, introductory videos here: http://zumero.com/howto/

Notice that Zumero works with any edition of SQL Server 2008 R2 Express or higher/later. Zumero Server is not free, but works with the free SQL Server Express for small scale solutions.

On the client side, the following impressive list client Operating Systems are supported:

Android (native and PhoneGap)
iOS (native and PhoneGap)
Xamarin
Windows, WinRT and Windows Phone 8
Mac OS X
Linux (Ubuntu 12.04+, Fedora 17+)

In my next blog post, I will be doing an interview:  “Hard Talk” with Eric Sink, Zumero founder.

Disclaimer: I am simply a Zumero user, and not affiliated in any way.

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 Toolbox 3.4–Visual Guide of new features

After nearly 190.000 downloads, version 3.4 of my free, open source 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

Data compare

The major new feature of this release is the table based data compare feature, that will generate a script with INSERT, UPDATE and DELETE statements to make two tables contains the same data. The two tables must have compatible schemas and same names. The feature works across both SQL Server and SQL Server Compact tables.

To try out this new (beta) feature, right click on a table and select “Compare Data…”:

clip_image002

Select the target database:

clip_image003

A script with the required statements will then open in the SQL editor.

Database Information

This feature will script information about the selected database in the SQL editor, both general information about the database, including Locale ID and case sensitivity, and also list number of rows for all user tables.

clip_image004

clip_image005

Maximum column width in Edit grid

This new option allows you to set a limit on the column width in the edit grid, useful if you have some columns with very long text string, and you want them all to be visible.

clip_image007

After setting the option to for example 200 pixels:

clip_image009

Visual Studio 2013 support

Server Explorer in Visual Studio 2013 no longer supports SQL Server Compact 4.0, and other tools that depend on Server Explorer (DDEX) will no longer work with SQL Server Compact 4.0. However, you can still use the SQL Server Compact Toolbox in Visual Studio 2013, both with version 3.5 and 4.0 database files. The only requirement is that you have the relevant SQL Compact runtime MSIs installed. In addition, the Toolbox supports code generation of LINQ to SQL DataContext classes, both for Windows Phone 7.5/8 and Desktop apps. For Entity Framework, no code generation is required provided you use the Code First workflow.

clip_image010

Other improvements and bug fixes

Validate Connections improved
Merge Replication save properties fixed
CSV import unicode issue fixed + improved error message
milliseconds included in datetime in text results
SQLite script contained GO separators, they are no longer there
Avoided scripting spatial indexes (SQL Server scripting)

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

WCF Data Services with Windows Phone – bandwidth requirements measured

In connection with testing Synchronization between a SQL Server Compact database on Windows Phone 8 and a SQL Server database (via a WCF Data Services service hosted in IIS), I have done some interesting observations regarding bandwidth requirements, that I would like to share.

I am testing against the Chinook sample database, by downloading the entire PlaylistTrack table (8715 rows) to the device via my WCF Data Services service. On the server side, I am using the latest release version of the WCF Data Services server components, version 5.3.0. Version 5.1 or later includes the newer lightweight JSON format (just to compare I am also including sizes for the previous JSON format)

On the server side, I have created a ASP.NET Web Application with a WCF Data Service, that exposes the Chinook database on my SQL Server via an Entity Framework DbContext. The power of WCF Data Services is that this requires basically no code to configure. I have configured my service like this:

    public class SyncService : DataService<ChinookEntities>
{
// This method is called only once to initialize service-wide policies.
public static void InitializeService(DataServiceConfiguration config)
{
config.UseVerboseErrors = true;
//config.SetEntitySetAccessRule("TrackPurchases", EntitySetRights.WriteAppend);
config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V3;
}
}

In order to access the IIS Express hosted service from my Windows Phone 8 emulator, I followed the instructions here: http://msdn.microsoft.com/en-us/library/windowsphone/develop/jj684580(v=vs.105).aspx 

To measure the size of the payload, I am using Fiddler2, by following the instructions here: http://blogs.msdn.com/b/fiddler/archive/2010/10/15/fiddler-and-the-windows-phone-emulator.aspx

The WCF Data Services team also supply a WCF Data Services client for Windows Phone, that can take advantage of a Service Reference, but this client has some severe limitations, that affects bandwidth consumption in a bad way: It only supports the XML based ATOM format, but you can enable compression, as described here: http://blogs.msdn.com/b/astoriateam/archive/2011/10/04/odata-compression-in-windows-phone-7-5-mango.aspx 

On the client side, I am simply using HttpWebRequest to call the REST url, and including support for gzip via the ICSharpCode.SharpZipLib library (for example http://nuget.org/packages/SharpZipLib-WP7/ )

Here is the implementation of the WebClient:

        static public async Task<T> GetData<T>(Uri uri, bool useJson = true, bool version3 = true, bool compress = true)
{
//uri = new Uri(uri.AbsoluteUri + "&format=json");
HttpWebRequest client = WebRequest.CreateHttp(uri);
{
if (compress)
client.Headers[HttpRequestHeader.AcceptEncoding] = "deflate, gzip";
if (version3)
{
client.Headers["MaxDataServiceVersion"] = "3.0";
}
else
{
client.Headers["MaxDataServiceVersion"] = "2.0";
}
if (useJson)
client.Accept = "application/json";

using (WebResponse response = await client.GetResponseAsync())
{
string result = await response.GetResponseText();

DataContractJsonSerializer serializer = new DataContractJsonSerializer(typeof(T));
T resultType;
using (MemoryStream stream = new MemoryStream(Encoding.Unicode.GetBytes(result)))
{
resultType = (T)serializer.ReadObject(stream);
}
return resultType;
}
}
}

public static async Task<string> GetResponseText(this WebResponse response)
{
using (
Stream stream = response.IsCompressed()
? new GZipInputStream(response.GetResponseStream())
: response.GetResponseStream())
{
using (var reader = new StreamReader(stream))
{
return await reader.ReadToEndAsync();
}
}
}

public static bool IsCompressed(this WebResponse response)
{
return Regex.IsMatch((response.Headers["Content-Encoding"] ?? "")
.ToLower(), "(gzip|deflate)");
}

(I am using Microsoft.Threading.Tasks.Extensions.dll to implement GetResponseAsync)

I am using DataContext classes generated by my SQL Server Compact Toolbox for deserialization, with a small addition – I have added this attribute to all EntitySet<T> and EntityRef<T> properties (this will be included in the next Toolbox release):

[global::System.Runtime.Serialization.IgnoreDataMember]

I am calling the following URL: http://<MyIP>:2065/SyncService.svc/PlaylistTracks

This is my test code:

//ATOM-XML
await WebClient.GetData<PlaylistTrackRoot>(uri, false, false, false);
//Verbose json
await WebClient.GetData<PlaylistTrackRoot>(uri, true, false, false);
//Verbose json + gzip
await WebClient.GetData<PlaylistTrackRoot>(uri, true, false, true);
//Plain json
await WebClient.GetData<PlaylistTrackRoot>(uri, true, true, false);
//Plain json + gzip
await WebClient.GetData<PlaylistTrackRoot>(uri, true, true, true);

public class PlaylistTrackRoot { public List<PlaylistTrack> value { get; set; } }

And finally the unbelievable numbers for the download of the entire PlaylistTrack table with 8715 rows (remember, that ATOM is the default WCF Data Services client format)

Payload type Body size (bytes) Body size (MB)
ATOM-XML 9,322,665 (100 % – default DS client implementation) 8.89 MB
JSON (verbose) 5,016,977 (54 %) 4.78 MB
JSON (verbose) + gzip 328,410 (3,5 %) 0.31 MB
JSON (plain) 790,845 (8,5 %) 0.75 MB
JSON (plain) + gzip 43,023 (0,5 %) 0.04 MB

So before you decide to use the WCF Data Services Windows Phone client, beware that the only format currently available is ATOM-XML. With the 5.1.0 or later desktop client, however, you can use the of the DataServiceContext to request JSON – ctx.Format.UseJson() – the default is still ATOM-XML.

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.