Store an image in a SQL Server CE database

Question

Does any one know of an example on how to store an image in a SQL Server CE database?

What data type should the column be? (I am guessing binary.)

I use Linq-To-Datasets. Is it possible using that to put the image into the database and pull it out again later?

Thanks for any advice.

---

Here is how I did it:

MemoryStream stream = new MemoryStream();
myBitmapImage.Save(stream, ImageFormat.Png);
myInsertLinqToDataSetRow.IMAGE_COLUMN = stream.ToArray();

To load it back out again I did this:

MemoryStream stream = new MemoryStream(myLinqToDataSetRow.IMAGE_COLUMN);
myBitmapImage.SignatureImage = new Bitmap(stream);

I found a page on MSDN that said that the Image column type is going away and that you should use varbinary(MAX). Max is not supported on SQL Server CE so I did varbinary(8000).

LATER NOTE: while varbinary(max) is not supported on SQL Server CE. Varbinary(8000) is not big enough for many images. I did end up using the Image type even though it is planned to be deprecated. Once ms offers a resonable alternitive on the mobile platform I will consider switching.

Kant

Answer 1

Here is an MSDN article explaining how:

http://support.microsoft.com/kb/318639

Unfortunately, the example is in VB, but I am sure that you can get the idea of how to do it.

I would say the binary datatype would be fine for storing the images.

Nikolaos

Answer 2

Why aren't you using the Image type? Here is a description of types, supported by the sql server ce.

Image - Variable-length binary data with a maximum length of 2^30 – 1 (1,073,741,823) bytes.

Oared

Because they are being deprecated. The help page says: ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. (Taken from: msdn.microsoft.com/en-us/library/ms187993.aspx) – Kant

Though I did end up going with the image type for SQL Server CE. (Cause varbinary(8000) was not big enough.) – Kant

I too ran into the max limit of varbinary (it's a bit larger than 9000 in SQLCE 4.0). Image worked way better, even for non-image byte[] binary blobs. – Exodontist

@Vaccano, it's not correct that the data type image is being deprecated. The link in your comment is to a list of data types in SQL Server 2012, which is not the compact edition. But if you look at the list of data types in SQL CE 4.0, there isn't any notice about image or the other data types being deprecated. See also this thread where SQL Server MVP ErikEJ says imageis not deprecated in CE. – Maynor

Answer 3

This is not a direct answer to your question, but I've had good success storing the image's filepath (example: C:\images\image1.png) as a string value in the database instead of the raw image.

One advantage to this is that it keeps the database size smaller.

Another is that multiple tables can point to the images without storing multiple copies of the image.

Whittling

reasonable idea if I were storing reference data. However, this is for screen shots of the current window. I need them in the database to keep them organized. – Kant

+1: I don't really like doing this and the biggest problem (the multiple copies of the image) can be resolved simply by creating a table Images with an id and the image. Although with the future deprecation of image type and the limitations on varbinary size I think it is actually the best option. How sad MS.. – Nobody

Something I figured out in one of my applications to stop duplication of the same image is to have image information on a separate table and then reference everything to that one table. I have the hash of the image, the binary content, and an id to go with it. Once the image is in the database, instead of duplicating binary content, I just add another reference to that image. – Equal