locked
Changing SyncAdapters outside of a Webservice's constructor RRS feed

  • Question

  • Hello

     

    I am trying to use Demo Project III as the basis for a sync project that I'm working on but I'm having a problem changing the SQL selects of the SyncAdapters after the constructor creates them.

     

    I need to pass username filter info to the webservice so that the syncadapters can use it to filter the data pulled from the server.  Since Webservices don't allow overloaded constructors I created a method, ApplyUserFilter, to add to the where clause of the select statements of the syncadapter.  I can debug thru the code below and the debug.prints show the correct SQL statements.  But when the synchronization occurs the addition to the where clause is not respected and I get all records instead of the Territory 9 ones.

     

    As a test, I can put the Territory filter into the filterSQL variable of the constructor.  This works perfectly but is hardcoded and not argument driven like I need.

     

    I suspect that something unknown to me is happening after the constructor is complete preventing changes to the syncadapter.

     

    Does anyone have thougths about my problem?

     

     

    Public Sub New()

     

    Dim serverConnection As SqlConnection = New SqlConnection(My.Settings.ServerConnectionString)

    _serverProvider = New DbServerSyncProvider()

    _serverProvider.Connection = serverConnection

    Dim strSQL As String = String.Empty

    Dim filterSQL As String = String.Empty

    Dim fNewDB As Boolean = False

     

    Dim strSQLInsert As String = _

    "where create_timestamp > @sync_last_received_anchor " & _

    "and create_timestamp <= @sync_new_received_anchor"

    Dim strSQLUpdate As String = _

    "where create_timestamp <= @sync_last_received_anchor " & _

    "and update_timestamp > @sync_last_received_anchor " & _

    "and update_timestamp <= @sync_new_received_anchor"

    Dim strSQLDelete As String = _

    "where update_timestamp > @sync_last_received_anchor " & _

    "and update_timestamp <= @sync_new_received_anchor"

     

    strSQL = "SELECT DATAID,CORPORATION,COMDIV,VPNUMBER,TERRITORYNUMBER,DISTRICTNUMBER,STORENUMBER,SASDAT,SARGHR,SAOVTM,SATHRW,SAVACH,SASKHL,SAOTHR,SATOTH,SAAUTH,SAADJ,Variance FROM TBL_AUTHORIZED_HOURS "

     

    Dim adaptorTBL_AUTHORIZED_HOURS As SyncAdapter = New SyncAdapter("TBL_AUTHORIZED_HOURS")

    Dim incInsTBL_AUTHORIZED_HOURSCmd As SqlCommand = New SqlCommand()

    incInsTBL_AUTHORIZED_HOURSCmd.CommandType = CommandType.Text

    incInsTBL_AUTHORIZED_HOURSCmd.CommandText = strSQL + strSQLInsert + filterSQL

    incInsTBL_AUTHORIZED_HOURSCmd.Parameters.Add(SyncSession.SyncLastReceivedAnchor, SqlDbType.Binary, 8)

    incInsTBL_AUTHORIZED_HOURSCmd.Parameters.Add(SyncSession.SyncNewReceivedAnchor, SqlDbType.Binary, 8)

    adaptorTBL_AUTHORIZED_HOURS.SelectIncrementalInsertsCommand = incInsTBL_AUTHORIZED_HOURSCmd

     

    If Not fNewDB Then

    Dim incUpdTBL_AUTHORIZED_HOURSCmd As SqlCommand = incInsTBL_AUTHORIZED_HOURSCmd.Clone()

    incUpdTBL_AUTHORIZED_HOURSCmd.CommandText = strSQL + strSQLUpdate + filterSQL

    adaptorTBL_AUTHORIZED_HOURS.SelectIncrementalUpdatesCommand = incUpdTBL_AUTHORIZED_HOURSCmd

     

    Dim incDelTBL_AUTHORIZED_HOURSCmd As SqlCommand = incInsTBL_AUTHORIZED_HOURSCmd.Clone()

    incDelTBL_AUTHORIZED_HOURSCmd.CommandText = "select DataID from TBL_AUTHORIZED_HOURS_tombstone " + strSQLDelete + filterSQL

    adaptorTBL_AUTHORIZED_HOURS.SelectIncrementalDeletesCommand = incDelTBL_AUTHORIZED_HOURSCmd

    End If

     

    _serverProvider.SyncAdapters.Add(adaptorTBL_AUTHORIZED_HOURS)

     

    ' select new anchor command

    Dim anchorCmd As SqlCommand = New SqlCommand()

    anchorCmd.CommandType = CommandType.Text

    anchorCmd.CommandText = "SELECT @@DBTS"

    _serverProvider.SelectNewAnchorCommand = anchorCmd

    End Sub

     

    <WebMethod()> _

    Public Sub ApplyUserFilter(ByVal name As String, ByVal aliasName As String)

    Dim userFilter As String

     

    'Some real code using name and aliasname to get the filter, but for now...

    userFilter = " And TerritoryNumber = 9"

     

    With _serverProvider.SyncAdapters("TBL_AUTHORIZED_HOURS")

    .SelectIncrementalInsertsCommand.CommandText += userFilter

    Debug.Print("{0} {1} {2}", "ApplyUserFilter", .SelectIncrementalInsertsCommand.CommandText,  Now.ToLongTimeString)

     

    .SelectIncrementalUpdatesCommand.CommandText += userFilter

    Debug.Print("{0} {1} {2}", "ApplyUserFilter", .SelectIncrementalUpdatesCommand.CommandText, Now.ToLongTimeString)

    End With

    End Sub

     

    • Moved by Max Wang_1983 Friday, April 22, 2011 10:10 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Tuesday, August 14, 2007 2:25 PM

