9.5.1 二进制文件存取示例(vb&vba).vbs

sqlserver 数据库编程的绝好脚本

' 1. 保存文件到数据库
Sub s_SaveFile()
    Dim iStm As ADODB.Stream
    Dim iRe As ADODB.Recordset
    Dim iConcStr As String

	'SQL数据库的连接字符串
    iConcStr = "Provider=SQLOLEDB.1;Persist Security Info=True;" & _
        "User ID=用户名;Password=密码;Initial Catalog=数据库名;Data Source=SQLServer实例名"
    '读取文件到内容
    Set iStm = New ADODB.Stream
    With iStm
        .Type = adTypeBinary  '二进制模式,如果是用text/ntext字段保存纯文本数据,则改用 adTypeText
        .Open
        .LoadFromFile "c:\test.doc" ‘读取文件
    End With
    
    '打开保存文件的表
    Set iRe = New ADODB.Recordset
    With iRe
        .Open "tablename", iConc, adOpenKeyset, adLockOptimistic
        .AddNew         '新增一条记录
        .Fields("保存文件数据的字段名") = iStm.Read
        .Update
    End With
    
    '完成后关闭对象
    iRe.Close
    iStm.Close
End Sub


' 2. 数据库中读取数据，并保存到文件的示例代码。
Sub s_ReadFile()
    Dim iStm As ADODB.Stream
    Dim iRe As ADODB.Recordset
    Dim iConcStr As String
    
    '数据库连接字符串
    iConcStr = "Provider=SQLOLEDB.1;Persist Security Info=True;" & _
        "User ID=用户名;Password=密码;Initial Catalog=数据库名;Data Source=SQLServer实例名"
    
    '打开保存文件数据的表
    Set iRe = New ADODB.Recordset
    iRe.Open "tablename", iConcStr, adOpenKeyset, adLockReadOnly
    iRe.Filter = "id=1"  ‘要读取文件的id
    if iRe("保存文件数据的字段名").ActualSize>0 Then
	    '保存到文件
	    Set iStm = New ADODB.Stream
	    With iStm
	        .Mode = adModeReadWrite
	        .Type = adTypeBinary '二进制模式,如果是用text/ntext字段保存纯文本数据,则改用 adTypeText
	        .Open
	        .Write iRe("保存文件数据的字段名")
	        .SaveToFile "c:\test.doc"
	    End With
    
	    '关闭对象
	    iStm.Close
	End If

    iRe.Close
End Sub