locked
Can I use a SqlParameter when overriding SelectIncrementalInsertsCommand? RRS feed

  • Question

  • I have implemented a partial class for my SyncAdapter, so that I can write custom sql for the queries.  If I try to add a SqlParameter to the command, I get an error saying that I am not allowed to set parameters in the server code.  I know how to set parameters from the client using the SyncAgent, but in this case, I do not want to allow the client code to specify these parameter values.  I have tried every possible cludge to forcably use a SqlParameter from the server, but all attempts have failed.  Is this possible?  I could ignore using a SqlParameter all together and just hard code in a literal value into the sql string that I am generating, but I know that would negatively affect performance of the query engine.

    Thursday, May 26, 2011 8:45 PM

All replies

  • which provider are you using ? is the error from setting the parameter or the setting the VALUE of the parameter ?

    in general, if you overwrite the sqlcommand, then the application should ensure the command ( sql cmd ) and the parameters are set correctly.

    thanks

    Yunwen 


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, May 26, 2011 10:17 PM
  • If I try to add a parameter that was not declared in the client SyncManager, then I get the error "Cannot set session parameters in DbServerSyncProvider".  If I have added a parameter in the client SyncManager and then try to change that value in SyncService or the SyncAdapter, the new value is just ignored, and the one from the client is used.  I am allowed to overwrite the CommandText of the SqlCommand, but it appears as though I am not allowed to add any parameters to it.
    Friday, May 27, 2011 11:55 AM
  • You can try "DbSyncAdapter" class to customize the SQL query and parameters.

    And bind this to SyncProvider.

    Thanks

    P

    • Marked as answer by Yunwen Bai Friday, May 27, 2011 5:21 PM
    • Unmarked as answer by Eric3333 Thursday, June 2, 2011 1:18 PM
    Friday, May 27, 2011 2:33 PM
  • I am not sure is DBSyncAdapter would work in a non client/server setup, but this does not seem to be the answer for me where I have SqlCeClientSyncProvider and DbServerSyncProvider.  In DbServerSyncProvider I am able to alter the CommandText, but I cannot add a new parameter with a value.  I can reference a parameter that was established on the client side in the sync agent, but I have a need to use a SQLParameter that I have set in the server code.
    Thursday, June 2, 2011 1:23 PM
  • can you share out some code around this ? the sqlcommands and dbsyncadapter off the dbServerSyncProvider should be editable by the sync applications so I cannot see anyreason you cannot set the parameter for it while you can set the CommandText.

    thanks

    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, June 2, 2011 10:28 PM
  • If I add @KnownParameter to my SyncAgent.Configuration in the client code, this works from the SyncAdapter:

    CommandForUpdate.CommandText = SelectQuery;
    CommandForUpdate.Parameters.Add(new System.Data.SqlClient.SqlParameter("@KnownParameter", System.Data.SqlDbType.Int));

    However, if I use the below code from the SyncAdapter with a NewParameter:

    CommandForUpdate.CommandText = SelectQuery;
    CommandForUpdate.Parameters.Add(new System.Data.SqlClient.SqlParameter("@KnownParameter", System.Data.SqlDbType.Int));
    CommandForUpdate.Parameters.Add(new System.Data.SqlClient.SqlParameter("@NewParameter", 1));

    I get this error:

    "Cannot set session parameters in DbServerSyncProvider. Cannot obtain the value for command parameter '@NewParameter' on command 'SelectIncrementalInsertsCommand'.  Some session parameters must be bound to SyncAdapter and DbServerSyncProvider commands. Please see the Sync Framework online documentation for more information on working with session variables and the parameters required for each command."

     

    I agree that it does not make any sense why I would not be allowed to specify my own SQL Parameters, but that appears to be the case.  Is there a work around?

    Friday, June 3, 2011 2:12 PM
  •  

    is it possible you mixed the update command the insert command here ?

    CommandForUpdate.CommandText = SelectQuery;
    CommandForUpdate.Parameters.Add(new System.Data.SqlClient.SqlParameter("@KnownParameter", System.Data.SqlDbType.Int));
    CommandForUpdate.Parameters.Add(new System.Data.SqlClient.SqlParameter("@NewParameter", 1));

    I get this error:

    "Cannot set session parameters in DbServerSyncProvider. Cannot obtain the value for command parameter'@NewParameter' on command 'SelectIncrementalInsertsCommand'

     

    you can refer to this bol section http://msdn.microsoft.com/en-us/library/bb902811(v=sql.100).aspx on how to use the session variables for dbserversyncprovider.

     

    thanks

    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, June 3, 2011 2:40 PM
  • The link you sent for using session variables explains how to reference the session variables as established by the sync framework in my queries.  I am trying to specify my own SQL parameter with a value that I have set, which is not managed by the sync framework.

    I am intentionally using SelectIncrementalInsertsCommand, which is the command that the sync framework uses to select data from my source database.  The SelectIncrementalUpdatesCommand is always null in my case.

    Friday, June 3, 2011 2:58 PM
  • yes, the link I shared is the sync session variables, but it contains all the code for modifying the commands with application's own logic for sync with different parameters.

    I was just wandering why the changes were made on the updae command but the exception was thrown on the Insert command.

    can you share out the commandtext and the parameter lists for the INSERT command ?

    thanks

    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, June 3, 2011 7:54 PM
  • You mention that the link you shared contains code for modifying the command with different parameters.  If that is the case, can you please detail specifically how to do that here in the forum, because I have failed to find that information.  The link shows how to change the command text, which I can already do successfully, as well as use session variables, which I also do not have a problem with.  I need to specify a new variable, that is not declared in the session, and be able to specify the value of that variable from code.

    Friday, June 3, 2011 8:06 PM
  • can you post your select query here?
    Saturday, June 4, 2011 3:01 AM
  • Besides the select query, can you also share with us on how the "@NewParameter" ( which is not part of the user defined sync parameters ) was used ? any reason this was specifed as a parameter but not servers as a syncParameter ? in talking with a few folks in the team, we do have a few restrictions on the sqlcommand and its parameters. once understanding your scenario and goal better, maybe we can find a workaround and a better solution on setting the value of the parameter.

    thanks

    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Sunday, June 5, 2011 7:53 PM
  • There is nothing special about my select query.  It is something along the lines of

    select * from mytable m where m.CreatedDate >= @FirstAllowableDate

    In this example I want to use @FirstAllowableDate as a SQL parameter that I have set to Now minus 30 days.  I have managed to get this to work by replacing @FirstAllowableDate with "dateadd(dd, -30, getutcdate())", but there are other scenarios where I really want to use SQL parameters as set in the server code.

    Currently, my client side code passes a primary key of the user record to the server, which I can use as a SQL parameter on my join clauses to ensure that only the data for the provided user is retrieved in the sync operation.  However, this is sort of a security hole, as the client could provide any user key, and my server side code will not be able to control it.  I would like, instead, for my client to provide a username and password.  The server side would verify that this information is correct and would assign the proper user primary key to the SQL parameter entirely from the server side code, preventing the client code from specifying the query parameters.

    Monday, June 6, 2011 12:25 PM
  • Hi Erick,

    may i know which specific Sync Fx method or event are you trying to inject the parameter on the server side?

    Tuesday, June 7, 2011 12:52 AM
  • I figure it makes the most sense to do this from my SyncAdapter.OnInitialized method, which is where I am altering the select query, but after that did not work I tried jamming this code into any place I could, but they all result in the same error.
    Tuesday, June 7, 2011 12:54 PM