DateTime to Int64

Rafik, Hi,

I'm using a newly created db... not the pub db from the demo. Turns out I had a bigInt column in one of the tombstone tables on the server... once I changed the data types to DateTime the error was gone... to many tables... lol :)

Thanks for the tip.... I have been deleting the the client db in between tests and some compiles and did notice if the anchor value did not match it would error out occassionaly.... Thanks a ton!

So now I totally have the tables being created on the client side... very cool stuff!!

Only thing is that my server data is not coming across and making it to the client db in the sync process.... still not 100% sure how to get the data to the client from the server.

Thanks,

Bill

billb59 at 2007-10-9 >

EXAMPLE SOURCE - one table:

privatevoid buttonSynchronize_Click(object sender, EventArgs e)

{

try

//

// 1. Create instance of the sync components (client, agent, server)

// This demo illustrates direct connection to server database. In this scenario,

// sync components - client provider, sync agent and server provider - reside at

// the client side. On the server, each table might need to be extended with sync

// related columns to store metadata. This demo adds three more columns to the

// orders and order_details tables for bidirectional sync. The changes are illustrated

// in demo.sql file.

//

//

DbServerSyncProvider serverSyncProvider = newDbServerSyncProvider();

SyncAgent syncAgent = newSyncAgent();

syncAgent.ServerSyncProvider = serverSyncProvider;

SqlConnectionStringBuilder builder = newSqlConnectionStringBuilder();

//

// 2. Prepare server db connection and attach it to the sync agent

//

builder["Data Source"] = textServerMachine.Text;

builder["integrated Security"] = true;

builder["Initial Catalog"] = "ffgscrm";

SqlConnection serverConnection = newSqlConnection(builder.ConnectionString);

serverSyncProvider.Connection = serverConnection;

//

// 3. Prepare client db connection and attach it to the sync provider

//

string connString = "Data Source=" + dbPathTextBox.Text;

if (false == File.Exists(dbPathTextBox.Text))

{

SqlCeEngine clientEngine = newSqlCeEngine(connString);

clientEngine.CreateDatabase();

clientEngine.Dispose();

}

SqlCeClientSyncProvider clientSyncProvider = newSqlCeClientSyncProvider(connString);

syncAgent.ClientSyncProvider = (ClientSyncProvider)clientSyncProvider;

//

// 4. Create SyncTables and SyncGroups

// To sync a table, a SyncTable object needs to be created and setup with desired properties:

// TableCreationOption tells the agent how to initialize the new table in the local database

// SyncDirection is how changes from with respect to client {Download, Upload, Bidirectional or Snapshot}

//

//

SyncTable tableProposalDetail = newSyncTable("ProposalDetail");

tableProposalDetail.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;

tableProposalDetail.SyncDirection = SyncDirection.Bidirectional;

//

// Sync changes for both tables as one bunch, using SyncGroup object

// This is important if the tables has PK-FK relationship, grouping will ensure that

// and FK change won't be applied before its PK is applied

//

//

SyncGroup orderGroup = newSyncGroup("AllChanges");

tableProposalDetail.SyncGroup = orderGroup;

// 5. Create sync adapter for each sync table and attach it to the agent

// Following DataAdapter style in ADO.NET, SyncAdapte is the equivelent for

// Sync. SyncAdapterBuilder is a helper class to simplify the process of

// creating sync commands.

//

//

SqlSyncAdapterBuilder ProposalDetailBuilder = newSqlSyncAdapterBuilder();

ProposalDetailBuilder.Connection = serverConnection;

ProposalDetailBuilder.SyncDirection = SyncDirection.Bidirectional;

// base table

ProposalDetailBuilder.TableName = "ProposalDetail";

ProposalDetailBuilder.DataColumns.Add("tblID");

ProposalDetailBuilder.DataColumns.Add("SQLkey");

ProposalDetailBuilder.DataColumns.Add("CreateDateTime");

ProposalDetailBuilder.DataColumns.Add("Alias");

ProposalDetailBuilder.DataColumns.Add("PropDNumb");

ProposalDetailBuilder.DataColumns.Add("PropDRevNumb");

ProposalDetailBuilder.DataColumns.Add("PropDLine");

ProposalDetailBuilder.DataColumns.Add("PropDItem");

ProposalDetailBuilder.DataColumns.Add("PropDMfg");

ProposalDetailBuilder.DataColumns.Add("PropDCat");

ProposalDetailBuilder.DataColumns.Add("PropDList");

ProposalDetailBuilder.DataColumns.Add("PropDCost");

ProposalDetailBuilder.DataColumns.Add("PropDGMDollar");

ProposalDetailBuilder.DataColumns.Add("PropDGM");

ProposalDetailBuilder.DataColumns.Add("PropDOGM");

ProposalDetailBuilder.DataColumns.Add("PropDSellPrice");

ProposalDetailBuilder.DataColumns.Add("PropDXSellPrice");

ProposalDetailBuilder.DataColumns.Add("PropDHideValue");

ProposalDetailBuilder.DataColumns.Add("PropDShipQty");

ProposalDetailBuilder.DataColumns.Add("PropDOptionalItem");

ProposalDetailBuilder.DataColumns.Add("PropDUOM");

ProposalDetailBuilder.DataColumns.Add("PropDStatus");

ProposalDetailBuilder.DataColumns.Add("PropDGMPercent");

ProposalDetailBuilder.DataColumns.Add("PropDNotes");

ProposalDetailBuilder.DataColumns.Add("PropDItemTotal");

ProposalDetailBuilder.DataColumns.Add("create_timestamp");

ProposalDetailBuilder.DataColumns.Add("update_timestamp");

ProposalDetailBuilder.DataColumns.Add("update_originator_id");

ProposalDetailBuilder.DataColumns.Add("sync_last_received_anchor");

ProposalDetailBuilder.DataColumns.Add("sync_new_received_anchor");

// tombstone table

ProposalDetailBuilder.TombstoneTableName = "ProposalDetail_tombstone";

ProposalDetailBuilder.TombstoneDataColumns.Add("tblID");

ProposalDetailBuilder.TombstoneDataColumns.Add("SQLkey");

ProposalDetailBuilder.TombstoneDataColumns.Add("CreateDateTime");

ProposalDetailBuilder.TombstoneDataColumns.Add("Alias");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDNumb");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDRevNumb");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDLine");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDItem");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDMfg");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDCat");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDList");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDCost");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDGMDollar");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDGM");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDOGM");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDSellPrice");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDXSellPrice");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDHideValue");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDShipQty");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDOptionalItem");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDUOM");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDStatus");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDGMPercent");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDNotes");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDItemTotal");

