We are converting an existing VB/SOAP 3/SQL 2000 app to VB.NET/Web Services/Remoting/SQL 2000.
Currently stored procedures return data using "FOR XML RAW" and we use the DOM (MSXML 4.0) to populate fields and cached data on the client. All parameters are sent to SQL 2000 as an XML string and stored procedures process this XML string using OPENXML.
This application needs to be a Windows Forms app as the functionality can not be provided via Web Forms. The app runs across LAN/WAN with some users accessing data stored in a central database situated 4000km away. The main reason for using SQL 2000 XML in the current version was to try and reduce network traffic and passing XML via SOAP was easier than trying to pass ADO Recordsets.
From a scalability and performance perspective, should we:
1. Keep using FOR XML/OPENXML via ExecuteXMLReader and Command objects and pass data as xml between the client/web service/remoting object/database, or
2. Should we fully parameterised stored procedures and stick to using Datasets/Datareaders and command objects and pass data as datasets between the client/web service/remoting object/database.
We want to move away from using the DOM and purely use datasets so even if we keep using option 1, we will need to convert the XML to a dataset at some stage.
I realise that this question is hard to answer without a full understanding of the architecture of the app, so any information would be appreciated.