不看会后悔的Windows XP之经验谈	简单快捷DIY实用家庭影院

如何把IO.Stream的数据写入SQL SERVER 的 image ?

楼主lingyumengmeng（落英）2005-01-07 00:01:02 在 .NET技术 / VB.NET 提问

我有一个本地的IO.Stream，请问如何写入SQL SERVER 的 image 类型的字段？可以直接
dim mystr as system.io.stream
.
.
..."INSERT INTO relation (str) VALUES ('" & mystr & "')" '其中str是image类型的
.
.
吗？
如果不行该怎么做？
另外SQL Server 里面除了image有没有别的更好的储存大数据量二进制数据的类型？问题点数：100、回复次数：9Top

1 楼NaZiChong（那子虫）回复于 2005-01-07 08:13:23 得分 0

可以，你可以将该字段绑定到image控件,最好是用存储过程

除了image类型,还可以是varbinary,binary,sql_variantTop

2 楼NaZiChong（那子虫）回复于 2005-01-07 08:16:06 得分 10

注意:如果是你的方法,先把mystr中的内容转化为byte[]Top

3 楼lingyumengmeng（落英）回复于 2005-01-07 10:47:37 得分 0

注意:如果是你的方法,先把mystr中的内容转化为byte[]

请问是不是这个意思：
dim bi() as byte
mystr.write(bi,0,mystr.length)
..."INSERT INTO relation (str) VALUES ('" & bi & "')"

varbinary,binary和image有什么区别？Top

4 楼MSTOP（陈建华）回复于 2005-01-07 11:01:27 得分 30

'******************************************************************
'函数:GetDbImage
'范围:Public
'作用:返回数据库中相应的图片字段
'参数:
' SqlCnn: SqlClient.SqlConnection.
' DataTabName:数据表名
' PhoFiled:图片字段名
' WhereStr:Where条件语句.
'
'返回值:存在图片,则返回一个IMAGE,否则,返回NOTHING
'******************************************************************
Public Function GetDbImage(ByRef SqlCnn As SqlClient.SqlConnection, _
ByVal DataTabName As String, _
ByVal PhoFiled As String, _
ByVal WhereStr As String) As System.Drawing.Image

If SqlCnn.State = ConnectionState.Closed Then SqlCnn.Open()
Dim StrSql As String = "SELECT TOP 1 " & PhoFiled & " FROM " & DataTabName & " " & WhereStr
Dim Cmd As New SqlClient.SqlCommand(StrSql, SqlCnn)
Dim ByteArr() As Byte
Dim RevImage As System.Drawing.Image

ByteArr = Cmd.ExecuteScalar()
If (ByteArr.Length > 0) Then
Dim Stream As New MemoryStream(ByteArr, True)
Stream.Write(ByteArr, 0, ByteArr.Length)
RevImage = New System.Drawing.Bitmap(Stream)
Stream.Close()
Else
RevImage = Nothing
End If
Return RevImage

End Function

'******************************************************************
'函数:GetDbImage
'范围:Public
'作用:返回数据库中相应的图片字段
'参数:
' SqlCnn: SqlClient.SqlConnection.
' StrSql:Sql取值语句.
'
'返回值:存在图片,则返回一个IMAGE,否则,返回NOTHING
'******************************************************************
Public Function GetDbImage(ByRef SqlCnn As SqlClient.SqlConnection, _
ByVal StrSql As String) As System.Drawing.Image

If SqlCnn.State = ConnectionState.Closed Then SqlCnn.Open()

Dim Cmd As New SqlClient.SqlCommand(StrSql, SqlCnn)
Dim ByteArr() As Byte
Dim RevImage As System.Drawing.Image

ByteArr = Cmd.ExecuteScalar()
If (ByteArr.Length > 0) Then
Dim Stream As New MemoryStream(ByteArr, True)
Stream.Write(ByteArr, 0, ByteArr.Length)
RevImage = New System.Drawing.Bitmap(Stream)
Stream.Close()
Else
RevImage = Nothing
End If

Return RevImage

End Function

'******************************************************************
'函数:UpImageToDb
'范围:Public
'作用:返回数据库中相应的图片字段
'参数:
' SqlCnn: SqlClient.SqlConnection.
' FileName:图片文件名
' DataTabName:数据表名
' PhoFiled:图片字段名
' WhereStr:Where条件语句.
'
'返回值:成功:TRUE, 失败:FALSE
'******************************************************************
Public Function UpImageToDb(ByRef SqlCnn As SqlClient.SqlConnection, _
ByVal FileName As String, _
ByVal DataTabName As String, _
ByVal PhoFiled As String, _
ByVal WhereStr As String) As Boolean
Dim StrSql As String

If SqlCnn.State = ConnectionState.Closed Then SqlCnn.Open()

If Len(Dir(FileName)) > 0 Then

