?
Solved

Web Services and Remoting, Datasets and TableAdapters

Posted on 2006-07-17
2
Medium Priority
?
1,115 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 2000 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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

The object model of .Net can be overwhelming at times – so overwhelming that quite trivial tasks often take hours of research. In this case, the task at hand was to populate the datagrid from SQL Server database in Visual Studio 2008 Windows applica…
This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

809 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