Everything SQL Server Compact

SQL Server Compact Code Snippet of the Week #15 : flush data to disk immediately

noreply@blogger.com (ErikEJ) — Tue, 21 May 2013 17:09:00 +0000

Under normal operation, SQL Server Compact keeps all pending disk writes in memory, and flushes them to disk at least every 10 seconds. The connection string property to control this is called “Flush Interval”, and valid values are between 1 and 1000 seconds. But you may want to flush to disk immediately under certain circumstances, and this weeks code snippet demonstrates how to do just that. This is possible via the CommitMode property on the SqlCeTransaction object Commit method, as demonstrated below:

using (SqlCeConnection conn = new SqlCeConnection(@"Data Source=C:\data\AdventureWorks.sdf;"))
{
    conn.Open();
    // Start a local transaction; SQL Server Compact supports the following
    // isolation levels: ReadCommitted, RepeatableRead, Serializable
    using (SqlCeTransaction tx = conn.BeginTransaction(IsolationLevel.ReadCommitted))
    {
        using (SqlCeCommand cmd1 = conn.CreateCommand())
        {
            // To enlist a command in a transaction, set the Transaction property
            cmd1.Transaction = tx;
            try
            {
                cmd1.CommandText = "INSERT INTO FactSalesQuota " +
                    "(EmployeeKey, TimeKey, SalesAmountQuota) " +
                    "VALUES (2, 1158, 150000.00)";
                cmd1.ExecuteNonQuery();

                // Commit the changes to disk immediately, if everything above succeeded;
                // Use Deferred mode for optimal performance; the changes will
                // be flashed to disk within the timespan specified in the
                // ConnectionString 'FLUSH INTERVAL' property (default 10 seconds);
                //
                tx.Commit(CommitMode.Immediate);
            }

            catch (Exception)
            {
                tx.Rollback();
            }
        }
    }
}

SQL Server Compact Code Snippet of the Week #14 : script all data in a table

noreply@blogger.com (ErikEJ) — Thu, 09 May 2013 07:23:00 +0000

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 INSERT statement for each row in the table.

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

For SQL Server Compact 4.0, the scripting library is now available on NuGet, making it even easier to get started. https://nuget.org/packages/ErikEJ.SqlCeScripting

SQL Server Compact Code Snippet of the Week #13 : reseed (reset) an IDENTITY column

noreply@blogger.com (ErikEJ) — Fri, 03 May 2013 05:42:00 +0000

A question that keeps re-appearing in the forum is, how can I reseed/reset an IDENTITY column in SQL Server Compact, as no DBCC command is available. You can simply use a special syntax of the ALTER TABLE command to do this, as follows:

ALTER TABLE [MyTableName] ALTER COLUMN [Id] IDENTITY (1, 1)

This will make the value of the next generated IDENTITY value 1 and increment with 1. Notice that you do not specify the column type, only the IDENTITY specification.

SQL Server Compact Code Snippet of the Week #12 : get the SQL Server Compact runtime version

noreply@blogger.com (ErikEJ) — Wed, 17 Apr 2013 13:19:00 +0000

Your app/web site may require a specific build version of the SQL Server Compact runtime, due to dependency on a bug fix, for example. This weeks code snippet will demonstrate how to get that information. I have an older blog post here, that goes into deep details about the various ways to get SQL Server Compact related version information.

To get the SQL Server Compact build version for 3.5 SP2 and 4.0, you can simply use:

var ver = new System.Data.SqlServerCe.SqlCeConnection().ServerVersion;

MSDN docs here.

For SQL Server Compact 4.0 SP1, this returns: 4.0.8876.1.

And for SQL Server Compact 3.5 SP2 CU6, the value would be: 3.5.8088.0

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

noreply@blogger.com (ErikEJ) — Wed, 10 Apr 2013 16:59:00 +0000

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.

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).

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

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

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

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

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

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.

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.

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.

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:

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:

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.

SQL Server Compact Code Snippet of the Week #11 : detect if SQL Server Compact is available

noreply@blogger.com (ErikEJ) — Mon, 08 Apr 2013 07:21:00 +0000

It can sometimes by useful to find out if SQL Server Compact is available on a system, for example in installers and if your application supports several database products. This week’s code snippet allows you to detect if the ADO.NET Provider is available and properly installed (it does not detect if the unmanaged DLL files are present). The code detects both if the AD.NET Provider is present, and if the DbProvider interface is properly registered (either in machine.config or in a local config file)

public bool IsV40Installed()
{
    try
    {
        System.Reflection.Assembly.Load("System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91");
    }
    catch (System.IO.FileNotFoundException)
    {
        return false;
    }
    try
    {
        var factory = System.Data.Common.DbProviderFactories.GetFactory("System.Data.SqlServerCe.4.0");
    }
    catch (System.Configuration.ConfigurationException)
    {
        return false;
    }
    catch (System.ArgumentException)
    {
        return false;
    }
    return true;
}

Replace Version=4.0.0.0 with Version=3.5.1.0 and use System.Data.SqlServerCe.3.5 for the DbProvider invariant name for SQL Server Compact 3.5

WCF Data Services with Windows Phone - bandwidth requirements measured

noreply@blogger.com (ErikEJ) — Tue, 19 Mar 2013 12:20:00 +0000

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.3–Visual Guide of new features

noreply@blogger.com (ErikEJ) — Fri, 15 Mar 2013 11:14:00 +0000

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:

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.

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.

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:

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

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

noreply@blogger.com (ErikEJ) — Tue, 12 Mar 2013 14:25:00 +0000

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.

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

noreply@blogger.com (ErikEJ) — Fri, 08 Mar 2013 13:20:00 +0000

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.

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

noreply@blogger.com (ErikEJ) — Sun, 03 Mar 2013 09:28:00 +0000

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

Some SQL Server Compact Toolbox usage graphs

noreply@blogger.com (ErikEJ) — Mon, 25 Feb 2013 13:25:00 +0000

The anonymously collected usage statistics provide by SmartAssembly in the latest release of the Toolbox also provides some general counters, that may be of general interest. Keep in mind that these figures are collected amongst Visual Studio users, and therefore do not represent the general public. The figures represent about 2000 Toolbox installations.

Visual Studio version used

More than 50% of the Toolbox users have Visual Studio 2012 installed.

OS platform

30% of the Toolbox users are using Windows 8, 66% Windows 7 and hardly any use Vista/XP

OS bitness

Only 15% of the Toolbox users run a 32 bit OS.

CPU cores

Toolbox users have modern machines – 75% of the users have a CPU with 4 cores or more

Fix for Entity Framework poor INSERT performance with SQL Server Compact and server generated keys

noreply@blogger.com (ErikEJ) — Tue, 19 Feb 2013 21:21:00 +0000

In this blog post I will describe the steps I took in order to find out why the title above was the case, and how it could be fixed.

On Stackoverflow the general opinion was that the reported slowness was “by design” and could not be fixed, but looking at recent tests posted on Stackoverflow pointed to the fact that something was not done right.

Since Entity Framework is now Open Source and available on CodePlex, I decided to have a deeper look.