ProposalDetailBuilder.TombstoneDataColumns.Add("create_timestamp");

ProposalDetailBuilder.TombstoneDataColumns.Add("update_timestamp");

ProposalDetailBuilder.TombstoneDataColumns.Add("update_originator_id");

ProposalDetailBuilder.TombstoneDataColumns.Add("sync_last_received_anchor");

ProposalDetailBuilder.TombstoneDataColumns.Add("sync_new_received_anchor");

// tracking\sync columns

ProposalDetailBuilder.CreationTrackingColumn = @"create_timestamp";

ProposalDetailBuilder.UpdateTrackingColumn = @"update_timestamp";

ProposalDetailBuilder.DeletionTrackingColumn = @"update_timestamp";

ProposalDetailBuilder.UpdateOriginatorIdColumn = @"update_originator_id";

SyncAdapter ProposalDetailSyncAdapter = ProposalDetailBuilder.ToSyncAdapter();

serverSyncProvider.SyncAdapters.Add(ProposalDetailSyncAdapter);

// 6. Setup provider wide commands

// There are two commands on the provider itself and not on a table sync adapter:

// SelectNewAnchorCommand: Returns the new high watermark for current sync, this value is

// stored at the client and used the low watermark in the next sync

// SelectClientIdCommand: Finds out the client ID on the server, this command helps

// avoid downloading changes that the client had made before and applied to the server

//

//

// select new anchor command

SqlCommand anchorCmd = newSqlCommand();

anchorCmd.CommandType = CommandType.Text;

anchorCmd.CommandText = "SELECT GetDate()";

serverSyncProvider.SelectNewAnchorCommand = anchorCmd;

// client ID command (give the client id of 1)

// in remote server scenario (middle tear), this command will reference a local client table for the ID

SqlCommand clientIdCmd = newSqlCommand();

clientIdCmd.CommandType = CommandType.Text;

clientIdCmd.CommandText = "SELECT 1";

serverSyncProvider.SelectClientIdCommand = clientIdCmd;

//

// 7. Kickoff sync process

//

// Setup the progress form and sync progress event handler

_progressForm = newProgressForm();

_progressForm.Show();

clientSyncProvider.SyncProgress += newEventHandler<SyncProgressEventArgs>(ShowProgress);

clientSyncProvider.ApplyChangeFailed += newEventHandler<ApplyChangeFailedEventArgs>(ShowFailures);

serverSyncProvider.SyncProgress += newEventHandler<SyncProgressEventArgs>(ShowProgress);

SyncStatistics syncStats = syncAgent.Synchronize();

// Update the UI

_progressForm = null;

buttonRefreshOrders_Click(null, null);

//buttonRefreshOrderDetails_Click(null, null);

catch (Exception exp)

{

MessageBox.Show(exp.Message);

if (_progressForm != null)

{

_progressForm.EnableClose();

_progressForm = null;

}

}

}

SQL SCRIPT - Server tables used above in source:

SETANSI_NULLSON

SETQUOTED_IDENTIFIERON

IFNOTEXISTS(SELECT*FROMsys.objectsWHEREobject_id=OBJECT_ID(N'[dbo].[ProposalDetail]')ANDtypein(N'U'))

BEGIN

