locked
How to pass a table-valued parameter as SyncParameter? RRS feed

  • Question

  • God day.
     In a current project we use Syncronization Framework 1.0  with  SQL2008 on the server and SqlCe on the client. The clients are running windows mobile.
    We would like to filter some tables on the server depending on a collection of values in the clients database.

    The only approach to do this that I've found for this is to add a table-valued SyncParameter (SqlDbType.Structured).

    Currently I'm only trying to get it to work with a manually created datatable. 

    In the the sync agent I've added this code.
    DataTable dt = new DataTable();
    DataRow row;
    dt.Columns.Add(new DataColumn("stat", System.Type.GetType("System.Int32")));
    row = dt.NewRow();
    row["stat"] = 1;
    dt.Rows.Add(row);
    row = dt.NewRow();
    row["stat"] = 3;
    dt.Rows.Add(row);
    dt.TableName = "Values";
    
    
    Configuration.SyncParameters.Add(new SyncParameter("@goodstatus", dt));

    And in the servers syncadapter I've added:
    SqlParameter tv = new SqlParameter("@goodstatus", SqlDbType.Structured);
    tv.TypeName = "Values";

    When this is executed I get the following runtime exception:

    The type System.Data.DataTable was not expected. Use the XmlInclude or SoapInclude attribute to specify types that are not known statically.
    What can I do to pass an arbitrary numbers of values from the client into one sqlparameter on the server?


    ps. The stacktrace on the client looks like this:
       at System.Xml.Serialization.XmlSerializationReflector.FindType(Type type, Boolean encoded, Boolean genericNullableArg, String defaultNamespace)
       at System.Xml.Serialization.XmlSerializationReflector.FindType(Type type, Boolean encoded, String defaultNamespace)
       at System.Xml.Serialization.XmlSerializationWriter.getTypeForSerialization(Accessor& accessor, Object value, Boolean& requiresTypeAttr)
       at System.Xml.Serialization.XmlSerializationWriter.SerializeAsElement(Accessor accessor, Object value, Object fetcherTarget, SerializationQueue delayedWork, Boolean recursiveCall, Boolean schemaReqTypeAttr, XmlSerializerNamespaces serializerNs)
       at System.Xml.Serialization.XmlSerializationWriter.serializeMembers(SerializationQueue queue, SerializationQueue delayedWork, Boolean recursiveCall, Boolean schemaReqTypeAttr, XmlSerializerNamespaces serializeNs)
       at System.Xml.Serialization.XmlSerializationWriter.SerializeComplexAsElement(LogicalType serializeAs, Accessor accessor, Object value, SerializationQueue delayedWork, Boolean recursiveCall, Boolean requiresTypeAttr, XmlSerializerNamespaces serializerNs)
       at System.Xml.Serialization.XmlSerializationWriter.SerializeAsElement(Accessor accessor, Object value, Object fetcherTarget, SerializationQueue delayedWork, Boolean recursiveCall, Boolean schemaReqTypeAttr, XmlSerializerNamespaces serializerNs)
       at System.Xml.Serialization.XmlSerializationWriter.LiteralElementSerializer.serialize(Object elt, XmlSerializerNamespaces serializerNs)
       at System.Xml.Serialization.XmlSerializationWriter.for_each(Object value, LogicalType type, ElementSerializer serializer, XmlSerializerNamespaces serializerNs)
       at System.Xml.Serialization.XmlSerializationWriter.serializeLiteralArrayLike(Accessor accessor, LogicalType type, Object value, Object fetcherTarget, Boolean writeXsiType, XmlSerializerNamespaces serializerNs)
       at System.Xml.Serialization.XmlSerializationWriter.SerializeArrayLikeAsElement(LogicalType serializeAs, Accessor accessor, Object value, Object fetcherTarget, SerializationQueue delayedWork, Boolean recursiveCall, Boolean requiresTypeAttr, XmlSerializerNamespaces serializerNs)
       at System.Xml.Serialization.XmlSerializationWriter.SerializeAsElement(Accessor accessor, Object value, Object fetcherTarget, SerializationQueue delayedWork, Boolean recursiveCall, Boolean schemaReqTypeAttr, XmlSerializerNamespaces serializerNs)
       at System.Xml.Serialization.XmlSerializationWriter.serializeMembers(SerializationQueue queue, SerializationQueue delayedWork, Boolean recursiveCall, Boolean schemaReqTypeAttr, XmlSerializerNamespaces serializeNs)
       at System.Xml.Serialization.XmlSerializationWriter.SerializeComplexAsElement(LogicalType serializeAs, Accessor accessor, Object value, SerializationQueue delayedWork, Boolean recursiveCall, Boolean requiresTypeAttr, XmlSerializerNamespaces serializerNs)
       at System.Xml.Serialization.XmlSerializationWriter.SerializeAsElement(Accessor accessor, Object value, Object fetcherTarget, SerializationQueue delayedWork, Boolean recursiveCall, Boolean schemaReqTypeAttr, XmlSerializerNamespaces serializerNs)
       at System.Xml.Serialization.XmlSerializationWriter.serializeMembers(SerializationQueue queue, SerializationQueue delayedWork, Boolean recursiveCall, Boolean schemaReqTypeAttr, XmlSerializerNamespaces serializeNs)
       at System.Xml.Serialization.XmlSerializationWriter.SerializeComplexAsElement(LogicalType serializeAs, Accessor accessor, Object value, SerializationQueue delayedWork, Boolean recursiveCall, Boolean requiresTypeAttr, XmlSerializerNamespaces serializerNs)
       at System.Xml.Serialization.XmlSerializationWriter.SerializeAsElement(Accessor accessor, Object value, Object fetcherTarget, SerializationQueue delayedWork, Boolean recursiveCall, Boolean schemaReqTypeAttr, XmlSerializerNamespaces serializerNs)
       at System.Xml.Serialization.XmlSerializationWriter.SerializeAsElement(Accessor accessor, Object value, XmlSerializerNamespaces ns)
       at System.Xml.Serialization.XmlSerializer.Serialize(XmlWriter xmlWriter, Object o, XmlSerializerNamespaces namespaces, String encodingStyle)
       at System.Xml.Serialization.XmlSerializer.Serialize(XmlWriter xmlWriter, Object o, XmlSerializerNamespaces namespaces)
       at System.Xml.Serialization.XmlSerializer.Serialize(XmlWriter xmlWriter, Object o)
       at Microsoft.Tools.ServiceModel.CFClientBase`1.CFContractSerializer.WriteObject(XmlDictionaryWriter writer, Object graph)
       at System.ServiceModel.Channels.XmlObjectSerializerBodyWriter.OnWriteBodyContents(XmlDictionaryWriter writer)
       at System.ServiceModel.Channels.BodyWriter.WriteBodyContents(XmlDictionaryWriter writer)
       at System.ServiceModel.Channels.BodyWriterMessage.OnWriteBodyContents(XmlDictionaryWriter writer)
       at System.ServiceModel.Channels.Message.OnWriteMessage(XmlDictionaryWriter writer)
       at System.ServiceModel.Channels.Message.WriteMessage(XmlDictionaryWriter writer)
       at System.ServiceModel.Channels.BufferedMessageWriter.WriteMessage(Message message, BufferManager bufferManager, Int32 initialOffset, Int32 maxSizeQuota)
       at System.ServiceModel.Channels.TextMessageEncoderFactory.TextMessageEncoder.WriteMessage(Message message, Int32 maxMessageSize, BufferManager bufferManager, Int32 messageOffset)
       at Microsoft.Samples.Indigo.GzipEncoder.CompressionMessageEncoder.WriteMessage(Message message, Int32 maxMessageSize, BufferManager bufferManager, Int32 messageOffset)
       at System.ServiceModel.Channels.MessageEncoder.WriteMessage(Message message, Int32 maxMessageSize, BufferManager bufferManager)
       at System.ServiceModel.Channels.HttpOutput.SerializeBufferedMessage(Message message)
       at System.ServiceModel.Channels.HttpOutput.Send(TimeSpan timeout)
       at System.ServiceModel.Channels.HttpChannelFactory.HttpRequestChannel.HttpChannelRequest.SendRequest(Message message, TimeSpan timeout)
       at System.ServiceModel.Channels.RequestChannel.Request(Message message, TimeSpan timeout)
       at System.ServiceModel.Channels.RequestChannel.Request(Message message)
       at Microsoft.Tools.ServiceModel.CFClientBase`1.getReply(Message msg)
       at Microsoft.Tools.ServiceModel.CFClientBase`1.Invoke[TREQUEST,TRESPONSE](CFInvokeInfo info, GetSchemaRequest request)
       at ServiceClient.GetSchema(GetSchemaRequest request)
       at ServiceClient.GetSchema(String[] tableNames, SyncSession syncSession)
       at System.Reflection.RuntimeMethodInfo.InternalInvoke(RuntimeMethodInfo rtmi, Object obj, BindingFlags invokeAttr, Binder binder, Object parameters, CultureInfo culture, Boolean isBinderDefault, Assembly caller, Boolean verifyAccess, StackCrawlMark& stackMark)
       at System.Reflection.RuntimeMethodInfo.InternalInvoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean verifyAccess, StackCrawlMark& stackMark)
       at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
       at System.Reflection.MethodBase.Invoke(Object obj, Object[] parameters)
       at Microsoft.Synchronization.Data.ServerSyncProviderProxy.GetSchema(Collection`1 tableNames, SyncSession syncSession)
       at Microsoft.Synchronization.SyncAgent.InitClientSchema()
       at Microsoft.Synchronization.SyncAgent.Synchronize()
       at Jetas5TestClient.Jetas5SyncAgent.Sync()
       at Jetas5TestClient.DatabasForm.SyncNow()
       at Jetas5TestClient.DatabasForm.button1_Click(Object sender, EventArgs e)
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.ButtonBase.WnProc(WM wm, Int32 wParam, Int32 lParam)
       at System.Windows.Forms.Control._InternalWnProc(WM wm, Int32 wParam, Int32 lParam)
       at Microsoft.AGL.Forms.EVL.EnterMainLoop(IntPtr hwnMain)
       at System.Windows.Forms.Application.Run(Form fm)
       at Jetas5TestClient.Program.Main()
    


     

    • Moved by Max Wang_1983 Thursday, April 21, 2011 10:24 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Thursday, November 26, 2009 9:11 AM