To test if the process could be improved, I created the following console app:

   1:  namespace EF6SqlCETest

   2:  {

   3:      using System;

   4:      using System.Data.Entity;

   5:      using System.Diagnostics;

6:

   7:      class Program

   8:      {

   9:          static void Main(string[] args)

  10:          {

  11:              using (var db = new StudentContext())

  12:              {

  13:                  Stopwatch sw = new Stopwatch();

  14:                  db.Database.Delete();

  15:                  sw.Start();

  16:                  db.Database.CreateIfNotExists();

  17:                  db.Configuration.AutoDetectChangesEnabled = false;

  18:                  db.Configuration.ProxyCreationEnabled = false;

  19:                  Console.WriteLine(

  20:                      "Db created in {0}", sw.Elapsed.ToString());

  21:                  sw.Restart();

  22:                  for (int i = 0; i < 4000; i++)

  23:                  {

  24:                      var student = new Student { Name = Guid.NewGuid().ToString() };

  25:                      db.Students.Add(student);

  26:                  }

  27:                  Console.WriteLine(

  28:                      "Entities added in {0}", sw.Elapsed.ToString());

29:

  30:                  sw.Restart();

  31:                  int recordsAffected = db.SaveChanges();

  32:                  sw.Stop();

  33:                  Console.WriteLine(

  34:                      "Saved {0} entities to the database, press any key to exit.",

  35:                      recordsAffected);

  36:                  Console.WriteLine(

  37:                      "Saved entities in {0}", sw.Elapsed.ToString());

  38:                  Console.ReadKey();

  39:              }

40:

  41:          }

  42:      }

43:

  44:      public class Student

  45:      {

  46:          public int Id { get; set; }

  47:          public string Name { get; set; }

  48:      }

49:

  50:      public class StudentContext : DbContext

  51:      {

  52:          public DbSet<Student> Students { get; set; }

  53:      }

54:

  55:  }

The test project and the related app.config is available for download here: http://sdrv.ms/UCL2j5

The test code is a simple Code First DbContext model. For each run I start with a new blank database, and creates it before doing SaveChanges, so that part of the process can be timed individually. The 2 options on lines 17 and 18 are there to ensure that the for loop runs quickly, without these option the loop adding objects takes much longer (test for yourself).

The resulting table looks like this:

CREATE TABLE [Students] (
  [Id] int NOT NULL  IDENTITY (1,1)
  , [Name] nvarchar(4000) NULL
);
GO
ALTER TABLE [Students] ADD CONSTRAINT [PK_dbo.Students] PRIMARY KEY ([Id]);
GO

In order to find out where time was spent during SaveChanges, I ran a Visual Studio Performance Analysis. It turned out that all the time was spent in sqlceqp40.dll, the SQL Server Compact 4.0 unmanaged query processor – so something was amiss.

As described in my earlier blogpost, the SQL statements generated in order to return the server generated id (the IDENTITY value), looked like the following:

SELECT [Id] FROM [Students] WHERE [Id] = @@IDENTITY

So using the SQL Server Compact Toolbox, I coulde analyze the 2 statements:

And got the following result:

So for every INSERT, a table scan was performed, as for some reason, the SQL Server Compact query processor could not figure out to do an Index Seek. And the more rows to be scanned, the worse the performance got. And all the time for the operation was spent doing this.

In order to avoid this, I decided that the goal of the statement executed should be to avoid table scans, but return a value with the exact same shape as the previous statement executed, that is; it should have the name of the IDENTITY column, and be of the correct type (only bigint and int are supported as IDENTITY types with SQL Server Compact).

The return value of @@IDENTITY is numeric, so simply using “SELECT @@IDENTITY AS [Id]” would not work. So the statement could be:

SELECT [Id] FROM [Students] WHERE [Id] = CAST(@@IDENTITY as int)

The type could then be either int or bigint and the column alias should of course be the correct column name.

I could then analyze the modified statement:

And no table scan, instead an index seek, consuming only 19% of the total query cost instead of 100%.

And so this is what I have implemented in my fix, that I now need to figure out how to “submit a pull request” for.

SQL Server Compact Code Snippet of the Week #7 : get the full path to a database file

noreply@blogger.com (ErikEJ) — Tue, 19 Feb 2013 03:59:00 +0000

A SQL Server Compact connection string allows you to specify the database name in various ways:

Data Source=c:\data\mydb.sdf

Data Source=mydb.sdf

Data Source=|DataDirectory|\mydb.sdf

But sometimes you need the full path to the database file based on a user defined connection string. This week’s code snippet allows you to do exactly that, and it is a little bit quirky, as it works around a bug in SQL Server Compact 4.0 SP1, that causes use of |DataDirectory| to not be resolved correctly using only the SqlCeConnectionStringBuilder.

 public string PathFromConnectionString(string connectionString)
 {
      SqlCeConnectionStringBuilder sb = new SqlCeConnectionStringBuilder(GetFullConnectionString(connectionString));
      return sb.DataSource;
 }

 public string GetFullConnectionString(string connectionString)
 {
     using (SqlCeReplication repl = new SqlCeReplication())
     {
         repl.SubscriberConnectionString = connectionString;
         return repl.SubscriberConnectionString;
     }
 }

Notice that the code above only works with version 4.0, not 3.5

SQL Server Compact Code Snippet of the Week #6 : list all user tables in a database

noreply@blogger.com (ErikEJ) — Tue, 12 Feb 2013 13:31:00 +0000

This week’s short code snippet shows how to list all the user table objects in a SQL Server Compact database file. Notice that a SQL Server Compact database can also contain a number of system tables, these can be created by the Merge Replication/RDA/Sync Framework APIs. The metadata (like list of tables) is not exposed as tables, but as something called views, specifically the INFORMATION_SCHEMA views (despite the fact that SQL Server Compact does not support user defined views).

SELECT table_name AS Name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE <> N'SYSTEM TABLE'

SQL Server Compact Code Snippet of the Week #5 : rename a table

noreply@blogger.com (ErikEJ) — Mon, 04 Feb 2013 16:43:00 +0000

The SQL Server Compact ADO.NET provider allows you to rename a table name by using the special sp_rename T-SQL “command” (SQL Server Compact does not support stored procedures, but this “command” corresponds to the sp_rename system stored procedure on SQL Server).

You cannot only use sp_rename against a table in SQL Server Compact using the ADO.NET interfaces, but using the native OLEDB interfaces, you can also rename columns, as Joao demonstrates here. This is used in his Data Port Console product. Otherwise you can just script a DROP and CREATE statement with the SQL Server Compact Toolbox, and rename both column and table names in the CREATE script.

To rename a table, use the following T-SQL code:

sp_rename 'OldName', 'NewName'

Fixing the Entity Framework designer “Generate Database from Model” T4 template

noreply@blogger.com (ErikEJ) — Fri, 01 Feb 2013 12:00:00 +0000

The Entity Framework Designer include a reverse engineer feature called “Generate Database from Model” that enables a “Model first” workflow, and also enable you to persist any model modification in a new database.

The T4 template used for this feature supports both SQL Server and SQL Server Compact, but unfortunately lacks consistency in it’s use of the GO keyword. GO is used after each statement, except FOREIGN KEY constraint creation statements. Apart for being inconsistent, this also prevents SQL Server Compact Toolbox from executing the script, without a number of manual edits.

I have proposed a fix on Codeplex for the next version of the designer, but it will not happen until version 7 (if ever).

So in the meantime, I have updated the template to fix this, you can start using it today as replacement for the current one as follows:

1: Download my updated T4 file from here. (The same file is used in both Visual Studio 2010 and 2012)

2: Copy the downloaded SSDLToSQL10GOFixed.tt file to the
C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\DBGen folder
(for VS 2012)
or to
C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\DBGen
(for VS 2010).

3: With the Entity Framework designer open, go to properties, and select the new file as the DDL Generation template:

4: Generate the script.

SQL Server Compact Code Snippet of the Week #4 : select rows 50 to 60 from a table

noreply@blogger.com (ErikEJ) — Mon, 28 Jan 2013 11:20:00 +0000

