Update IMAGE columns in SQL Server

[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.

 

[Robbe Morris [C# MVP]]

http://www.eggheadcafe.com/articles/20030624.asp
http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=799

 

[Kevin Yu [MSFT]]

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."

 

[Rob Nicholson]

http://www.eggheadcafe.com/articles/20030624.asp

That code throws the entire file around as a single byte array - which was
what worried me with large files.

Rob.

 

[Rob Nicholson]

Please check the link that Robbe has provided, which gives a complete

solution for updating database image fields.

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.

 

[Robbe Morris [C# MVP]]

If you disassemble the .NET Framework (using Lutz's Reflector), you'll
find everything sent to Sql Server via stored procedures eventually
gets converted to a byte array prior to getting sent.

Read the latter portion of this article and skip the sql injection stuff.
It also contains a link to Reflector.

http://www.eggheadcafe.com/articles/adonet_exec_stored_procedures_sqlhelper.asp

--
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.masterado.net/home/listings.aspx

 

[Kevin Yu [MSFT]]

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."

 

[Rob Nicholson]

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.

 

[Kevin Yu [MSFT]]

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."