locked
URGENT: How to pass the Database name from the client to the server in a N-Tier environement RRS feed

  • Question

  • Hello,

    I know it has been asked by others in this forum, but i did not find any answer... Then I am asking:

     

    How to pass the Database name from the client to the server in a N-Tier environement? I need the client to tell the server witch database to use for the syncronization.

     

    Any idea?

     

    _serverProvider.Connection = new SqlConnection("Server=xxx.xxx.xxx.xxx;Database=XXXXX;uid=xx;pwd=xxxx;");

     

    Thank you,

     

    Yann

     

    • Moved by Max Wang_1983 Friday, April 22, 2011 6:01 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Monday, August 4, 2008 6:56 PM

Answers

  • HI Daniel,

     

    Thank you for your help. I actualy tried again last night and could not make it works by reproducing in c# your vb snippet.

     

    Anyway, by playing around, I found another solution using SyncParameter that works then I will post it for other people.

     

    Client side: (no need to modifie the proxy webservice)

    Code Snippet

    SyncAgent syncAgent = new SyncAgent();

    ClientSyncWebService.ClientSyncService syncWebService = new ClientSyncWebService.ClientSyncService();

    syncAgent.RemoteProvider = new ServerSyncProviderProxy(syncWebService);

     

    string connString = "Data Source=xxx.sdf; password=xxx; ";

    SqlCeClientSyncProvider clientSyncProvider = new SqlCeClientSyncProvider(connString);

    syncAgent.LocalProvider = clientSyncProvider;

     

    SyncTable customerSyncTable1 = new SyncTable("Table1");

    customerSyncTable1.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;

    customerSyncTable1.SyncDirection = SyncDirection.Bidirectional;

    syncAgent.Configuration.SyncTables.Add(customerSyncTable1);

     

    SyncParameter param0 = new SyncParameter();

    param0.Name = "CustomerID";

    param0.Value = "00000000"; <-- this is my CustomerID used to lookup the Database

    syncAgent.Configuration.SyncParameters.Add(param0);

     

    syncAgent.Synchronize();

     

     

    Server side:

    Code Snippet

    [WebMethod]

    public SyncServerInfo GetServerInfo(SyncSession session)

    {

    FillServerProvider(session.SyncParameters["CustomerID"].Value.ToString());

    return _serverProvider.GetServerInfo(session);

    }

    [WebMethod]

    public SyncSchema GetSchema(Collection<string> tableNames, SyncSession session)

    {

    FillServerProvider(session.SyncParameters["CustomerID"].Value.ToString());

    return _serverProvider.GetSchema(tableNames, session);

    }

    [WebMethod]

    public SyncContext GetChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession)

    {

    FillServerProvider(syncSession.SyncParameters["CustomerID"].Value.ToString());

    SyncContext result = _serverProvider.GetChanges(groupMetadata, syncSession);

    return result;

    }

    [WebMethod]

    public SyncContext ApplyChanges(SyncGroupMetadata groupMetadata, DataSet dataSet, SyncSession syncSession)

    {

    FillServerProvider(syncSession.SyncParameters["CustomerID"].Value.ToString());

    return _serverProvider.ApplyChanges(groupMetadata, dataSet, syncSession);

    }

     

     

    Code Snippet

    private void FillServerProvider(string CustomerID)

    {

    _serverProvider = new DbServerSyncProvider();

    _serverProvider.Connection = new SqlConnection("Server=XXXXXXXX;Database=" + CustomerID + ";uid=XXXXXXXX;pwd=XXXXXXXX;");

     

    ...Remaining code needed to set up the Server Provider, Sync Tables, etc...

    ...

    ...

    }

     

     

    Cheers,

    Yann

     

     

    Wednesday, August 6, 2008 9:46 PM
  • Thanks Daniel and Yann for sharing out your solutions.

     

    Essentially what you did is right to resolve this issue. i.e. this would need to be handled at the application level. but instead of handling this by adding the logic in the GetServerInfo(). I would suggest to introduce a appWebSyncSession for it. for a couple of reasons.

     

    1. the GetServerInfo(), as it name speaks, is designed to getInformation, such as backend server type, what tables are avaiable at the server provider ( adapters ) etc.

    2. a appWebSyncSession would have logic like:

    guid beginappWebSyncSession(obj sessionInfo )

    do sync

    object endappWebSyncSession(guid)

    where in begin appWebSynSesison, the serverProvider can be set to the desired settings ( such as db name ) and other specially configurations for a particular sync, then the confiuration can be cached. the endappSyncSesison would close the sync session ( cleanup if needed ) and return any thing that occured for this sync ( i.e. server side conflict info etc).

     

    this will allow your app to expand if needed in the future.

     

    thanks

    Yunwen

     

    Wednesday, August 6, 2008 11:23 PM
    Moderator

