LINQ and Entity Framework Posts for 5/16/2008+

Note: This post is updated daily or more frequently, depending on the availability of new articles.

Danny Simmons on Why use the Entity Framework?

My Danny Simmons Answers the Frequently Asked Question: Why Use the Entity Framework? of May 18, 2008 analyzes Danny's answer and agrees with his conclusions, as do Jon Papa, Julie Lerman, and Jonathan Carter (so far).

Added: 5/18/2008

Location of Missing ADO.NET Data Services AJAX Client Library

My "Manipulate Data in the Cloud with ADO.NET [Data Services]" story for Visual Studio Magazine's May 2008 issue mentions the ADO.NET Data Services AJAX Client Library but doesn't give its location, which is ADO Data Service AJAX Client Library on CodePlex.

For a guided tour of using the AJAX Client library, try Jonathan Carter's ADO.NET Data Services Part 6: AJAX post.

Added: 5/18/2008

Mike Taulty Duplicates My Problems with VS 2008 SP1 Beta Installation

Mike Taulty had the same problem I encountered with my first installation of the VS 2008 SP1 Beta bits: Indication of a successful install followed by no templates for ADO.NET Entity Data Model or ADO.NET Data Services. (See the "VS 2008 SP1 Beta Didn't Install on My Primary Development Machine (but Said It Did)" topic below.)

Mike's VS 2008 Service Pack 1 - Docs and Installation post of May 18, 2008 tells the gory tale.

ADO.NET Program Manager Sanjay Nagamangalam has some suggestions for three problem scenarios in this VS2008 SP1 Beta fails to add "new item - ADO.NET Entity Data Model" Item Template thread in the Visual Studio 2008 SP1 (Beta) forum.

I decided, like Mike, to start over from scratch by removing my VS 2008 RTM installation and the entire Microsoft Visual Studio 9.0 folder. I also removed the previously unremovable KB949325 and KB945855 HotFixes manually by deleting all their Registry references. Reinstalling VS 2008 RTM and SP1 Beta proceeded without a hitch and ADO.NET Entity Data Model and ADO.NET Data Services templates and libraries appeared and worked as expected.

Added: 5/18/2008

Aghy Continues Her LINQ for SharePoint (LINQ4SP) Series

The LINQ4SP - Create new list item post of May 17, 2008 describes how to create a new SharePoint list item for an AdventureWorks Products list.

Quintin Clark and Jon Udell: WinFS Contributions to Entity Framework and SQL Server 2008

My Jon Udell Interviews Quintin Clark on WinFS's Downstream Effects: Entity Framework and SQL Server of May 16, 2008 contends that Entity Framework and LINQ to SQL descend from Object Spaces, not WinFS.

There's no question, however, the WinFS was the driving force behind SQL Server 2008's filestream and HierarchicalID datatypes.

Bill McCarthy Finally Gets VS 2008 SP-1 Beta to Install

Bill describes the agonizing process he used to get VS 2001 SP-1 Beta to install in his The trials and tribulations of installing VS 2008 SP1 post of May 16, 2008. I wasn't so lucky as the following post reports.

VS 2008 SP1 Beta Didn't Install on My Primary Development Machine (but Said It Did)

My Failed VS 2008 SP1 Beta Installation Indicates Success post of May 16, 2008 describes the trials and tribulations with an install that reported success but didn't install the DDLs and templates for ADO.NET Entity Framework or ADO.NET Data Services.

I filed a Connect bug report on May 14, 2008 and enclosed a rollup of all logs. As of May 17, 2008, all I've received is the standard "Dear John" letter:

Thanks for your feedback. We are escalating this issue to the appropriate group within the Visual Studio Product Team for triage and resolution. These specialized experts will follow-up with your issue.

Thank you,
Visual Studio Product Team

Update: May 18, 2008

I fixed the problem by removing and reinstalling VS 2008 RTM and SP1 Beta bits. See the earlier "Mike Taulty Duplicates My Problems with VS 2008 SP1 Beta Installation" topic.

Jim Wooley Discovers an Anomaly in VB's Anonymous Type Property Ordering

His Anonymous Type property ordering in VB post of May 16, 2008 notes that VB orders anonymous types alphabetically, while C# orders them in the sequence of declaration.

Hadi Eskandari Uses LINQ to Enhance WPF INotifyPropertyChanged Implementations

Iranian developer Hadi Eskandari describes in his Enhanced INotifyPropertyChanged post of May 16, 2008 the use of the GetPropertySymbol extension method to extract member names from a lambda expression in a PropertyChangedEventHandler that implements INotifyPropertyChanged.

Hadi's approach is based on Jafar Husain's early Symbols in C# 3.0 post of March 2, 2007.

John Papa Posts a Teaser for His Forthcoming Data Access with Silverlight 2 Book

