Solved

Web Services and Remoting, Datasets and TableAdapters

Posted on 2006-07-17
2
1,109 Views
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.Connection=MyConnectionString
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
taUsers.Connection=MySoapConnection
else
taUsers.Connection=MyConnectionString
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
taUsers.Connection=’LocalConnectionStringFromRemoteMachinesPerspective’
else
taUsers.Connection=MyConnectionString
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.

TIA

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..
0
Comment
Question by:lojk
2 Comments
 
LVL 5

Accepted Solution

by:
nickhoggard earned 500 total points
ID: 17127982
Hi,

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.

Cheers

Nick
0
 
LVL 9

Author Comment

by:lojk
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. ;-)
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

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:…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

828 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