Tuesday, December 14, 2010

Visual Studio Tools for SQL Server Compact 4 now available

The “Microsoft Visual Studio 2010 SP1 Tools for SQL Server Compact 4.0 CTP2” (nice shot name) has just been released. It can be installed from the Web Platform Installer 3.0 or directly from here. UPDATED link points to RTM tools.

image

After installing the SQL Server Compact 4 tools, you now have the same Server Explorer features that you currently have for SQL Server Compact 3.5 SP2.

Here are the Data Sources available from “Add Connection”:

image

The Server Explorer tools provides features such as:
Create, Upgrade, Shrink, Repair, Password protect database files.
Define tables, browse/edit/query data, drop tables, manage relationships, manage indexes.

You can read more about the expected release of SQL Server Compact 4 and the related tools here, and there is more information about VS 2010 SP1 here.

My SQL Server Compact Toolbox add-in has also just been updated with release 1.7 to include the new SQL Server Compact 4 Data Sources from Server Explorer. Just to summarize, the Toolbox supplements the Server Explorer tools, and includes the following features:
- Script tables, including data
- Script entire schema, optionally with data, both of SQL Server Compact and SQL Server 2005 or later databases (in SQL Server Compact T-SQL format)
- Import to SQL Server Compact from a SQL Server 2005/2008 database or a CSV file
- Migrate for SQL Server to SQL Server Compact
- Migrate from SQL Server Compact to SQL Server and SQL Azure
- Basic, free form query execution (not constrained like the one in Server Explorer)
- Parse SQL scripts
- Rename tables
- Create database diff scripts
- Display graphical estimated execution plan (with SSMS 2008 or VS 2001 Premium)
- Generate detailed DGML files for visualizing table columns and relationships (requires VS 2010 Premium or higher to view)
- Create and manage Merge Replication subscriptions
- Community driven CodePlex project, open source

UPDATE: The SQL Server Compact team has posted a short blog about the VS Tools.

Thursday, December 2, 2010

[OT] Team Foundation Server 2008 Web Access SP1 on Windows Server 2008 R2 x64 (standalone server)

Despite the fact, that Team Foundation Server 2008 SP1 (TFS 2008) does not support x64 platforms, I managed to get Team Foundation Work Item Only View to run on a Windows Server 2008 R2 x64 machine. I thought I would blog about it to help others facing the same issues I overcame:

This is what I did (no guarantees implied):

In web.config in the WIWA folder, set limitedMode=true.

Then, when I tested the WIWA app, the following error occurred on the Work Item form: Could not load file or assembly  'Microsoft.TeamFoundation.WorkItemTracking.Controls

I installed Sysinternals Process Monitor, and configured the following filter, to only monitor web application file system errors:

clip_image002

I then ran the application, and noticed the following error:

clip_image002[5]

As you may be able to see, the web page attempts to load a Team system DLL from the C:\Program Files folder, where it should have attempted to load it from the C:\Program Files (x86) folder.

My lame fix was to copy all the files in the PrivateAssemblies folder to a corresponding folder (that I created) in C:\Program Files.

Wednesday, November 24, 2010

SQL Server Compact “Private Deployment” on desktop–an overview

Scenario: You want to deploy a SQL Compact / .NET based application, using private deployment only. Your application depend on a certain .NET Framework version. The table below illustrates your options, as you can see there are special considerations, if your clients only have .NET 4.0 and not .NET 3.5 SP1 installed.

Installing the SQL Server Compact MSI also installs the required Visual C++ runtime files.

Also, notice that all files required for private deployment are included in the private folder in either Program Files or Program Files (x86) (C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\Private) for version 4.0.

For information on private deployment, see SQL Server Compact 3.5 SP2 Books Online and this blog post and this reply. Notice that 3.5 SP2 Books Online is only available “offline”.

UPDATE: Also see this blog post: http://erikej.blogspot.com/2011/02/using-sql-server-compact-40-with.html with a sample MSI project for a WPF app.

UPDATE 2: Also see this blog post for some advanced scenarios: SQL Server Compact Private Deployment tweaks

UPDATE 3: Private deployment of 3.5 SP2 article published

SQL Server Compact 3.5 SP2

SQL Server Compact 3.5 SP2 requires .NET 2.0 or later.

Application

Windows platform
SSCE 3.5 SP2
with .NET 2.0
SSCE 3.5 SP2
with .NET 3.5 SP1
SSCE 3.5 SP2
with .NET 4.0
XP (no .NET included)

NO

NO

NO

XP (.NET 2.0)

YES

NO

NO

XP (.NET 3.5 SP1)

YES

YES

NO

XP (.NET 4.0 only)

NO

NO

NO (1)

XP (.NET 3.5 SP1 and
.NET 4.0)

YES

YES

YES

Vista (.NET 2.0/3.0 included)

YES

NO

NO

Vista (.NET 3.5 SP1)

YES

YES

NO

Vista (.NET 3.0 and
.NET 4.0)

YES

NO

NO (1)

Vista (.NET 3.5 SP1 and
.NET 4.0)

YES

YES

YES

7 (.NET 3.5 SP1 included)

YES

YES

NO

7 (.NET 3.5 SP1 and
.NET 4.0)

YES

YES

YES

       

1: Microsoft Visual C++ 2005 SP1 Redistributable Package (x86)  and/or Microsoft Visual C++ 2005 SP1 Redistributable Package (x64) must be installed.

SQL Server Compact 4.0 RTW

SQL Server Compact 4.0 RTW requires .NET 3.5 SP1 or .NET 4.0

Application

Windows platform
SSCE 4.0
with .NET 3.5 SP1
SSCE 4.0 RTW
with .NET 4.0
XP (no .NET included)

NO

NO

XP (.NET 3.5 SP1)

YES

NO

XP (.NET 4.0 only)

NO

YES

XP (.NET 3.5 SP1 and
.NET 4.0)

YES

YES

Vista (.NET 2.0/3.0 included)

NO

NO

Vista (.NET 3.5 SP1)

YES

NO

Vista (.NET 3.0 and
.NET 4.0)

NO

YES

Vista (.NET 3.5 SP1 and
.NET 4.0)

YES

YES

7 (.NET 3.5 SP1 included)

YES

NO

7 (.NET 3.5 SP1 and
.NET 4.0)

YES

YES

     

Tuesday, November 16, 2010

Using Entity Framework with SQL Server Compact 4.0 CTP and ASP.NET – tips & tricks (part one)

As you may know by now, the major new feature of SQL Server Compact 4.0 is support for ASP.NET starter websites. In addition, the Entity Framework support has been improved with the following features:

- Support for Code First development (requires EF 4 Feature CTP 4), allowing you to create database and schema in code.

. Support for paging (can be implemented with Skip and Take in LINQ).

- Support for server generated keys (IDENTITY)

The purpose of this post is to show how to use Entity Framework 4 with SQL Server Compact 4.0 and ASP.NET without having the full support for SQL Server Compact 4.0 in VS 2010.

I will cover the following scenarios in the sample application:

1: Migrate data and schema for the solution from either SQL Server Compact 3.5 or SQL Server 2005 or later.

2: Create a Type-Per-Table Entity Data Model (EDM) based on a SQL Server Compact 4.0 database.

3: Create a layered application, where the UI has no knowledge of Entity Framework, in order to improve testability, minimize UI code and allow the Data Access layer to be replaced with another Data Access Technology (unlikely, I know Smile)

4: Provide the initial implementation of a music track list displayed in a grid, which can be sorted and paged.

5: Provide access to invoice data in Excel

6: Allow easy management of lookup tables like Genre, Artist and MediaType

7: Use various extension methods, that make your life with Entity Framework smoother.

For this sample I am using the Chinook database from CodePlex, which is delivered in script format and as a 3.5 SDF file. The Chinook database schema represents a digital media store, including tables for artists, albums, media tracks (3500 tracks), invoices and customers.

Creating the database (optionally migrating from 3.5 or Server)

Depending on your starting point, there are many ways to get to a 4.0 database.

If you have a 3.5 database, you can use SqlCeCmd40.exe to upgrade a copy of this database to 4.0:

sqlcecmd40 –d”Data Source=C:\data\Chinook40.sdf” –e upgrade

will do an in-place upgrade for you. (This requires 4.0 CTP2)

If you have a database script, either from a SQL Server database (created by Export2SqlCe.exe or from elsewhere), you can use SqlCeCmd40.exe to create an empty 4.0 database file and import the script:

sqlcecmd40 –d”Data Source=C:\data\Chinook40.sdf” –e create

sqlcecmd40 –d”Data Source=C:\data\Chinook40.sdf” –i C:\x.sql

Or you can use WebMatrix to graphically create the database. (Support for doing this in Visual Studio will come with VS 2010 SP1).

Creating the Data Access Layer

As mentioned, I will create a database first EDM. Due to the missing Server Explorer / Data Sources integration with SQL Compact 4, you will have to do this by using a workaround. I know of 2:

Either by using the command line tools as described in the blog: http://getsrirams.blogspot.com/2010/10/adonet-entity-data-model-for-sqlserver.html or

Have a 3.5 database handy with the exact same table definition (schema) as the 4.0 database. After generating the EDM based on the 3.5 database, tweak the model to work with a 4.0 database. Let’s see how this is done:

Start by creating an empty solution, named Chinook, to hold our projects:

image

Then add a C# Class library project to the solution, and name it Chinook.Data:

image

Remove Class1.cs, then go to file system, and copy the Chinook.sdf (the 3.5 version) to the solution folder (C:\projects\chinook).

image

(I have also copied the 4.0 version, so I can easily find it later). Now we can add the EDM based on the 3.5 database file:

image

Name the model ChinookModel.edmx and click Add. In the next step, select Generate From Database, and click Next.

On the “Choose you data connection” step, create a new connection to the Chinook.sdf 3.5 version database, using the SQL Server Compact 3.5 provider, and click Next:

image

Then you get this question:

image

(I suggest you reply No)

image

Select all tables, select pluralize and click Finish. Now we have our Model:

image

Now, let’s make the Model work with our SQL Server Compact 4.0 file. First, modify the connection string in app.config as follows:

image

Then modify the emdx file (right click the file, and select Open With.. XML Editor).

image

Verify your changes by opening the edmx file in the visual designer.

Creating the POCO Entities for use with Repository

