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>
<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
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
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
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