CREATETABLE [dbo].[ProposalDetail](

[tblID] [int] IDENTITY(1,1)NOTNULL,

[SQLkey] [int] NOTNULL,

[CreateDateTime] [datetime] NULL,

[Alias] [nvarchar](50)NOTNULL,

[PropDNumb] [nvarchar](10)NOTNULL,

[PropDRevNumb] [nvarchar](30)NULL,

[PropDLine] [numeric](4, 0)NULL,

[PropDItem] [nvarchar](50)NULL,

[PropDMfg] [nvarchar](3)NULL,

[PropDCat] [nvarchar](75)NULL,

[PropDList] [nvarchar](10)NULL,

[PropDCost] [numeric](10, 2)NULL,

[PropDGMDollar] [numeric](10, 2)NULL,

[PropDGM] [numeric](10, 2)NULL,

[PropDOGM] [numeric](10, 2)NULL,

[PropDSellPrice] [numeric](10, 2)NULL,

[PropDXSellPrice] [numeric](10, 2)NULL,

[PropDHideValue] [nvarchar](1)NULL,

[PropDShipQty] [numeric](9, 0)NULL,

[PropDOptionalItem] [nvarchar](1)NULL,

[PropDUOM] [nvarchar](6)NULL,

[PropDStatus] [nvarchar](18)NULL,

[PropDGMPercent] [numeric](10, 2)NULL,

[PropDNotes] [nvarchar](max)NULL,

[PropDItemTotal] [numeric](10, 2)NULL,

[create_timestamp] [datetime] NULL,

[update_timestamp] [datetime] NULL,

[update_originator_id] [nvarchar](max)NULL,

[sync_last_received_anchor] [datetime] NULL,

[sync_new_received_anchor] [datetime] NULL,

CONSTRAINT [PK_ProposalDetail] PRIMARYKEYCLUSTERED

[tblID] ASC

)WITH(PAD_INDEX =OFF, IGNORE_DUP_KEY =OFF)ON [PRIMARY]

)ON [PRIMARY]

END

SETANSI_NULLSON

SETQUOTED_IDENTIFIERON

IFNOTEXISTS(SELECT*FROMsys.objectsWHEREobject_id=OBJECT_ID(N'[dbo].[ProposalDetail_tombstone]')ANDtypein(N'U'))

BEGIN

CREATETABLE [dbo].[ProposalDetail_tombstone](

[tblID] [int] IDENTITY(1,1)NOTNULL,

[SQLkey] [int] NOTNULL,

[CreateDateTime] [datetime] NULL,

[Alias] [nvarchar](50)NOTNULL,

[PropDNumb] [nvarchar](10)NOTNULL,

[PropDRevNumb] [nvarchar](30)NULL,

[PropDLine] [numeric](4, 0)NULL,

[PropDItem] [nvarchar](50)NULL,

[PropDMfg] [nvarchar](3)NULL,

[PropDCat] [nvarchar](75)NULL,

[PropDList] [nvarchar](10)NULL,

[PropDCost] [numeric](10, 2)NULL,

[PropDGMDollar] [numeric](10, 2)NULL,

[PropDGM] [numeric](10, 2)NULL,

[PropDOGM] [numeric](10, 2)NULL,

[PropDSellPrice] [numeric](10, 2)NULL,

[PropDXSellPrice] [numeric](10, 2)NULL,

[PropDHideValue] [nvarchar](1)NULL,

[PropDShipQty] [numeric](9, 0)NULL,

[PropDOptionalItem] [nvarchar](1)NULL,

[PropDUOM] [nvarchar](6)NULL,

[PropDStatus] [nvarchar](18)NULL,

[PropDGMPercent] [numeric](10, 2)NULL,

[PropDNotes] [nvarchar](max)NULL,

[PropDItemTotal] [numeric](10, 2)NULL,

[create_timestamp] [datetime] NULL,

[update_timestamp] [datetime] NULL,

[update_originator_id] [nvarchar](max)NULL,

[sync_last_received_anchor] [datetime] NULL,

[sync_new_received_anchor] [datetime] NULL,

CONSTRAINT [PK_ProposalDetail_tombstone] PRIMARYKEYCLUSTERED

[tblID] ASC

)WITH(PAD_INDEX =OFF, IGNORE_DUP_KEY =OFF)ON [PRIMARY]

)ON [PRIMARY]

END

billb59 at 2007-10-9 >

Few things I noticed:

1- The update_originator_id column on the table should be of type int
2- You don't need the sync_last_received_anchor and sync_new_received_anchor columns on the base table or tombstone table.

This should be it. To test the app, make several changes on the server and make sure that the update_originator_id for the changes is set to 0.

RafikRobeal at 2007-10-9 >

The reason you are not getting the old data is because the tracking columns are not populated (set to null). In order to ensure that pre-existing rows will have a valid tracking value, you need to write your ALTER table statement as follows:

ALTER TABLE [table_name]
ADD [create_date] DateTime DEFAULT (GETUTCDATE()) WITH Values

You will need to do the same for all tracking columns including the update_originator_id (default is 0)

Thanks

RafikRobeal at 2007-10-9 >

I just used a simple... UPDATE ProposalDetail set create_timestamp = '2/27/2007 10:25:00 AM' ... to update the NULL table row values in the database and set the defaults as you mentioned above and the sync adapters are singing and dancing!!! :) Great Stuff!

Thanks again Rafik!

billb59 at 2007-10-9 >