Now let us create a separate class library for our POCO entities based on the model, This will allow us to reference this Model project from both the data access layer and the UI layer. In order to do this, follow the steps in this EF Team blog post, and you will end up with a solution structure like this:

image

Notice the following change,that was made to ChinookModel.tt in order to have the entities in a separate project:

string inputFile = @"..\Chinook.Data\ChinookModel.edmx";

Also, a reference to the Chinook.Model project was added to Chinook.Data:

image

And the Custom Tool Namespace for the Chinook.Model.Context.tt file was set to Chinook.Model:

image

Creating the Repository

Now, add a Chinook.Repository project to the solution, to create the data access methods used by the UI. This project should reference the Chinook.Model and the Chinook.Data projects. As always, remove the Class1.cs file. Now, we want to create a method that allows the UI to get a page of Track data for display in a GridView. This is an initial implementation (notice the parameters, which are passed by default when using and ObjectDataSource from the UI):

public List<Track> GetAll(string sortBy, int maximumRows, int startRowIndex)
{
using (var context = new ChinookEntities())
{
return context.Tracks.Include("Albums").Include("Artists").Include("Genres").ToList();

}
}


There are some things missing/not ideal with this:

- The Include statements are based on text strings.


- Sorting and paging is not implemented



In order to make the Include statement strongly typed, I have located an extension method from http://blogs.microsoft.co.il/blogs/shimmy/archive/2010/08/06/say-goodbye-to-the-hard-coded-objectquery-t-include-calls.aspx (Implemented in the ObjectQueryExtensions.cs file in the Chinook.Data project)



public List<Track> GetAll(string sortBy, int maximumRows, int startRowIndex)
{
using (var context = new ChinookEntities())
{
return context.Tracks.Include(t => t.Album.Artist).Include(t => t.Genre).ToList();
}
}


That’s much better, and avoids nasty runtime errors.



The sortBy parameter from the ObjectDataSource is just a text string (field name), optionally followed by “DESC”. So to do sorting, we could write a long case statement to set the sortexpression, or use another extension method, which accepts a sort expression. I found one here: http://landman-code.blogspot.com/2008/11/linq-to-entities-string-based-dynamic.html




public List<Track> GetAll(string sortBy, int maximumRows, int startRowIndex)
{
using (var context = new ChinookEntities())
{
if (string.IsNullOrWhiteSpace(sortBy))
{
sortBy = "Name";
}
return context.Tracks.Include(t => t.Album.Artist).Include(t => t.Genre).OrderUsingSortExpression(sortBy).Skip(startRowIndex).Take(maximumRows).ToList();
}
}


In addition to the GetAll method, in order to support paging, the repository must also implement a GetCount method: 



public int GetCount()
{
using (var context = new ChinookEntities())
{
return context.Tracks.Count();
}
}


Creating the UI Layer



Now, add a ASP.NET Web Application project. Call it Chinook.UI. Add a “WebForm using Master”, and call it TrackList.aspx. Add references to Chinook.Model and Chinook.Repository.



Add this content to TrackList.aspx:



image



Notice the highlighted sections in the markup.



Add the connection string entry from the Data project to web.config.



Set the UI project as Start Project and the TrackList.aspx as start page.



Press F5 and see it run!



To display the Album title rather than the albumId, add a AlbumName readonly property to a new Track.Custom.cs POCO partial class, and display the Album.Name when requested:



//For display only
public string AlbumName
{
get
{
if (this.Album != null)
{
return this.Album.Title;
}
else
{
return string.Empty;
}
}
}


Then change the asp:boundfield from AlbumId to AlbumName in the TrackList.aspx file (you can do the same for other foreign key fields)



<asp:BoundField DataField="AlbumName" HeaderText="Album" 
SortExpression="" />


Download the project so far from here.



In part 2, we will continue with the following additional features:



- Adding a Dynamic Data Project to quickly manage the lookup tables



- Creating an OData layer, allowing access from PowerPivot in Excel



- Deployment advice



(Thanks to Morten Hedekær Olsson for technical review)

Sunday, November 7, 2010

Meet me at TechEd Europe in Berlin next week

I am attending TechEd from 8-12 November, and looking forward to learning new things about SQL Server Compact, Sync Framework, Entity Framework, OData, WebMatrix, Visual Studio SP1 etc. (all technologies that are related to SQL Server Compact).

I will be present in the Technical Learning Centre, SQL Server Data Technologies & Developer Tools booth during the event, so feel free to come and have a chat about some of the technologies and tools, that the SQL Server Data Programmability team provides.

See you in Berlin!

Friday, November 5, 2010

SQL Server Compact 4.0 CTP2 released

Together with WebMatrix beta 2, SQL Server Compact CTP2 has been released. The build number for this release is: 4.0.8435.1.

(CTP1 build number was: 4.0.8402.1)

Direct download link is:

x64 runtime: http://go.microsoft.com/fwlink/?LinkID=202429 (includes x86 runtime for x64 systems)

x86 runtime: http://go.microsoft.com/fwlink/?LinkID=202428

There is no updated documentation with this release, but a couple of bugs reported by me have been fixed:

Inability to upgrade in-place - http://social.msdn.microsoft.com/Forums/en-IE/sqlce/thread/872e49da-1d3e-457a-9288-c1e1f0c90dc8

