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;
}
}

}
}


 



 

Wednesday, November 11, 2009

Two great tutorials

Recently, 2 tutorials involving SQL Compact have been published.

The first article describes how to use WPF data binding with SQL Compact, and also how to use ADO.NET Sync Services with SQL Compact, Visual Studio 2008 SP1 and SQL Server 2008.

The second article describes how to retrieve the identity from new rows from a SQL Compact database, a problem often encountered when working with SQL Compact and DataSets.