SQL Server存取图片

SQL Server存取图片

本人最近做的一款软件要用到从SQL Server中存取图片,现在做完了,贴出来供有用的朋友参考.
有些是工程的全局变量,请朋友注意区分一下.

'***************************************************************
'*                                                             *
'*此窗体用于存储临时图表、图表、LOGO、鼠标等信息               *
'*                                                             *
'*中国重庆。邹万秋                                             *
'***************************************************************

Option Explicit

Dim ConStr As String                                        'ODBC路径
Dim FileName As String                                      '图片文件名
Const BLOCKSIZE = 4096                                      '每次读写块的大小
Dim ADOCon As New ADODB.Connection                          'ADODB Connection对象
Dim ADORst As New ADODB.Recordset                           'ADODB Recordset 对象
Dim ADOFld As ADODB.Field                                   'ADODB Field 对象
Dim Mstream As ADODB.Stream                                 '定义一个流区

Private Sub SaveToDB(ByRef Fld As ADODB.Field, DiskFile As String)
    Dim byteData() As Byte                                  '定义数据块数组
    Dim NumBlocks As Long                                   '定义数据块个数
    Dim FileLength As Long                                  '标识文件长度
    Dim LeftOver As Long                                    '定义剩余字节长度
    Dim SourceFile As Long                                  '定义自由文件号
    Dim i As Long                                           '定义循环变量
   
    SourceFile = FreeFile                                   '提供一个尚未使用的文件号
    Open DiskFile For Binary Access Read As SourceFile      '打开文件
    FileLength = LOF(SourceFile)                            '得到文件长度
   
    If FileLength = 0 Then                                  '判断文件是否存在
        Close SourceFile
        MsgBox DiskFile & "图片不存在!"
    Else
        NumBlocks = FileLength / BLOCKSIZE                  '得到数据块个数
        LeftOver = FileLength Mod BLOCKSIZE                 '得到剩余字节数
        Fld.Value = Null
        ReDim byteData(BLOCKSIZE)                           '重新定义数据块大小
       
        For i = 1 To NumBlocks
            Get SourceFile, , byteData()                    '读到内存块中
            Fld.AppendChunk byteData()                      '写入FLD
        Next i
       
        ReDim byteData(LeftOver)                            '重新定义数据块大小
        Get SourceFile, , byteData()                        '读到内存块中
        Fld.AppendChunk byteData()                          '写入FLD
        Close SourceFile                                    '关闭源文件
    End If
End Sub

点击ImageMain浏览图片.
Private Sub ImageMain_Click()
On Error GoTo FileErr
    With frmHuoWuYiChang.CommonDialog1
        .Filter = App.Title & " Jpeg,Jpg,Gif,Bmp (*.Jpeg;*.Jpg;*.Gif;*.Bmp)|*.Jpeg;*.Jpg;*.Gif;*.Bmp"
        .CancelError = True
        .ShowOpen
    End With
   
Clipboard.Clear
FileName = CommonDialog1.FileName
Clipboard.SetData LoadPicture(CommonDialog1.FileName)
ImageMain.Picture = Clipboard.GetData

Exit Sub
FileErr:
    FileName = "": Me.ImageMain.Picture = Nothing
    Err.Clear
End Sub

以下为读取图片并在当前文件夹下生成TEMP图片.
Public Sub RunReadPIC(pReadID As Integer)
On Error Resume Next

Dim Mrc As ADODB.Recordset
Dim Msgmrc As String
Dim strSQLMrc As String

Set Mrc = New ADODB.Recordset
strSQLMrc = "Select id,图片 from main where id='" & pReadID & "' Order by id asc"
Set Mrc = ExecuteSQL(strSQLMrc, Msgmrc)

If Mrc.RecordCount <= 0 Then Exit Sub
Set Mstream = New ADODB.Stream
Mstream.Type = adTypeBinary
Mstream.Open
Mstream.Write Mrc.Fields("图片").Value
Mstream.SaveToFile App.Path & "/Temp.bmp", adSaveCreateOverWrite

strSQLMrc = ""
Mrc.Close
Set Mrc = Nothing
End Sub

以下为调用保存,这里使用的是ADODC控件.给ADODB.Field对象赋值.
其中"图片"为数据库中的字段.
Set ADOFld = frmZhiDanShouHuoEditSearch.AdodcEdit.Recordset!图片
调用子程序，给第三个字段(image)赋值
Call SaveToDB(ADOFld, FileName)

以下为读取图片.先调用读取图片子程序,再调入图片.
RunReadPIC (frmZhiDanShouHuoEditSearch.AdodcEdit.Recordset!ID)
以下把图片显示在ImageMain上.
Me.ImageMain.Picture = LoadPicture(App.Path & "/Temp.bmp")