即使图像未更改，如何更新数据库记录 [英] How to update database records even if the image is not changed
问题描述
我使用 localDB 作为我的数据库.
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
这就是我通过 C# 添加记录的方式
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 }");
}
这就是我将图像转换为 byte[] 数组的方式:
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
}
我在将图像转换为字节数组时尝试了上面的代码，当我INSERT它到数据库时成功，但是当我UPDATE一条记录(例如改变员工姓名")而不更改图像，它将显示错误:GDI+ 发生一般错误."
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+."
这与检索图像有关吗?
我没有在 datagridview 上显示我的图像二进制数据，但我像这样显示/检索我的图像:
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;
}
}
推荐答案
我已经通过修改这个方法解决了这个问题:https:///stackoverflow.com/a/14866755/11565087
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);
}
}
这篇关于即使图像未更改，如何更新数据库记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
