Retrieve Images from sql server database

Question

Hi All,

i am storing images to the database. How to retrieve all the images from the database.

Eg: select images from imagetable

Problem:

Data Logic:

           while (dr.Read())
          {
             ///Check for null
              if (!dr.IsDBNull(0))
             {
                try
                {
                    ///Converting the image into bitmap
                    byte[] photo = (byte[])dr[0];
                    ms = new MemoryStream(photo);
                    Bitmap bm = new Bitmap(ms);
                    bmp[i] = bm;

                    ms.Close();

                }
                catch (Exception ex)
                {

                }
            }

ASpx.CS page:

Bitmap[] bm= photos.GetImage();
    for (int i = 0; i < bm.Length; i++)
    {
  MemoryStream ms = new MemoryStream();

        **bm[i].Save(ms, ImageFormat.Jpeg);**(Error : A generic error occurred in GDI+.)

htmlCode.Append("<li><img ImageUrl=\\\"");
htmlCode.Append(**ms.GetBuffer()**);
htmlCode.Append("\" alt=\"\" width=\"100\" height=\"100\"></li>");
}

Image not getting displayed

Geetha

Answer 1

For SQL Server 2008 onwards, FILESTREAM is almost certainly the way to go.

Please see: SQL Server 2005 - How do I convert image data type to character format

Answer 2

You need to get the binary data from the DB, and then stream the binary data to the browser as image.

Answer 3

this is an example from Sql Server

        connection.Open();
        SqlCommand command1 = new SqlCommand("select imgfile from myimages where imgname=@param", connection);
        SqlParameter myparam = command1.Parameters.Add("@param", SqlDbType.NVarChar, 30);
        myparam.Value = txtimgname.Text;
        byte[] img = (byte[])command1.ExecuteScalar();
        MemoryStream str = new MemoryStream();
        str.Write(img, 0, img.Length);
        Bitmap bit = new Bitmap(str);
        connection.Close();

look here http://www.akadia.com/services/dotnet_read_write_blob.html

Answer 4

You are setting the Url of the image to be the byte stream - you need to save the image to the hard drive and provide the location.

A much better way would be to set the image url to be a resource handler with parameters that could then retrieve the image from the database and return it as a stream to the browser.