Tuesday, March 19, 2013

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 typeBody 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) + gzip328,410 (3,5 %)0.31 MB
JSON (plain)790,845 (8,5 %)0.75 MB
JSON (plain) + gzip43,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.

Friday, March 15, 2013

SQL Server Compact Toolbox 3.3–Visual Guide of new features

After more than 170.000 downloads, version 3.3 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

New text editor

In this release, the text editor used for the SQL Query window has been updated to use the AvalonEdit text control. The new control features improved syntax colour highlighting, also available during editing. In addition, the editor also enables search (use the new Search button on the toolbar), with hit highlighting:

clip_image002

New Maintenance menu

In order to support the fact that the Server Explorer is no longer available for version 3.5 in Visual Studio 2012, I have now slowly begun adding features from the SQL Server Compact Server Explorer Tools. The first is the maintenance options: Shrink, Compact, Verify and Repair.

clip_image003

Reinitialize Merge Replication subscriptions

In addition to synchronizing Merge Replication subscriptions, it is now possible to re-initialize a subscription, meaning starting over with a new set of data from the server, but retaining any local data.

clip_image004

Script SQL Server data only

The SQL Server scripting menu items have moved to a scripting menu and the missing “Script SQL Server Database Data” option is now there:

clip_image006

Improvements


SQL Server export - improved handling of default values and identical table names in different schemas (but this is not yet fully supported)
Less SmartAssembly prompts
CSV import error fixed

Tuesday, March 12, 2013

SQL Server Compact Code Snippet of the Week #10 : generate a CREATE TABLE script

Another entry in the scripting API samples, you will find an overview of getting started with the API here.

This weeks entry demonstrates the general pattern for scripting smaller chunks of SQL based on a single table, in this case a CREATE TABLE statement. The statement will also include any indexes and primary/foreign keys.

using (IRepository repository = new DBRepository(@"Data Source=C:\Northwind.sdf"))
{
Generator generator = new Generator(repository, null);
foreach (var tableName in repository.GetAllTableNames())
{
generator.GenerateTableScript(tableName);
}
System.IO.File.WriteAllText(@"C:\script.sqlce", generator.GeneratedScript);
}

The Generator / Generator4 class contains many methods to generate various script snippets, some of them listed here. After a number of GenerateXx calls, you can use the GeneratedScript property to get the accumulated script.

Friday, March 8, 2013

SQL Server Compact Code Snippet of the Week #9 : migrate a SQL Compact database to SQL Server

This week’s snippet directly follows the one from previous week, demonstrating my SQL Server Compact scripting API once again.

This time I will demonstrate how to migrate a complete SQL Server Compact database to SQL Server (LocalDB/Express/Full). The requirements are simply that the current user has access to an empty SQL Server database somewhere. Then all tables, constraints, indexes and data will be moved to the empty SQL Server database, all in just 6 lines of code:

using (IRepository ceRepository = new DB4Repository(@"Data Source=C:\Data\SQLCE\Test\nw40.sdf"))
{
string fileName = Path.GetTempFileName();
var generator = new Generator4(ceRepository, fileName);
generator.ScriptDatabaseToFile(Scope.SchemaData);
using (IRepository serverRepository = new ServerDBRepository4("Data Source=.;Trusted_Connection=true;Initial Catalog=Test"))
{
serverRepository.ExecuteSqlFile(fileName);
}
}

The code requires the following using statements:


using ErikEJ.SqlCeScripting;
using System;
using System.IO;


The ServerDBRepository constructor simply requires any valid SQL Server ADO.NET connection string.


The ScriptDatabaseToFile creates a script file with all content of the database, and the ExecuteSqlFile method runs the script against a SQL Server database.

Sunday, March 3, 2013

SQL Server Compact Code Snippet of the Week #8 : script a database to SQLite

This next instalment switches gears, and will demonstrate a simple usage of my SQL Server Compact scripting API, available on CodePlex.

This sample demonstrates how to create a script in SQLite format, but the general usage pattern is the same no matter what you are scripting. Notice that only three lines of code are required to complete this task!

In order to use the API, you must have the SQL Server Compact runtime installed and then reference the scripting API files. In order to script then add references to the scripting API DLL files,  SqlCeScripting40.dll and ISqlCeScripting.dll (use SqlCeScripting.dll instead of SqlCeScripting40.dll if you want to script 3.5 database files).

using ErikEJ.SqlCeScripting;

namespace SqlCeScript
{
class Program
{
static void Main(string[] args)
{
using (IRepository repository = new DB4Repository(@"Data Source=C:\Data\SQLCE\Test\nw40.sdf"))
{
IGenerator generator = new Generator4(repository, @"C:\Temp\nwlite.sql");
generator.ScriptDatabaseToFile(Scope.SchemaDataSQLite);
}
}
}
}



First you initialize an IRepository instance, which takes a SQL Server Compact connection string as parameter. The you initialize a IGenerator instance, which takes a IRepository instance and an optional file name as parameter.


Then you can simply call ScriptDatabaseToFile which takes a scope a parameter, which defines what to be scripted; data, schema, schema and data, or as in this case schema and data in SQLite format.


The following IRepository implementations are available: DBRepository (SQL Server Compact 3.5 SP2), DB4Repository (SQL Server Compact 4.0) and ServerDBRepository (SQL Server 2005 or later)


In order to create a SQLite database for the script file created (c:\temp\nwlite.sql), you can use the sqlite3.exe command line utility like so:


sqlite3 nwlite.db < nwlite.sql