0

My problem: I need to update the database in my program. The database I'm updating from is stored on a FTP server.

What I know: I already know how to download the file from the FTP server. I'm doing that like this:

string locFileName = Path.GetTempPath() + Path.GetFileNameWithoutExtension(Path.GetTempFileName()) + ".db3";
string ftpFileName = host + @"database/db_SuperiorOrderForm.db3";

using (WebClient request = new WebClient())
{
    request.Credentials = new NetworkCredential(user, pass);
    byte[] fileData = request.DownloadData(ftpFileName);

    using (FileStream file = File.Create(locFileName))
    {
        file.Write(fileData, 0, fileData.Length);
    }
}

To do the actual updating I was thinking I would wipe the local database, load the downloaded database into a List (like this question) and then load the new data back into the local database

However, I got the impression from the comment by @Dan Tao under the previous link and this post that using a List or Array would be bad. Could someone explain why? Also, what would be a better way to do what I'm asking?

I'm still fairly new to c#, so I'm trying to figure out all the best practices, but I want to do everything properly. If there's anything unclear in this question, please let me know before down-voting. Thanks in advance!

Community
  • 1
  • 1
gnarlybracket
  • 1,691
  • 4
  • 18
  • 37
  • 1
    You are downloading the complete database, edit the data and upload the complete database back to the ftp-server? – Tomtom Jan 15 '14 at 12:57
  • I have a local (on the client computer) database with the current inventory. If there is an update to the database, it will publish to an FTP server. I want to somehow update the local copy to the current version on the FTP server. Right now, I am downloading the FTP copy, and replacing the local data with the data from the downloaded copy. – gnarlybracket Jan 15 '14 at 13:02
  • 1
    So if you're wiping your local database when you get the file from the ftp, where would you get your new data from ? – Petko Petkov Jan 15 '14 at 13:31
  • Sounds like a bad design to me. Why can't you edit your database with a database connection, etc.? – Patrick Hofman Jan 15 '14 at 13:40
  • @PetkoPetkov I'm using the data from the downloaded file to repopulate the local database. – gnarlybracket Jan 15 '14 at 13:52
  • @PatrickHofman It's an inventory database with 10,000+ rows. It will take too much coding to go through every row and compare the values and update if necessary. I thought it would be simpler to just delete the old copy and insert the new data. – gnarlybracket Jan 15 '14 at 13:56
  • That sounds weird to me... Can you explain why you do it this way? Normally you connect to a Database in the network via connectionstring. And you have (in simple cases) only one instance where all the clients connect to and update the data. Is the reason that your clients are not in the same network (over the internet?) – Dannydust Jan 15 '14 at 13:58
  • @gnarlybracket I can't really understand what excatly you're trying to acomplish. But more on the specific question. There's nothing wrong in Using List<> per se as long as the items are properly structured objects and not just strings with comma separated values for example. I need a bit more perspective to your problem to be able give a better recommendation. – Petko Petkov Jan 15 '14 at 13:59
  • @Dannydust Can you connect directly to a FTP database via connection string? Cause if you can, then I would definitely do it this way. – gnarlybracket Jan 15 '14 at 14:01
  • This must consume a lot of bandwith, huh? Is there any room for a design improvement? Since you need to keep a local copy of the data (for offline using purposes, I guess), wouldn't be better if you had an online database and you have, for example, a web service to keep all synced? – Rodrigo Vedovato Jan 15 '14 at 14:02
  • @RodrigoVedovato You are correct, I need a local database for offline purposes. However, the web service idea sounds better. How would I implement it? Could you point me in the right direction? Like I said, I'm fairly new, so any improvement ideas are welcome. – gnarlybracket Jan 15 '14 at 14:06
  • @gnarlybracket No, but the main question (at least to me is): Why connect via ftp instead of using a connection string? If the reason is that your clients are not in the same network, then i would consider using a cloud database solution for this (maybe Sql Azure). Because I think you will get many problems with this approach (keeping all in sync without loss of changes/network traffic,...) – Dannydust Jan 15 '14 at 14:07
  • If you consider using a cloud Database. Then you can use the solution explained in the following post to sync your databases: http://stackoverflow.com/questions/14032175/entire-local-database-sync-with-sql-azure – Dannydust Jan 15 '14 at 14:13
  • Btw. I think the problem in the other post was not about using List. IMO it was more about using Arraylist (which is untyped) and using strings instead of the real datatypes. – Dannydust Jan 15 '14 at 14:17

1 Answers1

1

Downloading the entire database sounds like a pretty bad design idea for this solution. Since all you need to do is keep the data synced between client and server and you have internet access, you could create a webservice that does the sync process for you.

The way the WS will assure the content is updated depends on you business model (ie. if only one client will be using the system at a time, you can assume that the database with the latest update timestamp is the correct one, but if more than one client is using the application at the same time, you can do some hash-based validation)

Rodrigo Vedovato
  • 1,008
  • 6
  • 11