Jump to content

[Resolvido] Imagens na base de dados


Adão

Recommended Posts

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 by Caça
GeSHi
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site you accept our Terms of Use and Privacy Policy. We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.