Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Web Services and Remoting, Datasets and TableAdapters

Posted on 2006-07-17
Medium Priority
Last Modified: 2012-06-21
Here is the Scenario..

I have Client Application with various forms and datagridviews (yep, VS2005) binding to a set of datasets held in a separate assembly (they did used to be held in the same assembly but i have recently moved them out in anticipation of the eventual web interface that may need to be written), and that DataAssembly.dll pointing to a bunch of SQL Server Tables and Stored Procedures via a Couple of Strongly Typed DataSets.

The client Application still works great after moving all the Data Access to the seperate layer (i had to tweak the system generated TableAdapters to expose their connection property as public rather than friend but little else has changed so far) and has not impacted on performance significantly.

In my code currently I (obviously) do something a bit like this

Dim dsDataSet  as new DataSetAssembly.dsSystem
Dim taUsers as new DataSetAssembly.dsSystemTableAdapters.UsersTableAdapter

taUsers.Fill (dsDataSet.Users)

this works great and so does the corresponding Update command and allows me to bind my databound controls directly to tDS.Users.Username, tDS.Users.Password etc… use the dataview controls to filter data into datagridviews from those Datasets and so on.

During development it became apparent that assuming the data was in one location was not sufficient and really what i need is the ability to 'Switch' the DataSetAssembly ‘mode’ from 'Reading (and writing) Data Directly from a 'Local' SqlSource' (and by that i mean via port 1433 to an internal Intranet Address) to 'Reading (and writing) Data Via a Web Service'. (because sometimes the application will be used at a client site, retrieving data from our web server and sometimes will be used on a laptop accessing a local MSDE copy of the data - I cant install a web service on the latter, and make the whole thing dependant on a web service because any one machine could be any operating system, that might not support IIS to handle the local encapsualtion of the data connection)

What I really want to do is this..

Dim dsDataSet  as new DataSetAssembly.dsSystem
Dim taUsers as new DataSetAssembly.dsSystemTableAdapters.UsersTableAdapter

If RemoteMode then
end if
taUsers.Fill (dsDataSet.Users)

even this would suffice (I can handle changing a bit of code across the app)

Dim dsDataSet  as new DataSetAssembly.dsSystem
Dim taUsers as DataSetAssembly.dsSystemTableAdapters.UsersTableAdapter

If RemoteMode then
taUsers =new RemoteDataSetAssembly.dsSystemTableAdapters.UsersTableAdapter
end if
taUsers.Fill (dsDataSet.Users)

Now I've done plenty of work creating Web Services that return DataSets but the problem comes about because the Client Needs to be able to post data back to the server (in local or remote ‘mode’) and I (surely) don’t want to be sending that big fat dataset (and it will be fat because it can easily include several hundred or even thousand records in some of the tables) back and forwards (although at the minute, because of the way I have used the DataView objects I only retrieve and postback data ‘On Demand’ – i.e when the user hits ‘Load or Save’ although that still doesn’t negate the need to post back all records, or is there a way to create a clone of a dataset with only the changed or deleted records?).

I cant see a simple way to do this without rewriting about 80% of the app. A quick test here reveals that a Connection Object cannot be presented via a web service interface so I guess I want to retrieve and update data via the DataSetAssembly.dll exposed from the remote web server or   the DataSetAssembly.dll on the local machine.

My only other suggestion is to present our SQL Server onto an additional port on the server and this would allow me to completely transparently change the data source as and when required but I have been told by my line manager that this is ‘Highly Unpreffered’ (mostly because the data traveling over that port would then be unsecured/unencrypted). If it can be shown that this is the only way to do it then im sure I can persuade him but if I (we!) can come up with a manageable and secure way to do this via SOAP through the SSL then ‘Golden Boy’ I will be!

There is plenty of chatter online about the difference between web services and remoting. None of which so far has given me a definitive answer.

Suggestions generally and as much discussion as possible would be appreciated.


p.s. if just writing an Encrypted Tunnel Application is the way to go then im open for that too... Although i cannot use VPN, I should be able to open any port that i need and point it straight at the SQL Server..
Question by:lojk
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

nickhoggard earned 2000 total points
ID: 17127982

Firstly, You can get the changed rows in a dataset using the dataSet1.GetChanges.  You could use that to get a subset of your data to submit to the server for processing.

Is your remote application based on windows or a web forms setup?  If it is on windows (and therefore can cache some data locally) you could look at loading your datasets via the default web services that Sql Server 2005 can expose to wrap all its stored procedures.  

That way all the processing still resides on the client pc, just the way it gets its data is different ... in fact you could even have it use these web service wrappers in 'local' mode to make it easier to maintain (everything working the same way).

I'm not entierly sure if I understood what you were asking, but from my understanding of your problem the above might give you a couple of ideas to get started with.



Author Comment

ID: 17129053
OK following a meeting for this the spec has changed significantly and this is not going to happen like this anymore (i.e. instead of everything being strongly typed and 'proper' its gonna go down a webservice and more primitive class based route that is not worth detailing here)

Thanks nick for your .Getchanges comment, that alone is worth the points. ;-)

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question