All replies

  • I have been able to do this by changing the methods in my Web Service to require an extra parameter for Database Name.  In my case, my databases are differentiated by the client's ID.  So a sample method in my proxy class looks like this...

     

    Code Snippet

    Public Overloads Overrides Function GetServerInfo(ByVal syncSession As SyncSession) As Microsoft.Synchronization.Data.SyncServerInfo

    Return _service.GetServerInfo(_clientID, _lstTables, SyncSession)

    End Function

     

     

    (Note - I also added a list of tables that I am syncing as a parameter - _lstTables because that part of my sync is done dynamically also)....

     

    I had to add this parameter to every method in my web service. So my web service methods all look something like this...

     

    Code Snippet

    _

    Public Function GetServerInfo(ByVal clientID As Int32, ByVal syncSession As SyncSession) As Microsoft.Synchronization.Data.SyncServerInfo

    FillServerProvider(clientID)

    Return _serverProvider.GetServerInfo(syncSession)

    End Function

     

     

     

    The "FillServerProvider" method is just the code that was in the Web Service's constructor in the N-Tier demo...

     

    Code Snippet

    Private Sub FillServerProvider(ByVal ClientID As Int32)

     

    If Not _serverProvider Is Nothing Then Exit Sub

    _serverProvider = New DbServerSyncProvider()

    Dim builder As New SqlConnectionStringBuilder()

    '

    ' 1. Prepare server db connection and attach it to the sync agent

    '

    builder.DataSource =[Data Source]

    builder.InitialCatalog = [Catalog - based on Client_ID]

    builder.UserID =

    builder.Password =

    Dim serverConnection As New SqlConnection(builder.ConnectionString)

    _serverProvider.Connection = serverConnection

     

    Dim anchorCmd As New SqlCommand()

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

    anchorCmd.CommandText = "SELECT " + newAnchorVariable + " = GETDATE()"

    anchorCmd.Parameters.Add(newAnchorVariable, SqlDbType.DateTime)

    anchorCmd.Parameters(newAnchorVariable).Direction = ParameterDirection.Output

    anchorCmd.Connection = serverConnection

    _serverProvider.SelectNewAnchorCommand = anchorCmd

     

    ...Remaining code needed to set up the Server Provider, Sync Tables, etc...

    ...

    ...

    End Sub

     

     

     

    ...which means my Service's constructor doesn't have anything in it any more.

     

    I'm not sure if this is the best way to accomplish the need here, but it works for me anyway.  I'll follow this thread if you decide to try it and have any more questions...

     

    Daniel

    Monday, August 4, 2008 8:20 PM
  • Daniel,

     

    Thank you for your idea...

    That was the way i was trying but i got stuck at how do you then pass the param (ClientID in your sample) to the webservice on the client side?

     

     

    Code Snippet

    SyncAgent syncAgent = new SyncAgent();

    ClientSyncWebService.ClientSyncService syncWebService = new ClientSyncWebService.ClientSyncService();

    syncAgent.RemoteProvider = new ServerSyncProviderProxy(syncWebService);

     

    string connString = "Data Source=xxx.sdf; password=xxx; ";

    SqlCeClientSyncProvider clientSyncProvider = new SqlCeClientSyncProvider(connString);

    syncAgent.LocalProvider = clientSyncProvider;

     

    SyncTable customerSyncTable1 = new SyncTable("Table1");

    customerSyncTable1.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;

    customerSyncTable1.SyncDirection = SyncDirection.Bidirectional;

    syncAgent.Configuration.SyncTables.Add(customerSyncTable1);

     

    syncAgent.Synchronize();

     

     

     

    Monday, August 4, 2008 9:16 PM
  • You have to change your ServerSyncProviderProxy class by adding a parameter for whatever you need to pass to the webservice (ClientID).  The example for that was in the first snippet I provided.

     

    Remember that you have to add the parameter to every method in the ServerSyncProviderProxy class and in the WebService's methods.

    Monday, August 4, 2008 9:22 PM
  • Daniel,

    I did add the paramater in the client side and server side... but the parameter is not propagater to the server side... on my sample, on the server side, CustomerID is always null.... of course, I show only GetServerInfo but I did ht for l 4 methods(GetServerInfo, GetSchema, GetChanges and ApplyChanges)

     

    Did I miss something?

     

    Thanks again,

     

    Server side:

    Code Snippet

    [WebMethod]

    public SyncServerInfo GetServerInfo(SyncSession session, string CustomerID)

    {

    FillServerProvider(CustomerID); <<-- customerID is always null..

    return _serverProvider.GetServerInfo(session);

    }

     

     

    client side:

    Code Snippet

    [System.Web.Services.Protocols.SoapDocumentMethodAttribute("http://tempuri.org/GetServerInfo", RequestNamespace="http://tempuri.org/", ResponseNamespace="http://tempuri.org/", Use=System.Web.Services.Description.SoapBindingUse.Literal, ParameterStyle=System.Web.Services.Protocols.SoapParameterStyle.Wrapped)]

    public SyncServerInfo GetServerInfo(SyncSession session) {

    object[] results = this.Invoke("GetServerInfo", new object[] {

    session,

    "00000000"}); <<-- this is my customerID

    return ((SyncServerInfo)(results[0]));

    }

    /// <remarks/>

    public void GetServerInfoAsync(SyncSession session) {

    this.GetServerInfoAsync(session, null);

    }

    /// <remarks/>

    public void GetServerInfoAsync(SyncSession session, object userState) {

    if ((this.GetServerInfoOperationCompleted == null)) {

    this.GetServerInfoOperationCompleted = new System.Threading.SendOrPostCallback(this.OnGetServerInfoOperationCompleted);

    }

    this.InvokeAsync("GetServerInfo", new object[] {

    session,

    "00000000"}, <<-- this is my customerID

    this.GetServerInfoOperationCompleted, userState);

    }

    private void OnGetServerInfoOperationCompleted(object arg) {

    if ((this.GetServerInfoCompleted != null)) {

    System.Web.Services.Protocols.InvokeCompletedEventArgs invokeArgs = ((System.Web.Services.Protocols.InvokeCompletedEventArgs)(arg));

    this.GetServerInfoCompleted(this, new GetServerInfoCompletedEventArgs(invokeArgs.Results, invokeArgs.Error, invokeArgs.Cancelled, invokeArgs.UserState));

    }

    }

     

     

     

    Monday, August 4, 2008 10:43 PM
  • My c# is a little rough, so I may be off here.  Maybe you need to add the CustomerID parameter to this line on the client...

     

    public SyncServerInfo GetServerInfo(SyncSession session)

     

     If not, then I can't see anything missing by looking at the code you provided.

     

    Daniel

    Tuesday, August 5, 2008 1:41 PM
  • HI Daniel,

     

    Thank you for your help. I actualy tried again last night and could not make it works by reproducing in c# your vb snippet.

     

    Anyway, by playing around, I found another solution using SyncParameter that works then I will post it for other people.

     

    Client side: (no need to modifie the proxy webservice)

    Code Snippet

    SyncAgent syncAgent = new SyncAgent();

    ClientSyncWebService.ClientSyncService syncWebService = new ClientSyncWebService.ClientSyncService();

    syncAgent.RemoteProvider = new ServerSyncProviderProxy(syncWebService);

     

    string connString = "Data Source=xxx.sdf; password=xxx; ";

    SqlCeClientSyncProvider clientSyncProvider = new SqlCeClientSyncProvider(connString);

    syncAgent.LocalProvider = clientSyncProvider;

     

    SyncTable customerSyncTable1 = new SyncTable("Table1");

    customerSyncTable1.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;

    customerSyncTable1.SyncDirection = SyncDirection.Bidirectional;

    syncAgent.Configuration.SyncTables.Add(customerSyncTable1);

     

    SyncParameter param0 = new SyncParameter();

    param0.Name = "CustomerID";

    param0.Value = "00000000"; <-- this is my CustomerID used to lookup the Database

    syncAgent.Configuration.SyncParameters.Add(param0);

     

    syncAgent.Synchronize();

     

     

    Server side:

    Code Snippet

    [WebMethod]

    public SyncServerInfo GetServerInfo(SyncSession session)

    {

    FillServerProvider(session.SyncParameters["CustomerID"].Value.ToString());

    return _serverProvider.GetServerInfo(session);

    }

    [WebMethod]

    public SyncSchema GetSchema(Collection<string> tableNames, SyncSession session)

    {

    FillServerProvider(session.SyncParameters["CustomerID"].Value.ToString());

    return _serverProvider.GetSchema(tableNames, session);

    }

    [WebMethod]

    public SyncContext GetChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession)

    {

    FillServerProvider(syncSession.SyncParameters["CustomerID"].Value.ToString());

    SyncContext result = _serverProvider.GetChanges(groupMetadata, syncSession);

    return result;

    }

    [WebMethod]

    public SyncContext ApplyChanges(SyncGroupMetadata groupMetadata, DataSet dataSet, SyncSession syncSession)

    {

    FillServerProvider(syncSession.SyncParameters["CustomerID"].Value.ToString());

    return _serverProvider.ApplyChanges(groupMetadata, dataSet, syncSession);

    }

     

     

    Code Snippet

    private void FillServerProvider(string CustomerID)

    {

    _serverProvider = new DbServerSyncProvider();

    _serverProvider.Connection = new SqlConnection("Server=XXXXXXXX;Database=" + CustomerID + ";uid=XXXXXXXX;pwd=XXXXXXXX;");

     

    ...Remaining code needed to set up the Server Provider, Sync Tables, etc...

    ...

    ...

    }

     

     

    Cheers,

    Yann

     

     

    Wednesday, August 6, 2008 9:46 PM
  • Thanks Daniel and Yann for sharing out your solutions.

     

    Essentially what you did is right to resolve this issue. i.e. this would need to be handled at the application level. but instead of handling this by adding the logic in the GetServerInfo(). I would suggest to introduce a appWebSyncSession for it. for a couple of reasons.

     

    1. the GetServerInfo(), as it name speaks, is designed to getInformation, such as backend server type, what tables are avaiable at the server provider ( adapters ) etc.

    2. a appWebSyncSession would have logic like:

    guid beginappWebSyncSession(obj sessionInfo )

    do sync

    object endappWebSyncSession(guid)

    where in begin appWebSynSesison, the serverProvider can be set to the desired settings ( such as db name ) and other specially configurations for a particular sync, then the confiuration can be cached. the endappSyncSesison would close the sync session ( cleanup if needed ) and return any thing that occured for this sync ( i.e. server side conflict info etc).

     

    this will allow your app to expand if needed in the future.

     

    thanks

    Yunwen

     

    Wednesday, August 6, 2008 11:23 PM
    Moderator
  • Hi Yunwen,

     

    That sounds pretty interesting... will give it a try soon.

     

    Cheers,

     

    Yann

     

    Thursday, August 7, 2008 1:28 AM
  • Hi

    I tried Yann's method and it works fine for me.

    Can I ask you please to clarify Yunwen's method.

     

    Thanks,

    SIX

     

    Thursday, August 28, 2008 6:33 AM