图片在数据库中的存储方式？

1.你的數據庫是access2000還是sql server2000?2.如果圖片太多﹐我覺得不要直接存儲圖片﹐那樣數據會很大的。換個方法﹐只存儲圖片的路徑﹐圖片放在另外一個地方﹐取圖版時按圖片的路徑存取﹗

50W张图片,如果存到数据库,那就很大了.用sql server2000存进去也可以.
如果保密性要求不高的话,最好将图片存到一个目录下.数据库就存路径名就好了

新建一个表  img表列 id int ,tp   image
'是一个类savepicture
Dim Save_Rs As New ADODB.Recordset
Dim FileLength As Long              '定义文件长度
Dim NumBlocks As Integer            '定义块数量
Dim LeftOver As Integer             '定义剩余数量
Dim byteData() As Byte              '定义二进制数据块
Const BlockSize = 1000              '定义快大小
Dim MyFile As New FileSystemObject
Dim MyFolder As Folder
Dim i As Integer
Public Function Save(Ado As ADODB.Connection, DiskFile As String, Table As String, Col As String, id As String, Optional IdValue As String) As Boolean
    Dim strSQL As String
    '没有文件路径
    If Len(DiskFile) = 0 Then
        Save = False
        Exit Function
    End If
    '没有表名
    If Len(Table) = 0 Then
        Save = False
        Exit Function
    End If
    '没有字段名称
    If Len(Col) = 0 Then
        Save = False
        Exit Function
    End If
   '没有条件列名
    If Len(id) = 0 Then
        Save = False
        Exit Function
    End If
    '没有条件列名的值
    If Len(IdValue) = 0 Then
        Save = False
        Exit Function
    End If

    On Error GoTo err1
    If MyFile.FolderExists("c:\MyTemp") = True Then
        MyFile.DeleteFolder "c:\MyTemp", True
    End If
    MyFile.CreateFolder "c:\MyTemp"

    If Save_Rs.State = 1 Then Save_Rs.Close

    strSQL = "select " & id & "," & Col & " from " & Table & " where " & id & " ='" & IdValue & "'"

    Save_Rs.Open strSQL, Ado, adOpenKeyset, adLockOptimistic    Open DiskFile For Binary Access Read As #1
    FileLength = LOF(1)
    NumBlocks = FileLength \ BlockSize
    LeftOver = FileLength Mod BlockSize

    ReDim byteData(BlockSize)
    For i = 1 To NumBlocks
       Get #1, , byteData()
       Save_Rs.Fields(Col).AppendChunk byteData()
    Next
    ReDim byteData(LeftOver)
    Get #1, , byteData()
    Save_Rs.Fields(Col).AppendChunk byteData()
    Close #1
    Save_Rs.Update

    If Save_Rs.State = 1 Then Save_Rs.Close

    Save = True
    Exit Function

err1:
    Save = False
   If Save_Rs.State = 1 Then Save_Rs.Close
   MsgBox "保存没有成功" & Err.Description, vbQuestionEnd FunctionPublic Function Show(Ado As ADODB.Connection, Table As String, Col As String, id As String, Optional IdValue As String) As String
    Dim strSQL As String
    Dim TempF As String    '没有表名
    If Len(Table) = 0 Then
        Show = ""
        Exit Function
    End If
    '没有字段名称
    If Len(Col) = 0 Then
        Show = ""
        Exit Function
    End If
   '没有条件列名
    If Len(id) = 0 Then
        Show = ""
        Exit Function
    End If
    '没有条件列名的值
    If Len(IdValue) = 0 Then
        Show = ""
        Exit Function
    End If

    On Error GoTo err1

    If MyFile.FolderExists("c:\MyTemp") = True Then
        MyFile.DeleteFolder "c:\MyTemp", True
    End If
    MyFile.CreateFolder "c:\MyTemp"

    If Save_Rs.State = 1 Then Save_Rs.Close
    strSQL = "select " & id & "," & Col & " from " & Table & " where " & id & " ='" & IdValue & "'"

    Save_Rs.Open strSQL, Ado, adOpenKeyset, adLockOptimistic

    FileLength = Save_Rs.Fields(1).ActualSize
    TempF = "c:\mytemp\asd.txt"

    Open TempF For Binary Access Write As #2
    NumBlocks = FileLength \ BlockSize
    LeftOver = FileLength Mod BlockSize

    ReDim byteData(LeftOver)
    byteData() = Save_Rs.Fields(1).GetChunk(LeftOver)
    Put #2, , byteData()

    For i = 1 To NumBlocks
       ReDim byteData(BlockSize)
       byteData() = Save_Rs.Fields(1).GetChunk(BlockSize)
       Put #2, , byteData()
     Next
     Close #2
     Show = TempF
     If Save_Rs.State = 1 Then Save_Rs.Close
    Exit Function
