Click here to Skip to main content
Click here to Skip to main content
Add your own
alternative version

Take Data Offline Using Microsoft Synchronization Services for ADO.NET

, 11 Jan 2008 CPOL
Build occasionally connected applications using Microsoft Synchronization Services for ADO.NET
takedataoffline_demo.zip
OfflineAppDemo-Builder
app
Settings.settings
bin
Release
SyncApplication.exe
SyncApplication.pdb
SyncApplication.vshost.exe
Properties
setup
--
-- Create fresh new server database called "pub"
--
drop database pub
go
create database pub
go

CREATE TABLE pub..orders(order_id int NOT NULL primary key, order_date datetime NULL)
go

CREATE TABLE pub..order_details(order_id int NOT NULL primary key, order_details_id int NOT NULL, product nvarchar(100) NULL, quantity int NULL)
go


--
-- Add sync columns
-- 1. Create update_originator_id column defaulted to 0 to indicate server change
ALTER TABLE pub..orders add update_originator_id int null default 0
ALTER TABLE pub..order_details add update_originator_id int null default 0
go
    
-- Add last update timestamp column
ALTER TABLE pub..orders add update_timestamp timestamp
ALTER TABLE pub..order_details add update_timestamp timestamp
go

-- Add create timestamp column (use bigint since one one timestamp column type is allowed per table)
ALTER TABLE pub..orders add create_timestamp bigint default @@DBTS+1
ALTER TABLE pub..order_details add create_timestamp bigint default @@DBTS+1
go    

--
-- Create tombstone tables to store deletes
--
CREATE TABLE pub..orders_tombstone(order_id int NOT NULL primary key, order_date datetime NULL, update_originator_id int default 0,  update_timestamp timestamp, create_timestamp bigint)
CREATE TABLE pub..order_details_tombstone(order_id int NOT NULL primary key, order_details_id int NOT NULL, product nvarchar(100) NULL, quantity int NULL, update_originator_id int default 0,  update_timestamp timestamp, create_timestamp bigint)
go

-- 
-- Create Update and Delete Triggers
-- Since there will be changes on the server outside of sync application
-- we need triggers to fixup update_originator_id back to 0 which designated
-- for server change

-- update triggers
use pub
go
CREATE TRIGGER orders_update_trigger on orders for update
as
    update o
    set o.update_originator_id = 0        
    from [orders] o join [inserted] i on o.order_id = i.order_id
	where not UPDATE(update_originator_id)
go

CREATE TRIGGER order_details_update_trigger on order_details for update
as
    update o
    set o.update_originator_id = 0        
    from [order_details] o join [inserted] i on o.order_id = i.order_id
	where not UPDATE(update_originator_id)
go

-- delete triggers
use pub
go
CREATE TRIGGER orders_delete_trigger on orders for delete
as
	insert into pub..orders_tombstone (order_id, order_date, create_timestamp, update_originator_id) select order_id, order_date, create_timestamp, 0 from deleted
go	

CREATE TRIGGER order_details_delete_trigger on order_details for delete
as
	insert into pub..order_details_tombstone (order_id, order_details_id, product, quantity, create_timestamp, update_originator_id) select order_id, order_details_id, product, quantity, create_timestamp, 0 from deleted
go	



--
-- Test Sample
--
insert into orders (order_id, order_date) values(1, GetDate())
insert into orders (order_id, order_date) values(2, GetDate())

insert into order_details (order_id, order_details_id, product) values(7, 2 , 'DVD')
insert into order_details (order_id, order_details_id, product) values(3, 3 , 'CD')
insert into order_details (order_id, order_details_id, product) values(4, 2 , 'Floppy Disk')

update order_details set product = 'HDTV' where order_id =2
update order_details set order_details_id = 11 where order_id in (20,21,23)

delete orders where order_id = 2


select @@dbts

select * from orders
select * from orders_tombstone
select * from order_details
select * from order_details_tombstone
select * from msrepl_info


By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Rafik Robeal
Chief Technology Officer Raveable.com
United States United States
Entrepreneur, developer and then some. I started Raveable Hotel Reviews in 2008 to help travelers find the best hotel for their vacation with no history of bed bug reports (hint: don't go new york without checking bed bugs in NYC hotels page), cases of personal property theft, or food poisoning incidents.
 
Prior to Raveable.com, I was a development lead at Microsoft Corporation. I led the team developing the next generation social networking for Windows Mobile. Before that I played leading role in the design and development of Microsoft Sync Framework, Sync Services for ADO.NET, SQL Server Replication and Windows File Replication.

My Company: Raveable Hotel Reviews
My Sync Blog: The Synchronizer
My Sync WebSite: www.syncguru.com

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141216.1 | Last Updated 11 Jan 2008
Article Copyright 2007 by Rafik Robeal
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid