Saving an Image file to sql Server and converting byte array into image

Question

I am storing images in a database and would like to convert them from byte array to image. I have no problem converting an object to byte array but I get an error of "Parameter is not valid" when trying to convert from byte array to image. The object I am passing to my method is from a dataset row.

Stored procedure

USE [----------------]
GO
/****** Object:  StoredProcedure [dbo].[usp_imageloader_add_test]    Script Date: 01/16/2012    09:19:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER   procedure [dbo].[usp_imageloader_add_test]
@p_Image Image
as 

INSERT into Test_Images VALUES(@p_Image)

Upload File control /convert Image file to byte array and save data to database

 protected void btnUpload_Click(object sender, EventArgs e)
    {
        if (ctrlUpload.PostedFile != null)
        {
            if (ctrlUpload.PostedFile.ContentLength > 0)
            {
                // Get Posted File
                HttpPostedFile objHttpPostedFile = ctrlUpload.PostedFile;

                // Find its length and convert it to byte array
                int ContentLength = objHttpPostedFile.ContentLength;

                // Create Byte Array
                byte[] bytImg = new byte[ContentLength];

                // Read Uploaded file in Byte Array
                objHttpPostedFile.InputStream.Read(bytImg, 0, ContentLength);

                using (SqlConnection dbConnection = new SqlConnection(app_settings.sql_conn_string_db))
                {
                    try
                    {
                        string sql = "usp_imageloader_add_test";
                        SqlCommand cmd = new SqlCommand(sql, dbConnection);
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@p_Image", bytImg).SqlDbType = SqlDbType.Binary;
                        cmd.Connection.Open();
                        cmd.ExecuteNonQuery();
                        cmd.Connection.Close();
                    }


                    catch (Exception ex)
                    {
                        ex.Message.ToString();
                    }
                }
            }
        }
    }

Table method which calls objToImg method

protected void Page_Load(object sender, EventArgs e)
    {
        generateTable(false);
    }


private Table generateTable(bool flag)
    {
        Table tb = BuildList(GetData(), flag);
        if (imgloadercms != null)
        {

            PlaceHolder ph = new PlaceHolder();
            StringBuilder sb = new StringBuilder();
            ph.Controls.Add(new LiteralControl(sb.ToString()));
        }
        imgloadercms.Controls.Add(tb);
        return tb;
    }


protected Table BuildList(DataTable tb, bool flag)
    {   
        Table tblImageLibrary = new Table();
        tblImageLibrary.BorderStyle = BorderStyle.Solid;
        tblImageLibrary.BorderWidth = Unit.Pixel(8);

        if (tb.Rows.Count > 0)
        {
            try
            {
                if (!flag)
                {

                    tblImageLibrary.BorderColor = Color.Black;
                    tblImageLibrary.BorderWidth = Unit.Pixel(1);
                    TableRow tr = new TableRow();  // Table row for header of table
                    tr.BackColor = Color.LightBlue;

                    TableCell c1 = new TableCell();
                    TableCell c2 = new TableCell();


                    c1.Controls.Add(new LiteralControl("Image Id"));
                    tr.Cells.Add(c1);
                    c2.Controls.Add(new LiteralControl("Image"));
                    tr.Cells.Add(c2);

                    tblImageLibrary.Rows.Add(tr);
                }

                int i = 0;

                foreach (DataRow r in tb.Rows) // Create new row foreach row in table
                {
                    TableRow tr = new TableRow();
                    if (i % 2 == 0)
                    {
                        tr.BackColor = Color.LightYellow;
                    }
                    // Build cells
                    TableCell c1 = new TableCell();
                    TableCell c2 = new TableCell();
                    c2.Width = 300;

                    c1.Controls.Add(new LiteralControl(r["Image_Id"].ToString()));
                    tr.Cells.Add(c1);

                    // Call method to serialize obj to byte array
                    //System.Drawing.Image dbImg = 
                    ObjToImg(r["Image_File"]);

               }
            catch (Exception ex)
            {
                ex.ToString();
            }
            if (!flag)
            {

            }
        }
        return tblImageLibrary;
    }

Return Image

  private System.Drawing.Image ObjToImg(object obj)
    {
        //byte[] byteArray = null;

        if (obj == null)
            return null;
        else
        {

            BinaryFormatter bf = new BinaryFormatter();
            using (MemoryStream ms = new MemoryStream())
            {
                bf.Serialize(ms, obj); //now in Memory Stream
                ms.ToArray(); // Array object
                ms.Seek(0, SeekOrigin.Begin);

                //return (System.Drawing.Image)bf.Deserialize(ms);

                System.Drawing.Image myImage = (System.Drawing.Image)bf.Deserialize(ms);

                return myImage;
            }

Whenever I try to add the memory stream object to the image object constructor I get the error message of "Parameter is not valid". Maybe I made a mistake when insert the byte array into the database because I have looked at other code and it doesn't make sense how it's not working.

Solution 1

Try to deserialize the object first from byte array with your BinaryFormatter!

Try to use following two methods:

private System.Drawing.Image ObjToImg(byte[] obj)
    {
        if (obj == null)
            return null;
        else
        {
            BinaryFormatter bf = new BinaryFormatter();
            using(MemoryStream ms = new MemoryStream(obj))
            {
              return (System.Drawing.Image)bf.Deserialize(ms);
            }
        }
    }
private byte[] ImgToObj(System.Drawing.Image obj)
    {
        if (obj == null)
            return null;
        else
        {
            BinaryFormatter bf = new BinaryFormatter();
            using(MemoryStream ms = new MemoryStream())
            {
              bf.Serialize(ms, obj);
              return ms.ToArray();
            }
        }
    }

Solution 2

I just recently had to do the exact same thing in VB.NET. Here is my code, run through Telerik's Code Converter. It was quite tricky to get working, and is still honestly a pain.

To upload an image:

private bool uploadImage(ref Bitmap p)
{
    SqlConnection con = new SqlConnection();
    con.ConnectionString = Configuration.ConfigurationManager.ConnectionStrings("ConnStringHere").ConnectionString;
    SqlCommand cmd = new SqlCommand();
    cmd.CommandText = "INSERT INTO Table_Name (File2) VALUES (@File2)"; //I named the column File2 simply because "File" seemed to be a keyword in SQLServer
    cmd.CommandType = CommandType.Text;
    cmd.Connection = con;

    SqlParameter File1 = new SqlParameter("@File2", SqlDbType.Image);
    MemoryStream ms = new MemoryStream();

    using (Bitmap tempImage = new Bitmap(p))
    {
        tempImage.Save(ms, p.RawFormat);
    }

    byte[] data = ms.GetBuffer();
    if (!isValidImage(data)) //optional, will include code if requested.
    {
        return false;
    }
    File1.Value = data;
    cmd.Parameters.Add(File1);

    con.Open();
    int result = cmd.ExecuteNonQuery();
    if (result > 0)
    {
        // SUCCESS!
        con.Close();
        return true;
    }
    else
    {
        //failure
        con.Close();
        return false;
    }

}

To retrieve an image:

private Bitmap retrieveBitmap()
    {
        Image image1 = null
        if (dt1.Rows.Count > 0)
        {
            byte[] imageData1 = null;
            if (dt1[0].Count > 0)
            {
                if (!Information.IsDBNull(dt1.CopyToDataTable()[0].Item("File2")))
                {
                    imageData1 = (byte[])dt1.CopyToDataTable()[0].Item("File2");
                }
            }
            if ((imageData1 != null))
            {
                if (isValidImage(imageData1))
                {
                    using (MemoryStream ms = new MemoryStream(imageData1, 0, imageData1.Length))
                    {
                        ms.Write(imageData1, 0, imageData1.Length);
                        image1 = Image.FromStream(ms, true);
                    }
                    return image1;
                }
                else
                {
                    // "Invalid image on server";
                    return null;
                }
            }
        }
    }

My code may need small formatting changes, please edit whatever has invalid syntax (my C# is a little rusty, and my code was run through a converter).

Comments

[nick gowdy]

The SqlDbType was originally set to Image but I that didn't make a difference.

[nick gowdy]

I tried that and it threw an exception of "trying to deserialize an empty stream".

[Oleg Dok]

You definitely pave a problem with operation with your formatter's output - check it.

[nick gowdy]

This may sound like a stupid question but how would you suggest I do this?

[Oleg Dok]

Added possible decision to the answer

[nick gowdy]

I tried your code and I am getting an invalid cast exception. I have updated my code.

[Oleg Dok]

Again. You cannot get an Image witn Serialuze method. Play with it. And IMAGE datatype in sql server has no any connection to saving pictures.

[nick gowdy]

Hey I ended up casting the object from the datarow to a byte array and adding that to the memorystream. I also used this code in an ashx file where I can use a query string to recieve the image. Thanks for your help anyway. I didn't use binary formatter in the end because it wasn't required for what I was trying to do.