Get SqlCeReplication.SubscriberConnectionsString returns an invalid connections string - http://connect.microsoft.com/SQLServer/feedback/details/589806/sql-server-compact-4-replication-library-bug-w-subscriberconenctionstring

Update: CTP2 causes some issues with EF Feature CTP4 (and MVC 3 beta), if you are using |DataDirectory| macro in your connection string:
http://social.msdn.microsoft.com/Forums/en/adonetefx/thread/808df2cc-1177-47d9-b333-304701520cc8

Saturday, October 30, 2010

New release of ExportSqlCe SSMS add-in and command line utilities–Script to SQL Azure

The new release (version 3.5) of my scripting utilities for SQL Server Compact includes a couple of bug fixes and a single new feature: Script Schema and Data for SQL Azure.

image

This new feature allows you to migrate your SQL Server Compact solution to a SQL Azure database. The SQL Azure compatible script includes the following changes compared to the standard, SQL Server Compact and SQL Server compatible script:

- Primary keys are scripted before any INSERTs, as a clustered index is required on all SQL Azure tables.

- INSERTs are batched (1000 per GO), to improve performance when running the script against a SQL Azure database.

- ROWGUIDCOL property is not supported, so it is not included.

The SQL Azure script is also SQL Server Compatible, but not SQL Server Compact compatible.The matrix below illustrates your options:

Script option SQL Server and SQL Server Compact SQL Azure and SQL Server
Schema and Data

X

N/A

Schema and Data for SQL Azure

N/A

X

Schema and Data with BLOB files

X (Compact)
(requires SqlCeCmd)

N/A

Schema only

X

(X)

Happy scripting!

Wednesday, October 27, 2010

SQL Compact 4.0 now available as a .zip file

With the recent release of NuPack beta, SQL Server Compact 4.0 CTP is now available as a .zip file. a feature requested by many users: since SQL Server Compact is just a collection of DLL files, why do you need to install an MSI to get hold of these DLL files.

NuPack has the purpose of simplifying the process of incorporating third party libraries into a .NET application during development. An introduction to NuPack is available here.

You can download the NuPack add-in here.

Once you have downloaded the add-in, you can add the SQLCE package from the References context menu in Solution Explorer, using the new “Add Package Reference” dialog:

 image

image

Let’s have a closer look at the contents of the SQLCE NuPackage and the changes it makes to your project.

As you can see with “Show All Files” enabled, the installation has added a packages folder to the file system:

image

Let’s Open Folder in Windows Explorer and have a closer look:

image

The packages\SQLCE.4.0.8402.1 folder contains the .zip file that constitutes the SQLCE package, and folders with content from this .zip file:

Content contains a file with changes to your projects web.config or app.config file, which enables the SQLCE 4.0 DbProviderFactory, the lib folder contains the .NET DLL to be installed and referenced, the NativeBinaries folder contains two folders for the platform specific parts of the SQL Server Compact 4.0 engine. and finally, the tools folder contains scripts to add a post build job to your project, that copies the files in the NativeBinaries folder to the bin folder.

