Imports System.Data.SqlClient
Imports System.IO

Public Class Form1
    Const CONNSTR As String = "Data Source=.\SQLEXPRESS;Initial Catalog=DB_test;Integrated Security=True;"

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        lblRutaImagen.Text = ""
    End Sub

    ''' <summary>
    ''' Guarda en la base de datos el contenido de un archivo gráfico
    ''' </summary>
    ''' <param name="id">Identificador único (clave de la tabla) de la imagen</param>
    ''' <param name="filefoto">ruta y nombre de la imagen</param>
    ''' <remarks></remarks>
    Private Sub InsertarFotoEnBDD(id As Integer, filefoto As String)

        Try
            Dim ms As MemoryStream = New MemoryStream()
            Dim fs As FileStream = New FileStream(filefoto, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)

            ms.SetLength(fs.Length)
            fs.Read(ms.GetBuffer(), 0, fs.Length)

            Dim arrImg() As Byte = ms.GetBuffer()
            ms.Flush()
            fs.Close()

            Using conn As New SqlConnection(CONNSTR)
                Using cmd As SqlCommand = conn.CreateCommand()

                    conn.Open()

                    cmd.CommandText = "insert into IMAGENES (" +
                                    " [id]" +
                                    ",[nombre]" +
                                    ",[imagen]" +
                                    ") VALUES (" +
                                    " @id" +
                                    ",@nombre" +
                                    ",@imagen" +
                                    ")"

                    cmd.Parameters.Add("@id", SqlDbType.BigInt).Value = id
                    cmd.Parameters.Add("@nombre", SqlDbType.NVarChar, 64).Value = Path.GetFileName(filefoto)
                    cmd.Parameters.Add("@imagen", SqlDbType.VarBinary).Value = arrImg

                    cmd.ExecuteNonQuery()
                End Using
            End Using
            ms.Close()
        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try
    End Sub

    ''' <summary>
    ''' Accede a la base de datos para recuperar una imagen
    ''' </summary>
    ''' <param name="id">Valor del ID en la tabla de las imágenes</param>
    ''' <param name="savetofolder">Ruta del disco donde se guardará la imagen recuperada. La imagen se guardará con el mimso con el que se guardó originalmente</param>
    ''' <remarks></remarks>
    Private Sub ObtenerFotoDeBDD(id As Integer, savetofolder As String)
        Try
            Using conn As New SqlConnection(CONNSTR)
                Using cmd As SqlCommand = conn.CreateCommand
                    conn.Open()

                    cmd.CommandText = "select [nombre], [imagen] from IMAGENES where [id]=@id"
                    cmd.Parameters.Add("@id", SqlDbType.BigInt).Value = id

                    Dim reader As SqlDataReader = Nothing

                    reader = cmd.ExecuteReader
                    reader.Read()

                    Dim nombreFicheroBDD As String = reader.Item(0)
                    Dim nSave As String = savetofolder & nombreFicheroBDD

                    Dim arrImg() As Byte = reader.Item(1)

                    Dim ms As MemoryStream = New MemoryStream(arrImg)
                    Dim fs As FileStream = New FileStream(nSave, FileMode.Create, FileAccess.Write, FileShare.ReadWrite)

                    ms.WriteTo(fs)

                    fs.Flush()
                    fs.Close()
                    ms.Close()
                End Using
            End Using
        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try
    End Sub

    ''' <summary>
    ''' Devuelve un objeto de tipo Image
    ''' </summary>
    ''' <param name="id">Identificador de la imagen</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function ObtenerBitmapdeBDD(id As Integer) As Image
        Try
            Using conn As New SqlConnection(CONNSTR)
                Using cmd As SqlCommand = conn.CreateCommand
                    conn.Open()

                    cmd.CommandText = "select [imagen] from IMAGENES where [id]=@id"
                    cmd.Parameters.Add("@id", SqlDbType.BigInt).Value = id

                    Dim arrImg() As Byte = cmd.ExecuteScalar

                    Dim ms As New MemoryStream(arrImg)

                    Dim img As Image = Image.FromStream(ms)
                    ms.Close()
                    Return img

                End Using
            End Using
        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try
    End Function


