DataReader 的默認行為是在整個數據行可用時立即以行的形式加載傳入數據
但是
對於二進制大對象 (BLOB) 則需要進行不同的處理
因為它們可能包含數十億字節的數據
而單個行中無法包含如此多的數據
Command
ExecuteReader 方法具有一個重載
它將采用 CommandBehavior 參數來修改 DataReader 的默認行為
您可以將 CommandBehavior
SequentialAccess 傳遞到 ExecuteReader 方法來修改 DataReader 的默認行為
以便讓 DataReader 按照順序在接收到數據時立即將其加載
而不是加載數據行
這是加載 BLOB 或其他大數據結構的理想方案
在將 DataReader 設置為使用 SequentialAccess 時
務必要注意訪問所返回字段的順序
DataReader 的默認行為是在整個行可用時立即加載該行
這使您能夠在讀取下一行之前按任何順序訪問所返回的字段
但是
當使用 SequentialAccess 時
必須按順序訪問由 DataReader 返回的不同字段
例如
如果查詢返回三個列
其中第三列是 BLOB
則必須在訪問第三個字段中的 BLOB 數據之前返回第一個和第二個字段的值
如果在訪問第一個或第二個字段之前訪問第三個字段
則第一個和第二個字段值將不再可用
這是因為 SequentialAccess 已修改 DataReader
使其按順序返回數據
當 DataReader 已經讀取超過特定數據時
該數據將不可用
當訪問 BLOB 字段中的數據時
請使用 DataReader 的 GetBytes 類型化訪問器
該訪問器將使用二進制數據填充 byte 數組
您可以指定要返回的特定數據緩沖區大小以及從返回的數據中讀取的第一個字節的起始位置
GetBytes 將返回 long 值
它表示所返回的字節數
如果向 GetBytes 傳遞空的 byte 數組
所返回的長值將是 BLOB 中字節的總數
您可以選擇將字節數組中的某索引指定為所讀取數據的起始位置
以下示例從 Microsoft SQL Server 中的 pubs 示例數據庫中返回發行者 ID 和徽標
發行者 ID (pub_id) 是字符字段
而徽標則是圖形
即 BLOB
請注意
由於必須按順序訪問字段
所以將在訪問徽標之前訪問當前數據行的發行者 ID
[Visual Basic]
Dim pubsConn As SqlConnection = New SqlConnection(Data Source=localhost;Integrated Security=SSPI;Initial Catalog=pubs;)
Dim logoCMD As SqlCommand = New SqlCommand(SELECT pub_id
logo FROM pub_info
pubsConn)
Dim fs As FileStream
Writes the BLOB to a file (*
bmp)
Dim bw As BinaryWriter
Streams the binary data to the FileStream object
Dim bufferSize As Integer =
The size of the BLOB buffer
Dim outbyte(bufferSize
) As Byte
The BLOB byte() buffer to be filled by GetBytes
Dim retval As Long
The bytes returned from GetBytes
Dim startIndex As Long =
The starting position in the BLOB output
Dim pub_id As String =
The publisher id to use in the file name
Open the connection and read data into the DataReader
pubsConn
Open()
Dim myReader As SqlDataReader = logoCMD
ExecuteReader(CommandBehavior
SequentialAccess)
Do While myReader
Read()
Get the publisher id
which must occur before getting the logo
pub_id = myReader
GetString(
)
Create a file to hold the output
fs = New FileStream(logo & pub_id &
bmp
FileMode
OpenOrCreate
FileAccess
Write)
bw = New BinaryWriter(fs)
Reset the starting byte for a new BLOB
startIndex =
Read bytes into outbyte() and retain the number of bytes returned
retval = myReader
GetBytes(
startIndex
outbyte
bufferSize)
Continue reading and writing while there are bytes beyond the size of the buffer
Do While retval = bufferSize
bw
Write(outbyte)
bw
Flush()
Reposition the start index to the end of the last buffer and fill the buffer
startIndex = startIndex + bufferSize
retval = myReader
GetBytes(
startIndex
outbyte
bufferSize)
Loop
Write the remaining buffer
bw
Write(outbyte)
bw
Flush()
Close the output file
bw
Close()
fs
Close()
Loop
Close the reader and the connection
myReader
Close()
pubsConn
Close()
[C#]
SqlConnection pubsConn = new SqlConnection(Data Source=localhost;Integrated Security=SSPI;Initial Catalog=pubs;);
SqlCommand logoCMD = new SqlCommand(SELECT pub_id
logo FROM pub_info
pubsConn);
FileStream fs; // Writes the BLOB to a file (*
bmp)
BinaryWriter bw; // Streams the BLOB to the FileStream object
int bufferSize =
; // Size of the BLOB buffer
byte[] outbyte = new byte[bufferSize]; // The BLOB byte[] buffer to be filled by GetBytes
long retval; // The bytes returned from GetBytes
long startIndex =
; // The starting position in the BLOB output
string pub_id = ; // The publisher id to use in the file name
// Open the connection and read data into the DataReader
pubsConn
Open();
SqlDataReader myReader = logoCMD
ExecuteReader(CommandBehavior
SequentialAccess);
while (myReader
Read())
{
// Get the publisher id
which must occur before getting the logo
pub_id = myReader
GetString(
);
// Create a file to hold the output
fs = new FileStream(logo + pub_id +
bmp
FileMode
OpenOrCreate
FileAccess
Write);
bw = new BinaryWriter(fs);
// Reset the starting byte for the new BLOB
startIndex =
;
// Read the bytes into outbyte[] and retain the number of bytes returned
retval = myReader
GetBytes(
startIndex
outbyte
bufferSize);
// Continue reading and writing while there are bytes beyond the size of the buffer
while (retval == bufferSize)
{
bw
Write(outbyte);
bw
Flush();
// Reposition the start index to the end of the last buffer and fill the buffer
startIndex+= bufferSize;
retval = myReader
GetBytes(
startIndex
outbyte
bufferSize);
}
// Write the remaining buffer
bw
Write(outbyte);
bw
Flush();
// Close the output file
bw
Close();
fs
Close();
}
// Close the reader and the connection
myReader
Close();
pubsConn
Close();
From:http://tw.wingwit.com/Article/program/net/201311/12026.html