Answers

  • Hi Tod,

     

    I had to work around a very similar problem also.

     

    The reason you are having trouble with this is because of how web applications (website, web services) work.  The "New" method is called every time your sync services proxy communicates with the webservice.  For instance, when "GetSchema" is called, "New" is called, when "GetChanges" is called, "New" is called again, etc.  So a new _ServerProvider is created every time also.  Thus you lose any changes you made to it between calls.

     

    The way I worked around this was to pass my filter into each method call.  So I edited "GetSchema", "GetChanges", etc to include any parameters I needed for filtering.  In your case, I would have done this with GetChanges...

     

    Code Snippet

    Public Function GetChanges(ByVal name as String, ByVal aliiasName as String, ByVal groupMetadata As SyncGroupMetadata, ByVal syncSession As SyncSession) As SyncContext

    'Include code here to use name and aliasName in your serveradapter

     

    Return _serverProvider.GetChanges(groupMetadata, syncSession)

    End Function

     

     

     

    This is the best solution I could come up with and it seems to work well.  Another idea would be to store name and aliasName in the web session, though I'm not sure if that is the best route.  Anyone else please chime in if they have a better solution.

     

    Daniel

    Tuesday, August 14, 2007 4:31 PM
  • todwag,

     

    I've been thinking about this again lately.  The main reason being that the updated "offline" demo shows how to use the new "ServerSyncProviderProxy" class in Beta 2.  Before this was added, we had to create our own proxy on the client end.  Now, we can just simply do something like this....

     

    Code Snippet
    Dim syncWebService As New SyncWebServiceProxy.Service()
    syncAgent.RemoteProvider = New ServerSyncProviderProxy(syncWebService)

     

     

     

    Well, that would be great if I wasn't changing the methods on the webservice to have additional parameters.  Since I am, I still have to create my own proxy to compensate for the extra parameters.

     

    What I just tested briefly was using the SyncParameters property.  So on the client, my code looked like this...

     

    Code Snippet
    syncAgent.Configuration.SyncParameters.Add("TestName", "Test Value")

     

     

     

    And on the server....

     

    Code Snippet
    m_strTestValue = syncSession.SyncParameters("TestName")

     

     

     

    As I had hoped, the parameter was passed to the webservice and I could work with it from then on.

     

    I will try to test with it some more, but I wanted to pass it along to you so you can try it or let me know if you have already tried it and found that it fails somewhere.

     

     

    Daniel

     

    PS.  To anyone on the Sync Services team - I find it a little bit annoying that we still have to manually update the webservice Reference file.  I was hoping that requirement would go away once we started using the ServerSyncProviderProxy.  Am I doing something wrong and\or is this something that will be changed in the future?

    Thursday, August 23, 2007 2:12 PM

