Home > Windows Phone > Writing an Image to SQL CE / Linq to SQL

Writing an Image to SQL CE / Linq to SQL

Here is a short few steps I used to write an image to a SQL CE database on Windows Phone 7. As you may know Windows Phone 7.5 Mango supports SQL CE which makes it very convenient to store data in a proper relational database on the phone.

Creating the Column

 

There is some excellent help how to create your first SQL CE database which can be found here: http://msdn.microsoft.com/en-us/library/hh286405(v=VS.92).aspx

To create the column add the following code to your [Table] class:

[Column]
        public byte[] ItemImage
        {
            get { return _ItemImage; }
            set
            {
                if (_ItemImage != value)
                {
                    _ItemImage = value;
                    NotifyPropertyChanging(“ItemImage”);
                    NotifyPropertyChanged(“ItemImage”);
                }
            }
        }

Method for Adding the Image to the your Database

 

This method will take the stream you pass from your CameraCaptureTask and convert it to a byte array that can be saved in your database:

private byte[] GetImageBytes(Stream stream)
        {
            using (MemoryStream ms = new MemoryStream())
            {
                WriteableBitmap wb = PictureDecoder.DecodeJpeg(stream, 200, 200);
                wb.SaveJpeg(ms, 200, 200, 0, 30);
                return ms.ToArray();
            }
        }

public void AddNewItem(Stream image, string url)
        {
            //Get Byte array of Image
            byte[] byteArray = GetImageBytes(image);

            //Add Image to Database
            MyDatabaseItem item = new MyDatabaseItem { Count = 1, ItemImageUrl = url, ItemImage = byteArray };
            MyDatabaseItemModel.Add(item);
            MyDatabaseDB.MyDatabaseItems.InsertOnSubmit(item);
            MyDatabaseDB.SubmitChanges();
        }

Showing the Image from your database in XAML

 

You need to make use of a converter that convert the byte array to a BitmapImage, here is the code for the converter you can use:

public class ImageConverter: IValueConverter
    {
        public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
        {
            if (value is byte[])
            {
                MemoryStream memoryStream = new MemoryStream(value as byte[]);
                WriteableBitmap writeBitmap = PictureDecoder.DecodeJpeg(memoryStream, 200, 200);
                return writeBitmap;
            }
            else
                return null;
        }

        public object ConvertBack(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
        {
            throw new NotImplementedException();
        }
    }

And then you will need to call the converter in your Image XAML:

<Image Source="{Binding ItemImage, Converter={StaticResource ImageConverter}}" Stretch="UniformToFill"/>

About these ads
Categories: Windows Phone
  1. November 1, 2011 at 10:07 pm

    Hi,

    nice example. But how to handle more than 8000 bytes? It seems there is a limitation in SQLCE & my picture is “of course” bigger.

    Than in advance.

    R

    • November 1, 2011 at 10:28 pm

      Hi Richard,

      Unfortunately this seems to be the limit, articles I read at the time I implemented this suggested that you make use of Isolated Storage due to the 8000 bytes limit. Let’s hope an update will be made soon to allow larger blob types.

      Thanks,
      Anton

    • socram
      November 3, 2011 at 2:25 pm

      Hi Richard,

      Try [Column(DbType="Image")] on the column that will contain the blob.

      SQL CE has an image type that is variable length varbyte.

      Hope it works for you.

  2. SheikAbdullah
    February 9, 2012 at 1:59 pm

    Hi,

    Can u pls share source for this

    • February 27, 2012 at 8:26 am

      Hi,

      I unfortunately do not have any source code available for this.

  3. edison
    February 26, 2012 at 3:51 pm

    Hi!
    Can you give a sample app w/ source/tutorial for this? I want to know further on how to add images on SQL CE for windows phone…

    • February 27, 2012 at 8:27 am

      Hi,

      I don’t have a sample app at the moment, will see if I can create one with source.

      • edison
        February 27, 2012 at 10:40 am

        great! hope to see a tutorial for saving images to sql ce. We really need it for our app…

  4. Paulo Flores
    February 27, 2012 at 7:14 am

    Nice guide, but what is this:

    MyDatabaseItem item = new MyDatabaseItem { Count = 1, ItemImageUrl = url, ItemImage = byteArray }; ?

    • February 27, 2012 at 8:28 am

      It is just to create the sample Entity that will be added to the database. MyDatabaseItem will be stored in the database, so if you make use of EF this will be your Entity. Trust that make sense.

  5. Paulo Flores
    February 27, 2012 at 10:40 am

    MyDatabaseItem is what object? i tried your code but can’t recognize MyDatabaseItem, sorry i am still new here in wp7 app dev

  6. Paulo Flores
    February 27, 2012 at 10:45 am

    i think i understand it now, MyDatabase Item is your table? Count, url and ItemImage is your columns?

  7. Paulo Flores
    February 28, 2012 at 5:38 am

    Hello sir, I have a question regarding this:

    public class ImageConverter: IValueConverter
    {
    public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
    {
    if (value is byte[])
    {
    BitmapImage image = new BitmapImage();
    WriteableBitmap writeBitmap = new WriteableBitmap(200, 200);
    wb.LoadJpeg(image);
    MemoryStream memoryStream = new MemoryStream(value as byte[]);
    writeBitmap.LoadJpeg(memoryStream);

    image.SetSource(memoryStream);
    return image;
    }
    else
    return null;

    }

    public object ConvertBack(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
    {
    throw new NotImplementedException();
    }
    }

    When I use this code, there is an error on wb.LoadJpeg(image); it says the wb does not exist in the current context. Is it just a typo error? should it be writeBitmap.LoadJpeg(image)? Please help me ;) thanks!

    • Paulo Flores
      February 28, 2012 at 6:01 am

      also wb.LoadJpeg(image) requires a Stream parameter, but in this code, image is a BitmapImage

  8. Randy Lim
    April 24, 2012 at 1:21 pm

    wb.LoadJpeg(image) requires a Stream parameter, but in this code, image is a BitmapImage.
    did u type a typo???

  9. April 24, 2012 at 1:44 pm

    Hi Randy, the code progressed a bit since I published it. Please have a look if it makes more sense now. Thanks

  10. Randy Lim
    April 26, 2012 at 3:57 am

    Hi, thanks for the reply! =)
    but i still got 1 problem that i dont understand, hope you can give me guidances on it. after i save to the db right, when i tried to retrieve it out, i am given a black image instead. i been trying to google it but no result. hope you can help me. thanks =)

  11. April 26, 2012 at 7:17 am

    I had the same problem back when I wrote this. Just make sure that your image you retrieve from the db is valid by saving it to a file and try to open it. It has been quite some time since I wrote this but if I remember correctly it had something to do with the converter not converting the bytes to a BitmapImage. HTH

  12. Randy Lim
    April 27, 2012 at 4:33 am

    what do you mean by saving it to a file? cause i am new to this. do you have a source code that i can reference?? thanks.

  13. Randy Lim
    April 27, 2012 at 9:32 am

    We changed the converter and problem is solved. Thanks a lot!

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: