VB.NET中如何将PictureBox中的签名保存至SQL数据库

问题描述 #

我正在开发一款访客管理程序，需要将访客信息及其签名保存至SQL数据库。目前PictureBox的签名绘制功能运行正常，但无法将签名图片保存到数据库。我了解到可以将图片转为二进制存储，由于访客不重复，我不想本地存储，希望所有数据都存入数据库，请问有什么解决办法？

签名绘制逻辑参考Stack Overflow相关实现。

解决思路 #

直接将Bitmap对象赋值给数据库字段会失败，需要先把Bitmap转换为字节数组（二进制数据），再存入数据库对应字段，同时确保数据库中存储签名的字段类型为VARBINARY(MAX)（SQL Server）或其他数据库对应的二进制大对象类型。

修改后的完整代码 #

Imports System.Drawing
Imports System.Drawing.Drawing2D
Imports System.Drawing.Imaging
Imports System.IO
Public Class registrovisitas
    Private signatureObject As New Dictionary(Of Integer, List(Of Point))
    Private signaturePen As New Pen(Color.Black, 4)
    Private currentCurvePoints As List(Of Point)
    Private currentCurve As Integer = -1

    Private Sub pBoxSignature_MouseDown(sender As Object, e As MouseEventArgs) Handles pBoxSignature.MouseDown
        currentCurvePoints = New List(Of Point)
        currentCurve += 1
        signatureObject.Add(currentCurve, currentCurvePoints)
    End Sub

    Private Sub pBoxSignature_MouseMove(sender As Object, e As MouseEventArgs) Handles pBoxSignature.MouseMove
        If e.Button <> MouseButtons.Left OrElse currentCurve < 0 Then Return
        signatureObject(currentCurve).Add(e.Location)
        pBoxSignature.Invalidate()
    End Sub

    Private Sub pBoxSignature_Paint(sender As Object, e As PaintEventArgs) Handles pBoxSignature.Paint
        If currentCurve < 0 OrElse signatureObject(currentCurve).Count = 0 Then Return
        DrawSignature(e.Graphics)
    End Sub

    Private Sub DrawSignature(g As Graphics)
        g.CompositingQuality = CompositingQuality.HighQuality
        g.SmoothingMode = SmoothingMode.AntiAlias

        For Each curve In signatureObject
            If curve.Value.Count < 2 Then Continue For
            Using gPath As New GraphicsPath()
                gPath.AddCurve(curve.Value.ToArray(), 0.5F)
                g.DrawPath(signaturePen, gPath)
            End Using
        Next
    End Sub

    Private Sub registrovisitas_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        dtp_FECHA.Format = DateTimePickerFormat.Custom
        dtp_FECHA.CustomFormat = "dd/MM/yyyy"
    End Sub

    Private Sub Nuevo_ID_VISITA()
        Dim rs As New ADODB.Recordset
        Dim strconsultamax As String = "Select max(ID_RegistroVisitas)+1 as Nuevo_ID_VISITA from [Registro Visitas]"
        rs.Open(strconsultamax, BdSQL, 1, 3)

        lbl_ID.Text = rs("Nuevo_ID_VISITA").Value.ToString

        rs.Close()
        rs = Nothing
    End Sub

    ' 新增：Bitmap转字节数组方法
    Private Function BitmapToByteArray(bmp As Bitmap) As Byte()
        Using ms As New MemoryStream()
            bmp.Save(ms, ImageFormat.Png) ' 使用PNG格式保证签名清晰度
            Return ms.ToArray()
        End Using
    End Function

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Nuevo_ID_VISITA()

        Dim bmp As New Bitmap(pBoxSignature.Width, pBoxSignature.Height)
        pBoxSignature.DrawToBitmap(bmp, New Rectangle(0, 0, pBoxSignature.Width, pBoxSignature.Height))

        ' 将Bitmap转为字节数组
        Dim signatureBytes As Byte() = BitmapToByteArray(bmp)
        ' 释放Bitmap资源
        bmp.Dispose()

        Dim rs As New ADODB.Recordset
        rs.Open("Select * from [Registro Visitas]", BdSQL, 1, 3)
        rs.AddNew()

        rs("ID_RegistroVisitas").Value = lbl_ID.Text
        rs("Fecha_RegistroVisitas").Value = dtp_FECHA.Value
        rs("Visitante_RegistroVisitas").Value = txt_VISITANTE.Text
        rs("DNI_RegistroVisitas").Value = txt_DNI.Text
        rs("hentrada_RegistroVisitas").Value = txt_hE.Text
        rs("NTarjeta_RegistroVisitas").Value = txt_mE.Text
        rs("Motivo_RegistroVisitas").Value = txt_MOTIVO.Text
        rs("Destinatario_RegistroVisitas").Value = txt_DEST.Text
        ' 赋值字节数组到数据库字段
        rs("FirmaDest_RegistroVisitas").Value = signatureBytes
        rs("Tipo_de_Registro").Value = "1"

        rs.Update()
        MessageBox.Show("S'han guardat els canvis")
        rs.Close()
        rs = Nothing
    End Sub
End Class

关键修改点说明 #

• 新增BitmapToByteArray方法：将Bitmap对象通过MemoryStream转换为字节数组，选择PNG格式保存可避免签名失真。
• 在保存逻辑中：将Bitmap转为字节数组后赋值给数据库字段，同时及时释放Bitmap资源避免内存泄漏。
• 移除了重复的rs.AddNew()调用，避免逻辑冗余。

内容的提问来源于stack exchange，提问作者Santos