How to I convert a SQL server image field to text?

Question

0.00/5 (No votes)

See more:

I have a field in a Sql Server database, the data type of this field is an image, apparently the content of this field is text from an email.

How do I extract the readable text if the datatype is an image?

What I have tried:

I have tried many variations of -

SQL -

SQL

select cast(cast(emailCompressed as varbinary(MAX)) as nvarchar(max)) as emailCompressed
from   [DB].[EmailMessage] where emailID = 1

and

C# -

C#

byte[] gzBuffer = (byte[])reader["emailCompressed"];


                        using (MemoryStream ms = new MemoryStream())
                        {
                            int msgLength = BitConverter.ToInt32(gzBuffer, 0);
                            ms.Write(gzBuffer, 0, gzBuffer.Length);

                            byte[] buffer = new byte[msgLength];

                            ms.Position = 0;
                            int length;
                            using (GZipStream zip = new GZipStream(ms, CompressionMode.Decompress))
                            {
                                length = zip.Read(buffer, 0, buffer.Length);
                            }

                            var data = new byte[length];
                            Array.Copy(buffer, data, length);
                            string s = Encoding.UTF8.GetString(data);

                        }

Posted 27-Jan-20 19:30pm

the_smiths

Updated 28-Jan-20 19:31pm

Maciej Los

v2

Add a Solution

Comments

Richard Deeming 28-Jan-20 14:28pm

NB: ntext, text, and image data types[^] will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

That warning's been on Microsoft's documentation for 15 years. It would probably be a good idea to start updating your databases to use the supported types instead. :)

the_smiths 28-Jan-20 17:36pm

I'm completely with you mate. I would never have used an image field myself, but this isn't my database, it's from an old system. Any ideas of a solution? Cheers.

2 solutions

Solution 2

An Image field is just a stream of bytes, so if it contains text, then just convert them in your C# code:

C#

byte[] bytes = ...
string s = System.Text.Encoding.UTF8.GetString(bytes);

The encoding could be ASCII, UTF16. or UTF8 depending on what was used to convert the email body to bytes in the first place.

Posted 27-Jan-20 20:52pm

OriginalGriff

Comments

the_smiths 28-Jan-20 17:49pm

Didn't work I'm afraid, it just comes out garbage, apparently it's zipped which is why I have to use something like GZip.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

the_smiths · Accepted Answer · 2020-01-28T19:33:00

I've managed to get it working with -

byte[] gzBuffer = (byte[])reader["emailCompressed"];

MemoryStream ms1 = new MemoryStream(gzBuffer);

DeflateStream df1 = new DeflateStream(ms1, CompressionMode.Decompress, true);

var sr = new StreamReader(df1);

string res = sr.ReadToEnd();