将资料以二进制方式存入MsSql数据库
[日期:2007-11-23] | 来源:asp.net魔法学院 作者:佚名 | [字体:大 中 小] |
这个范例示范了如何将档案存入资料库中的 Image 栏位及如何由 Image 栏位取出该档案。
假设资料表 Tb_File 有 ID,ExcelFile 两个栏位,ID 栏位型态为Integer,ExcelFile 栏位型态为 Image,先加入一笔记录,ID=1,ExcelFile=Null,范例中将Excel档案利用Update写入ID=1的记录中及由此记录读取 Image栏位内容并储存成档案。
’’’ <summary>
’’’ 读取档案内容写入Buffer中。
’’’ </summary>
Function FileToBuffer(ByVal FileName As String, ByRef Buffer() As Byte) As Boolean
Dim oFileStream As System.IO.FileStream
If Not System.IO.File.Exists(FileName) Then
Return False
End If
’开启档案为读取状态
oFileStream = System.IO.File.OpenRead(FileName)
’将档案读入 Buffer 中
ReDim Buffer(oFileStream.Length)
oFileStream.Read(Buffer, 0, oFileStream.Length)
oFileStream.Close()
Return True
End Function
’’’ <summary>
’’’ 将 Buffer 的内容写入档案中。VB.NET专题站点
’’’ </summary>
Function BufferToFile(ByVal FileName As String, ByVal Buffer() As Byte) As Boolean
Dim oFileStream As System.IO.FileStream
Try
If System.IO.File.Exists(FileName) = False Then
’ Create the file.
oFileStream = System.IO.File.Create(FileName)
oFileStream.Close()
End If
’开启档案为写入状态
oFileStream = System.IO.File.OpenWrite(FileName)
’将 Buffer 写入档案中
oFileStream.Write(Buffer, 0, Buffer.Length)
oFileStream.Close()
Return True
Catch
Return False
End Try
End Function
’Button1 Click 将档案存入资料库中的 Image 型别的栏位'www.wewill.cn
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sConnStr As String = String.Empty
Dim cn As New SqlClient.SqlConnection()
Dim cmd As New SqlClient.SqlCommand()
Dim param As SqlClient.SqlParameter
Dim sSQL As String = String.Empty
Dim Buffer() As Byte = Nothing
If Not FileToBuffer("C:\test1.xls", Buffer) Then Exit Sub
sConnStr = "Data Source=localhost;Initial Catalog=CHIComm;Integrated Security=True"
cn.ConnectionString = sConnStr
cn.Open()
sSQL = "Update Tb_File Set ExcelFile=@ExcelFile Where ID=1"
cmd = New SqlClient.SqlCommand(sSQL, cn)
param = New SqlClient.SqlParameter("@ExcelFile", SqlDbType.Image)
param.Value = Buffer
cmd.Parameters.Add(param)
cmd.ExecuteNonQuery()
cmd = Nothing
cn.Close()
cn = Nothing
End Sub
’Button2 Click 读取资料库的 Image 型别栏位并储存成档案
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim sConnStr As String = String.Empty
Dim cn As New SqlClient.SqlConnection()
Dim cmd As New SqlClient.SqlCommand()
Dim sSQL As String = String.Empty
Dim dr As SqlClient.SqlDataReader
Dim Buffer() As Byte = Nothing
sConnStr = "Data Source=localhost;Initial Catalog=CHIComm;Integrated Security=True"
cn.ConnectionString = sConnStr
cn.Open()
sSQL = "Select ExcelFile From Tb_File Where ID=1"
cmd = New SqlClient.SqlCommand(sSQL, cn)
dr = cmd.ExecuteReader()
dr.Read()
Buffer = dr.Item("ExcelFile")
BufferToFile("C:\test2.xls", Buffer)
End Sub
评论