(This custom PowerShell solution for copying the native DLL files may be changed to a more general solution in the future: http://nupack.codeplex.com/workitem/109 )

So in summary, NuPack helps with many of the manual process involved in privately deploying SQL Server Compact 4.0.

Sunday, October 17, 2010

HOW TO: Detect SQL Server Compact version information

There are various aspects to getting the version information for SQL Server Compact. This blog post is an attempt to cover them all, let me know if something is missing.

Assembly Version

The System.Data.SqlServerCe.dll ADO.NET Provider version. (AssemblyVersion)

This is not part of the file information, but .NET specific, part of the assembly’s identity.

var version = typeof(System.Data.SqlServerCe.SqlCeConnection).Assembly.GetName().Version;

This will give you the assembly version of the System.Data.SqlServerCe.dll, not the build number.

For 3.1, this is 9.0.242.0 for the desktop and 3.0.3600.0 for devices (not exactly what you would expect)

For 3.5 RTM, this is 3.5.0.0

For 3.5 SP1 and SP2 this is 3.5.1.0

“ServerVersion”

The ServerVersion property of the SqlCeConnection object.

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

For 3.5/4.0 this will give you the build version of the database engine, including any patches. For 3.5 SP2 this is "3.5.8080.0"

(Sadly for 3.1, this returns “9.0.242.0”)

File version of the managed DLLs

Getting the file system version of the managed ADO.NET provider will give you more precise version information (in particular for 3.0/3.1), and will not require guessing the location of any DLL files (as below for getting the version for unmanaged files).

string fullPath = System.Reflection.Assembly.GetAssembly(typeof(System.Data.SqlServerCe.SqlCeConnection)).Location;
var asmInfo = System.Diagnostics.FileVersionInfo.GetVersionInfo(fullPath);
string asmFileVersion = asmInfo.FileVersion;

For 3.1, this returns 3.0.5300.0 (for patches the final zero will be from 1 to 14)

File version of the unmanaged DLLs

As you may know, the SQL Server Compact engine consists both of managed DLL files (the System.Data.SqlServerCe.dll that has been referenced in all code above), and a number of C++ unmanaged DLL files.

Getting information about these files is more challenging, as we need to locate the unmanaged DLL files. This can be done like so for 3.5 and 4.0 (if installed by an administrator on the machine):

Microsoft.Win32.RegistryKey registryKey = Microsoft.Win32.Registry.LocalMachine.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server Compact Edition\v3.5", false);
string pathName = (string)registryKey.GetValue("InstallDir");
var info = System.Diagnostics.FileVersionInfo.GetVersionInfo(System.IO.Path.Combine(pathName, "sqlceme35.dll"));
string fileVersion = info.FileVersion;

File format/version of database file

The sample in this blog entry gives you the file version, there are only difference in this per major version (2.0, 3.1/3.0, 3.5 and 4.0)

http://erikej.blogspot.com/2010/08/how-to-upgrade-version-3x-database-file.html

Product version information

Information from the product team about product release versions is available here: SQL Server Compact Release Versions and here: Description of the various build versions of SQL Server Compact Edition

In addition, I have lists of available hotfixes on this blog for version 3.5 SP1 and 3.5 SP2

Wednesday, September 29, 2010

Sync Framework news roundup

Sync Framework 2.1 is now available.

Notable features include: SQL Azure Synchronization, and support for SQL Server Compact 3.5 SP2 (which includes new APIs for managing change tracking in code).

The Sync Framework Team has also published a code sample, which demonstrates a Sync from SQL Azure to SQL Server Compact.

If you are using Sync Framework, make sure to follow JuneT’s blog. The latest entry concerns Sync Framework Provisioning (creation of metadata to support Sync Framework) and the new Deprovisioning API in Sync Framework 2.1.

To help you prepare a database for Sync, a tool is now available on CodePlex: SyncPrep.

Monday, September 20, 2010

SQL Server Compact 3.5 SP2 downloadable update list

The updates for SQL Server Compact 3.5 SP2 are now referred to a CU (Cumulative Update), which is what the hotfixes also were previously. The difference now is that the CU installation overwrites the existing installation, and a remove of the RTM bits is no longer required before installing the CU.

The table below gives an overview of the current CUs. All CUs apply to SQL Compact 3.5 SP2, version 3.5.8080.0.

Build KB Title Platforms
8081 982565 FIX: "An SqlCeParameter with ParameterName '<Parameter Name>' is not contained by this SqlCeParameterCollection" error message when you use the .NET Compact Framework Data Provider for SQL Server Compact to access SQL Server Compact 3.5 x86, x64
8082 983516

FIX: A Deadlock occurs when two transactions try to perform a DML operation on a table that contains a TABLOCK locking hint or an XLOCK locking hint

x86, x64
8082 2300599 FIX: Heap corruption occurs when you use SQL Server Compact 3.5 Service Pack 2 x86, x64
8083 2361070

FIX: Some "Image" column content does not open in SQL Server Compact 3.5 SP2 after the "SqlCeEngine.Compact" or "SqlCeEngine.Repair" method is called

x86, x64
8084 2479589

FIX:"A call to SQL Server Reconciler failed" error message occurs when you try to add or reinitialize a subscriber of SQL Server Compact 3.5 for a merge publication

Server Tools only,
x86, x64
8085 2494715

FIX: Incorrect result when you run a query that contains a conjunction and a disjunction in SQL Server Compact 3.5

x86, x64
8087 2585082 FIX: "A specified value violated the integrity constraints for a column or table" error if the merge publication contains many articles and constraints in SQL Server Compact 3.5 x86, x64
8088 2527494 A hotfix is available for SQL Server Compact 3.5 SP2 that adds support for replication with SQL Server "Denali" x86, x64, Server Tools, WinCE 5,6,7 (?)

8089

 

2665342

Incorrect sort order for a subscriber in SQL Server Compact 3.5 SP2 which synchronizes with a publisher in SQL Server

Server Tools only,
x86, x64

8092 2718718 FIX: "Cannot open the shared memory" error message when you try to create or open a SQL Server Compact 3.5 SP2 database file on a shared network folder x86, x64
8109 2860310 FIX: Incorrect results when you run a query that uses a LIKE operator in a WHERE clause in SQL Server Compact 3.5 x86, x64, Server Tools, WinCE 6,7

Please let me know if there are errors or omissions.

Monday, September 6, 2010

New book: .NET Compact Framework 3.5 Data Driven Applications

A new book has been published this spring, which supplements the other books available on Windows Mobile development with SQL Server Compact. It covers from a very practical and hands-on perspective many facets of Windows Mobile development (now a legacy platform). It attempts to cover use cases for even the more obscure Windows Mobile technologies, like MSMQ, Infrared and Bluetooth, and Pocket Outlook, so it cover a lot of ground.

In terms of data access, there are three chapters devoted to various aspects of this domain:

Building the Data Tier: Describes how to build a data provider independent data layer, and implement this with both SQL Server Compact and Oracle Lite.

Building Search Functionality: Describes how to implement a standard parameterized search function, and also how to implement full text search for SQL Server Compact (a question often asked in the MSDN forums).

Data Synchronization: Describes how to implement Microsoft Sync Services on Windows Mobile, including adding filters and conflict resolution.

Optimizing for Performance: Describes how to optimize for performance using caching and indexes. As the data access layer is based on DataSet, there is no mention of using SqlCeResultSet, Seek and similar APIs for SQL Server Compact.

You can read more about the book here.

Thursday, September 2, 2010

SQL Server Compact Toolbox add-in now supports version 4.0 databases

The latest release of the SQL Server Compact Toolbox, an add-in for Visual Studio 2010, includes support for version 4.0 (CTP1) SQL Server Compact database files in addition to version 3.5 files.

To start using the add-in with version 4 files, download and install the latest release (1.4 at the time of writing this).

Using 4.0 files with the add-in

Create the file, using either SqlCeCmd40, WebMatrix or another tool, that supports 4.0.

Create a permanent connection, by selecting “Add SQL Server Compact 4.0 Connection”:

image

The simple dialog allows you to build the connection string and test it.

image

When you select “Save”, the connection information is saved in a version 3.5 database file here:

C:\Users\<Username>\AppData\Local\SqlCe35AddinStore.sdf

(An empty database is included as an Embedded Resource with the add-in, to see how to extract this to a sdf file using C#, see the DataConnectionHelper.cs in the source).

To remove the connection, select a version 4 connection in the tree, and select “Remove Connection”.

image

When the Visual Studio Server Explorer support for version 4 arrives, the add-in will be updated to enumerate your version 4 connections from Server Explorer rather than having a separate store for these.

Friday, August 27, 2010

SQL Server Compact 4.0 ASP.NET Membership provider

According to ScottGu, an ASP.NET membership provider that works with SQL Server Compact 4 is not coming in the near future. This poses a problem, if you would like to use Forms authentication with a website using only SQL Server Compact 4.

To remedy this, I have implemented an ASP.NET Membership provider for SQL Server Compact 4.0 (CTP1), for use with Forms Authentication for small web sites using only a single SQL Server Compact 4.0 database. The project provides files that contain a Membership provider and Role provider for ASP.NET. (Note that SQL Server Compact 4 is in beta, and is not supported for production).

How to use the provided files (3 simple steps)

1. Change SqlCeMembershipProvider::encryptionKey to a random hexadecimal value of your choice.

2. Copy the three files in the /App_Code folder to your web sites' ~/App_Code folder.

3. Modify your web.config using the template on the CodePlex page 
(if you are on a shared hosting server, you will have to set writeExceptionsToEventLog to false).

That's it - you can now create users, roles and use the ASP.NET login controls.
Site file layout (to verify that the provider is working):

sqlceprov.png

If you encounter any bugs, have suggestions or any other issues, please provide feedback here

Wednesday, August 25, 2010

SQL Server Compact version detector

With three current SQL Server Compact versions (file formats) available 
3.0/3.1 on Windows Mobile 6.x devices,
3.5 with Visual Studio 2008 and 2010,
and 4.0 currently available as a CTP (Community Technical Preview),
it can sometimes be hard to tell what the file version of a SQL Compact file in your possession is.

To help you, I have created a small utility that you can place on your desktop and drop any SQL Server Compact file on.

You can download a .zip file with the utility here. And the source code for the utility is available here.

Place the SQL Compact Detector.exe on your desktop, and drop a SQL Server Compact file on it!

image

image

Saturday, August 21, 2010

How to Migrate/Downsize a SQL Server database to SQL Server Compact 4.0 (and 3.5)

Release 3.0.0.6 of my SQL Compact schema and data script utility on Codeplex contains a command line utility, named Export2SqlCe, that allows you to export schema and data from a SQL Server 2005/2008 database in a SQL Compact compatible SQL format.

The Export2SqlCe tool does not create a SQL Compact sdf database file, but just creates a T-SQL script , that you can run with a tool like my SqlCeCmd Codeplex utility or SQL Server Management Studio 2008. This approach gives you the flexibility to modify the script in a text editor before creating the sdf file – but requires an extra step.

Below are the steps required to migrate a SQL Server database (tables and table indexes/constraints only) to a SQL Server Compact database.

First, run Export2SqlCe against your SQL Server database (2005 and 2008 are supported), using a command line similar to:

Export2sqlce "Data Source=(local);Initial Catalog=AdventureworksLT;Integrated Security=True" AW.sqlce

image

This will create a file named C:\aw.sqlce – let’s have a look:

image

This file contains a script to create tables, data and constraints (indexes and foreign keys), all in SQL Server Compact 3.5/4.0 compatible T-SQL script.

Then you can either open the aw.sqlce script in SQL Server Management Studio 2008 or use sqlcecmd to create the sdf file and populate the file based on the script – meaning the whole process can be run from a batch file and completely automated!

(To manage SQL Server 4.0 databases, use SqlCeCmd40.exe, for 3.5 use SqlCeCmd.exe)

First create the database:

sqlcecmd40 -d "Data Source=C:\aw.sdf" -e create

Then run the generated script against the database:

sqlcecmd40 -d "Data Source=C:\aw.sdf" -i aw.sqlce > log.txt

Examine the log.txt to ensure no errors occurred - search for “error code” – please let me know if you encounter any errors, so they can possibly be fixed in an update to the utility.

Entire batch file:

Export2sqlce "Data Source=(local);Initial Catalog=AdventureworksLT;Integrated Security=True" AW.sqlce
sqlcecmd40 -d "Data Source=C:\aw.sdf" -e create







sqlcecmd40 -d "Data Source=C:\aw.sdf" -i aw.sqlce > log.txt



del log.txt




for %%f in (aw*.sqlce) do sqlcecmd -d "Data Source= C:\aw.sdf " -i %%f >> log.txtt

Monday, August 16, 2010

SQL Server Compact 4.0 news roundup

Since ScottGu’s recent announcement about SQL Compact support for ASP.NET, there have been additional releated postings on the web. This post is an attempt to give an overview of these.

At the recent MVC Virtual Conference, Ambrish Mishra, Senior Program Manager in the SQL Server Compact product development team, gave a presentation on SQL Compact 4.0. The slides and video from this presentation are now available.

On Channel 9, Scott Hanselman has posted a video where he chats with developer Damian Edwards about "Razor," IIS Express, SQL 4 Compact Edition and VS2010 Tooling.

Finally, Chris Auld demonstrates how to use Windows Azure Drives and SQL Server Compact 4.0

Thursday, August 12, 2010

SQL Server Compact 3.5 Toolbox updated

My Visual Studio 2010 add-in for SQL Server Compact 3.5 has just been updated. The new version 1.2 contains the following new features and fixes a bug:

- Parse and Show Estimated Plan features to editor
- improved DGML diagram (table fields with various info added)
- check for newer version on load
- BUG: Identity columns were ignored in scripts

Download the latest version from here: http://visualstudiogallery.msdn.microsoft.com/en-us/0e313dfd-be80-4afb-b5e9-6e74d369f7a1

SQL editor improvements

image

To show the Estimated Execution Plan, you must either have Visual Studio 2010 Premium or Ultimate installed. If you are using Visual Studio 2010 Professional, you must have SQL Server Management Studio installed to see the graphical execution plan.

Improved DGML diagram

In addition to showing table relationships, the generated DGML diagram (which you can view in Visual Studio 2010 Premium or higher) now contains all table fields and a visual indication if a field is either a primary key, a foreign key, optional (NULLable).

image

Update notification

image 

If the add-in has been updated, you will get a visual indication, and a link to the CodePlex site.

Saturday, August 7, 2010

HOW TO: Upgrade a version 3.x database file to SQL Server Compact 4.0

See this excellent sample: http://blogs.msdn.com/b/jimmytr/archive/2010/04/26/upgrade-sql-ce-database-from-3-1-to-3-5.aspx

I have updated the sample extension method to work with 4.0. Notice that the Upgrade method allows you to upgrade both from 3.1 and 3.5 file formats.

Due to a bug in the 4.0 CTP1, it is currently not possible to do in-place database upgrades (as the code below does), see this thread: http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/872e49da-1d3e-457a-9288-c1e1f0c90dc8

public static class SqlCeUpgrade
{
public static void EnsureVersion40(this System.Data.SqlServerCe.SqlCeEngine engine, string filename)
{
SQLCEVersion fileversion = DetermineVersion(filename);
if (fileversion == SQLCEVersion.SQLCE20)
throw new ApplicationException("Unable to upgrade from 2.0 to 4.0");

if (SQLCEVersion.SQLCE40 > fileversion)
{
engine.Upgrade();
}
}
private enum SQLCEVersion
{
SQLCE20 = 0,
SQLCE30 = 1,
SQLCE35 = 2,
SQLCE40 = 3
}
private static SQLCEVersion DetermineVersion(string filename)
{
var versionDictionary = new Dictionary<int, SQLCEVersion>
{
{ 0x73616261, SQLCEVersion.SQLCE20 },
{ 0x002dd714, SQLCEVersion.SQLCE30},
{ 0x00357b9d, SQLCEVersion.SQLCE35},
{ 0x003d0900, SQLCEVersion.SQLCE40}
};
int versionLONGWORD = 0;
try
{
using (var fs = new FileStream(filename, FileMode.Open))
{
fs.Seek(16, SeekOrigin.Begin);
using (BinaryReader reader = new BinaryReader(fs))
{
versionLONGWORD = reader.ReadInt32();
}
}
}
catch
{
throw;
}
if (versionDictionary.ContainsKey(versionLONGWORD))
{
return versionDictionary[versionLONGWORD];
}
else
{
throw new ApplicationException("Unable to determine database file version");
}
}


}


Sample usage:



string filename = @"C:\Data\SQLCE\Northwind31.sdf";
var engine = new System.Data.SqlServerCe.SqlCeEngine("Data Source=" + filename);
engine.EnsureVersion40(filename);

Thursday, July 15, 2010

Getting started with SQL Server Compact 4.0 and ASP.NET 4.0 (no WebMatrix)

While professional developers are waiting for the Visual Studio Tools and Designers for SQL Server Compact 4.0, I will show how impatient develoers can include SQL Server Compact with ASP.NET applications, and use it from ASP.NET pages.

Previously, you had to circumvent the SQL Compact ASP.NET blocker by adding a line of code to global.asax, as I describe here. This is no longer required, and SQL Compact 4.0 can now reliably handle web load.

Including SQL Server Compact 4.0 with your ASP.NET 4.0 app

1: Download http://tiny.cc/cfjia and install the 4.0 CTP runtime.

2: Copy the contents of the folder C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\Private (show below) to the bin folder in your web app (you may have to use Show All Files in VS to see this folder).

image

3: Place your SQL Compact sdf file in your App_Data folder, so your solution looks like this (with Show All Files on):

image

You can include the database file and the SQL Compact as content (Do not copy), if desired (so they become part of your project file).

image

4: Add a connection string to web.config, notice the |DataDirectory| macro, which will expand to the App_Data folder.

  <connectionStrings>
<
add name ="NorthWind"
connectionString="data source=|DataDirectory|\Nw40.sdf" />
</
connectionStrings>


5: Write code to connect. For now, you must use vanilla ADO.NET.Later you will be able to use Entity Framework and other OR/Ms to provide and model of your database. (Not LINQ to SQL, however). – UPDATE: EF CTP with SQL Compact support just released: Microsoft announced a new Entity Framework CTP today.



using System;
using System.Configuration;
using System.Data.SqlServerCe;

namespace Ce40ASPNET
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
using (SqlCeConnection conn = new SqlCeConnection())
{
conn.ConnectionString = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT TOP (1) [Category Name] FROM Categories", conn))
{
string valueFromDb = (string)cmd.ExecuteScalar();
Response.Write(string.Format("{0} Time {1}", valueFromDb, DateTime.Now.ToLongTimeString()));
}
}
}
}
}