For many scenarios it can be useful to be able to get only a subset of rows fro a long result set. SQL Server Compact version 4.0 adds support for the OFFSET – FETCH keywords, that allows you to retrieve a single “page” of data. Sadly, this is not possible with version 3.5. Notice, that this syntax is supported on the SQL Server Compact version used on Windows Phone, despite the 3.5 label of that runtime.

For 4.0 you can use:

SELECT [Order ID]
,[Product ID]
,[Unit Price]
,[Quantity]
,[Discount]
FROM [Order Details]
ORDER BY [Order Id]
OFFSET 50 ROWS FETCH NEXT 10 ROWS ONLY
GO

This statement returns only 10 rows.

For 3.5, you can only use TOP, and must then skip the rows that you do not need:

SELECT TOP (60) [Order ID]
,[Product ID]
,[Unit Price]
,[Quantity]
,[Discount]
FROM [Order Details]
ORDER BY [Order Id]
GO

This statement returns 60 rows, and you must manually skip the first 50.

SQL Server Compact Code Snippet of the Week #3 : locate the rowversion column in a table

noreply@blogger.com (ErikEJ) — Tue, 22 Jan 2013 08:47:00 +0000

The third data type that is only allowed to appear in a single column per table is the rowversion data type, also known as timestamp (no relation to datetime). Rowversion is a data type that exposes automatically generated, unique binary numbers in a database, and contains unique values across the entire database. The size is 8 bytes. You can use the @@DBTS function to get the most recent rowversion value in the database.

Here is the code to determine if a table has a rowversion column, and get the column ordinal (position in the table):

 public Int32 GetRowVersionOrdinal(string tableName)
        {
            object value = ExecuteScalar("SELECT ordinal_position FROM information_schema.columns WHERE TABLE_NAME = '" + tableName + "' AND data_type = 'rowversion'");
            if (value != null)
            {
                return (int)value - 1;
            }
            return -1;
        }

SQL Server Compact Code Snippet of the Week #2 : locate the IDENTITY column in a table

noreply@blogger.com (ErikEJ) — Mon, 14 Jan 2013 18:15:00 +0000

The next instalment in this series also concerns a column type that only exists once per table, the IDENTITY column. The type of the column must be either bigint or int, and you must also specify an initial value (seed) and the value for the difference between each value (the increment), the default values for both being 1.

In order to detect which column is the IDENTITY column, you can use the following SQL statement (an code):

        public bool HasIdentityColumn(string tableName)
        {
            return (GetIdentityOrdinal(tableName) > -1);
        }

        public int GetIdentityOrdinal(string tableName)
        {
            object value = ExecuteScalar("SELECT ordinal_position FROM information_schema.columns WHERE TABLE_NAME = N'" + tableName + "' AND AUTOINC_SEED IS NOT NULL");
            if (value != null)
            {
                return (int)value - 1;
            }
            return -1;
        }

SQL Server Compact Toolbox 3.2–Visual Guide of new features

noreply@blogger.com (ErikEJ) — Fri, 11 Jan 2013 12:27:00 +0000

After more than 150.000 downloads, version 3.2 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 from SQL Server to SQL Server Compact 4.0 in a single workflow

Previously with the Toolbox, in order to export a SQL Server database to SQL Server Compact, you had to export a script, create a new blank 4.0 database file, and then run the script against this new file. This process has been simplified to a single workflow, where you define the database and tables to export, and then specify the 4.0 database file name.

To use the feature, right click the root node in the Toolbox:

Select the server database:

If selecting a LocalDB database, type (localdb)\v11.0 in the server name box, and type the database name, do not pick the database name from the dropdown list and do not press the test connection button. This is caused by the fact that this connection dialog (that supports SQL Server authentication) uses an old API to connect to SQL Server, which only partially works with LocalDB.

Select which tables to be scripted:

And type the name of the new SQL Server Compact database file:

Once the background process completes, you can see in the Visual Studio status bar that the export completed:

As this is a new feature, please provide any suggestion for improving it…

Option to preserve Server schema names as part of Server export files