Answers

  • Unfortunately table values parameter is not supported. You would need to pass in your filter value one-by-one.

    BTW, what is the scenario you are trying to solve that only a table values parameter would solve and not a simple parameter?
    This posting is provided AS IS with no warranties, and confers no rights
    Saturday, December 5, 2009 2:03 AM

All replies

  • Unfortunately table values parameter is not supported. You would need to pass in your filter value one-by-one.

    BTW, what is the scenario you are trying to solve that only a table values parameter would solve and not a simple parameter?
    This posting is provided AS IS with no warranties, and confers no rights
    Saturday, December 5, 2009 2:03 AM
  • I have several tables that I want to sync. One main table which contains something work orders. The workorders that should be downloaded to the client, via a WCF service, is determined by the values on several columns in the main table.
    The problem is that even if the column have changed to a state where it should be downloaded, it isn't necessarily that it hasn't already been downloaded. The main problem here is to decide what should be downloaded from the other tables, which are depending on what rows from the main table that are being inserted to the client.

    I.e. if a row already exists on the client, you don't want to download all background information to the client again, even if you need to fetch the row from the main table again.
    Therefor we want to attach which rows that exists on the client (as the rows GUIDs).


    The way that we currently have "solved" this is to extend the WCF interface to also include a GUID[], which is added by the proxy on the client side.
    Monday, December 7, 2009 10:35 AM
  • So what you are saying is the client already contains the rows but for some reason column values in the main table change that may cause the row to be re-downloaded to the client and you want to avoid that. I would then ask to revisit the table structures and the filtering you have on your tables and see if there is anything you can change rather than trying a complex way of subsetting the rows like you are doing currently.
    This posting is provided AS IS with no warranties, and confers no rights
    Tuesday, December 8, 2009 6:47 AM