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.

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.

Answer 2

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.

Answer 3

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.