How to update database records even if the image is not changed

Question

I am using localDB as my database.

I have an employee table, and the employee images are stored in another table

This is my stored procedure for create and update:

    IF NOT EXISTS (SELECT * 
                   FROM   dbo.Employee 
                   WHERE  employee_id=@employee_id)   
    BEGIN TRY
        BEGIN TRAN
            INSERT INTO dbo.Employee 
            (employee_name,
             city,
             department,
             gender
            )  
            OUTPUT inserted.employee_id 
            INTO   @employee_id_PK (employee_id) 
            VALUES 
            (@employee_name,
             @city,
             @department,
             @gender
            )  
            
            SELECT @FK_Employee_Image_To_Employee_Table = employee_id 
            FROM   @employee_id_PK 
            INSERT INTO dbo.Employee_Image
            (user_image,
             file_extension,
             employee_id
            )
            VALUES
            (@user_image,
             @file_extension,
             @FK_Employee_Image_To_Employee_Table
            )
        COMMIT TRAN 
    END TRY

    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRAN --RollBack in case of Error
        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH
            

    ELSE  
    BEGIN TRY
        BEGIN TRAN
            UPDATE e
            SET    e.employee_name=@employee_name,
                  e.city=@city,
                  e.department=@department,  
                  e.gender=@gender
            FROM   dbo.Employee e, dbo.Employee_Health_Insurance h
            WHERE  e.employee_id=@employee_id AND h.employee_id=@employee_id
                                                                         
            UPDATE i
            SET    i.user_image=@user_image,
                  i.file_extension=@file_extension
            FROM   dbo.Employee_Image i, dbo.Employee e
            WHERE  i.employee_id=@employee_id AND e.employee_id=@employee_id
        COMMIT TRAN
    END TRY

    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRAN --RollBack in case of Error
        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH

This is how I add my records through C#

