Winforms document manager using file system and SQL database

Question

I am trying to create a document manager for my winforms application. This is not a web interface.

I would like to be able to allow users to “attach” documents to various objects (personnel, companies, work orders, tasks, parties, etc.) in my application.

After much research, I decided to use the file system to store files instead of blob in SQL. I will create a folder to store all the files, but I will store information about the document (file path, loaded, modified, version, etc.) In the parent-child relationship with the entity in the sql database.

I want users to be able to work with documents through the application so that database files and records are not synchronized. Somehow I have to protect the document folder from ordinary users, but at the same time allow the application to work with it. My initial thoughts were to install the application with a single username and password with access to the folder and use impersonation to enter the folder and work with files. From the feedback in a recent thread, I started, now I believe that this was not a good idea, and working with impersonation was a headache.

I also thought about using a web service, but some of our customers just ran the application on laptops without a Windows server. Most of them use a windows server or a citrix / windows server.

What would be the best way to configure this so that only the application processes documents?

Answer 1

I would go with these options in a specific order.

• Create a folder on the server that is not accessible to users. You have a web service running on a server (using IIS or a standalone WCF application) that has a way to upload and download files. Your web service should manage the directory in which the files are stored. The SQL database must have all the necessary metadata to search for documents. Thus, only your application can access these files. Thus, users could see documents only through the application.

• I see that you decided to save the documents in the file system. I wrote a similar system (for example, attachments for customers / orders / sellers / etc ...), except that I store it in SQL Server. It really works very well. I was initially worried that so much data would slow down the database, but it turned out to be wrong. It works great. The only advice I can give if you take this route is to create a separate database for all your investments. What for? Since, if you want to get a copy of the DBMS for local testing, you do not want to copy a 300 GB database, which consists of 1 GB of actual data and 299 GB of attachments.

• You mentioned that some of your users will carry laptops. In this case, they may not connect to the local network. If so, I would consider storing files (and possibly metadata) in the cloud (EC2, Azure, Rackspace, etc.).

Answer 2

I know what you said you read about blobs, but do you know about the FILESTREAM options in SQL Server 2008 and beyond? Basically, instead of saving drops to your database, which is not always a good idea, you can instead save drops to the NTFS file system using transactional NTFS. It seems to me what you are trying to achieve.

All file access protection will be processed through the SQL server (since this will be the only thing needed to access the folder), and you do not need to write your own logic to add and remove files from the file system. To delete a file from the file system, you simply delete the corresponding entry in the sql server table and process its deletion from the file system.

Cm:

http://technet.microsoft.com/en-us/library/bb933993.aspx

Answer 3

Option 1 (Simple): Security through Obscurity

Let everyone read (and write accordingly) access to your document directories. Save the "path" document as the full URI (\\ servername \ dir1 \ dir2 \ dir3 \ file.ext) so that your users can access the files, but they are not immediately available if someone is wandering around their mapped drives.

Option 2 (Harder): File submission with SQL Server

You can use the CLR or SQLDMO function to read a file from disk, present it as a varbinary field, and restore it on the client side. The surface is that your users will see a copy, not the real thing; makes browsing safer by editing and saving more difficult.

Enjoy !; -)