err1:
    Show = ""
    If Save_Rs.State = 1 Then Save_Rs.Close
End Functionvb中
Dim adocon As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim DiskFile As String
Dim FileLength As Long
Dim NumBlocks As Integer
Dim LeftOver As Integer
Dim byteData() As Byte
Const BlockSize = 1000
Dim MyFile As New FileSystemObject
Dim MyFolder As Folder
Dim i As IntegerDim TP As New SaveBin'保存
Private Sub Command1_Click()
dim diskfile as string
CD.ShowOpen
DiskFile = CD.FileName
if len(trim(diskfiel))>0 then
TP.Save adocon, DiskFile, "img", "img", "nno", "1"

endif

End Sub
'显示
Private Sub Command2_Click()Dim TempFile As String
    TempFile = TP.Show(adocon, "image11", "img", "nno", "1")
        Image1.Picture = LoadPicture(TempFile)
End Sub

我也觉得放在目录上比较好.
其次用sql server 2000.

1.方案选择
  如果你的数据库经常转移地方,则把图片存储在数据库中,这样方便
  如果你着重于效率,则将图片文件直接放在文件服务器中,数据库中只保存检索必须的信息,例如:文件名,目录,简介,检索关键字2.字段类型
  图片放在数据库中的话,sql数据库字段类型用image,ACCESS用ole,其他数据库各自有各自的对应字段类型

'3.图片放在数据库中的存取'VB/VBA中实现数据库中的文件存取'示例数据库为ACCESS数据库,用SQL数据库的话,只需要改连接字符串
'
'*************************************************************************
'**
'** 使用 ADODB.Stream 保存/读取文件到数据库
'** 引用 Microsoft ActiveX Data Objects 2.5 Library 及以上版本
'**
'** ----- 数据库连接字符串模板 ---------------------------------------
'** ACCESS数据库
'** iConcStr = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False" & _
'**     ";Data Source=数据库名"
'**
'** SQL数据库
'** iConcStr = "Provider=SQLOLEDB.1;Persist Security Info=True;" & _
'**     "User ID=用户名;Password=密码;Initial Catalog=数据库名;Data Source=SQL服务器名"
'**
'*************************************************************************
'
'保存文件到数据库中
Sub s_SaveFile()
    Dim iStm As ADODB.Stream
    Dim iRe As ADODB.Recordset
    Dim iConcStr As String

    'ACCESS数据库的连接字符串
    iConcStr = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False" & _
        ";Data Source=F:\My Documents\客户资料1.mdb"

'SQL数据库的连接字符串
    iConcStr = "Provider=SQLOLEDB.1;Persist Security Info=True;" & _
        "User ID=用户名;Password=密码;Initial Catalog=数据库名;Data Source=SQL服务器名"
    '读取文件到内容
    Set iStm = New ADODB.Stream
    With iStm
        .Type = adTypeBinary    '二进制模式
        .Open
        .LoadFromFile "c:\test.doc"
    End With

    '打开保存文件的表
    Set iRe = New ADODB.Recordset
    With iRe
        .Open "表", iConc, adOpenKeyset, adLockOptimistic
        .AddNew         '新增一条记录
        .Fields("保存文件内容的字段") = iStm.Read
        .Update
    End With

    '完成后关闭对象
    iRe.Close
    iStm.Close
End Sub'从数据库中读取数据,保存成文件
Sub s_ReadFile()
    Dim iStm As ADODB.Stream
    Dim iRe As ADODB.Recordset
    Dim iConc As String

    '数据库连接字符串
    iConc = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False" & _
        ";Data Source=\\xz\c$\Inetpub\zj\zj\zj.mdb"

    '打开表
    Set iRe = New ADODB.Recordset
    iRe.Open "tb_img", iConc, adOpenKeyset, adLockReadOnly
    iRe.Filter = "id=64"    if iRe("img").ActualSize>0 Then     '保存到文件
    Set iStm = New ADODB.Stream
    With iStm
        .Mode = adModeReadWrite
        .Type = adTypeBinary
        .Open
        .Write iRe("img")
        .SaveToFile "c:\test.doc"
    End With

    '关闭对象
    iStm.Close
End If    iRe.Close
End Sub

调试易

图片在数据库中的存储方式？

解决方案 »