DataReader 的默认行为是在整个数据行可用时立即以行的形式加载传入数据但是对于二进制大对象 (BLOB) 则需要进行不同的处理因为它们可能包含数十亿字节的数据而单个行中无法包含如此多的数据CommandExecuteReader 方法具有一个重载它将采用 CommandBehavior 参数来修改 DataReader 的默认行为您可以将 CommandBehaviorSequentialAccess 传递到 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
pubsConnOpen()
Dim myReader As SqlDataReader = logoCMDExecuteReader(CommandBehaviorSequentialAccess)
Do While myReaderRead()
Get the publisher id which must occur before getting the logo
pub_id = myReaderGetString()
Create a file to hold the output
fs = New FileStream(logo & pub_id & bmp FileModeOpenOrCreate FileAccessWrite)
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 = myReaderGetBytes( startIndex outbyte bufferSize)
Continue reading and writing while there are bytes beyond the size of the buffer
Do While retval = bufferSize
bwWrite(outbyte)
bwFlush()
Reposition the start index to the end of the last buffer and fill the buffer
startIndex = startIndex + bufferSize
retval = myReaderGetBytes( startIndex outbyte bufferSize)
Loop
Write the remaining buffer
bwWrite(outbyte)
bwFlush()
Close the output file
bwClose()
fsClose()
Loop
Close the reader and the connection
myReaderClose()
pubsConnClose()
[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
pubsConnOpen();
SqlDataReader myReader = logoCMDExecuteReader(CommandBehaviorSequentialAccess);
while (myReaderRead())
{
// Get the publisher id which must occur before getting the logo
pub_id = myReaderGetString();
// Create a file to hold the output
fs = new FileStream(logo + pub_id + bmp FileModeOpenOrCreate FileAccessWrite);
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 = myReaderGetBytes( startIndex outbyte bufferSize);
// Continue reading and writing while there are bytes beyond the size of the buffer
while (retval == bufferSize)
{
bwWrite(outbyte);
bwFlush();
// Reposition the start index to the end of the last buffer and fill the buffer
startIndex+= bufferSize;
retval = myReaderGetBytes( startIndex outbyte bufferSize);
}
// Write the remaining buffer
bwWrite(outbyte);
bwFlush();
// Close the output file
bwClose();
fsClose();
}
// Close the reader and the connection
myReaderClose();
pubsConnClose();