#Region "Botones"

    ' Botón para seleccionar una imagen
    Private Sub btnImgDialog_Click(sender As System.Object, e As System.EventArgs) Handles btnImgDialog.Click
        Dim fd As New OpenFileDialog

        fd.Filter = "*.bmp;*.gif;*.jpg;*.png|*.bmp;*.gif;*.jpg;*.png"
        fd.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyPictures
        fd.Title = "seleccionar la imagen que se guardará en la base de datos"
        fd.RestoreDirectory = True

        If fd.ShowDialog = Windows.Forms.DialogResult.OK Then
            lblRutaImagen.Text = fd.FileName
            pictureBox1.Image = Image.FromFile(fd.FileName)
            pictureBox1.SizeMode = PictureBoxSizeMode.AutoSize
            btnSaveToBDD.Enabled = True
        Else
            lblRutaImagen.Text = ""
            pictureBox1.Image = Nothing
            btnSaveToBDD.Enabled = False
        End If
    End Sub

    ' Botón Guardar la imagen seleccionada en la base de datos
    Private Sub btnSaveToBDD_Click(sender As System.Object, e As System.EventArgs) Handles btnSaveToBDD.Click
        If IsNumeric(mskId.Text.Trim) = False Then
            MessageBox.Show("Falta introducir un id", "Atención", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            mskId.Focus()
        Else
            If IsNothing(pictureBox1.Image) OrElse lblRutaImagen.Text = "" Then
                MessageBox.Show("Falta seleccionar la foto a guardar en la base de datos", "Atención", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            Else
                Try
                    InsertarFotoEnBDD(Int32.Parse(mskId.Text.Trim), lblRutaImagen.Text)
                    MessageBox.Show("Se guardó la foto en la base de datos", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information)

                Catch ex As Exception
                    MessageBox.Show("Error: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                End Try
            End If
        End If
    End Sub

    ' Botón Recuperar imagen desde la base de datos y generar un nuevo fichero en C:\Temp con
    ' el contenido de dicha imagen
    Private Sub btnLoadFromBDDtoFile_Click(sender As System.Object, e As System.EventArgs) Handles btnLoadFromBDDtoFile.Click
        If IsNumeric(mskId.Text.Trim) = False Then
            MessageBox.Show("Falta introducir un id", "Atención", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            mskId.Focus()
        Else
            Try
                lblRutaImagen.Text = ""

                btnSaveToBDD.Enabled = False
                pictureBox1.Image = Nothing


                ObtenerFotoDeBDD(Int32.Parse(mskId.Text.Trim), "C:\temp\")
                mskId.Text = ""
                MessageBox.Show("foto guardada con éxito")
            Catch ex As Exception
                MessageBox.Show("Error: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End If
    End Sub

    ' Botón Recuperar imagen desde la base de datos y mostrar dicha imagen en un picturebox
    Private Sub btnLoadFromBDDtoImg_Click(sender As System.Object, e As System.EventArgs) Handles btnLoadFromBDDtoImg.Click
        If IsNumeric(mskId.Text.Trim) = False Then
            MessageBox.Show("Falta introducir un id", "Atención", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            mskId.Focus()
        Else
            Try
                lblRutaImagen.Text = ""
                btnSaveToBDD.Enabled = False

                pictureBox1.Image = ObtenerBitmapdeBDD(Int32.Parse(mskId.Text.Trim))
                pictureBox1.SizeMode = PictureBoxSizeMode.AutoSize
                mskId.Text = ""
            Catch ex As Exception
                MessageBox.Show("Error: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End If
    End Sub

#End Region

End Class