All replies

  • Hi Tod,

     

    I had to work around a very similar problem also.

     

    The reason you are having trouble with this is because of how web applications (website, web services) work.  The "New" method is called every time your sync services proxy communicates with the webservice.  For instance, when "GetSchema" is called, "New" is called, when "GetChanges" is called, "New" is called again, etc.  So a new _ServerProvider is created every time also.  Thus you lose any changes you made to it between calls.

     

    The way I worked around this was to pass my filter into each method call.  So I edited "GetSchema", "GetChanges", etc to include any parameters I needed for filtering.  In your case, I would have done this with GetChanges...

     

    Code Snippet

    Public Function GetChanges(ByVal name as String, ByVal aliiasName as String, ByVal groupMetadata As SyncGroupMetadata, ByVal syncSession As SyncSession) As SyncContext

    'Include code here to use name and aliasName in your serveradapter

     

    Return _serverProvider.GetChanges(groupMetadata, syncSession)

    End Function

     

     

     

    This is the best solution I could come up with and it seems to work well.  Another idea would be to store name and aliasName in the web session, though I'm not sure if that is the best route.  Anyone else please chime in if they have a better solution.

     

    Daniel

    Tuesday, August 14, 2007 4:31 PM
  • Daniel

     

    Thanks for the fast reply.

     

    I have made a test of your suggestion and it worked perfectly.

     

    But if anyone else has a better solution, please post it.

    Tuesday, August 14, 2007 9:10 PM
  • todwag,

     

    I've been thinking about this again lately.  The main reason being that the updated "offline" demo shows how to use the new "ServerSyncProviderProxy" class in Beta 2.  Before this was added, we had to create our own proxy on the client end.  Now, we can just simply do something like this....

     

    Code Snippet
    Dim syncWebService As New SyncWebServiceProxy.Service()
    syncAgent.RemoteProvider = New ServerSyncProviderProxy(syncWebService)

     

     

     

    Well, that would be great if I wasn't changing the methods on the webservice to have additional parameters.  Since I am, I still have to create my own proxy to compensate for the extra parameters.

     

    What I just tested briefly was using the SyncParameters property.  So on the client, my code looked like this...

     

    Code Snippet
    syncAgent.Configuration.SyncParameters.Add("TestName", "Test Value")

     

     

     

    And on the server....

     

    Code Snippet
    m_strTestValue = syncSession.SyncParameters("TestName")

     

     

     

    As I had hoped, the parameter was passed to the webservice and I could work with it from then on.

     

    I will try to test with it some more, but I wanted to pass it along to you so you can try it or let me know if you have already tried it and found that it fails somewhere.

     

     

    Daniel

     

    PS.  To anyone on the Sync Services team - I find it a little bit annoying that we still have to manually update the webservice Reference file.  I was hoping that requirement would go away once we started using the ServerSyncProviderProxy.  Am I doing something wrong and\or is this something that will be changed in the future?

    Thursday, August 23, 2007 2:12 PM
  • Thanks again Daniel. 

     

    I have experimented with the parameters collection and I think it will work.

     

    Wednesday, September 5, 2007 7:32 PM
  •  

    good discussion.

     

    another approach is to create the server provider as some sort of static object on the web service ( based on your case, you can either only have one or have mutiple of them stored in a hashtable. ) you 'll need to introduce your own web session in order to have the provider consturcted and desposed, by using BeginMyWebSyncSession(), EndMyWebSyncSession() webmethod. this should provide a more flexible way to deal with the server provider on the web server.

     

    thanks

    Yunwen

    Saturday, September 8, 2007 6:31 PM
    Moderator