14 处理大型对象

14.1 什么是LOB、BLoB和CLOB

14.1.1 LOB的存储位置

14.1.2 处理LOB

14.1.3 读取BLOB数据

 

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="NewString" 
         connectionString="Data Source=.\SQLEXPRESS;
         AttachDbFilename=|DataDirectory|\northwnd.mdf;
         Integrated Security=True; User Instance=True"
         providerName="system.data.SqlClient"/>
  </connectionStrings>
</configuration>

 

从数据库获取照片并保存到文件中

 

Imports System.Configuration
Imports System.Data.SqlClient
Imports System.IO

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object,
                              ByVal e As System.EventArgs) Handles Button1.Click
        Const employeeIdColumn As Integer = 0
        Const employeePhotoColumn As Integer = 1
        'bufferSize must be bigger than oleOffset
        Const bufferSize As Integer = 100
        Dim buffer(bufferSize) As Byte
        Dim byteCountRead As Integer
        Dim currentIndex As Long = 0

        Dim nwSetting As ConnectionStringSettings =
            ConfigurationManager.ConnectionStrings("NwString")
        Using cn As New SqlConnection
            cn.ConnectionString = nwSetting.ConnectionString
            cn.Open()

            Using cmd As SqlCommand = cn.CreateCommand
                cmd.CommandText = "SELECT EmployeeID, Photo FROM Employees"
                Dim rdr As SqlDataReader =
                    cmd.ExecuteReader(CommandBehavior.SequentialAccess)
                While rdr.Read
                    Dim employeeId As Integer = rdr.GetInt32(employeeIdColumn)
                    Dim fileName As String = "c:\employee" &
                        employeeId.ToString.PadLeft(2, "0"c) & ".bin"
                    'create a file to hold the output.
                    Using fs As New FileStream(
                        fileName, FileMode.OpenOrCreate, FileAccess.Write)
                        currentIndex = 0
                        byteCountRead = CInt(rdr.GetBytes(
                                employeePhotoColumn, currentIndex, buffer, 0, bufferSize))
                        While byteCountRead <> 0
                            fs.Write(buffer, 0, byteCountRead)
                            currentIndex += byteCountRead
                            byteCountRead = CInt(rdr.GetBytes(
                                    employeePhotoColumn, currentIndex, buffer, 0, bufferSize))
                        End While
                    End Using
                End While
            End Using
        End Using
        MessageBox.Show("Done")
    End Sub
End Class

用字节数组将数据写入文件

Private Sub Button2_Click(ByVal sender As System.Object,
                      ByVal e As System.EventArgs) Handles Button2.Click
    Const oleOffset As Integer = 78
    Const oleTypeStart As Integer = 20
    Const oleTypeLength As Integer = 12

    Const employeeIdColumn As Integer = 0
    Const employeePhotoColumn As Integer = 1
    Const bufferSize As Integer = 100 'bufferSize must be bigger than oleOffset
    Dim buffer(bufferSize) As Byte
    Dim bufferStart As Integer = 0
    Dim byteCountRead As Integer
    Dim currentIndex As Long = 0

    Dim nwSetting As ConnectionStringSettings =
        ConfigurationManager.ConnectionStrings("NwString")
    Using cn As New SqlConnection
        cn.ConnectionString = nwSetting.ConnectionString
        cn.Open()

        Using cmd As SqlCommand = cn.CreateCommand
            cmd.CommandText = "SELECT EmployeeID, Photo FROM Employees"
            Dim rdr As SqlDataReader =
                cmd.ExecuteReader(CommandBehavior.SequentialAccess)
            While rdr.Read
                Dim employeeId As Integer = rdr.GetInt32(employeeIdColumn)
                Dim fileName As String = "c:\employee" &
                    employeeId.ToString.PadLeft(2, "0"c) & ".bmp"
                'create a file to hold the output.
                Using fs As New FileStream(
                    fileName, FileMode.OpenOrCreate, FileAccess.Write)
                    currentIndex = 0
                    'read until we have the oleheader, if possible
                    While currentIndex < oleOffset
                        byteCountRead = CInt(
                            rdr.GetBytes(employeePhotoColumn, currentIndex,
                                         buffer, CInt(currentIndex), bufferSize - CInt(currentIndex)))
                        If byteCountRead = 0 Then Exit While
                        currentIndex += byteCountRead
                    End While
                    byteCountRead = CInt(currentIndex)
                    'process oleheader, if it exists
                    If byteCountRead >= oleOffset Then
                        Dim type As String = Encoding.ASCII.GetString(
                            buffer, oleTypeStart, oleTypeLength)
                        If type = "Bitmap Image" Then
                            bufferStart = oleOffset
                            byteCountRead = byteCountRead - oleOffset
                        End If
                    End If

                    While byteCountRead <> 0
                        fs.Write(buffer, bufferStart, byteCountRead)
                        bufferStart = 0
                        byteCountRead = CInt(
                            rdr.GetBytes(employeePhotoColumn, currentIndex, buffer, 0, bufferSize))
                        currentIndex += byteCountRead
                    End While
                End Using
            End While
        End Using
    End Using
    MessageBox.Show("Done")
End Sub

 

14.1.4 写入BLOB数据

 

Private Sub Button3_Click(ByVal sender As System.Object,
                          ByVal e As System.EventArgs) Handles Button3.Click
    Const bufferSize As Integer = 100
    Dim buffer(bufferSize) As Byte
    Dim currentIndex As Long = 0
    Dim photoPtr() As Byte
    Dim nwSetting As ConnectionStringSettings =
        ConfigurationManager.ConnectionStrings("NwString")
    Using cn As New SqlConnection
        cn.ConnectionString = nwSetting.ConnectionString
        cn.Open()
        Using cmd As SqlCommand = cn.CreateCommand
            cmd.CommandText = "SELECT TEXTPTR(Photo) FROM Employees WHERE EmployeeId=1"
            photoPtr = CType(cmd.ExecuteScalar, Byte())
        End Using
        Using cmd As SqlCommand = cn.CreateCommand
            cmd.CommandText = "UPDATETEXT Employees.Photo @Pointer @Offset null @Data"
            Dim ptrParm As SqlParameter = cmd.Parameters.Add("@Pointer", SqlDbType.Binary, 16)
            ptrParm.Value = photoPtr
            Dim photoParm As SqlParameter = cmd.Parameters.Add("@Data", SqlDbType.Image)
            Dim offsetParm As SqlParameter = cmd.Parameters.Add("@Offset", SqlDbType.Int)
            offsetParm.Value = 0
            Using fs As New FileStream("Girl.gif", FileMode.Open, FileAccess.Read)
                Dim count As Integer = fs.Read(buffer, 0, bufferSize)
                While count <> 0
                    photoParm.Value = buffer
                    photoParm.Size = count
                    cmd.ExecuteNonQuery()
                    currentIndex += count
                    offsetParm.Value = currentIndex
                    count = fs.Read(buffer, 0, bufferSize)
                End While
            End Using
        End Using
    End Using
    MessageBox.Show("Done")
End Sub