Author: Unknown, please contact me
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. 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 DataRead's Gettes Type Accessor that populates the Byte array using binary data. You can specify the starting position of the specific data buffer to return and the first byte read from the returned data. GetTes will return the long value that represents the number of bytes returned. If an empty BYTE array is passed to GetBytes, the returned length will be the total number of bytes in the BLOB. You can choose to specify an index in the byte 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. 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 ( "; Integrated Security = SSPI; Initial Catalog = pubs; Data Source = localhost") SqlConnection 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 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 (0) 'CREATE A FiLe to Hold The Output. fs = new filestream ("logo" & pub_id & ".bmp", filemode.openorcreate, filemode.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 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 (1, StartIndex, Outbyte, 0, Buffersize) loop 'Write the remaining buffer. bw.write (outbyte) bw.flush ()' close the output file. bw.close () fs.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 retval; // the bytes returned from getBytes. Long startIndex = 0; ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////> Reader = logoCMD.ExecuteReader (CommandBehavior.SequentialAccess); 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 return. Retval = MyReader.getbytes (1, StartIndex, Outbyte, 0, Buffersize);
// 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); bw.Flush (); // Close the output File. Bw.close (); fs.close ();} // close the reader and the connection. MyReader.close (); pubsconn.close ();
