VB 写的二进制文件读写数据库操作模块

来源：互联网发布：域名所有者查询编辑：程序博客网时间：2024/04/29 17:43

'/-----------------------------------------------------------------------
'/ Desc:将文件以二进制流的形式写入数据库中
'/ Author:By Bytesoon
'/-----------------------------------------------------------------------

Public dbConn As ADODB.Connection
Public curImgPath As String

' 创建并打开数据库
Public Function OpenDB(dbServer As String, dbName As String) As Boolean
     Err.Clear
    OpenDB = False

    '创建数据库连接对象
    Set dbConn = New ADODB.Connection
    '设置为可以读写操作
    dbConn.Mode = adModeReadWrite
    '访问SQLserver
    dbConn.Provider = "sqloledb"

    On Error Resume Next
    '打开数据库连接
    dbConn.Open "Data Source=" & dbServer & ";Initial Catalog=" & dbName & ";Persist Security Info=false;User ID=sa"

    If Err.Number <> 0 Then
        MsgBox "Connect failed"
        Exit Function
    End If

    OpenDB = True

End Function

' 关闭数据库连接并释放资源
Public Sub CloseDB(Conn As ADODB.Connection)
    If Conn Is Nothing Then
        If Conn.State = 1 Then
            Conn.Close
            Set Conn = Nothing
        End If
    End If
End Sub

' 将给定路径下的文件写入到数据库
Public Function SaveImgToDB(Conn As ADODB.Connection, tableName As String, fieldName As String, ImgPath As String) As Boolean
    SaveImgToDB = False

    ' 先判断数据库是否已经打开
    If Conn.State = 1 Then

        Dim rs As ADODB.Recordset
        Set rs = New ADODB.Recordset
        ' 打开表记录
        rs.Open "select [" & fieldName & "] from [" & tableName & "]", Conn, 1, 3

        ' 打开流对象，并装载文件
        Dim os As ADODB.Stream
        Set os = New ADODB.Stream
        os.Mode = 3
        os.Type = 1
        os.Open
        os.LoadFromFile ImgPath

        ' 将文件流写入指定字段中
        If Not (rs.EOF Or rs.BOF) Then
            rs.MoveFirst
            rs.Fields(0).AppendChunk os.Read()
            rs.Update
        End If

        ' 关闭数据记录集对象
        rs.Close
        Set rs = Nothing

        ' 关闭流对象
        os.Close
        Set os = Nothing
    Else
        MsgBox "database not connected"
        Exit Function
    End If
    SaveImgToDB = True
End Function

' 从数据库中获得文件
Public Function GetFileFromDB(Conn As ADODB.Connection, tableName As String, fieldName As String, saveToPath As String) As Boolean
    GetFileFromDB = False

    ' 先判断数据库是否已经打开
    If Conn.State = 1 Then

        Dim rs As ADODB.Recordset
        Set rs = New ADODB.Recordset
        ' 打开表记录
        rs.Open "select [" & fieldName & "] from [" & tableName & "]", Conn, 1, 3

        ' 打开流对象
        Dim os As ADODB.Stream
        Set os = New ADODB.Stream
        os.Mode = 3
        os.Type = 1

        ' 从指定字段中读取文件流并保存文件
        If Not (rs.EOF Or rs.BOF) Then
            rs.MoveFirst

            os.Flush
            os.Open
            os.Write rs(0).GetChunk(rs(0).ActualSize)
            os.SaveToFile saveToPath, adSaveCreateOverWrite

        End If

        ' 关闭数据记录集对象
        rs.Close
        Set rs = Nothing

        ' 关闭流对象
        os.Close
        Set os = Nothing
    Else
        MsgBox "database not connected"
        Exit Function
    End If
    GetFileFromDB = True
End Function