補充一下SQL脚本:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_InsertPhoto]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_InsertPhoto]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc sp_InsertPhoto
@name as varchar(100),
@photo as image
as
insert into photos values (@name,@photo)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Public Class frmPicReadSave
Public cn As New SqlConnection
Public sqlCMD As New SqlCommand, sqlda As New SqlDataAdapter, ds As New DataSet
Public strPicFileName As String, BshowPic As Boolean = False
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'图片浏览
With OpenFileDialog1
.InitialDirectory = "d:\我的文件\My Pictures"
.DefaultExt = "jpg"
.Multiselect = True
.Filter = "所有图片文件(*.jpg,*.jpeg,*.bmp)|*.jpg;*.jpeg;*.bmp"
End With
'If Me.OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
' strPicFileName = Me.OpenFileDialog1.FileName
' Me.TextBox1.Text = strPicFileName
' Me.PictureBox1.Image = Image.FromFile(Me.OpenFileDialog1.FileName)
'End If
'If Me.OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
' strPicFileName = Me.OpenFileDialog1.FileName
' Me.TextBox1.Text = strPicFileName
' Me.PictureBox1.Image = Image.FromFile(Me.OpenFileDialog1.FileName)
'End If
If Me.OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
For Each strPicFileName In Me.OpenFileDialog1.FileNames
Dim st As New FileStream(strPicFileName, FileMode.Open, FileAccess.Read)
Dim s As String = strPicFileName
Dim mbr As BinaryReader = New BinaryReader(st)
Dim buffer(s.Length) As Byte
mbr.Read(buffer, 0, CInt(s.Length))
st.Close()
InsertImage(buffer, s)
Next
Me.Button6_Click(sender, e)
Me.ComboBox1.SelectedIndex = 0
End If
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
'添加到数据库
Dim st As New FileStream(strPicFileName, FileMode.Open, FileAccess.Read)
Dim s As String = Me.TextBox1.Text
Dim mbr As BinaryReader = New BinaryReader(st)
Dim buffer(s.Length) As Byte
mbr.Read(buffer, 0, CInt(s.Length))
st.Close()
InsertImage(buffer, s)
End Sub
Public Function InsertImage(ByRef buffer, ByVal str)
cn = New SqlConnection(cn.ConnectionString)
cn.Open()
sqlCMD = New SqlCommand("sp_InsertPhoto", cn)
sqlCMD.CommandType = CommandType.StoredProcedure
' sqlCMD.Parameters.Add("@name", SqlDbType.Char).Value = Me.TextBox1.Text
sqlCMD.Parameters.Add("@name", SqlDbType.Char).Value = strPicFileName
sqlCMD.Parameters.Add("@photo", SqlDbType.VarBinary).Value = buffer
sqlCMD.ExecuteNonQuery()
ToolStripStatusLabel1.Text = "保存成功:" & str
cn.Close()
End Function
Public Sub showImage(ByVal s As String)
cn = New SqlConnection(cn.ConnectionString)
cn.Open()
Dim str As String = "SELECT photo FROM Photos WHERE name='" & s & "'"
Dim cmd As New SqlClient.SqlCommand(str, cn)
TextBox1.Text = s
Dim st As New FileStream(s, FileMode.Open, FileAccess.Read)
Dim mbr As BinaryReader = New BinaryReader(st)
Dim buffer(s.Length) As Byte
mbr.Read(buffer, 0, CInt(s.Length))
Me.PictureBox1.Image = Image.FromStream(st)
st.Close()
cn.Close()
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
'显示记录
Dim str As String
str = InputBox("请输入名字", "显示图片")
Me.showImage(str)
End Sub
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
'退出
Me.Dispose(True)
End Sub
Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
'删除记录
Dim s As String
s = InputBox("请输入要删除的文件名:", "删除照片")
cn = New SqlConnection(cn.ConnectionString)
cn.Open()
Dim str As String = "delete photos where name='" & s & "'"
Dim cmd As New SqlCommand(str, cn)
cmd.ExecuteNonQuery()
ToolStripStatusLabel1.Text = "已成功删除记录:" & s
End Sub
Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
'选择图片
If BshowPic = True Then
If Len(Me.ComboBox1.SelectedValue.ToString) > 0 Then
Me.TextBox1.Text = Me.ComboBox1.SelectedValue.ToString
showImage(Me.ComboBox1.SelectedValue.ToString)
End If
End If
End Sub
Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
'刷新
sqlda = New SqlDataAdapter("select name from photos", cn.ConnectionString)
sqlda.Fill(ds)
Me.ComboBox1.DataSource = ds.Tables(0)
Me.ComboBox1.DisplayMember = ds.Tables(0).Columns(0).ToString
Me.ComboBox1.ValueMember = ds.Tables(0).Columns(0).ToString
End Sub