Best way to transfer whole SQL table data to end user's C# app

[Mark B]

We've got a table with around 50,000 rows. Periodically we want our Outlook
Add-in (C#) app to automatically download it as XML for reference look-up
use.

Is the best way to use write a webservice for this (transferring in XML
format) or is there a better way to transfer such a large amount of data?

 

[Eric Isaacs]

The best way is going to determine a lot on the business side of this
problem. What type of data is it. How often is it being downloaded?
How often could it be downloaded? What updates the source of this
data in SQL Server? Could the data be access directly from SQL
without XML?

Why are you thinking of using a webservice? The webservice would be a
great interface if SQL isn't accessible directly by providing an easy
to use interface that could be used across an intranet or the Internet
to access the data. But if the user's machine is on the same network
as the SQL Server, your app could easily just read it directly from
SQL Server even in XML format. Either way, it's still likely
transferring the same amount of XML, so speed probably won't be too
different whether you're using XML from a webservice or XML directly
from SQL Server.

Again, there's always more than one way to solve these types of
problems, but the best solution usually depends more on business
requirements than technology. We can tell you that what you are
asking is possible, but whether it's the best solution is questionable
given the limited background you provided.

What are the technical and political limitations, if any? What are
the business requirements beyond being an XML feed? Is that even
required?

I hope that helps.

-Eric Isaacs

 

[Mark B]

Thanks Eric.

The table stores all text strings that the app uses, e.g. "Welcome to the
app", "Error: You need to use a valid username" etc. It's in many different
languages which accounts for the volume of rows.

We use a website to allow translators to add translated text after we do the
'en-US' values and maybe once a month the app could be refreshed with these
new values.

The process will be automatic in the background. The users will have
internet access only -- no LAN or WAN.

The main thing I was thinking was, what happens if it takes an hour and
someone shuts down their computer or a connection is lost before it's all
transferred. I thought ASP.NET might have another method which recovers
files from broken connections. I wasn't sure if ASP.NET using a webservice
with XML had this functionality built-in.

 

[Philipp Post]

Mark,

The main thing I was thinking was, what happens if it takes an hour and someone shuts down their computer or a connection is lost before it's all transferred. <

What about taking a SELECT COUNT(*) ... on that table prior the
download starts and saving this up together with a "DownloadStatus" =
"Started" in a Settings file in the client app. Then prior the
"DownloadStatus" is set to "Completed" the app. should check the
number of entries in the XML file. Also a half-transferred XML would
most likely not validate if you run a check against the associated
definition file. - If there is a built-in method in .NET to re-start
incomplete downloads I am not sure about, but I doubt it is.

brgds

Philipp Post