I'm working on a Custom Document Management System that uses Filestream / FileTable as Document Storage.
The Directory structure is as follows: APPNameDir --> Year --> Month --> Files
+ APPNameDir
+ 2015
+ 12
File 1.pdf
File 2.pdf
+ 2016
+ 01
File 3.pdf
File 4.pdf
+ 02
File 5.pdf
File 6.pdf
What I want to do is to call from C# code a Stored Procedure and pass a folder tree (for example (BooksApp/2016/03) and the file_stream data.
Then, in the stored procedure I would like to check if the folder tree already exists, otherwise create the folders that do not exist.
I have seen this post so I am able to create one folder at a time but not several folders at once.
I guess that I can check if 2016 not exists, then create it. Then, check if month 03 not exists, then create it and so forth but I'm worried about performance when millions of documents are in the DataBase because I want to insert the document from the web application in less than 2 seconds (is a restriction). I did some performance benchamark some days ago and inserting a document from the web application took between 100 and 300 milliseconds when 100K documents in the database. If I have to query the FileTable for each directory and then create them if they don't exist I'm afraid of the performance to decrease when such amount of documents in the DB.
Have you experienced similar need in your projects? Any idea?
I just created a CLR integration project for this. https://github.com/rhyous/Db.FileTableFramework
It has various functions or procedures that you would want: CreateFile, CreateDirectory, DirectoryExists. And on GitHub it of course can be modified and improved by anyone.