There is now a new option that enables you to keep the Server schema name (for example dbo) as part of the exported table name:

Once this feature is enabled, the resulting CREATE TABLE script will look like this:

CREATE TABLE [dbo.Shippers] (

[Shipper ID] int NOT NULL IDENTITY (33,1)

, [Company Name] nvarchar(40) NOT NULL

);

rather than the usual:

CREATE TABLE [Shippers] (

[Shipper ID] int NOT NULL IDENTITY (33,1)

, [Company Name] nvarchar(40) NOT NULL

);

This does not mean that SQL Server Compact now supports or understands the concept of schemas, it simply means that the table name is now: “dbo.Shippers”

Red Gate SmartAssembly integration

As I blogged about earlier, SmartAssembly is now integrated with the Toolbox for error reporting and anonymous feature usage tracking. If you encounter an error that merits being reported to me, so I can fix it in a future release, please do so.

Once an error is encountered, you will see this dialog:

If the error is not expected, click “Send Error Report”:

Enter your email address if desired, and I will contact you regarding the error, and click send:

I have also added an option to opt out of feature tracking:

Improvements

SQL Server tables displayed are properly sorted by schema.name:

Notifying user that 3.5 SP2 is required for many features to work - this has now become an issue on new Windows 8 installation with Visual Studio 2012 only, as SQL Server Compact 3.5 SP2 is not installed on these systems:

Click the red link:

As always, you can use the About box (blue question mark on the toolbar above) to see what SQL Server Compact components you have installed (notice that the Visual Studio 2012 Server Explorer does not support SQL Server Compact 3.5, so no DDEX provider in VS 2012):

Improved SQL Server DGML diagram, the DGML feature now works with AdventureWorks2012.

Improved object tree view performance, in particular for databases with many objects.

Bug fixes

SQL query editor button texts now visible with Visual Studio 2012 dark theme:

Merge Replication dialog fixed to work with "Windows Integrated Authentication":

Updated scripting API, with foreign key related bug fixes, and various performance improvements

SQL Server Compact Code Snippet of the Week #1 : locate the ROWGUIDCOL column in a table

noreply@blogger.com (ErikEJ) — Mon, 07 Jan 2013 14:32:00 +0000

During the next many weeks, I plan to publish a short, weekly blog post with a (hopefully) useful code snippet relating to SQL Server Compact. The code snippets will come from 3 different areas: SQL Server Compact T-SQL statements, ADO.NET code and samples usage of my scripting API.

The ROWGUIDCOL column property is defined like this in Books Online:

Indicates that the new column is a row global unique identifier column. Only one uniqueidentifier column per table can be designated as the ROWGUIDCOL column. The ROWGUIDCOL property can be assigned only to a uniqueidentifier column.

ROWGUIDCOL automatically generates values for new rows inserted into the table.

(You can also use a default of NEWID() to automatically assign values to uniqueidentifier columns)

The ROWGUIDCOL is used by Merge Replication, all Merge Replicated tables must have a ROWGUIDCOL column.

Enough talk, show me the code snippet:

SELECT column_flags, column_name, table_name 
FROM information_schema.columns
WHERE column_flags = 378 OR column_flags = 282

I am using the undocumented “column_flags” column to determine the ROWGUIDCOL column, and the reason for the 2 different values is that a uniqueidentifier column can be either NULL or NOT NULL.

Integrating Red Gate SmartAssembly in the SQL Server Compact Toolbox

noreply@blogger.com (ErikEJ) — Wed, 19 Dec 2012 10:25:00 +0000

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.

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" :

<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:

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.

SQL Server Compact Merge Replication Library alpha released

noreply@blogger.com (ErikEJ) — Thu, 29 Nov 2012 14:27:00 +0000

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>

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).

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

noreply@blogger.com (ErikEJ) — Tue, 27 Nov 2012 08:20:00 +0000

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.

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.