Whats the best way to store/handle file attachments in WPF with a local database?

heres the quick background of the app

Basically..

  1. Multiple users(usually only one) per machine
  2. local sql server database running on each machine holdings its own data
  3. WPF app has ability to export data in the table to zip files

As it sits now, I cannot change the environment its on.

I see two options with dealing with saving/uploading attachments to docs(pdf,doc) currently there are no sizing requirements or required extensions to my knowledge

  1. Saving each file as a varbinary directly into the database and using FK's and a reference table to get those file attachments back in the application for opening

  2. Having the same FK's and reference tables, but I save to file to some folder on the local harddrive(hash + encryption?). Then I rename that file to a generic name and store the name and location of that file in the database.

the first option seems hokey when I dealt with this in web development, but since I had an application server I didn't have to worry about possible user interference.

I like option two, but the files can be moved by the user and throw everything out of whack, and possibly leaves the option for orphaned child records in the database or orphaned files.

Is there a best practice for this?

Answers


If using a supporting version of SQL Server your best bet is to use the FILESTREAM data type as it provides a lot of performance benefits to make it worth while unless all the stored objects will be less than 1MB.

From the article:

If the following conditions are true, you should consider using FILESTREAM:

Objects that are being stored are, on average, larger than 1 MB.

Fast read access is important.

You are developing applications that use a middle tier for application logic.

For smaller objects, storing varbinary(max) BLOBs in the database often provides better streaming performance.

Hope that helps!


Need Your Help

This hash only works for enumeration types

c++ xcode stl clang unordered-map

I'm working on a (very) simple class in C++ that has a unordered_map member:

What's the easiest way to get user Current Location Android

android eclipse google-maps currentlocation

I need to get the user's Current Location on Android, I've seen some methods but they all seemed overkill. I've already implemented Google Maps on the app, is there any easy method to get the user's

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.