Update IMAGE columns in SQL Server

  • Thread starter Thread starter Rob Nicholson
  • Start date Start date
R

Rob Nicholson

We need to update an image stored in a SQL server. I understand that the
basic code *could* go something like:

BEGIN
Load the image from the file into a byte array
Create a SqlConnection
Create a SqlCommand and specify the command text as "Update [People] Set
[Photo]=@Photo Where [PersonID]=2"
Add a parameter to the command for @Photo declared at SqlBinary and using
the byte array as the data source
Fire off a ExecuteNonQuery
END

The question is, for SqlBinary data type, does ADO.NET in effect convert the
entire byte array into a very long hex string and in effect, something like
the following is passed to the SQL Server:

Update [People] Set
[Photo]=0x56856856856856985968596895865656563ABCD4545DFEA8884 (etc) Where
[PersonID]=2

If so, I guess this method should only be used for relatively small files?

Does the same happen on loading as well as when you dig into a data reader
or data set, the column is a byte array.

Cheers, Rob.
 
Thanks for Robbe's quick response.

Hi Rob,

As far as I know, when assign value to the @Photo parameter, we needn't use
SqlBinary type. SqlDbType.Image is the right type for the parameter. The
code looks something like the following:

cmd.Parameters.Add("@Photo", SqlDbType.Image, size)

Please check the link that Robbe has provided, which gives a complete
solution for updating database image fields.

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Please check the link that Robbe has provided, which gives a complete
solution for updating database image fields.
Click to expand...

My question was not how to write the code (which I know how to) but *how*
does ADO.NET implement this update? It only has access AFAIK to the standard
T-SQL commands. Is this is correct, then what T-SQL is it generating? Is it
a very long (and inefficient) hex string in a standard Update
Set
Photo=0x47474848484...

Or does ADO.NET have a more efficient mechanism for writing large images via
UPDATETEXT or something similar. I suspect not as I've use the profiler to
watch the low-level communication between the and noticed bog standard
Update
Set Photo=0xlonghexstring in the log.

My reason for asking is *if* somebody tried to upload a 1GB file to the
database via this mechanism (daft thing to do but hey, you know users), then
a temporary 1GB binary array would have to be created in memory and then
passed to the SQL command as a parameter. I wouldn't be surprised if ADO.NET
then tried to build a 2GB hex string in memory.

You can see the problem if the web server has 1GB of memory with 1GB swap
file...

The code link given also uses a single binary array with a data reader. This
is fine again for small files. However, DataReader.GetBytes is a more
efficient (memory wise) way of reading an image into a local file in small
chunks. However, I'm still uncertain as to whether ADO.NET has already read
the entire 1GB file into the data reader and you're just reading small
chunks out of an already memory based array.

However, there is no equivalent DataWriter.PutBytes function. In fact, there
is no DataWriter at all so you have to use either a DataSet or SqlCommand.
Both of these require a single byte array of the entire image/file.

Hmm, thinking aloud, it should be possible to write an SPROC to use the
WRITETEXT and UPDATETEXT T-SQL to write the file in smaller chunks. I've
never wandered into these but I believe they are non-logged - does this mean
that you can't use transactions with them?

Cheers, Rob.
 
Hi Rob,

As you can see, the SqlCommand can execute any T-SQL statements. So we can
also write an SPROC to use the WRITETEXT and UPDATETEXT to change only a
portion of the field. Just use a parameter to represent the portion and
pass the byte array to the parameter.

Is that what you need?

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Is that what you need?

No, the question still is how does ADO.NET at the low level write a binary
array field (in a dataset)/parameter (in a command) to the database. I'll
try and simply the question as much as I can:

1. Does it generate a single (huge) Update
Set
[ImageColumn]=0x123456...
2. Does it use WRITETEXT and UPDATETEXT mechanism in smaller chunks

If (1) then developers have to be aware that this should only be used for
small files and that if they want to upload large documents, then it may be
better to roll-your-own and use WRITETEXT/UPDATETEXT.

Of course, it's questionable as to whether users should be stored huge files
in database columns :-)

Cheers, Rob.
 
Hi Rob,

If you're using the SqlDataAdapter configuration wizard to generate all the
statements for a DataAdapter, yes, it is generating an UPDATE statement.
But the value of each column is not put in the command text directly. They
are passed through parameters.

If the developers are aware that a column might be storing large binary
data, we can write our own statement for the UpdateCommand with WRITETEXT
and UPDATETEXT to write large binary data to database in smaller chunks.

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top