The day after Orcas Beta1 appeared for public download reports appeared on the ADO.NET Technology Preview forum that the EDM Wizard (that is, "Add New Item/ADO.NET Entity Data Model) wasn't working. And, sure enough, if you choose "Generate From Database" and carefully choose which tables to use, you get a model with no entities. If you choose an empty model, you get "Specified argument out of range of valid values", and get the three mapping files, but no language file. What to do?

One way around this is to use the files generated from the March CTP wizard. Because the XML schemas for the mapping files haven't changed since March CTP, these work fine. In fact, all of the EDM code that worked with March CTP continues to work unchanged. That's what I used in the SQLCE and EDM blog entry posting.

If you can't keep an old March CTP around just to generate EDM Data Models, you can use the EDMGen.exe command line utility in the C:\WINDOWS\Microsoft.NET\Framework\v3.5.20404 directory. This utility appears to work and generates useable code. It also generates a richer model than the wizard did (with AssociationSets and Associations) but it takes a little more work to put together a project. I'll post a really simple one if there's interest. And EDMGen doesn't generate the same output as the wizard used to (for example, columns of type NTEXT are ignored in the model) and uses different default naming conventions. So at least there are a few workarounds for now.

SQLCE 3.5 comes with an ADO.NET data provider that supports entities, by supporting the ADO.NET 3.5 entity data model (EDM). I noticed this in Orcas March CTP with the inclusion of a new DLL, System.Data.SqlCe.Entities. Although the DLL was there, there was no support in Visual Studio for any provider but SqlClient, and trying to do this manually failed (DbProviderFactory.GetService returned null, IIRC).

In Orcas B1, there still seems to be no support in the Visual Studio "Add ADO.NET Entity Data Model" dialog, but you can do this manually. To set things up, I used a SQLCE table that was roughly the same as the jobs table in the pubs database, used an EDM generated in Mar CTP by pointing at SQL Server's pubs database and tweaked the SSDL file a bit to be consistant with the SQLCE table. One thing I was surprised with was that SQLCE's EDM implementation didn't seem to mind SSDL's EntityContainer being named "dbo" and generated the correct query anyhow (in SQLCE "select * from dbo.jobs" fails, and I hoped this wouldn't be the query that was generated). It used the right query.

Here's a simple example of using EDM with SQLCE 3.5, database included. You need to put the pubs.sdf database in c:\temp or change the connection string in the app.config to make this work. The sample uses both EntityClient and Entity Services. It could just as easily use LINQ for Entities.

An interesting idea is that if the internals of Sync Services were tweaked sightly, they could use the EDM and EDM's generated SQL in addition to (or instead of) DataSets. Imagine replicating/synchronizing Employee entities with associated Job information instead of synchronizing Employee and Jobs tables. The logic could be hooked in from what AcceptChanges does.

One of the latest developments in the SQLCE space is the beta for ADO.NET Sync Services. Sync Services ships as a standalone download, runnable on Visual Studio 2005 and is also built-in to Visual Studio Orcas. Both versions require a new version of SQLCE, version 3.5. This runs side-by-side with SQLCE 3.1, although both version's DLLs have the same names they are installed in different directories. The Orcas B1 version has a newer version of the DLLs and install GUI support.

The Orcas GUI support consists of a new component "Local Database Cache" that can be added a various types of C# and VB.NET projects. This bring up a Configure Data Synchronization dialog. In this dialog, you can configure a Server connection and a Client (SQLCE) connection, select "Cached data objects" (tables to be replicated) and replication specifics. You can also configure the feature of Sync Services that allows you to make synchronization service-based, that is, use a middle-tier WCF, Web Service, or other middle-tier component for connect to the database and perform the synchronization.

Besides service-based synchronization, Sync Services allows you to sync to data sources other than SQL Server. You can sync to most any database that supports ADO.NET, for example, Oracle. It works by instanciating a DataSet (although I'd though it could/might use EDM in future, see next post) and performing synchronization through the DataSet. Sync's DbServerSyncProvider uses a SyncAdapter, making it even more ADO.NET-like. Although DbServerSyncProvider can use any compliant ADO.NET provider, SqlCeClientSyncProvider is SQLCE specific.

For some excellent examples of Sync Services in action, check out The Synchronizer's (aka Rafik Robeal) blog. I think Rafik is up to six samples now, one of which works for an Oracle backend database.

For some excellent videos showing Sync Services with Visual Studio Orcas, check out the links on Steve Lasker's blog. Although I haven't tried it out yet, I think his WCF service-based example is running from the Orcas Beta1 version of the designer.

Next: SQLCE and Entities

So now that I've found SQLCE and know which version I have, what can I do with it besides use the ADO.NET and OLE DB APIs? Well, turns out I need some auxiliary pieces. If you use 3.0, the pieces "come with", but when you upgrade to 3.1, you need to upgrade these.

I must admin I found this somewhat confusing, probably because I "started from scratch" and also wanted to use 3.1. I had to intall some pieces before it would "recognize" others. For example, unless you have the Windows Mobile dev tools installed, the 3.1 VS tools won't install. And after installing the Windows Mobile dev tools on VS SP1, I had to install VS SP1 again. And none of these installs made the "Configure Web Synchronization Wizard" app available until I installed the Server Tools.

This turns out to be more important than you might think. For example, the GUI DLLs are used to CREATE the database itself. I've not yet investigated if there is a flie format difference in different SQLCE versions, as there is with different SQL Server versions. Or how/when SQLCE database files with data are upgraded. And using the 3.1-specific features, that is the |Data Directory| connection string directive, and Click-Once deployment support require the updated version of the dev tools. When you install SQL Server 2005 SP2, your bits are updated, but these are a subset of the bits you use for development in VS.

Here's the list of additional bits (most are 3.1 updates) I came up with, along with some terse notes:

-- Server Tools (SQLCE30setupen - localized)
   Updates IIS to support connectivity solutions (Merge Repl)
   Special considerations for IIS 7.0 (IIS on Vista)
   No need if SQL Server and IIS on same machine
   These are not updated for SQLCE 3.1
   Includes Configure Web Synchronization Wizard app

-- Tools for VS2005 SP1 (SSCE31VSTools)
   Requires installating VS2005 SP1 first
   Adds Click Once Support for SQLCE 3.1
   Updates design-time UI to work with SQLCE 3.1
   Adds 3.1 device cab files
   Requires VS WindowsMobile dev tools install
   If VS not upgraded to SP1, uses SQLCE 3.0 design time UI

-- Developer SDK (SSCE31SDK)
   Cab files for devices
   MSI for desktop and tablet PC editions
   Header files
   MSI for Compact Edition help files
   Northwind sample app

-- Books Online (SSCE31BOL)

-- SQL Server 2005 Compact Edition Access Database Synchronizer
   Supports Access 2000 SP3, 2002 SP3, 2003 SP2, 2007
   Not sure if this supports 3.0, 3.1 or both
   Runs as a service, Uses RDA access and HTTP transport.
   Requires Active Sync 4.0 or later

-- SQL Server 2005 Sync Services CTP
   Installs 3.5 side-by-side, but no new device support yet
   Installs new ADO.NET-based sync
   Included with Orcas B1, works with VS2005 also
   There is a separate documentation and demos download

I'll only be using 3.1 and above because (blush) I don't have a compact device except for the emulator in Visual Studio. So I'm working desktop and emulator only for now.

Next: Newest stuff

As part of a project, I've had occasion to look at the SQL Server Compact Edition in earnest. Although I've taken a cursory glance before this, its my first attempt to put all the pieces into place. So I thought I'd write it down as I went along.

SQL Server Compact Edition's original name was SQL Server Mobile, but as of version 3.1 its now supported on a variety of mobile devices but its also supported on Tablet and Desktop PCs. For simplicity, I'll refer to all versions as SQLCE from now on.

SQLCE ships with SQL Server 2005 and will also ship with the next version of SQL Server (Katmai). I believe it installs if you install the SQL Server 2005 adminstrative tools, but doesn't show up in Add/Remove Programs.  It also ships with Visual Studio, if you install the Compact Framework dev tools. You can also download it stand alone.

It consists of 6 DLLs, no services, not even an exe. You can program it with the ADO.NET data provider or OLE DB provider. Each is included as a separate DLL. When installed with VS or standalone it lives in the directory C:\Program

Files\Microsoft SQL Server Compact [or Mobile] Edition\v3.x. There are subdirectories that contain the redistributables for various compact devices and versions of Windows CE. When installed with SQL Server it lives in the C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE directory.

The versions that come with various product versions are:

SQL Server 2005 RTM and SP1: Version 3.0, build 3.0.5207.0
SQL Server 2005 SP2: Version 3.1, build 3.0.5300.0
Visual Studio 2005: Version 3.0, build 3.0.5207.0
Standalone download of SQL Server Compact Edition 3.1: Version 3.1, build 3.0.5300.0
Mar CTP standalone and Mar CTP of VS Orcas: Version 3.5, build 3.5.5305.0
VS Orcas Beta 1: Version 3.5, build 3.5.5334.0

Interestingly, the DLLs always end with "30" in all versions. You know which version by looking at the file properties, not the DLL names. Version 3.5 will supposedly also ship with SQL Server Katmai, but this is not available yet.

The reason SQLCE ships with SQL Server and Visual Studio is that these are the development and admin environments for it. You don't just start up the .exe and work from the command line, you use either SQL Server Management Studio or Visual Studio's Server Explorer. Each of these tools come with dialog boxes that allow you to create a database and interact through the GUI (VS's Server Explorer/Data Connections or SSMS's Registered Servers/Object Explorer/Query Window). You can also create and manipulate a database through the programmatic APIs. You must have SSMS to make this work with SQL Server, there is no support for SQLCE in SQL Server Management Studio Express.

In addition to DDL and DML, both environments allow you to configure merge replication, as support for what's called "Occasionaly Connected Systems" is one of the main points of SQLCE. Besides SQL Server Merge Replication, you can interact with the "main server database" through RDA (remote data access) or the new ADO.NET Sync Services, currently in beta.

Next: Associated pieces

I always look through the SQL Server 2005 samples with each new incantation, and always seem to find things in there that are new and interesting. In the SP2 version, the samples included a new sample database, "AdventureWorks Light". Also known as AdventureWorksLT.

Because folks that teach SQL Server do have a bunch of canned queries for exposition and know exactly how those queries are supposed to behave, sample databases really never disappear. Pubs was the sample database inherited from Sybase, although Sybase itself moved on to "Pubs2" last time I looked. The problem with Pubs was that it really didn't have much data at all. I always point out that, of the 8 publishers in Pubs, only 3 have actually published books. Northwind was inherited from Microsoft Access, being the original Access sample database. It had more data, but not a lot.

In SQL Server 2005, no sample databases are installed with the product by default, which is a good thing. But the "AdventureWorks series" is the new sample database, including an OLTP, data warehouse, and analysis databases, as well as case-senstive and case-insensitive collation versions. It has a reasonable amount of data and its design is said to reflect current thoughts on best practices. It is built around multiple departments in a putative organization, including Sales, HR, and Manufacturing. This allows it to include a employee/manager hierarchy and a bill-of-materials table.

But the Adventure Works database is a nice 3NF database, with lots of tables (~70) and it sometimes difficult to use for exposition, because of the long multipart object names and lack of familiarity. AdventureWorks Light is a bit more approachable, weighing in at about 3MB, but still over available as an MSI (no simple create script). It contains:

10 tables and 3 views
500 or so rows in each table
All in single object schema SalesLT
No stored procs, but 1 scalar and 2 table-valued functions
A single XML schema collection

It even includes a version of Sara Tahir's uspPrinterror and uspLogerror procedures for error handling, but interestingly uspRethrowError is left out.

We'll see if this new sample database is simple enough for widespread pedagogical use. At least it may get folks more use to using (memorizing) the "AdventureWorks family" table and column names.

PS: The SQL Server Samples now have their own Codeplex project at http://codeplex.com/SQLServerSamples

I just returned from Microsoft Developer and ITPro days in Ghent, Belgium. The hospitality was great, and the event itself drew some big crowds of top notch developers and IT pros. Ghent was an amazing city, with medieval architecture sharing the city with modern buildings. Besides the four talks on SQL Server 2005 and ADO.NET that I did for the main conference, Wednesday, I gave a special talk on Event Notifications for the Belgian SQL Server user group. On the previous, the user group and I went out for ribs and beer. I sampled quite a few different excellent Belgian beers, there is quite a variety!

Thanks to all for a really good time. The conference demo scripts are posted up on the SQLskills website.