Silverlight Consuming REST Services of May 16, 2008 explains combining REST APIs with LINQ to XML:

So raw XML comes barreling into your Silverlight application asynchronously, LINQ to XML makes it fall in line, and its bound to where it needs to go via XAML.

Sending data back via REST is also very cool. I've got that working now too. ... Interacting with REST from Silverlight applications is just one piece of the data access puzzle, but its pretty cool.

How about a source code preview, John?

Bill Wagner Has a New C# Book in the Works

His Rough Cuts available for "More Effective C#" post of May 16, 2008 says:

I've been working quite a bit on my next book:  More Effective C#. It's getting closer, and it's now available on Rough Cuts.  Rough Cuts is a Safari Books Online service that provides you with pre-publication first access to upcoming books.  Chapters 1 & 2 are up right now. 

You can see more about the Rough Cuts program here: http://safari.informit.com/roughcuts

More Effective C# is here:  http://safari.informit.com/9780321580481

Alex James Explains the Pros and Cons of the Multiple EntitySets per Type (MEST) Model

Multiple EntitySets per Type is a model that few, if any, other object/relational management (O/RM) tools offer. In his MEST - What is it and how does it work? post of May 16, 2008, Alex describes how MEST works graphically by deriving GoodCustomer and BadCustomer entities from a base Customer entity.

He also describes the morass you enter when the GoodCustomers and BadCustomers EntitySets have two-way associations with Orders, OrderLines and Products EntitySets. Alex says:

• The EntityFramework in V1 just can't handle this ambiguity.
• Going forward we are trying to work out how to handle this situation gracefully.
• Still today the only workaround is to duplicate the whole graph.

Bob Beauchemin Explains VS 2008 SP1's Enhancements to ADO.NET Sync Services for Change Tracking with SQL Server 2008.

Bob's SQL Server 2008 Change Tracking and Sync Services ARE made for each other... in VS2008 SP1 post of May 16, 2008 is a paen to a new feature of VS 2008's Sync Services Designer:

Visual Studio 2008 SP1 Beta contains direct support for using SQL Server 2008 Change Tracking. When you use ADO.NET 1.0 Sync Services with a SQL Server 2008 database, the Sync Services designer (that's Add/New Item/Local Database Data Cache) adds a checkbox that allows you simply to "Use SQL Server Change Tracking". No extra triggers, no tombstone tables, change tracking does it all for you. Visual Studio generates some scripts to enable change tracking at a database level and at a table level for the tables you select.

He then goes on to discuss generated code to warn users who don't resync frequently enough and SQL Server Books Online's recommendation to use Snapshot isolation with this feature.

This feature is likely to enhance SQL Server 2008's participation in Live Mesh projects.

Dinesh Kulkarni Continues his LINQ to SQL Server Tips Series

LINQ to SQL Tips 7: Using stored procs that return multiple results of May 16, 2008 is the latest episode. Following are links to earlier posts:

• LINQ to SQL Tips 4: Use DeleteOnNull if you want to delete object with null FK
• LINQ to SQL Tips 3: Deferred (lazy) or eager loading of related objects with stored procs
• LINQ to SQL Tips 2: how to use common base class for all entities
• LINQ to SQL Tips 1: how to map an enum

The mystery is where are Tips 5 and 6?

Om Malik Presenting Cloud Computing Conference June 25, 2008 in San Francisco

A Web technology isn't real until it has its own conference. Om's Structure 08 Conference subtitled "Put Cloud Computing to Work" will be held at San Francisco's new Mission Bay Conference Center of June 25, 2008.

Among the speakers will be Werner Vogels (Amazon VP, CTO and keynoter), Microsoft's Debra Chrapaty (Microsoft Corporate Vice President, Global Foundation Services), and Christophe Bisciglia (Google Sr. Software Engineer).

Why should we ‘Refresh the Net’? is a sidebar that purports to offer "more about the topics and issues at Structure 08 from community thought leaders."

Technorati tags: Visual Studio 2008 SP1, VS 2008 SP1, Visual Studio 2008 SP1 Beta, VS 2008 SP1 Beta, Entity Framework, Entity Data Model, EDM, ObjectSpaces, WinFS, Jon Udell, VB 9.0, C# 3.0, Silverlight 2, LINQ, LINQ to SQL, SQL Server Data Services, SSDS, Cloud Computing, SQL Server 2008, Sync Services, Microsoft Sync Services, LINQ4SP, Live Mesh

ADO.NET Data Services, SQL Server Data Services and Synchronization Sessions at Tech*Ed Developers 2008

This list was compiled from the Tech*Ed Developers 2008 Session Catalog with ADO.NET, SQL Server Data Services, Sync or Compact as the Keywords.

Update 6/1/2008: See the "Steve Lasker Posts SQL Server Compact and ADO.NET Sync Services Sessions for Tech*Ed Developers 2008" topic of LINQ and Entity Framework Posts for 5/29/2008+ for a complete list of all SQL Server Compact and ADO.NET Sync Services sessions and other presentations.

ADO.NET Data Services (Project Astoria)

Includes breakout and Technology Learning Center (TLC) sessions.

ADO.NET Data Services for the Web (a.k.a. Project “Astoria”)

Track: Web and User Experience

Presenter: Mike Flasko

Level: 300

The new wave of Web applications are built on technologies such as AJAX and Microsoft Silverlight, which enable developers to build better, richer user experiences. These technologies bring a shift in how applications are organized, including a stronger separation of presentation from data. The goal of ADO.NET Data Services is to enable applications to expose data as a REST-based data service that can be consumed by Web clients within a corporate network and across the Internet. The data service is reachable over HTTP, and URIs are used to identify the various pieces of information available through the service. Interactions with the data service happens in terms of HTTP verbs, such as GET, POST, PUT, and DELETE, and the data exchanged in those interactions is represented in simple formats, such as ATOM/APP and JavaScript Object Notation (JSON).

ADO.NET Data Services Deep Dive (TLC)

Track: Web and User Experience

Presenter: Mike Flasko

Level: 300

Take a deep dive tour of ADO.NET Data Services where we show how to enable applications to expose data as a REST-based data service that can be consumed by Web clients. See demos of reaching the data service over HTTP and using URIs to identify the various pieces of information available through the service. We show the interaction with the data service in terms of HTTP verbs (GET, POST, PUT, and DELETE) and how the data is represented in simple formats, such as AtomPub and JavaScript Object Notation (JSON).

Using LINQ, the ADO.NET Entity Framework and ADO.NET Data Services with Third Party Databases

Track: Windows and Frameworks

Presenter: David Sceppa

Level: 300

The ADO.NET Entity Framework extends the existing ADO.NET provider model to allow provider writers to make their data stores available through the Entity Framework. This model allows developers to execute store [database-]agnostic queries against their data stores using LINQ, Entity SQL or ADO.NET Data Services. Provider writers will be in attendance at this Interactive Theater presentation to talk about their plans to support the Entity Framework and demonstrate their current bits.

SQL Server Data Services

Includes breakout and Technology Learning Center (TLC) sessions.

Introduction to Microsoft SQL Server Data Services

Track: Database Platform

Presenter: Sumitra Sengupta

Level: 200

Cloud computing offers many benefits to business customers looking to increase operational efficiencies, lower operational costs and/or move to a Software as a Service (SaaS) model. This session provides an overview of Microsoft SQL Server Data Services (SSDS) and walks through on-boarding and typical usage scenarios to show how CDS brings the power of scale-free storage, distributed computing, and reliable data processing to new and existing business solutions.

Developing Applications with Microsoft SQL Server Data Services

Track: Database Platform

Presenter: Nigel Ellis

Level: 300

This session covers the Microsoft SQL Server Data Services (SSDS) architecture, data model, and service APIs. It then provides coding examples of how to develop a simple end-to-end application using SSDS. It also identifies the challenges developers face, along with the necessary best practices to address these challenges.

Best Practices in Building Applications with Microsoft SQL Server Data Services

Track: Database Platform

Presenter: Jeff Currier

Level: 300

This interactive session provides a hands-on roadmap for developing and managing applications that use Microsoft SQL Server Data Services (SSDS), details best coding practices, and answers questions related to using on-demand storage services for business solutions.

ADO.NET Synchronization, Synchronization Framework, and SQL Server Compact

Includes breakout and Technology Learning Center (TLC) sessions.

Taking Data Offline: Introduction to Microsoft Synchronization Services for ADO.NET

Track: Windows Mobile

Presenter: Liam Cavanaugh

Level: 200

With Microsoft SQL Server Compact, developers can now easily cache data on the client in a compact, yet capable, transactional, queryable document-like database format. To maintain the cache, Sync Services for ADO.NET enables developers to easily cache data directly or over services using Windows Communication Foundation. Microsoft Visual Studio 2008 delivers new integrated, developer-oriented sync designers making caching an easy task to increase user productivity, decrease the server workload, and simplify data access. In addition to caching, you can use Sync Services for ADO.NET to enable full offline scenarios as well. This session also shows how Sync Services for ADO.NET is being integrated with SQL Server Change Tracking to increase developer productivity by providing integrated change tracking and conflict detection and subsequently decreasing the barrier to entry associated with implementing 2-way synchronization. Finally, this session discusses how Sync Services for ADO.NET has been built on top of the sync platform currently referred to as Microsoft Sync Framework. Among other things, this platform has allowed us to extend Sync Services for ADO.NET beyond the traditional hub-spoke topology to support peer-to-peer synchronization.

Microsoft Synchronization Services for ADO.NET: From Desktops to Devices

Track: Windows Mobile

Presenter: Vijay Tandra Sistla

Level: 300

Learn how to build an occasionally connected application with Winforms and Smart Device application with Sync Services for ADO.NET. The session demonstrates the integration with Microsoft Visual Studio 2008 and how to leverage Microsoft SQL Server 2008 database backends.

Building Custom Sync Providers for the Microsoft Sync Framework

Track: Windows Mobile

Presenter: Andy Wrigley

Level: 400

The Microsoft Sync Framework is the new sync platform enabling collaboration and offline access for applications, services, and devices. It supports any data type, any data store, any transfer protocol and network topology. The Sync Framework comes with three built-in providers: Sync Services for ADO.NET, for File Systems, and for FeedSync (RSS and ATOM feeds). However, a key aspect of the Microsoft Sync Framework is the ability to create custom synchronization providers. Using custom providers, you can make use of the Sync Framework to sync data items between a data source and a replica, which can be a custom data store on a device. In this session, learn about the Sync Framework architecture, and how to build a custom sync provider. There are plenty of demos, as we walk through a practical example of creating a custom sync provider to synchronize data items between a backend Server and 'Edge of the Network' computers such as laptops and mobile devices.

The New Windows Mobile Enterprise Architecture

Track: Windows Mobile

Presenter: Rob Tiffany

Level: 300

The way Windows Mobile integrates with enterprise assets has changed dramatically since the original Compaq iPAQ. From its humble beginnings synchronizing Pocket Access via an ActiveSync cradle, Windows Mobile has now become an integration "Swiss Army Knife" that plays well in any enterprise with a little help from its friends. System Center Mobile Device Manager joins Windows Mobile to the Domain, manages software updates and device settings while providing an efficient Mobile VPN connection. The Windows Communication Foundation Store and Forward transport provides reliable client-to-server, server-to-client, and peer-to-peer messaging via Microsoft Exchange 2007 and Direct Push. Sync Services for ADO.NET allows Windows Mobile to sync its data with Microsoft SQL Server, Oracle, DB2 or almost any other data source. Microsoft BizTalk 2006 R2 allows Windows Mobile to work with enterprise packages like SAP, Siebel, PeopleSoft, JD Edwards, and many others through its array of adapters. When you put all these technologies together, you have a mobile infrastructure that's optimized to be a strategic asset in your organization.

Take It With You!: Building Occasionally-Connected SharePoint Applications Using the Microsoft Sync Framework

Track: Office and SharePoint

Presenter: Darrin Bishop

Level: 300

Since the dawn of SharePoint we have lived a connected life. Microsoft Office SharePoint Server 2007 and Windows SharePoint Services v3 coupled with Microsoft Office Outlook 2007, Office Groove 2007, and a few third-party components, provides some support for the occasionally-connected application. Microsoft Sync Framework (currently in CTP) provides a extensible platform enabling offline access for applications. This session explores the possible integration points between Microsoft Sync Framework and SharePoint, allowing developers to more easily extend SharePoint data into the disconnected and occasionally-connected world.

Introduction to Microsoft Sync Framework

Track: Windows and Frameworks

Presenter: Darrin Bishop

Level: 400

In this session we introduce Microsoft Sync Framework. Learn about the capabilities of the synchronization platform and see a demonstration of data collaboration across devices and services. We use this demonstration to guide our walkthrough of the key components involved in building an application using Microsoft Sync Framework that will enable any data store to exchange information.

The New Windows Mobile Enterprise Architecture

Track: Windows Mobile

Presenter: Rob Tiffany

Level: 400

The way Windows Mobile integrates with enterprise assets has changed dramatically since the original Compaq iPAQ. From its humble beginnings synchronizing Pocket Access via an ActiveSync cradle, Windows Mobile has now become an integration "Swiss Army Knife" that plays well in any enterprise with a little help from its friends. System Center Mobile Device Manager joins Windows Mobile to the Domain, manages software updates and device settings while providing an efficient Mobile VPN connection. The Windows Communication Foundation Store and Forward transport provides reliable client-to-server, server-to-client, and peer-to-peer messaging via Microsoft Exchange 2007 and Direct Push. Sync Services for ADO.NET allows Windows Mobile to sync its data with Microsoft SQL Server, Oracle, DB2 or almost any other data source. Microsoft BizTalk 2006 R2 allows Windows Mobile to work with enterprise packages like SAP, Siebel, PeopleSoft, JD Edwards, and many others through its array of adapters. When you put all these technologies together, you have a mobile infrastructure that's optimized to be a strategic asset in your organization.

Microsoft SQL Server 2005 Compact Edition Ultimate Performance Tuning

Track: Windows Mobile

Presenter: Darren Shaffer

Level: 400

This session equips the advanced Windows Mobile developer with invaluable performance tuning techniques to get the most out of SQL Server Compact Edition. A deep dive into the query processor, storage engine, and the managed provider for SQL Compact Edition is followed by specific query tuning techniques and how to get the absolute best merge replication performance in your Windows Mobile data synchronization architecture.

Unlocking the Power of Microsoft SQL Server Compact Edition 3.5

Track: Database Platform

Presenter: Steve Lasker

Level: 300

The tiniest member of the SQL Server family just gets better and better as version 3.5 delivers major enhancements: new language features, stronger encryption, better tool support, and more synchronization options are just some of them! Come to this session to see what's hot in SSC 3.5 and how to leverage it in your smart client and Windows Mobile applications.

Advanced Object-Oriented Data Access with the Microsoft .NET Compact Framework 3.5 and Microsoft SQL Server Compact Edition 3.5

Track: Windows Mobile

Presenter: Rabi Shatter

Level: 300

This session looks at how the new Mobile Line of Business Accelerator handles data access. While the new data layer encapsulates advanced concepts, it can be used to rapidly build enterprise mobile applications. The data access layer allows for a standard way to access data. It includes stored procedures to isolate SQL from code. In addition, it includes a mechanism for triggers which are ideal for handling tasks like updating new data objects with auto incrementing keys. Finally, take a deep dive into using custom enumerators in order to reduce the number of loops required to display data.

Technorati tags: ADO.NET Data Services, Project Astoria, SQL Server Data Services, SSDS, ADO.NET Synchronization Services, ADO.NET Sync Services, Synchronization Framework, Sync Framework, SQL Server 2005 Compact Edition, SQL Server Compact Edition 3.5, SQL Server Compact 3.5, SSCE 3.5, TechEd, TechEd 2008, Tech*Ed, Tech*Ed 2008

SQL Server Compact Edition and Synchronization Updates

Steve Lasker's Creating your SQL Server Compact Edition database and schema in code post proposes to create your local SSCE database on the fly from T-SQL commands that you store as an application resource. SSCE's DDL doesn't accept multiple (batched) commands but SQL Server Management Studio [Express] (SSMS[X]) SP2 hides this limitation from you by allowing GO to separate batched commands.

Steve suggests the neat trick of adding the batch file that you tested and saved in SSMS[X] to the project's resources, and then use String.Split in code like the following to obtain an array of commands:

Dim commands() As String
commands = My.Resources.DatabaseCreation.Split( _
   New String() {"GO"}, StringSplitOptions.RemoveEmptyEntries)

If you use Orcas's Sync Designer to generate the local database cache from the server database's metadata, you must add foreign key and other constraints, as well as indexes, to SSCE with code. So Steve's approach applies to the SSCE Sync Framework, too.

Click here for a table that describes the most important SQL Server 2005 [Express] features that are missing in SSCE. My "Lighten Up Your Local Databases" article from the March 2007 Visual Studio Magazine issue shows you how to take full advantage of SSCE as a local data cache.

SyncGuru Rafik Robeal's sixth Sync Framework demo, Decoupling of Change Tracking Metadata, separates tracking information (for example, CreationDate, LastEditDate, and ClientID fields) into a separate tracking table. Using a separate tracking table minimizes the effect of synchronizing client caches on a production database's schema. Rafik says:

The good news though is that the building a decoupled change tracking layer is not a big deal. In the decoupled model, the base table schema remains intact. As the demo shows, three triggers (insert, update, and delete) and separate tracking table are enough to build equivalent change tacking model. Although, the sync commands need to change since it needs to grab corresponding tracking information by joining the base table with the tracking table, the changes are simple and easy to understand.

Technorati tags: SQL Server 2005 Compact Edition, SSCE, Sync Designer, Microsoft Synchronization Services, Sync Services

A Sync Services Bidirectional Test Harness

The Microsoft Synchronization Services (Sync Services) 1.0 API (runtime) requires a substantial amount of developer-authored code to define the basic elements required to perform bidirectional synchronization between an SQL Sever 2005 Compact Edition (SSCE) client and an SQL Server 2005 [Express] or other RDBMS server. The VB code (without empty lines or comments) to implement bidirectional synchronization for a pair of simple tables is about 95 lines if you take advantage of the runtime's CommandBuilder or about 165 lines if you don't.

Beta 1 Update May 15, 2007: The test harness was built and tested with the Orcas March 2007 CTP. Upgrading the project to Beta 1 exposed some new Sync Designer and SSCE v3.5 problems that prevent its operation. There is no effective workaround available at this time. (See my Sync Designer/SSCE Version Problems with Orcas Beta 1 post in The Microsoft Synchronization Services for ADO.NET forum.) The Sync Services team says that the Sync Designer problems are "fixed in a later build" and "most changes to runtime and designer are coming in beta 2.0." The SSCE team hasn't replied regarding fixes for compatibility problems with SQL Server Management Studio and Server Explorer, which another forum participant has experienced. I'll update this post when an upgraded test harness becomes available for download from the Visual Studio Magazine site.

Background

The Sync Services Designer that debuts in the Orcas March 2007 CTP greatly reduces the effort required to get a simple unidirectional service up and running. Completing a couple of simple forms generates a DatabaseName.sync XML document and VB or C# class file with all but two lines of code required to produce a simple one-way, download-only synchronization project. The price you pay for automating the service design process is the requirement to use SQL Server 2005 [Express] (or SQL Server/MSDE 2000) as the server RDBMS. The Sync Services 1.0 runtime is server-agnostic, as demonstrated by Rafik Robeal's use of Oracle 10g Express in his Demo V: Offline Application - Oracle Backend C# project.

All of Rafik's Sync Services runtime demos use simple orders and order_details tables with random primary key values. The orders table has order_id and order_date columns and order_details has order_id, order_details_id, product (name), and quantity columns. Both tables use order_id (int) as the sole primary key column, which has as PK_orders or PK_order_details (PK, Unique, Non-clustered), and UQ__orders__##### or UQ__order_details__##### (Unique, Non-clustered) indexes. This selection of keys prevents establishing a one-to-many relationship between orders and order_details tables, so there are no foreign key fields.

Update: 3/24/2007: Rafik added a Deep in Sync: Handling PK-FK Constraints post to The Synchronizer blog yesterday. This post explains Rafik's reason for not including PK/FK relationships and details the workings of—and settings for—these relationships in detail. Sync Services interprets the first table in the addition of client-side SyncTables and server-side SyncAdapters to their respective collections as the parent table. Adding related SyncTables to the SyncGroup that's attached to the SyncAgent assures that Sync Services processes the table changes as a unit.

A Sync Services Bidirectional Test Harness (Work in Progress)

Here's a preview of my Sync Services test harness, which (as usual) uses the Northwind Orders and Order_Details tables to emulate pseudo "real world" order and line items data. There's a one-to-many relationship (FK_Order_Details_Orders) between the identity primary key (OrderID) on the Orders table and composite primary key (OrderID, ProductID) on the Order_Details tables of both the client and server database. One of the purposes of the test harness is to determine whether it's practical to use Sync Services to replace merge replication for master/child tables. (This question remains unanswered at present, but the ability to replace RemoteDataAccess (RDA) seems assured).

The test harness project is in development at present; the downloadable sample code will accompany an article for Visual Studio Magazine's May 2007 issue. Click the images to display a full-size version.

Figure 1 - SQL Server 2005 Compact Edition Client Cache Page

The Client above and Server (below) pages enable selecting automated UPDATE, INSERT, or DELETE operations and provide rapid comparison of the latest additions to the client and server tables. You type the number of Orders and Order Details records in the text boxes and then click the Random Insert/Update/Delete button to apply the changes to the SSCE tables. Updates randomly alter the EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, and Freight field values of the Orders table and ProductID, Quantity, Unit Price, and Discount of the Order_Details table. Inserts add a random selection of a CustomerID value from the Customers table.

Clicking Synchronize starts the synchronization process. You can select from three methods of handling synchronization data conflicts on the client page.

Clicking the Add FK Constraints button adds a DataRelation between the Orders and Order_Details tables to the database and the NorthwindDataSet. (By design Sync Services doesn't add DataRelation(s) during the database and table creation process, and a problem with the DataSet Designer prevents persisting changes.) Code adds or updates the LastEditDate value to the Orders table (not shown) and the Order_Details table.

Figure 2 - SQL Server 2005 Express Server Data Source Page

Figure 3 - Client Schemas and Sync Statistics Page

The schemas and statistics page has a DataGridView control to display SSCE INFORMATION_SCHEMA "views" (actually tables). The Client ID combo box and Set button are for testing behavior of SSCE's identity feature. Text boxes display sync statistics and CommandText property values for all operations. You can copy the commands to Notepad for better visibility.

Figure 4 - Sync Payload Page

The Payload page shows the data transferred between the server and client (and vice-versa).

Figure 5 - Test Grids Page

Test grids hold snapshots of data transferred in a more readable format than the Payload page's.

Updated 3/24/2007: Added link to Rafik PK/FK posts, plus minor additions and clarifications.

Technorati tags: SQL Server 2005 CE, SSCE, Sync Services, Sync Designer, Orcas March 2007 CTP, Orcas, Visual Studio Magazine

SSCE Sync Designer Q&A and Screencast

Sync Services pilgrims working with the Sync Designer preview in the Orcas March 2007 CTP had many of their questions answered by Steve Lasker's Additional Q&A on the Visual Studio Orcas Sync Designer post of March 21, 2007, which supplements Steve's original Q&A on OCS & Sync Services for ADO.NET post of March 18, 2007. First look at the Visual Studio Orcas Sync Designer and Going N Tier w/WCF, Synchronizing data using Sync Services for ADO.NET and SQL Server Compact Edition are a pair of screencast posts (dated March 22 and 23, 2007), which cover the Sync Designer that's scheduled to debut in Visual Studio Orcas. SSCE Sync Designer Q&A

Steve answers these questions to which I've added some related references:

• Why does the Orcas Feb CTP Typed DataSet designer not work on Vista? I discovered this problem at the end of the aborted guided tour described in my Guided Tour of Orcas's Sync Services Designer for SSCE post of March 17, 2007.
• Will the Sync Designer generate time based sync? I mentioned the lack of this feature in the same post.
• Will tombstone records be automatically cleaned up? Rafik Robeal covers this topic in his Sync Services: Periodic Tombstone Cleanup post of February 16, 2006 to The Synchronizer blog.
• How do I get my cached tables to be synchronized in a single transaction? Rafik's A nice gift from SQL Server 2005 SP2 to sync developers post discusses an SQL Server 2005 fix for potential timestamp errors with uncommitted transactions.
• Once all the tables are placed in a single transaction, how do I control the order the tables are updated to handle parent/child relationships? Rafik discussed this issue in a "Synchronizing an 'Order'" thread in the Microsoft Synchronization Services for ADO.NET forum.
• Does the sync runtime create relationships locally within SQLce? I mentioned this problem in conjunction with the problem of inability to save design changes to SSCE DataSets in the Guided Tour of Orcas's Sync Services Designer for SSCE post.
• Does the sync runtime work with server side identities for PK's? The test harness I'm building has OrderID identity columns on the client and server sides and currently uses identity partitioning (similar to merge replication's approach) to identify the client machine that's the source of the update. Ultimately, the test harness will use ROWGUIDCOL columns.

SSCE Sync Designer Screencast—Part 1: First look at the Visual Studio Orcas Sync Designer

Steve's first Sync Designer (a.k.a. Cache Designer) screencast (25:49) demonstrates two-tier, one-way (download-only) synchronization of updates to reference data (Customers, Employees, and Shippers) for the Northwind Orders table. Reference (also called catalog) data, such as customer, vendor, or product lists, ordinarily are quite large but usually change relatively slowly. Two-tier, one-way sync for changes only is likely to be the most common Occasionally Connected System (OCS) scenario.

These are the only two lines of code in the Synchronize button's event handler that you need to sync the client with the server tables using the defaults you set in the designer:

Dim SyncAgent As NorthwindCacheSyncAgent = New NorthwindCacheSyncAgent

Dim SyncStats As Microsoft.Synchronization.Data.SyncStatistics = _ SyncAgent.Synchronize

The test harness's Synchronize button's event handler has about 100 lines of code to specify sync type and conflict handling, add and remove event handlers, and display SyncStatistics.

SSCE Sync Designer Screencast—Part 2: Going N Tier w/WCF, Synchronizing data using Sync Services for ADO.NET and SQL Server Compact Edition

The second screencast covers the n-tier scenario with a Windows Communications Foundation (WCF) service as an intermediary between the client and server. The architecture is similar to that Rafik Robeal demonstrated in his Demo III: Offline Application – WebService project.

Update 3/23/2007: My Sync Services demo project (a work in progress) has been moved to this new location: A Sync Services Bidirectional Test Harness. Added link to Part 2 of the screencast. Incorporated reference to original Sync Services Q&A in first paragraph.

Technorati tags: SQL Server 2005 CE, SSCE, Sync Services, Sync Designer, OCS, Occasionally Connected Systems, Orcas March 2007 CTP, Orcas

Sync Services for ADO.NET Overview

Steve Lasker's lengthy Q&A on OCS & Sync Services for ADO.NET post covers use of merge replication, Remote Data Access (RDA) and Sync Services for ADO.NET for synchronizing data between servers and clients (or publishers and subscribers) of Occasionally Connected Systems. As you'd expect, the emphasis is on Sync Services for ADO.NET with SQL Server Compact Edition (SSCE) v3.5.

Surprisingly, there's only one brief reference to the Sync Designer (in the answer to "Does Sync Services Support N Tier?") With the Sync Designer debuting in Orcas, I've been expecting more Q&A on the Sync Designer in the Microsoft Synchronization Services for ADO.NET forum. So far, searching on "designer" returns only two hits (one on 1/24/2007 and another 2/28/2007).

Maybe the lack of traffic is due to the strange name of the Orcas designer template: Local Database Cache (see Guided Tour of Orcas's Sync Services Designer for SSCE.) Data[base] Synchronization Service makes more sense to me. As of today, Google returned relevent hits on "Local Database Cache" only for Nick Randolph's and my posts. Same for "Sync Services Designer."

Update 3/21/2007: I should have searched for "local data cache" orcas, to pick up the Visual Basic Team blog's New Data Tools Features in Visual Studio Orcas post by Young Joo (3/13/2007). The post includes a "Local Data Cache with SQL Compact Edition" topic. The author omitted "base" and called the template "Local Data Cache." The article also:

• Describes Hierarchical Updates with the new TableAdapterManager class, which simplifies code for executing updates on all of your DataSet's table adapters with the TableAdapterManager.UpdateAll(DataSet) method
• Previews the newly-renamed Object Relational Designer (formerly the DLinq Designer)
• Introduces n-tier support for typed DataSets by splitting the class file into another project.

Local Database Cache also suffers from lack of any documentation whatsoever. Try searching online help for "Local Database Cache" -- nada. (Hierarchical Update's help topics appear complete and Object Relational Designer has an unfinished walkthrough.)

Get The Bits

Here are OakLeaf links to details for downloading the current SSCE CTPs, RTMs and samples:

• Microsoft Sync Services 1.0 Books Online CTP
• Conflict Resolution with Sync Services
• Three Sample C# SSCE Sync Services Projects
• Microsoft Releases Synchronization Services CTP
• New SQL Server CE Technical Articles and Tutorials
• SQL Server 2005 Compact Edition RTM Available

Technorati tags: SQL Server 2005 CE, SSCE, OCS, Sync Services, Sync Designer, Orcas March 2007 CTP, Orcas, Hierarchical Updates, Object Relational Designer, LINQ to SQL

Guided Tour of Orcas's Sync Services Designer for SSCE

Nick Randolph, co-author of WROX's Professional Visual Studio 2005 and Microsoft Visual Developer/Device Application Development MVP , has published a two-part demonstration for using the Orcas March 2007 CTP's new Sync Designer that starts when you add a Local Database Cache (LocalDataCache1.sync) template to your project.

Part 1 stops at the Add New Item dialog that displays Local Database Cache and Service-based Database templates.

Part 2 continues with the Configure Data Synchronization and Configure Tables for Offline Use dialogs.

Note: For assistance with Sync Services and Sync Designer issues, I recommend the Microsoft Synchronization Services for ADO.NET forum. The Synchronizer blog and Rafik Robeal's SyncGuru site offer downloadable C# Sync Services sample projects, documentation, and commentary. Nick Randolph's SQL Server CE Portal site offers FAQs for SSCE and Sync Services, including a sample VB project that improves on Rafik Robeal's C# code for configuring Sync Services.

My Problems with the Sync Designer

The series is similar to a demonstration page that I put together last week for the Orders and Order_Details tables of the Northwind sample database. I didn't publish it to the blog because the Wizard failed at the last step.

As noted in steps 9 through 11 of my test drive:

9. Under Windows Vista running on Virtual Server 2003 R2 Beta 1, the Orcas Data Source Configuration Wizard creates NorthwindDataSet.xsd, .xsc, and .xss, but doesn't create the the NorthwindDataSet.Designer.vb file for a typed NorthwindDataSet. (This problem doesn't occur with the Orcas Data Source Wizard running under virtualized Windows Server 2003 R2.)

10. When I attempted to generate a typed data set from Northwind.sdf with the Data Source Configuration Wizard under virtualized Vista, I received this informative error message:

This message has nothing to do with SSCE. It also occurs when attempting to create a typed DataSet from SSCE or SQL Server [Express] with the Data Source Configuration Wizard under Vista in my configuration. A search for the error message returns a link to this even less informative suggestion from the MSDN Library: "Inspect the error message and check for any errors in the Task List (Visual Studio) that can be fixed."

11. I received this error message when attempting to save any changes, such as adding the DataRelationship between the Orders and Order Details tables to the DataSet Designer for the client SSCE database's typed DataSet. This exception occurs under virtualized Windows Vista and virtualized Windows Server 2003 R2:

12. I set the sync interval to 5 minutes, but was unable to detect any evidence that synchronization was occurring between the SSCE client and SSX server.

Subsequently, I wrote a Sync Services test harness to add the DataRelation for the client and a customized SyncGroup at runtime. A future blog post and Visual Studio Magazine article will provide more details on the test harness.

I plan to redo the test drive with virtualized Windows XP SP2 after I finish the test harness studies.

Technorati tags: SQL Server 2005 CE, SSCE, Sync Services, Sync Designer, Orcas March 2007 CTP, Orcas