You can now upload you project files to any web hosting site running ASP.NET 4.0, and your database runtime will be included in the upload. No SQL Server subscription will be required.



Hope this will get you started with SQL Compact 4.0  and ASP.NET.



Tuesday, July 13, 2010

How to check the Visual Studio 2010 edition from code

In my SqlCeToolBox add-in, I only expose some features if the user has Visual Studio Premium or higher, as I depend on some of the Data Dude tools.

The code to do this looks like this, notice the RegistryView option on the OpenBaseKey method. This ensures that this code works on both x64 and x86 based registries.

public static bool IsPremiumOrUltimate()
{
// From http://blogs.msdn.com/b/heaths/archive/2010/05/04/detection-keys-for-net-framework-4-0-and-visual-studio-2010.aspx
//Key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\DevDiv\VS\Servicing\10.0\$(var.ProductEdition)\$(var.LCID)
//The values for $(var.ProductEdition) include the following table.
//Visual Studio 2010 Ultimate VSTSCore
//Visual Studio 2010 Premium VSTDCore
//Visual Studio 2010 Professional PROCore
//Visual Studio 2010 Shell (Integrated) IntShell
using (var key = RegistryKey.OpenBaseKey(RegistryHive.LocalMachine, RegistryView.Registry32))
{

var ultimateKey = key.OpenSubKey(@"SOFTWARE\Microsoft\DevDiv\VS\Servicing\10.0\VSTSCore");
if (ultimateKey != null)
return true;

var premiumKey = key.OpenSubKey(@"SOFTWARE\Microsoft\DevDiv\VS\Servicing\10.0\VSTDCore");
if (premiumKey != null)
return true;
}
return false;


}



