How to map a Folder to a Table in SQL Server 2008?

I am using SQL Server 2008.

I have a table (TBL_FILE) that stores user uploaded files in binary column. However users do not want to open our system to access the file. They want to have a folder (network drive, Web folder, local d开发者_开发百科rive, local folder... are accepted) that maps to the table (TBL_FILE). So they can directly open the file directly in File Explorer.

The key point is they want to open files directly in File Explorer.

Is it possible to do that? And what kind of program I need to write to do that? And how to do security?

Thanks!

Alex

Have you considered writing an application that would prompt for a login, then present a list of files to the user in a friendly user interface? You could drop a shortcut to that application in the folder they want these files to live in.

If you must have shortcuts directly from the filesystem into your binary data fields, then you are going to have to be a little bit hacky. Depending on how often the files are updated, you can try one of these options:

1 - Write an application that will run as a Windows Service or as a scheduled job. Periodically check for changed binary data, and save it to disk. Disadvantage: the file system will only be updated at intervals, so database changes will not be immediately available.

2 - Write a trigger on the table that saves the binary file to disk. Fire the trigger whenever the row changes- preferably by monitoring a 'last modified time' or similar field, rather than checking the binary value directly. The trigger could fire a CLR stored procedure, or you can do it directly from T-SQL using the method described here. Disadvantage: You have a potentially time-consuming trigger on the table.

In either case, security becomes the problem of the Windows filesystem. Just grant access to the folder to whomever should see the files.

Good luck!

after searching in google, I finally find a solution of this problem.

We could create a logical drive with .NET technology or other third party libraries. One of the libraries is Doken http://dokan-dev.net/en/.

Doken is able to let us to create a drive in computer and do the logic ourselves. It seems that it is able to map a folder to a table in Database. But I haven't tried it yet.

Thanks!

Alex