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

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  [email protected]_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    [email protected]_name,
                  [email protected],
                  [email protected],  
                  [email protected]
            FROM   dbo.Employee e, dbo.Employee_Health_Insurance h
            WHERE  [email protected]_id AND [email protected]_id
                                                                         
            UPDATE i
            SET    [email protected]_image,
                  [email protected]_extension
            FROM   dbo.Employee_Image i, dbo.Employee e
            WHERE  [email protected]_id AND [email protected]_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 [email protected]_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;
    }
}

Answer

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

Leave a Reply

Your email address will not be published. Required fields are marked *