[Resolvido] Imagens na base de dados

[Adão]

Olá, alguém sabe como é que eu posso inserir imagens numa base de dados?

[jlpcalado]

Vou dar-te um exemplo completo.

Tens 2 buttons - 'btAct' e 'btDel' e uma picturebox 'Pic'

Substitui os sqlcommands em conformidade com a tua bd.

Imports System.Data.SqlClient
Imports System.IO
Public Class FotoAluno
Dim TemFoto As Boolean
Public idAluno As Integer
Private Sub Foto_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
 Dim k As Integer = ReadBytes()
 If k > -1 Then carrega_foto(ReadBytes)
End Sub
Private Function ReadBytes() As Long
 Dim nBytes As Long = 0
 Dim bufferSize As Integer = 1000	 ' The size of the BLOB buffer.
 Dim outbyte(bufferSize - 1) As Byte ' The BLOB byte() buffer to be filled by GetBytes.
 Dim retval As Long				 ' The bytes returned from GetBytes.
 Dim startIndex As Long = 0		 ' The starting position in the BLOB output.
 ' Open the connection and read data into the DataReader.
 Dim cn1 As New SqlClient.SqlConnection(My.Settings.CnString)
 Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand("SELECT ID, Foto FROM [ALUNO] WHERE ID= " & idAluno, cn1)
 cn1.Open()
 Dim myReader As SqlClient.SqlDataReader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)
 Try
	 Do While myReader.Read()
		 ' Reset the starting byte for a new BLOB.
		 startIndex = 0
		 ' Read bytes into outbyte() and retain the number of bytes returned.
		 retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize)
		 nBytes = retval
		 ' Continue reading and writing while there are bytes beyond the size of the buffer.
		 Do While retval = bufferSize
			 ' Reposition the start index to the end of the last buffer and fill the buffer.
			 startIndex += bufferSize
			 retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize)
			 nBytes += retval
		 Loop
	 Loop
 Catch
	 nBytes = -1
 Finally
	 myReader.Close()
	 cn1.Close()
 End Try
 Return nBytes
End Function
Private Sub carrega_foto(ByVal nBytes As Long)
 Dim cn As New SqlConnection(My.Settings.CnString)
 Dim cmd As New SqlCommand("SELECT Foto FROM [ALUNO] WHERE ID= " & idAluno, cn)
 Dim dr As SqlDataReader
 Dim pictureColumn As Integer = 0
 Dim b(nBytes) As Byte
 cn.Open()
 dr = cmd.ExecuteReader()
 Try
	 dr.Read()
	 Dim m As Long = dr.GetBytes(pictureColumn, 0, b, 0, b.Length)
	 Dim bs As System.IO.Stream = New System.IO.MemoryStream()
	 bs.Write(b, 0, m)
	 pic.Image = Image.FromStream(bs)
	 TemFoto = True
 Catch ex As Exception
	 TemFoto = False
 Finally
	 dr.Close()
	 cn.Close()
 End Try
End Sub
Private Function ReadFileFromDB() As FileStream
 Dim fs As FileStream = Nothing
 Dim bw As BinaryWriter
 Dim bufferSize As Integer = 100
 Dim outbyte(bufferSize - 1) As Byte
 Dim retval As Long
 Dim startIndex As Long = 0		 ' The starting position in the BLOB output.
 Try
	 Kill(Environment.CurrentDirectory.ToString & "\Foto") : Catch : End Try
 Dim cn1 As New SqlClient.SqlConnection(My.Settings.CnString)
 Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand("SELECT Foto FROM [ALUNO] WHERE ID = " & idAluno, cn1)
 cn1.Open()
 Dim myReader As SqlClient.SqlDataReader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)
 Do While myReader.Read()
	 fs = New FileStream("Foto" & idAluno.ToString, FileMode.OpenOrCreate, FileAccess.Write)
	 bw = New BinaryWriter(fs)
	 startIndex = 0
	 retval = myReader.GetBytes(1, startIndex, outbyte, 0, outbyte.Length + 1)
	 Do While retval = bufferSize
		 bw.Write(outbyte)
		 bw.Flush()
		 startIndex += bufferSize
		 retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize)
	 Loop
	 bw.Write(outbyte, 0, retval - 1)
	 bw.Flush()
	 bw.Close()
	 fs.Close()
 Loop
 myReader.Close()
 cn1.Close()
 Return fs
End Function
Private Sub btAct_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btAct.Click
 If btAct.Text = "Nova" Then
	 opDialog.ShowDialog()
	 If opDialog.FileName.Length > 0 Then
		 pic.Image = Image.FromFile(opDialog.FileName)
		 btAct.Text = "Inserir"
	 End If
 Else
	 If Actualiza_Ficheiro() Then
		 btAct.Text = "Nova"
	 Else
		 MsgBox("Operação cancelada!")
	 End If
 End If
End Sub
Public Function Actualiza_Ficheiro() As Boolean
 Dim cn1 As New SqlClient.SqlConnection(My.Settings.CnString)
 Dim myCommand As New SqlClient.SqlCommand
 myCommand.Connection = cn1
 myCommand.CommandText = "UPDATE ALUNO SET Foto=@fich WHERE ID = @mId"
 Dim fs As New System.IO.FileStream(opDialog.FileName, IO.FileMode.Open, IO.FileAccess.Read)
 Dim b(fs.Length() - 1) As Byte
 fs.Read(b, 0, b.Length)
 fs.Close()
 Dim P As New SqlClient.SqlParameter("@mid", idAluno)
 myCommand.Parameters.Add(P)
 P = New SqlClient.SqlParameter("@fich", SqlDbType.Image, b.Length, ParameterDirection.Input, False, 0, 0, Nothing, DataRowVersion.Current, b)
 myCommand.Parameters.Add(P)
 If cn1.State = ConnectionState.Closed Then
	 cn1.Open()
 End If
 If myCommand.ExecuteNonQuery() > 0 Then
	 Actualiza_Ficheiro = True
 Else
	 Actualiza_Ficheiro = False
 End If
 cn1.Close()
End Function
Private Sub btDel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btDel.Click
 Dim cn1 As New SqlClient.SqlConnection(My.Settings.CnString)
 Dim myCommand As New SqlClient.SqlCommand
 myCommand.Connection = cn1
 myCommand.CommandText = "UPDATE ALUNO SET Foto=null WHERE ID = @mId"
 Dim P As New SqlClient.SqlParameter("@mid", idAluno)
 myCommand.Parameters.Add(P)
 If cn1.State = ConnectionState.Closed Then
	 cn1.Open()
 End If
 Try
	 myCommand.ExecuteNonQuery()
	 pic.Image = Nothing
 Catch ex As Exception
 End Try
 cn1.Close()
End Sub
End Class

Edited June 19, 2012 at 09:33 PM by Caça
GeSHi