Dim St As New FileStream(FileName, FileMode.Open, FileAccess.Read)
Dim Mbr As BinaryReader = New BinaryReader(St)
Dim Buffer(St.Length) As Byte
Dim SqlCommand As New SqlClient.SqlCommand

Try
Mbr.Read(Buffer, 0, CInt(St.Length))
SqlCommand.CommandText = "Update " & DataTabName & " set " & PhoFiled & "=@photo " & WhereStr
SqlCommand.Connection = SqlCnn
Dim Prm As New SqlClient.SqlParameter("@photo", SqlDbType.VarBinary, Int(St.Length), ParameterDirection.Input, False, 0, 0, "", DataRowVersion.Current, Buffer)
SqlCommand.Parameters.Add(Prm)
SqlCommand.ExecuteNonQuery() '执行更新语句
St.Close()
Return True
Catch
Err.Clear()
Return False
End Try
Else
StrSql = "Update " & DataTabName & " SET " & PhoFiled & "=Null " & WhereStr
ExeSqlCmd(SqlCnn, StrSql)
Return False
End If

End Function

'******************************************************************
'函数:UpImageToDb
'范围:Public
'作用:返回数据库中相应的图片字段
'参数:
' SqlCnn: SqlClient.SqlConnection.
' Image :图片
' DataTabName:数据表名
' PhoFiled:图片字段名
' WhereStr:Where条件语句.
'
'返回值:成功:TRUE, 失败:FALSE
'******************************************************************
Public Function UpImageToDb(ByRef SqlCnn As SqlClient.SqlConnection, _
ByVal Image As System.Drawing.Image, _
ByVal DataTabName As String, _
ByVal PhoFiled As String, _
ByVal WhereStr As String) As Boolean
Dim FileName As String
Dim StrSql As String
Dim R As Integer

If SqlCnn.State = ConnectionState.Closed Then SqlCnn.Open()

If Image Is Nothing Then
StrSql = "Update " & DataTabName & " SET " & PhoFiled & "=Null " & WhereStr
R = ExeSqlCmd(SqlCnn, StrSql)
Return CBool(R)
Else
FileName = Application.StartupPath() '//程序所有路径.
If Microsoft.VisualBasic.Right(FileName, 1) <> "\" Then
FileName = FileName & "\"
End If
FileName = FileName & "TmpFile001.Tmp"

SavePicture(Image, FileName)
If Len(Dir(FileName)) > 0 Then
Dim St As New FileStream(FileName, FileMode.Open, FileAccess.Read)
Dim Mbr As BinaryReader = New BinaryReader(St)
Dim Buffer(St.Length) As Byte
Dim SqlCommand As New SqlClient.SqlCommand

Try
Mbr.Read(Buffer, 0, CInt(St.Length))
SqlCommand.CommandText = "Update " & DataTabName & " set " & PhoFiled & "=@photo " & WhereStr
SqlCommand.Connection = SqlCnn
Dim Prm As New SqlClient.SqlParameter("@photo", SqlDbType.VarBinary, Int(St.Length), ParameterDirection.Input, False, 0, 0, "", DataRowVersion.Current, Buffer)
SqlCommand.Parameters.Add(Prm)
SqlCommand.ExecuteNonQuery() '执行更新语句
St.Close()
Kill(FileName)
Return True
Catch
Err.Clear()
Return False
End Try
Else
Return False
End If
End If
End FunctionTop

5 楼lingyumengmeng（落英）回复于 2005-01-08 20:42:13 得分 0

谢谢陈建华
我知道该怎么读取了，但是我还是不知道该怎么写入？Top

6 楼hdhai9451（☆新人类☆）回复于 2005-01-08 21:34:03 得分 0

到sql server版裡問吧，可能會有人做得出來Top

7 楼landlordh（work wonders）回复于 2005-01-09 14:50:32 得分 0

只要把图像的二进制流写成16进制的字符格式就行了

insert 表(image字段) values(0x23423ff)Top

8 楼chenhaohf（）回复于 2005-01-10 08:38:09 得分 30

If o1.ShowDialog = DialogResult.OK Then
Dim path As String = o1.FileName
Dim fs As New IO.FileStream(path, IO.FileMode.Open)
Dim size As String = fs.Length.ToString
Dim byts(fs.Length) As Byte
fs.Read(byts, 0, fs.Length)
fs.Close()
Dim cmd As New SqlCommand

With cmd
.Connection = conn

.Parameters.Add("@ddnr", byts)

End With
cmd.CommandText = "insert into mytest(pic) values(@ddnr)"
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
End If

sql2000 字段为imageTop

9 楼Jeacey（）回复于 2005-01-10 08:59:27 得分 30

Stream objStream;
int intMystr = mystr.Length;
byte[] buffer = new byte[intMystr ];
objstream.Read(buffer, 0, intMystr );
把buffer保存到数据库中，即可！
Top