Wednesday, July 7, 2010

SQL Server Compact 4.0 CTP1 released

The SQL Server Compact team has just released of CTP of version 4.0 of SQL Server Compact Edition.

Ambrish Mishra has a detailed blog about the new features. Here is a short summary:

ASP.NET support

The main feature of version 4.0 is support for using SQL Compact as a file based database engine with ASP.NET websites.

The work done to enable ASP.NET includes:

Higher Reliability – can handle load of starter websites

Setup Enhancements – easier private deployment and a single x64 installer.

Support for ASP.NET – removal of the ASP.NET blocker

Virtual Memory Reduction – allows up to 256 concurrent connections (max) without virtual memory errors.

Medium Trust – enables web hosting with .NET 4.0

It is a part of the recently announced WebMatrix tool for starter websites as a starter database engine, which also includes a tool to design the database used on the website:

image

T-SQL changes

Support for paging queries:

SELECT * FROM Customers ORDER BY [Customer ID] OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

ADO.NET Provider enhancements

Seamless Integration with ADO.NET Entity Framework 4.0 (.NET FX 4) – server generated keys and code first support coming soon.

System.Data.SqlServerCe.SqlCeConnection.GetSchema() – allows you to get schema information for use in scripting tools etc.

System.Data.SqlServerCe.SqlCeConnectionStringBuilder() –allows you to build a strongly typed connection string.

Migration

The WebMatrix tools includes a tool to migrate a SQL Compact database to SQL Server 2006/2088/Azure. The tool does not generate a script, but simply creates a new database on the server selected.

image

For migration from SQL Server to SQL Compact, (and script based migration from SQL Compact to SQL Server), you can continue to use my ExportSqlCe tools. I have described the procedure to migrate from SQL Server to SQL Server Compact here. In addition, I have just released SQL Server Compact 4.0 versions of ExportSqlCe and SqlCeCmd.

Visual Studio

Later, a Visual Studio update will enable you to use SQL Server Compact from the Visual Studio Data/Server Explorer, including from Visual Web Developer Express. I assume a similar update for SQL Server Management Studio (SSMS) is not in the cards.