currently I'm working on a project with the following setting:
- There is a proprietary application which performs transactions on a Microsoft Access database (which is actually stored as an .mdb file)
- The API of the application doesn't have enough functionality for the requirements we're facing
- Our application has direct access to the database
We're designing a C# application which has to react to events which are not triggered through the API, so the only way I can think of is to somehow observe the database directly and react accordingly to custom events.
One approach would probably be to query the DB time triggered and react, but this possibly leads to performance issues when the DB is large. I would rather appreciate an approach, where the DB itself notifies our application if specified events occur.
EDIT: The proprietary application is Enterprise Architect modeling tool, so the expected number of concurrent users will definitely not exceed 10 users and the database size will not be larger than 50 MB. Would it probably be an acceptable solution to outsource the polling activity in a separate thread and notify the rest of the application by events?
Concerning the number of users and database size, which load can the MS Access database approximately handle?
Is there any well-known pattern/approach for this kind of problem available and if you don't know any, how would you face this problem?
Best Answer
The most common solution for this kind of problem is: try to design your C# application in a way it does not have to react to changes within your database. Even when using a "real" client/ server database where you have things like triggers available, most client/server applications avoid the need for a "push model" where the server has to send a "trigger event" to the client. Indeed, there are only few databases which provide such a mechanism. At least client applications typically don't rely on getting all events, since the network between client and server may fail sometimes.
Of course, I don't know your requirements, and how much scope is left for dicussing them, and some requirements can be really hard to be implemented without an appropriate event mechanics.
If you really need this, I guess polling is the only option you have.
Technically: probably yes, but you have to measure the performance for your specific case. Crucial factors are how much data has to be scanned in each cycle, and which latency is acceptable (seconds? ok. miliseconds or fewer? may become hard).