The default behavior of DataReader is to load incoming data in the form of rows throughout the data line. However, for binary large objects (BLOB), different processing is required because they may contain billions of bytes of data, and so many data cannot be included in single rows. The Command.executeReader method has an overload that will use the Commandbehavior parameter to modify the DataReader's default behavior. You can pass CommandBehavior.SEQUENTIALALALALAndBehavior.SequentialAlaseReader method to modify DataReader's default behavior so that DataRead is loaded immediately when receiving data in order, rather than loading data lines. This is an ideal solution to load BLOB or other big data structures. Note that this behavior may vary depending on the data source. For example, returning BLOB from Microsoft Access will cause the entire BLOB to be loaded into memory instead of loading data in order to receive data.
When setting the DataReader to use SequentialAaCcess, be sure to pay attention to the order of the returned fields. DataReader's default behavior is to load the line immediately when the entire row is available, allowing you to access the returned fields in any order before reading the next line. However, when using SequentialAlaccess, you must access the different fields returned by DataReader in order. For example, if the query returns three columns, where the third column is BLOB, the value of the first and second fields must be returned before the BLOB data accessed in the third field. If you access the third field before accessing the first or second field, the first and second field values will no longer be available. This is because SequentialAracse has modified DataReader to return data in order, which will not be available when DataReader has read more than specific data.
When accessing data in the blob field, use the DataReader Gettes or getChars Type Accessor, they will use data to populate arrays. You can also use GetString for character data, but in order to save system resources, you may not want to load the entire BLOB value into a single string variable. You can specify the specific data buffer size to return, and the starting position of the first byte or character read from the returned data. GetTes and getchars will return a long value that represents the number of bytes or characters returned. If you pass an empty array to getBytes or getChars, the returned length will be the total number of characters or characters in BLOB. You can choose to specify an index in the array as the starting position of the read data.
The following example returns the issuer ID and logo from the PUBS sample database in Microsoft SQL Server. The issuer ID (PUB_ID) is a character field, while the logo is graphic, namely BLOB. Since the logo field is a bitmap, this example returns binary data using GetBytes. Note that since the field must be accessed in order, the issuer ID of the current data line will be accessed before accessing the logo.
[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 = 100 'The size of the BLOB buffer.Dim outbyte (bufferSize - 1) AS BYTE 'The blob byte () Buffer to be filled by getBytes.dim Retval as long' The bytes returned from getBytes.dim startINDEX As long = 0 '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 keto the datareader.pubsconn.open () Dim MyReader as SqldataReader = logocmd.executeReader (Commandbehavior.SequentialAlaccess)
Do while myreader.read () 'get the public g d Getting the logo. Pub_id = myReader.getstring (0)
'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 = 0
'Read bytes Into Outbyte () and retain the number of bytes returned. RetVal = MyReader.getbytes (1, StartIndex, Outbyte, 0, Buffersize)
'Continue Reading and Writing While there is bytes beyond the size of the buffer. Do while = buffersize bw.write (outbyte) bw.flush ()
'Reposition the start index to the end buffer and fill the buffer. StartIndex = buffersize retval = myreader.getbytes (1, StartIndex, Outbyte, 0, Buffersize) Loop
'Write the remaining buffer. Bw.write (Outbyte, 0, Retval - 1) 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 = 100; // size of the blob buffer.byte [] outbyte = new byte [buffersize]; // the blob byte [] buffer to be filled by getBytes.long return, // the bytes returned from getBytes.long STARTINDEX = 0; // the starting position in the blob output.
String pub_id = ""; // The public name.
// open the connection and read data inTo the datareader.pubsconn.open (); sqldataareader myreader = logocmd.executeReader (Commandbehavior.SequentialAlaccess);
While (MyReader.Read ()) {// Get The Publisher ID, Which Must Occur Before getting the logo. pub_id = myreader.getstring (0);
// 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 = 0;
// read the bytes Into Outbyte [] and retain the number of bytes returned. Retval = MyReader.getbytes (1, StartIndex, Outbyte, 0, Buffers);
// Continue Reading and Writing While there is 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 (1, startIndex, outbyte, 0, buffersize);
// Write the remaining buffer. Bw.write (Outbyte, 0, (int) RetVal - 1); bw.flush ();
// close the output file. Bw.close (); fs.close ();
// close the reader and the connection.myreader.close (); pubsconn.close ();
