The Clob type field in the database appears garbled

Data | The default behavior of database DataReader is to load incoming data as a row as soon as the entire row of data is available. However, for binary large Objects (BLOBs), different processing is required because they can contain billions of of bytes of data and cannot contain so much data in a single row. The Command.ExecuteReader method has an overload that uses the CommandBehavior parameter to modify the default behavior of the DataReader. You can pass commandbehavior.sequentialaccess to the ExecuteReader method to modify the default behavior of DataReader so that DataReader is loaded as soon as the data is received in order. Instead of loading the data rows. This is the ideal scenario for loading blobs or other large data structures. Note that the behavior may vary depending on the data source. For example, returning a blob from Microsoft Access causes the entire blob to be loaded into memory instead of loading the data in the order in which it was received.

When setting DataReader to use SequentialAccess, it is important to be aware of the order in which the fields returned are accessed. The default behavior of DataReader is to load the row immediately when the entire row is available, allowing you to access the fields returned in any order before the next row is read. However, when you use SequentialAccess, you must sequentially access the different fields returned by DataReader. For example, if a query returns three columns, where the third column is a blob, you must return the value of the first and second fields before you access the BLOB data in the third field. If you access the third field before you access the first or second field, the first and second field values are no longer available. This is because SequentialAccess has modified the DataReader to return the data sequentially, and the data is not available when DataReader has read more than a particular data.

When accessing data in a BLOB field, use the DataReader GetBytes or GetChars typed accessors, which populate the array with data. You can also use GetString for character data, but in order to conserve system resources, you may not want to load the entire BLOB value into a single string variable. You can specify the size of the specific data buffer to return and the starting position of the first byte or character read from the returned data. GetBytes and GetChars will return a long value that represents the number of bytes or characters returned. If an empty array is passed to GetBytes or GetChars, the returned long value will be the total number of characters or characters in the BLOB. You can optionally specify an index in the array as the starting position for the data being read.

The following example returns the Publisher ID and logo from the pubs sample database in Microsoft SQL Server. The publisher ID (pub_id) is a character field, and the logo is a graphic, or BLOB. Because the Logo field is a bitmap, the example uses GetBytes to return binary data. Note that because fields must be accessed sequentially, the publisher ID of the current data row is accessed before the logo is accessed.

[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-1) As Byte ' BLOB byte () buffer to is 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 = "" ' 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, 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 are bytes beyond the size of the buffer.
Do While retval = buffersize
Bw. Write (Outbyte)
Bw. Flush ()

' Reposition ' the "start" to "the" last 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 is filled by GetBytes.
Long retval; The bytes returned from GetBytes.
Long StartIndex = 0; The starting position in the BLOB output.

string pub_id = ""; The publisher ID to is 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 (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, 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" to "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 ();