Storing Images in DB - Networked Desktop Applications_问答_开发者

Related: Storing Images in DB - Yea or Nay?

After reading the above question, it seems the preferred method for image storage with databases is to store only the filepath within the database. However, most of these answers seem to focus on web servers.

In my case, I'm developing a desktop application that will be used across multiple computers within an intranet. A dedicated server will host the database, containing information related to performing tests on various equipment.

Images need to be stored on the server in some way. Would storing the images in the database be the correct approach in this case, or even the only approach?

Pros:

Backup is limited to only the database.
No need to open up the server's file system to the network.
Single protocol for server information access.
Protected file access. (User can't go in and delete all the images)

Cons

Perfo开发者_StackOverflowrmance issues in future if there's too many images.

Edit: As stated in the tags, the application is being written in C#/.NET. If writing the images to the file system is an option in this case, I could use some help understanding how this is done.

Edit 2: As elaborated some in the comments below, for now I'm assuming a MySQL database, although the FileStream capabilities of SQL Server 2008 could potentially change that.

Also in my case, images will be added often, and can be considered read-only after this point since they should never be changed, and will just be read out when needed. Images will likely be small (~70k each), and I'm also considering some other binary format storage on the server, files which are ~20k each which I can likely apply the same approach for storing and retrieving.

I'd suggest keeping those files on disk in the file system, rather than in the database. File system for files, databases for relational data, etc.

Deliver by Web Service

Consider delivering those images to your desktop app by hosting a web service/app on that DB machine. That app's job it is to serve only images. Setup a web server on that machine with an ASP.NET application. Have an .ashx handle requests and stream the binary image. Something like this:

http://myserver/myapp/GetImage.ashx?CustomerID=123&ImageID=456

Security

If intranet security is an issue, this would be the point where you could ensure that the user is authenticated and authorized for read access to the image. Audit trails could be implemented here as well.

File System Security

Regarding security on those images, consider that NTFS gives you a lot of measures to ensure that only those who are authorized can read/delete/put files as required. The task then would be to define those roles and implement Windows security groups.

Future Needs

This approach allows you to securely consume those images from anywhere on the intranet. Perhaps this app would be migrated to a web application at some point? Perhaps a feature request comes from the customer where a web solution is appropriate?

This might sound like overkill rather than reading a blob from the database, but it's great from a security perspective. Consider your customers' and patients' expectations on privacy and security.

<%@ WebHandler Language="C#" Class="Handler" %>

public class Handler : IHttpHandler {

public void ProcessRequest (HttpContext context) 
{
    //go to the DB and get the path for this ID.
    string filePath = GetImagePath(context.Request.QueryString["ImageID"]);

    //now you have the path on disk; read the file
    byte[] imgBytes=GetBytesFromDisk(filePath);

    // send back as byte[]
    context.Response.BinaryWrite(imgBytes);
}

I think the answer is that there is no right answer. As with most things in programming (and life), It DEPENDS.

Here are some Pros and Cons of storing in DB:

PROS

Easy backup, management and one stop shop for data in your application
Less dependencies in your app and fewer moving parts. KISS Principle
Works fine on small files under 1GB.
Hey its a DB, so saves can be done inside transactions and rolled back if there are network problems
Sharepoint and TFS store everything in the DB and work just fine. even the big boys do it
Security can be easily controlled by the app and not involve file/folder permissions

Cons

Eats up db space
Potentially effect performance if not done right
Not such a great idea if always storing large files (>1GB) unless using Filestream in SQL Server 2k8
Requires you to implement a decent caching strategy (although you would probably want this anyways)
File system feels more natural than DB and easier for manually replacing/viewing files.

I guess when it comes to your situation, I would lean towards the simplicity of storing in the DB.

From an architecture perspective, you'll get the best performance by splitting the solution into two pieces: a database server, and an image server.

You would do this both in order to keep row sizes small, and also to separate your transactional environment from content. Relational databases in the vein of SQL Server and mysql will support big BLOBs but aren't optimized for them.

Most people equate "image server" to "web server" because they work on web applications and therefore have a de facto image repository (a directory on a local disk). However, this does not have to be the case. Images can be served from any location over any protocol.

You mentioned a C#/.NET platform and an intranet. Can we assume a Windows environment, possibly Active Directory?

If so, a plain vanilla file server could be your image server. Set up a file share, set read/create (but not modify/delete) permissions on it for all users of this app, store the UNC path somewhere in the database (so you don't have to redeploy the app if you decide to relocate it), and have your client application generate a unique, relative path using something reliable like a Guid.

It's not as elegant as a web service (which is my preferred approach), nor quite as maintenance-free as the pure-database approach, but my impression of this topic is that you're on a tight budget with a short delivery deadline, and a Windows or NFS file server is cheaper, easier, and faster to set up and maintain (including backups) than a full-fledged web server, so it might be just what you're looking for here.

Most businesses already have a file server, so usually this won't require any new infrastructure whatsoever. But even if you don't, I've seen file servers run off old reconditioned workstations - it's not fancy, but in a low-traffic environment it gets the job done.

If you choose this approach, I would suggest some kind of directory structure on the file share to simplify backups, archiving, etc. For example:

\\ImageServer\MyAppRepository\yyyy-mm\{image-file-name-or-guid}.{ext}.

Hope that helps.

How many images are we talking? Are they unique/updated frequently? If not can you package the images with the client that you are going distribute to multiple computers?

Personally, I would avoid storing images in the database, and instead as you said store the file paths.

If you have read through all of the other similar questions (This, this, and this) but are still asking if this is a good idea, then maybe your problem is different enough that this would be a good idea.

My company developed a Windows forms c# application that stores images in a database and it worked out pretty well. We have been actively using it since 2003 and have about 150 gigs of data in the system.

First, let me say that this is NOT the optimal performance architecture. We have had some problems with keeping the database statistics up to date and keeping the indexes tuned correctly. We basically have to re-index the system monthly. You need to be aware that the built-in optimization system of most RDBMS servers is not set up for large collections of binary objects.

The reason we chose to put the images in the database is because of database level replication. Our system is spread across seven offices in five states and I needed to sync the data to each site. So, I pinned up a VPN between each site and our corporate office and set up SQL merge replication on the database. In this way, I can sync the data and images at the same time with only one channel open between offices. So, I would say that images in the database is not the optimal solution in most cases but it worked out for our requirements.

I don't think it matters where the images are stored. Pick the simplest approach that will work. But you should have an architecture where you can change the approach if it proves to be the wrong one.

To accomplish this, I would put the data and the image storage both behind a web services interface. Pick a technology - doesn't matter. All access to the data (and images) would be the same way - through the web service.

By doing this, you have decoupled where the data is stored from the desktop application. The desktop app doesn't care. All it knows is that the server at a certain address can get it the data.

Then store the data and the images wherever you want. Choose the simplest thing for you. If you end up having issues, then (and only then) should you add additional complexity in order to solve the problem. The good news is that the additional complexity and work shouldn't affect the desktop applications at all. You can make the changes on the server without having to deploy a new version of the desktop applications.

If you're looking for alternatives, one of my favorites is a ten-line HTTP POST file upload handler (PHP, .NET, Java, etc.) + one webserver. When the script validates max file size, and possibly extracts the width & height, it inserts a row into the database. Retrieval need not go through the script. Standard file hosting will work. This would require you to open port 80. You needn't complicate this with SOAP or anything. A regular upload handler would do the job.

Then there's WebDAV, along the same lines. Of course, with this method, you'd have to monitor the filesystem and adjust the database accordingly. You could use a polling service or hook into file system events. Actually, you could also inject an ISAPI filter or Apache handler to perform the database updates.

You could use FTP. Add an extension to ProFTPd that will update the database and keep everything in sync.

Lots of ways to avoid putting image data into tables.

If you opt for the database solution, just be sure to segment your BLOBs into separate tables. Separate table spaces / devices / partitions, if you can. Or, use Oracle and ignore everything I've said.

Use Amazon S3 storage for your images

Just store the GUID or other file name in the DB

Amazon is simple , fast, cheap. secure etc etc

It scales fine, and optionally provides CDN like edge services directly from S3

Storing images in the DB always seems to turn into a nightmare over time

It seems to me that what you want to do something like what Infovark do.

They use Firebird for this and I'll give you a link on Firebird and storing image

you should try MS SQl 2008, it comes with a Type: FileStream, which automatically store blob in file system.