求助!如何向数据库中存取图片?

[yasmong]

在vb.net中如何把图片存入到sqlserver2000数据库中啊,我只知道利用image类型,但不知道如何在vb.net中进行操作.各位高手帮忙啊!最好有程序.先谢过

[ConanKid]

呵呵，没有这么复杂，也很简单的，我公司的电脑上有代码，一时想不起来，如果有兴趣，明天找我要。（QQ：66970551）

[yyj135]

自己看一下代碼吧：不好意思，我沒時間寫備注。

Imports System.IO
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient

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 frmPicReadSave_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
cn.ConnectionString = "Server=(local);Database=test;user id=sa;pwd=;"
sqlCMD.CommandText = "dbo.[sp_InsertPhoto]"
sqlCMD.CommandType = CommandType.StoredProcedure
sqlCMD.Parameters.Add(New SqlParameter("@RETURN_VALUE", _
SqlDbType.Int, 4, ParameterDirection.ReturnValue, False, CType(10, Byte), CType(10, Byte), _
"", DataRowVersion.Current, Nothing))
sqlCMD.Parameters.Add(New SqlParameter("@name", SqlDbType.Char, 100))
sqlCMD.Parameters.Add(New SqlParameter("@photo", SqlDbType.VarBinary, 2147483647))
Me.Panel1.AutoScroll = True

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
BshowPic = True

' Me.TextBox2.DataBindings.Add("Text", ds.Tables(0), ds.Tables(0).Columns(0).ToString)

End Sub

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


End Class

[yyj135]

補充一下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

[ljhkim6]

一般情况下，将图片存入数据库都是存放路径而不采用image类型，直接采用varchar
image类型实际上将图片转化为流的形式，在以流的形式读取，一般不采用这个，太复杂。