using (SqlConnection con = new SqlConnection(connectionStringConfig))
using (SqlCommand sqlCmd = new SqlCommand("spCreateOrUpdateData", con))
{
try
{
    con.Open();
    sqlCmd.CommandType = CommandType.StoredProcedure;

    //Employee Record
    sqlCmd.Parameters.Add("@employee_id", SqlDbType.NVarChar).Value = EmployeeId;
    sqlCmd.Parameters.Add("@employee_name", SqlDbType.NVarChar, 250).Value = txtEmpName.Text;
    sqlCmd.Parameters.Add("@city", SqlDbType.NVarChar, 50).Value = txtEmpCity.Text;
    sqlCmd.Parameters.Add("@department", SqlDbType.NVarChar, 50).Value = txtEmpDept.Text;
    sqlCmd.Parameters.Add("@gender", SqlDbType.NVarChar, 6).Value = cboEmpGender.Text;

    //Employee Image 
    sqlCmd.Parameters.Add("@user_image", SqlDbType.VarBinary, 8000).Value = ConvertImageToByteArray(pictureBox1.Image); <-----------------error here according to StackTrace
    sqlCmd.Parameters.Add("@file_extension", SqlDbType.VarChar, 12).Value = lblFileExtension.Text;

    int numRes = sqlCmd.ExecuteNonQuery();
    string ActionType = (btnSave.Text == "Save") ? "Saved" : "Updated";
    if (numRes > 0)
    {
        MessageBox.Show($"{ txtEmpName.Text }'s record is { ActionType } successfully !!!");
        RefreshData();
    }
    else
        MessageBox.Show($"{txtEmpName.Text} Already Exist !!!");
}
catch (Exception ex)
{
    MessageBox.Show($"Cannot INSERT or UPDATE data! \nError: { ex.Message }");
}

This is how I convert my image to byte[] array:

byte[] ConvertImageToByteArray(Image img)
{
    //with memory stream:
    /*[1]
    using (MemoryStream ms = new MemoryStream())
    {
        img.Save(ms, img.RawFormat);<-----------------error here according to StackTrace
        return ms.ToArray();
    }*/

    /*[2]
    using (MemoryStream ms = new MemoryStream())
    {
        img.Save(ms, img.RawFormat);<-----------------error here according to StackTrace
        byte[] arrImage = ms.GetBuffer();
        return arrImage;
    }*/


    // with image converter
    /*ImageConverter converter = new ImageConverter();
    return (byte[])converter.ConvertTo(img, typeof(byte[]));*/ <-------------error here according to StackTrace
}

I have tried the above code when converting image to byte array, it is successful when I INSERT it to database, but when I UPDATE a record (e.g. changed the "Employee's name") without changing the image it will display an error: "A generic error occurred at GDI+."

EDIT:

Does it have something to do with retreiving the image?

I do not diplay my image binary data on my datagridview but I display/retreive my image like this:

private void dgvEmpDetails_CellClick(object sender, DataGridViewCellEventArgs e)
{
    try
    {
        if (e.RowIndex != -1)
        {
            DataGridViewRow row = dgvEmpDetails.Rows[e.RowIndex];
            EmployeeId = row.Cells[0].Value?.ToString();             
            txtEmpName.Text = row.Cells[1].Value?.ToString();
            txtEmpCity.Text = row.Cells[2].Value?.ToString();
            txtEmpDept.Text = row.Cells[3].Value?.ToString();
            cboEmpGender.Text = row.Cells[4].Value?.ToString();

            //Display user image
            using (SqlConnection con = new SqlConnection(connectionStringConfig))
            using (SqlCommand sqlCmd = new SqlCommand("SELECT user_image, file_extension FROM dbo.Employee_Image WHERE employee_id=@employee_id", con))
            {
                con.Open();
                sqlCmd.Parameters.Add("@employee_id", SqlDbType.NVarChar).Value = EmployeeId;

                using (SqlDataReader reader = sqlCmd.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        reader.Read();
                        pictureBox1.Image = ConvertByteArrayToImage((byte[])(reader.GetValue(0))); <------------- displaying the image here
                        lblFileExtension.Text = reader.GetValue(1).ToString();
                    }
                    else
                    {
                        pictureBox1.Image = null;
                    }
                }
            }
            btnSave.Text = "Update";
            btnDelete.Enabled = true;
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show($"Something is wrong with the selected record! \nError: { ex.GetType().FullName }");
    }
}

My method in converting byte array to image:

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

With the generic GDI+ error, it's usually to do with disposing of the stream that the image object was created from. — ProgrammingLlama, Mar 11 '21 at 09:49
Side notes: don't block the thread with long calculations or message boxes while the connection is open. Since you are only inserting one record, there is no need for `OUTPUT`, you can use `SCOPEIDENTITY()` to get the most recent identity value — Charlieface, Mar 11 '21 at 10:08
@Llama but I used a using statement in my ConvertImageToByteArray() method. Or am I doing it wrong though? — paraJdox1, Mar 12 '21 at 08:22
Hey @Charlieface can you elaborate more on what is a "thread"? I've seen it in some stackoverflow questions and answers but I still don't have a good grasp about it. And, also, I have found out (here in stack overflow) that using output is much better. — paraJdox1, Mar 12 '21 at 08:26
I think [this question](https://stackoverflow.com/questions/336387/image-save-throws-a-gdi-exception-because-the-memory-stream-is-closed) might help. — ProgrammingLlama, Mar 12 '21 at 08:43
A thread is an execution context: it holds variables that tell the OS, and ultimately the CPU, what to execute next. A thread can only execute sequentially (barring asynchronous "promises"), as it is a stream of ordered commands to be executed. Here, the connection needs to be closed, but a message box is open and waiting for user input. So the connection is held open. This is not ideal for the server. — Charlieface, Mar 12 '21 at 09:05
`OUTPUT` is not necessarily better, it has a higher overhead than `SCOPE_IDENTITY()` due to the table variable. If you don't need it don't use it. Obviously if you have multiple rows, or you need other columns, then *do* use it, it's more efficient than re-querying the base table again — Charlieface, Mar 12 '21 at 09:06
does it have something to do with retrieving the image? I'll update my question. — paraJdox1, Mar 12 '21 at 09:34

score 0 · Accepted Answer · answered Mar 14 '21 at 08:49

I already solved this problem by modifying this approach: https://stackoverflow.com/a/14866755/11565087

This is my code for converting the image from my pictureBox into byte[]:

public static byte[] ImageToBytes(Image userImage)//Get bytes of the image
{
    using (MemoryStream ms = new MemoryStream())
    using (Bitmap tempImage = new Bitmap(userImage))
    {
        /*copy the object (userImage) into a new object (tempImage), 
            then use that object(tempImage) to "Write" */
        tempImage.Save(ms, userImage.RawFormat);
        return ms.ToArray();
    }
}

This is my code for converting my image's binary data from the database and load it to my pictureBox:

public static Image BytesToImage(byte[] buffer) //Get image from database
{
    using (MemoryStream ms = new MemoryStream(buffer))
    {
        return Image.FromStream(ms);
    }
}

score 0 · Answer 2 · answered Mar 15 '21 at 09:04

This is my approach for getting images from database

   // This method use to update the form.
    private void loadFormWithID(int ID)
    {
        dbServer conn = new dbServer(sysController.getConn);
        DataTable tbl = conn.getQueryList("SELECT * FROM Products WHERE ID = " + ID);
        DataRow row = tbl.Rows[0];      
        // This is how i update the Picture Box
        pictureBoxItem.Image = row["Image"] == DBNull.Value ? pictureBoxItem.InitialImage : ImageController.bytesToImage((byte[])row["Image"]);  
     }

This is my dbserver class which communicates with database.

   public class dbServer
   {
        public string _connectionLink;

        public dbServer(string connectionString)
        {
            _connectionLink = connectionString; 
        }

        public DataTable getQueryList(string sqlQuery)
        {
             DataTable tbl = new DataTable();

           using (SqlConnection conn = new SqlConnection(_connectionLink))
           {
               using (SqlCommand cmd = new SqlCommand(sqlQuery, conn))
                {
                   conn.Open();
                   SqlDataReader reader = cmd.ExecuteReader();
                   tbl.Load(reader);
                }
            }
            return tbl;
        }
   }

I hope this solves the Issue.

How to update database records even if the image is not changed

2 Answers2

Linked