locked
Help me on "How to: Upload Incremental Data Changes to a Server" from WM5 RRS feed

  • Question

  • Hi All,

     

    I am learning "How to: Upload Incremental Data Changes to a Server" at http://msdn2.microsoft.com/en-us/library/bb726033(SQL.100).aspx

     

    I create a new "Smart Device" project in Visual Studio 2008 and then choose "Console Application", “Windows Mobile 5.0 Pocket PC SDK” and “.Net Compact Framework version 3.5”.  Then I copy sample VB code from the above linked page. 

     

    I add references as follow on my machine with Windows Vista.

    D:\Program Files\Microsoft Synchronization Services\ADO.NET\v1.0\Microsoft.Synchronization.Data.dll

    D:\Program Files\Microsoft Synchronization Services\ADO.NET\v1.0\Microsoft.Synchronization.Data.Server.dll

    D:\Program Files\Microsoft Synchronization Services\ADO.NET\v1.0\Microsoft.Synchronization.Data.SqlServerCe.dll

    D:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Devices\Client\System.Data.SqlClient.dll

    D:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Devices\System.Data.SqlServerCe.dll

     

    And the following is referenced by VS2008 automatically after I create this project.

    D:\Program Files\Microsoft.NET\SDK\CompactFramework\v3.5\WindowsCE\System.dll

    D:\Program Files\Microsoft.NET\SDK\CompactFramework\v3.5\WindowsCE\System.Core.dll

    D:\Program Files\Microsoft.NET\SDK\CompactFramework\v3.5\WindowsCE\System.Data.dll

    D:\Program Files\Microsoft.NET\SDK\CompactFramework\v3.5\WindowsCE\System.Data.DataSetExtensions.dll

    D:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Devices\Client\System.Data.SqlClient.dll

    D:\Program Files\Microsoft.NET\SDK\CompactFramework\v3.5\WindowsCE\System.Web.Services.dll

    D:\Program Files\Microsoft.NET\SDK\CompactFramework\v3.5\WindowsCE\System.Xml.dll

    D:\Program Files\Microsoft.NET\SDK\CompactFramework\v3.5\WindowsCE\System.Xml.Linq.dll

     

    Then I add “Program.vb” class to this project.  But look at following part of code with error underlined.

    Public Class SampleServerSyncProvider

        Inherits DbServerSyncProvider

     

        Public Sub New()

            'Create a connection to the sample server database.

            Dim util As New Utility()

            Dim serverConn As New SqlConnection(util.ServerConnString)

     

            Me.Connection = serverConn

     

            'Create a command to retrieve a new anchor value from

            'the server. In this case, we use a timestamp value

            'that is retrieved and stored in the client database.

            'During each synchronization, the new anchor value and

            'the last anchor value from the previous synchronization

            'are used: the set of changes between these upper and

            'lower bounds is synchronized.

            '

            'SyncSession.SyncNewReceivedAnchor is a string constant;

            'you could also use @sync_new_received_anchor directly in

            'your queries.

            Dim selectNewAnchorCommand As New SqlCommand()

            Dim newAnchorVariable As String = "@" + SyncSession.SyncNewReceivedAnchor

            With selectNewAnchorCommand

                .CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1"

                .Parameters.Add(newAnchorVariable, SqlDbType.Timestamp)

                .Parameters(newAnchorVariable).Direction = ParameterDirection.Output

                .Connection = serverConn

            End With

            Me.SelectNewAnchorCommand = selectNewAnchorCommand

     

            'Create a SyncAdapter for the Customer table, and then define

            'the commands to synchronize changes:

            '* SelectIncrementalInsertsCommand is used to get the schema

            '  from the server.

            '* InsertCommand, UpdateCommand, and DeleteCommand are used to apply

            '  to the server the changes that the client provider has selected

            '  from the client.

     

            'Create the SyncAdapter.

            Dim customerSyncAdapter As New SyncAdapter("Customer")

     

            'Get the schema from the server.

            Dim customerIncrInserts As New SqlCommand()

            With customerIncrInserts

                .CommandText = _

                    "SELECT CustomerId, CustomerName, SalesPerson, CustomerType " _

                  & "FROM Sales.Customer"

                .Connection = serverConn

            End With

            customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts

     

            'Apply inserts to the server.

            Dim customerInserts As New SqlCommand()

            With customerInserts

                .CommandText = _

                   "INSERT INTO Sales.Customer (CustomerId, CustomerName, SalesPerson, CustomerType, InsertId, UpdateId) " _

                 & "VALUES (@CustomerId, @CustomerName, @SalesPerson, @CustomerType, @sync_client_id, @sync_client_id) " _

                 & "SET @sync_row_count = @@rowcount"

                .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)

                .Parameters.Add("@CustomerName", SqlDbType.NVarChar)

                .Parameters.Add("@SalesPerson", SqlDbType.NVarChar)

                .Parameters.Add("@CustomerType", SqlDbType.NVarChar)

                .Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)

                .Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int)

                .Connection = serverConn

            End With

            customerSyncAdapter.InsertCommand = customerInserts

     

            'Apply updates to the server.

            Dim customerUpdates As New SqlCommand()

            With customerUpdates

                .CommandText = _

                    "UPDATE Sales.Customer SET " _

                  & "CustomerName = @CustomerName, SalesPerson = @SalesPerson, " _

                  & "CustomerType = @CustomerType, " _

                  & "UpdateId = @sync_client_id " _

                  & "WHERE (CustomerId = @CustomerId) " _

                  & "AND (@sync_force_write = 1 " _

                  & "OR (UpdateTimestamp <= @sync_last_received_anchor " _

                  & "OR UpdateId = @sync_client_id)) " _

                  & "SET @sync_row_count = @@rowcount"

                .Parameters.Add("@CustomerName", SqlDbType.NVarChar)

                .Parameters.Add("@SalesPerson", SqlDbType.NVarChar)

                .Parameters.Add("@CustomerType", SqlDbType.NVarChar)

                .Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)

                .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)

                .Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit)

                .Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)

                .Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int)

                .Connection = serverConn

            End With

            customerSyncAdapter.UpdateCommand = customerUpdates

     

            'Apply deletes to the server.           

            Dim customerDeletes As New SqlCommand()

            With customerDeletes

                .CommandText = _

                    "DELETE FROM Sales.Customer " _

                  & "WHERE (CustomerId = @CustomerId) " _

                  & "AND (@sync_force_write = 1 " _

                  & "OR (UpdateTimestamp <= @sync_last_received_anchor " _

                  & "OR UpdateId = @sync_client_id)) " _

                  & "SET @sync_row_count = @@rowcount " _

                  & "IF (@sync_row_count > 0)  BEGIN " _

                  & "UPDATE Sales.Customer_Tombstone " _

                  & "SET DeleteId = @sync_client_id " _

                  & "WHERE (CustomerId = @CustomerId) " _

                  & "END"

                .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)

                .Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit)

                .Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)

                .Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)

                .Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int)

                .Connection = serverConn

            End With

            customerSyncAdapter.DeleteCommand = customerDeletes

     

            'Add the SyncAdapter to the server synchronization provider.

            Me.SyncAdapters.Add(customerSyncAdapter)

     

        End Sub 'New

    End Class 'SampleServerSyncProvider

     

     

    The error message in VS2008 shows:

    Reference required to assembly 'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' containing the type 'System.Data.IDbConnection'. Add one to your project.

     

    I check the reference within Visual Studio Solution Explorer. There does have “System.Data” which point to:
    D:\Program Files\Microsoft.NET\SDK\CompactFramework\v3.5\WindowsCE\System.Data.dll

     

    Notice that this console application is smart device application which is supposed to be running on Windows Mobile 5.0 (I use emulator).  That’s why “System.Data” is from Compact Framework.  I need to run this console application from device emulator to sync data to upload to My desktop Sql Server 2000. 

     

    Is there anyone can give me help on how to get rid of this reference error? 

    Heap thanks.

     

    Stanley,

    • Moved by Max Wang_1983 Friday, April 22, 2011 8:03 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Friday, April 11, 2008 7:14 AM

All replies

  • Hi Stanley,

     

    In order to create a sync solution for device, you will need to follow the Ntier model of the sync service. i.e. create a client project and then create the server project ( either and WCF or a web service ) at the IIS server, and the client project references ( uses ) the web services or the WCF service.

     

    the program.vb you described above is purely a server side component, i.e. it should be used in the web service project.

     

    Please follow this link for details on the Sync Service for ADO.NET for device http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2957116&SiteID=1

     

    thanks

    Yunwen

    Monday, April 14, 2008 5:34 PM
    Moderator