Is there a recommended common approach for reading and writing arrays of Bytes t

[Burton G. Wilkins]

Gentlemen:

I am building a general tool that should be able to read
and write all SQL Server datatypes. Presently, I am
working on that section of the program that reads and
writes Images. Going to the helps that come with .Net, I
find that Image map to an array of Bytes. Checking other
SQL DataTypes, I find that there are other SQL DataTypes
which map to an array of Bytes as well. Those datatypes
that apparently map to an array of Bytes are the
following: Binary, Image, TimeStamp, and VarBinary. My
first question, are all four of these datatypes considered
BLOBs? Are their other SQL Datatypes besides these four
that would map to arrays of Bytes, and are considered
Blobs?

Now, going to MSDN (Article 317044), I find that to read
or write an Image, one uses "the Microsoft SQL Server
READTEXT and UPDATETEXT statements to read and write data
from BLOB (LongVarBinary) columns in a database table."
The article further states that "Unlike with ADO 2.6 and
later, ADO.NET does not support reading and writing BLOB
objects by using Stream objects. ADO.NET data providers do
not have GetChunk and AppendChunk methods available to the
Data Access Object (DAO) and ActiveX Data Objects (ADO)
Recordset objects. To read a BLOB field for in-memory
manipulation, you can use a DataReader object to select
the row, or you can cache the data in a DataSet.
However, if you want to stream the data to a different
medium, such as disk or Web response, then you can read
the BLOB from the server in smaller chunks to minimize the
amount of memory that the process consumes."

Now my second question is, besides Images is this ALSO the
suggested approach for reading and writing Binary,
TimeStamp, and VarBinary datatypes to and from SQL
databases, or could one safely read or write Binary,
TimeStamp, and VarBinary with a "SELECT" and "UPDATE" SQL
statement? What then is the recommended approach, the
one that will most likely produce success all the time
reading and writing arrays of Bytes in ADO.Net?

Thank you in advance for answering this three general
question.

Sincerely,

Burton G. Wilkins.

 

[Pablo Castro [MS]]

Answers inline.

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.

Gentlemen:

I am building a general tool that should be able to read
and write all SQL Server datatypes. Presently, I am
working on that section of the program that reads and
writes Images. Going to the helps that come with .Net, I
find that Image map to an array of Bytes. Checking other
SQL DataTypes, I find that there are other SQL DataTypes
which map to an array of Bytes as well. Those datatypes
that apparently map to an array of Bytes are the
following: Binary, Image, TimeStamp, and VarBinary. My
first question, are all four of these datatypes considered
BLOBs?

No, timestamp, binary and varbinary are not "blobs" in that they are not
"large" in the current releases of sql server.

Are their other SQL Datatypes besides these four
that would map to arrays of Bytes, and are considered
Blobs?

blobs and arrays of bytes is not the same thing. For example, Timestamp is a
byte[], but it's not "large".

Now, going to MSDN (Article 317044), I find that to read
or write an Image, one uses "the Microsoft SQL Server
READTEXT and UPDATETEXT statements to read and write data
from BLOB (LongVarBinary) columns in a database table."
The article further states that "Unlike with ADO 2.6 and
later, ADO.NET does not support reading and writing BLOB
objects by using Stream objects. ADO.NET data providers do
not have GetChunk and AppendChunk methods available to the
Data Access Object (DAO) and ActiveX Data Objects (ADO)
Recordset objects. To read a BLOB field for in-memory
manipulation, you can use a DataReader object to select
the row, or you can cache the data in a DataSet.
However, if you want to stream the data to a different
medium, such as disk or Web response, then you can read
the BLOB from the server in smaller chunks to minimize the
amount of memory that the process consumes."

Now my second question is, besides Images is this ALSO the
suggested approach for reading and writing Binary,
TimeStamp, and VarBinary datatypes to and from SQL
databases, or could one safely read or write Binary,
TimeStamp, and VarBinary with a "SELECT" and "UPDATE" SQL
statement?

Do READTEXT for Image (and text/ntext) only, the other types won't even
work. Also, the other types (binary, varbinary and timestamp) are never
really big, so it's not worth an extra round trip.

What then is the recommended approach, the
one that will most likely produce success all the time
reading and writing arrays of Bytes in ADO.Net?

If you want to be able to handle large pieces of data as well as small ones,
one good approach is to specific CommandBehavior.SequentialAccess in the
call to SqlCommand.ExecuteReader. Later on, when accessing the blob column,
use SqlDataReader.GetBytes instead of GetValue or the indexer ([] in C#, ()
in VB). GetBytes lets you get a chunk at a time, without the need to buffer
the whole value in memory. You can send each chunk to a file or web response
before fetching the next one, efficiently using memory.

This approach is better than using TEXTPTR/READTEXT when you're going to
bring down from the server the entire value for the BLOB because it doesn't
require multiple round-trips. If you just need a small part of a large field
value, TEXTPTR/READTEXT might be better.