Document Management

Question

I'm writing a simple document management system for my work. I'm only a hobbyist but have been programming for a while now. My problem is this. When I delete a number of documents through my app, I then need to remove the related database records. What's the best way to go about this without ending up with files with no database records, or records pointing to files that do not exist, if there is an exception thrown?

Answer 1

This is a kind of "distributed transaction" system. You probably want to write a consolidation routine and run it once in a while, you will have inconsistencies at some point.

Answer 2

Delete the database records first and audit repository occasionally. By audit the repository I mean look for files that don't have a database record. If you delete the DB record first your code won't be looking for documents that aren't actually there.

How many documents, and what type of documents are you planing to store?

Answer 3

You should use a distributed transaction which will include your database and MS Messaging queue (MSMQ). Check that your database can be enrolled in a distributed transaction with Microsoft Distributed transaction Coordinator(DTC).

Program all file operations with MSMQ.

This link might help you

Answer 4

You said "through my app". Does this mean they can only remove files through the application interface? If so, wrap the deletion up in a custom transaction to both remove the file and remove the db record. If one side fails, roll back the other.

If you want to allow them to remove files from the file directory and then automatically remove the db records, you could do lots of things, including periodic clean-ups and/or a file directory watcher Windows service that monitors the activity in a directory and then kicks off clean-ups.

Answer 5

I have similar functionality in some of my apps.

What I do is delete the file first thru the ap, and then deleted the database record that has a link/pointer to the file. When users request a document(which means the link to the file exists in the database still), the app checks for the file first, and if for some reason it is not there as it expects it to be, it cleans up the database record at that point and notifies the user of the "error" gracefully.

"Orphaned" documents don't happen much anyway, but Using this method ensures that the database is constantly cleaned as a normal part of running the app.

There are lots of other ways of handling this, but this one works for me.

Answer 6

Store the files in the database. Then you can have referential integrity without adding complexity (MSMQ etc) to your application. Yes, this will increase the size of your db. But the files are already on your server, so it's just the same bits in a different place.

Answer 7

Thanks for your responses. I was already thinking of going the BLOB route but wasn't sure if my boss would go for it. He likes a good directory structure, you know? As for distributed transactions, I knew nothing about them but they seem like the most professional way to approach it. But I'm a hobbyist and it all seems too involved so I'm going to win my boss round on the BLOB front. Cheers.