Sunday, November 22, 2009

How to save and retrieve Images using LINQ to SQL with SQL Compact

The code below demonstrates several features of working with System.Drawing.Image object together with LINQ to SQL, which maps a byte[] to the System.Data.Linq.Binary type.

In addition, the code also demonstrates how to convert between byte[] and System.Drawing.Image. Hope you find it useful.

 

The CREATE TABLE statement for the sample database looks like this:

-- Script Date: 22-11-2009 15:03  - Generated by ExportSqlCe version 2.2.0.3
CREATE TABLE [Images] (
  [ImageName] nvarchar(100) NOT NULL
, [Image] image NULL
);
GO
ALTER TABLE [Images] ADD PRIMARY KEY ([ImageName]);
GO
CREATE UNIQUE INDEX [UQ__Images__000000000000000A] ON [Images] ([ImageName] ASC);
GO

- thank to Georgi Yankov for inspring this blog entry!

 

using System;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Windows.Forms;

namespace StoreImagesToSqlCe
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void buttonStoreImageToDb_Click(object sender, EventArgs e)
{
// Open the DataContext
Database1 db = new Database1("Data Source=Database1.sdf");
try
{
// Convert System.Drawing.Image to a byte[]
byte[] file_byte = ImageToByteArray(pictureBox1.Image);
// Create a System.Data.Linq.Binary - this is what an "image" column is mapped to
System.Data.Linq.Binary file_binary = new System.Data.Linq.Binary(file_byte);
Images img = new Images
{
Image = file_binary,
ImageName = "Erik testing "
};
db.Images.InsertOnSubmit(img);
}
finally
{
// Save
db.SubmitChanges();
}
}

private void buttonRetireveImageFromDb_Click(object sender, EventArgs e)
{
// Open the DataContext
Database1 db = new Database1("Data Source=Database1.sdf");

// Get as single image from the database
var img = (from image in db.Images
where image.ImageName == "Erik testing"
select image).Single();
// Convert the byte[] to an System.Drawing.Image
pictureBox1.Image = ByteArrayToImage(img.Image.ToArray());
}

private byte[] ImageToByteArray(System.Drawing.Image imageIn)
{
using (MemoryStream ms = new MemoryStream())
{
imageIn.Save(ms, System.Drawing.Imaging.ImageFormat.Gif);
return ms.ToArray();
}
}

public Image ByteArrayToImage(byte[] byteArrayIn)
{
using (MemoryStream ms = new MemoryStream(byteArrayIn))
{
Image returnImage = Image.FromStream(ms);
return returnImage;
}
}

}
}


 



 

4 comments:

Jewel said...

Hi ,

Tons of Thanks for this Great Tutorial. This is exactly what i was looking for for the past few days. am very very grateful to you...:D

can I have one more help please...?
That is,
I want to create a desktop winform application with the same table (Images) you used in your tutorial here. and then deploy it with a click Once set up project. Now my question is: How to package my database along with the SQL Compact 3.5 as prerequisites ?

I assume the target compters don't have any database (SQL Server Or SQL Compact)Installaed.

Most Importantly, what should be the connection string of my desktop App so that my app can connect to the .sdf database (with the Images table)after successful installation.

All these are simply to ensure that the end users can get all read in one installation of my desktop winform App.

Thanks a lot in advance

ErikEJ said...

Jewel: See my blog posts here: http://erikej.blogspot.dk/2011/02/using-sql-server-compact-40-with.html and here: http://erikej.blogspot.dk/2012/05/private-deployment-of-sql-server.html

Jewel said...

Hi ErikEJ,
Thanks for your example. I got stuck in one phase. My Visual Studio 2010 (Ultimate Version)doesn't support LINQ To SQL with SqlCompact. It doesn't support Entity Framework too.

So, I have just installed VS2010 SP1 along side my Visual Studio 2010 Ultimate. I also installed SqlServerCE 4 and Visual Studio 2010 SP1 Tools for SQL Server Compact 4.0 as well.

But no Results. Still it doesnt support Linq to Sql or Enity framework.

N.B: I can differentiate whether I have opened VS Studio SP1 or Ultimate when I open VS2010. Any Suggestions Regarding these issues , please ?

Thanks for your patience

ErikEJ said...

Jewel: use Help, About, and for further questions, please ask in the MSDN forum