2

Problem:

I have multiple instances of the same C# application running on different PCs (OS: Windows XP, Windows 7) in the same LAN. I have to share some configuration data among them. Each process must have read-write access to the data. My employer insists on storing these shared data in a file, which is in a shared directory on one of these PCs.

Possible solutions:

  1. Exclusive file opening: The data is stored in a TXT file (serialization to and from a binary file is also an option). Each process uses File.Open with FileShare.None when trying to open the file. Getting an IOException means that the file is already in use, so the process has to wait and try again later.
  2. SQL Server CE embedded DB: The data is stored in an SDF file. The engine can handle at most 256 simultaneous connections (v3.5 SP2), which is more than enough.
  3. SQLite embedded DB: The data is stored in an SQLite DB file. The documentation says SQLite works, but may be unreliable when used on a network share.
  4. Other?

What is the preferred way to do this?

Community
  • 1
  • 1
kol
  • 27,881
  • 12
  • 83
  • 120

3 Answers3

1

You don't really describe the type of data you're working with so I'd say the answer varies.

Using a proper DBMS for this would be best if the data you are working with could generally be considered record/field oriented (and under rare circumstance even if it isn't). In this case I would recommend MSSQL CE since its runtime will mitigate multi-user issues for you.

SQLite was generally considered a single user/application database (at least back when I used it in C) though things could have changed in the last 5 years. If you're using .NET 4 then there are few free adapters available for use from what I've found unless you're comfortable with a mixed framework application.

I would only monitor the file locking manually if you're in a situation where the data is pretty flat by design (like a log file), though if it was log like data I would probably look into how some of the open source log libraries do it. You basically said you have control over the data structure so I'd suggest redesigning the data to be more normalized/rigid to avoid using this solution.

M.Babcock
  • 18,753
  • 6
  • 54
  • 84
  • +1 for mentioning log libraries, I will check them out. The shared data set is very simple, it's like a single DB table (records with the same fields). – kol Jan 22 '12 at 01:04
  • 1
    As an alternative it wouldn't be difficult to write a web service wrapper over the SQLite API which could enforce the concurrency for you. Log libraries are generally write only (which admittedly is the hard part of what you're trying to accomplish) so if you can master that it should be simple enough to share reads. – M.Babcock Jan 22 '12 at 01:07
  • The web service is good idea, but I have to use the simplest possible approach. My employer prefers copy-paste deployment of small executables, and doesn't want to use installers, web services etc. – kol Jan 22 '12 at 01:10
  • Do you have a source for your claim that MSSQL CE handles locking from multiple computers? – Ben Voigt Jan 22 '12 at 01:18
  • @kol: Copy-paste deployment doesn't preclude opening a listening socket. But how will you find the master configuration file over the network, with copy-paste deployment? A text file in the app directory, that's copied along with the executable? – Ben Voigt Jan 22 '12 at 01:20
  • @BenVoigt - Not directly though I've used it lightly in a similar environment. Admittedly in a low write situation, but it seemed to work. I only specified it as my suggested option because it was one of the options given (besides "Others"). If I had a choice though I would choose a full blown MSSQL distro (or even one of the free alternatives). – M.Babcock Jan 22 '12 at 01:22
  • @CharlesLambert He is not a software developer. Anyway this is a vworker.com project, so I will have another employer after I finished the job :) – kol Jan 22 '12 at 01:24
  • @BenVoigt I have simple test programs for all three options. I think I should stress-test each method, and after the test, check whether the shared data looks as it should. – kol Jan 22 '12 at 01:30
  • @BenVoigt Microsoft claims that SSCE supports DB file storage on a network share: http://download.microsoft.com/download/A/4/7/A47B7B0E-976D-4F49-B15D-F02ADE638EBE/Compact_Express_Comparison.doc – kol Jan 22 '12 at 02:06
  • @kol: That doesn't mean used concurrently from multiple computers. It means you could have the data file stored in your home directory (on the network server, for automated backup) where only one client is using it at a time. Views of a file are coherent between processes on a single computer, even if the file is remote. But views from different remote computers are not coherent. Extra work is needed to implement remote locking. – Ben Voigt Jan 22 '12 at 02:13
  • Please note that I'm not saying that MSSQLCE doesn't support that scenario. Just that the document you provided is unconvincing. – Ben Voigt Jan 22 '12 at 02:17
  • @BenVoigt - "So you got lucky and didn't have two computers writing the file at the exact same instant." doesn't say that? – M.Babcock Jan 22 '12 at 02:20
1

Don't know if is the best way, but I've done this in C ages ago, it was working well for me.

Each process will read and create a personal copy of the file and then work on that.

At a fixed moment (upon process termination or triggered via some UI or whatever you feel like) each process will send its copy of the file to a master process in charge of rebuilding the original file in the shared directory and signaling the other process that they need to reload.

Each process reloads the file (containing infos coming from all the other processes).

Of course this solution requires that the file writing process has knowledge on how to rebuild the file and how to resolve conflicts (but this depends on data format)

BigMike
  • 6,683
  • 1
  • 23
  • 24
  • +1 for the idea of personal copies. Unfortunately I cannot use a master process. It's a requirement that all processes must have the same rights and they cannot communicate with each other over the network. – kol Jan 22 '12 at 01:01
  • Even putting data in a db won't solve completely the resource access races (but it will ease the management). How often does the update of the file occurrs? – BigMike Jan 22 '12 at 01:14
  • Funny, but I'm getting used to the idea of having a master process :) – kol Jan 22 '12 at 01:28
  • @BigMike: It depends on the database engine. [Jet, for example, explicitly supports this scenario.](http://stackoverflow.com/questions/694921/ms-access-mdb-concurrency) – Ben Voigt Jan 22 '12 at 02:01
  • @BenVoigt: interesting read. Comes to mind that also good old btree used to allow concurrency and record level locking. Using a db (even a file based db) gives an ease to locking management, but the problem is still there. JET allows dirty reads, but is up to the access/vb application make sure that data is consistent between all the consumers. Even with Oracle or DB2 (a little more complex db engine than JET), concurrency is a serious issue and has to be taken into account. – BigMike Jan 23 '12 at 00:06
  • @BigMike: At least an application can take care of record-level concurrency. Inconsistencies in indexes and allocation would likely take place if the database engine isn't designed for simultaneous remote access, and an application hasn't a prayer of overcoming that kind of corruption. – Ben Voigt Jan 23 '12 at 05:13
  • @BenVoigt: yes, of course, I was just referring to data access concurrency. Media access (datafiles or whatever is the underlying storage) is a completely different matter. – BigMike Jan 23 '12 at 08:13
1

Create a web service and make your programs pull the configuration from there. You can control file locking from inside the web service and not have to deal with that at the program level. This also affords you the abstraction that if you decide to change how the settings are stored (e.g. move them from a file to a database) you can do this without having to make any changes to your program.

Charles Lambert
  • 5,042
  • 26
  • 47