Storing files in a better way

[Jewtus]

I am dealing with an issue that I'm trying to think of a clever workaround for. The network drives that I have for deployment are painfully slow and when I put EXE files on them, it takes forever to load. That being said, I use the same network directory for handling "attachments" in various parts of my tool. I crypt the files and store the files extension as well as the crypted password in a database and put the crypted files into a subdirectory.

I'm not going to include my code here because really what I'm trying to figure out is if there is a way to deconstruct any file (mainly pdfs, msg files, and word docs) and store it as a string in my database or something of that nature. Sometimes the files are hundreds of MB and I don't know if I can even store something that big in a DB (I know you can with BULK statements, but that needs a path relative to the server which I cannot do). I want to get rid of the network directory dependency to improve performance.

[jchd]

All SQL engines I know off support BLOBS (binary data) columns. If your DB is local you could just encrypt the whole DB and store everthing you want in BLOBs. For instance you can use SQLite for that. The system.data.sqlite.dll is 100% compatible with the legacy dll but adds crypt support so you don't even have to launch a third-party program to decrypt your precious data. This works at DB page level, so that the whole DB file is equivalent to random binary to anyone who doesn't have the passphrase. Works like a charm but don't forget the key!

[Jewtus]

Ya, but to do blob storage, don't you need to use BULK commands with a filepath that is relative for the server?

 

Because the people who would be attaching files are likely doing it from their local machines, the server wouldn't have permissions to their desktop for example (or the documents dir). I wanted to use BULK, but unfortunately, I cannot push files to the server to do a BULK statement and the server cannot path to end user machines.

 

If I were to use SQLite for blob storage, I would still need a place to store the SQLite DB that all end users would be able to access (thats why I'm looking to put it directly on the SQL Server)

 

I was even looking into fileread to do an insert statement with the read string, but after 30 minutes of reading a file (only about 80 MB) I gave up on that method. The network drives are slow but not THAT slow.

[jchd]

If the DB won't be local to user, then forget abour bare SQLite. Yet other engines can be used like PostGres, MySQL, ...

[Jewtus]

MySQL did it! Thanks for the suggestion!