Writing jpeg image into a SQL Server 2000 style TEXT field

Question

I have a client who is asking me to import jpeg files into their SQL Server database.

The problem is that their database vendor originally build the product in SQL Server 2000 (or earlier) although their database currently sits on a SQL Server 2008 R2 instance. It appears that their old data looks like it's just being dumped into a big field of datatype text.

Is it possible to read the jpeg file into a MemoryStream then write that directly to the SQL Server database? If so, how would I go about doing this? Or, is there a better way?

Thanks,

Andre

You can read this first: http://stackoverflow.com/questions/3748/storing-images-in-db-yea-or-nay — 2power10, May 21 '13 at 01:49
Better way: convert that column to `VARBINARY(MAX)` - after all, a picture is **binary** data, not text...... — marc_s, May 21 '13 at 04:35

score 1 · Answer 1 · answered May 21 '13 at 01:55

1

It is not cost effective nor efficient to store pictures in the DB your best approach is to store the path to the file.

answered May 21 '13 at 01:55

HectorDZJ

43
5

Hector, While I happen to agree with you, and if I were the architect of a brand new solution I would certainly choose to link instead of embed. However my requirement is to work with a pre-built app and one where the raw contents of the jpeg file are written to a TEXT data type, instead of bulk-loaded COLUMNSTORE to a varchar(max) data type. That being said, is their any way in C# to write the raw contents of a unicode stream directly to database? – Andre Ranieri May 21 '13 at 03:22

score 0 · Accepted Answer · answered May 21 '13 at 14:21

I suppose if you really needed to do this, you can build the byte-stream into a string like this:

        MemoryStream ms = [your stream];
        Byte[] memoryBytes = ms.ToArray();
        StringBuilder sBuilder  = new StringBuilder();

        foreach(byte nextByte in memoryBytes)
            sBuilder.Append((char)nextByte);

        string res = sBuilder.ToString();

Then store it in the database by passing the string. You should try and get some extra work out of your client by offering to update their database structure though ;)

Thanks you for your assistance. I will try this this afternoon and let you know if it works. Cheers! Andre — Andre Ranieri, May 22 '13 at 22:37

Writing jpeg image into a SQL Server 2000 style TEXT field

2 Answers2