todwag

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




Re: Microsoft Synchronization Services for ADO.NET Changing SyncAdapters outside of a Webservice's constructor

Daniel E R

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





Re: Microsoft Synchronization Services for ADO.NET Changing SyncAdapters outside of a Webservice's constructor

todwag

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.






Re: Microsoft Synchronization Services for ADO.NET Changing SyncAdapters outside of a Webservice's constructor

Daniel E R

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





Re: Microsoft Synchronization Services for ADO.NET Changing SyncAdapters outside of a Webservice's constructor

todwag

Thanks again Daniel.

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






Re: Microsoft Synchronization Services for ADO.NET Changing SyncAdapters outside of a Webservice's constructor

Yunwen Bai

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