Monday, March 22, 2010

Read OLE Object type image field in C#.net

When I was doing a C#.net project, I needed to retrieve an image, which is stored in an access database, into my application. I searched the web for almost 3-4 days to find a solution and it appeared that many have the same problem. So I decided to make put this article to give a solution to your burden problem. :)

The problem comes when you are trying to add an image directly in access. What happens there is Access adds some headers to Ole object field,So what you basically have to do is to remove those headers.No problem will come when you try to insert image from your application.

This is my Access database.It is so simple one.




Then I created a windows forms project and added a picture box, text box and a button.



pictureBox1 : name of the picture box, valid: name of the text box and view : name of the button.

When you click the view button it shows the image given in the valid textbox. Following is the C#.net code for that.



private void view_Click(object sender, EventArgs e)
{
OleDbConnection connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\blog\\Test.mdb");
MemoryStream stream = new MemoryStream();
try
{
int id = Int32.Parse(valid.Text);
connection.Open();
String sql = "SELECT picture FROM pic WHERE ID=" + id;
OleDbCommand command = new OleDbCommand(sql, connection);
byte[] pic = (byte[])command.ExecuteScalar();

byte[] img = GetOriginal(pic);
stream.Write(img,0,img.Length);
pictureBox1.Image = Image.FromStream(stream);
}
catch (Exception ex)
{
throw ex;
}


finally
{
stream.Close();
stream.Dispose();
connection.Close();
}
}

Let me explain the code. First I have created a OleDbConnection and given the connection string to the Access database. So you must change this accordingly. Then I have instantiated a memory stream object which is used in later. Then I have get the id from the text box. After that I have opened the Database connection and sql statement also created.Next I have created anOleDbCommand and executed it. Here I have used ExecuteScalar method because it returns the first column of first row of result set.There I have casted it into byte array. GetOrighinal(pic)method simply removes access headers from the byte array and returns original image as an byte array.

private byte[] GetOriginal(byte[] dbbytes)

{

const string BITMAP_ID_BLOCK = "BM";

const string JPG_ID_BLOCK = "\u00FF\u00D8\u00FF";

const string PNG_ID_BLOCK = "\u0089PNG\r\n\u001a\n";

const string GIF_ID_BLOCK = "GIF8";

const string TIFF_ID_BLOCK = "II*\u0000";

byte[] imgbytes;

Encoding e7 = Encoding.UTF7;

String strtemp = e7.GetString(dbbytes);

String strfirsttemp = strtemp.Substring(0, 300);

int iPos = -1;

if (strfirsttemp.IndexOf(BITMAP_ID_BLOCK) != -1)

iPos = strfirsttemp.IndexOf(BITMAP_ID_BLOCK);

else if (strfirsttemp.IndexOf(JPG_ID_BLOCK) != -1)

iPos = strfirsttemp.IndexOf(JPG_ID_BLOCK);

else if (strfirsttemp.IndexOf(PNG_ID_BLOCK) != -1)

iPos = strfirsttemp.IndexOf(PNG_ID_BLOCK);

else if (strfirsttemp.IndexOf(GIF_ID_BLOCK) != -1)

iPos = strfirsttemp.IndexOf(GIF_ID_BLOCK);

else if (strfirsttemp.IndexOf(TIFF_ID_BLOCK) != -1)

iPos = strfirsttemp.IndexOf(TIFF_ID_BLOCK);

else

throw new Exception("Unable to determine header size for the OLE Object");

if (iPos == -1)

throw new Exception("Unable to determine header size for the OLE Object");

//imgbytes=new byte[dbbytes.LongLength-iPos];

MemoryStream stream = new MemoryStream();

stream.Write(dbbytes, iPos, dbbytes.Length - iPos);

imgbytes = stream.ToArray();

stream.Close();

stream.Dispose();

return imgbytes;

}


Then the returned original image byte array is written to a memory stream and picturebox's image property is set as above.

Here is the final result.