How can I store and retrieve an image to a MySQL database using VB.NET 2005?
+3
A:
- Read it in to a string variable.
- Store it in a BLOB field.
- Don't store image data in the database, it is better practice just store a unique filename that points to the actual image file, that will help reduce DB bloat and save you from having to fetch huge records.
karim79
2009-08-10 12:30:35
+1
A:
Use the BLOB field type and write to it as usual. Remember to store the image type as well, it's necessary to have when you want to show your images.
Björn
2009-08-10 12:31:12
+2
A:
It depends on the image sizes
If they are large (more than a meg or so) and you have a lot of them or an open-ended amount of them -- then it might be better to store on the filesystem and just store the location in the database.
Pros:
- Better DB growth characteristics
- Easier to do incremental back-ups
- Easier to have tiered storage (Fast expensive storage for heavily used images, slow storage for not so often used ones)
- Possible to have random access to part of the file (don't need to read the whole thing)
Cons:
- Hard to do transactions
- Separate place that needs to be managed
- Separate access controls that need to be managed
- blobs are easier to program
If the images are in the few k range, and you have a limited number of them then just use a MemoryStream (save into it as with any stream) and transfer the bytes to a blob field.
SQL Server gives you the best of both worlds with their filesystem field type (externalizes the blob into a file, but otherwise is treated just like a blob)
It's also nice to store extra information about the image in other fields -- so that you don't have to read the image blob in order to do some things with it. For example:
- The size
- The type
- If multipage (like TIFF), the number of pages
I wrote about some of the considerations of images in a DB on my blog:
Lou Franco
2009-08-10 12:41:57