how do i insert html file that have hebrew text and read it back in sql server 2008 using the filestream option ?

Question

hello all,

i am new to the filestream option in sql server 2008, but i have already understand how to open this option and how to create a table that allow you to save files. let say my table contains: id,name, filecontent

i tried to insert an html file (that has hebrew chars/text in it) to this table. i'm writing in asp.net (c#), using visual studio 2008.

but when i tried to read back the content , hebrew char becomes '?'.

the actions i took were:

1. i read the file like this:

        // open the stream reader
        System.IO.StreamReader aFile = new StreamReader(FileName,   System.Text.UTF8Encoding.UTF8);

        // reads the file to the end
        stream = aFile.ReadToEnd();

        // closes the file
        aFile.Close();

return stream; // returns the stream

1. i inserted the 'stream' to the filecontent column as binary data.

2. i tried to do a 'select' to this column and the data did return (after i coverted it back to string) but hebrew chars become '?'

how do i solve this problem ? what I should pay attention to ?

thanks, gadym

Answer 1

Looks like the UTF8 encoding may not work with the Hebrew?

See here for an older discussion: http://social.msdn.microsoft.com/Forums/en-US/csharplanguage/thread/73c81574-b434-461f-b766-fb9d0e4353c7

sr = new StreamReader(fs, Encoding.GetEncoding("windows-1255"));

Alternatively, are you sure the file is encoded in UTF8?

Also, FILESTREAM may actually perform worse if the BLOB is under 1MB, and HTML files I would expect to fit that description. Have you considered NVARCHAR(MAX) instead.

http://blogs.msdn.com/manisblog/archive/2007/10/21/filestream-data-type-sql-server-2008.aspx

Answer 2

hello all, I succeeded to solve this problem. I was wrong , the problem wasnt in the sql server , but in my code, when i transfer it from binary to string and vice versa.

when you need to convert string (that have hebrew chars) to binary you can write the following lines:

System.Text.UTF8Encoding encoding = new System.Text.UTF8Encoding();
//HtmlFile = the file i read as string and now i want to convert it to bytes array.
byte[] ConvertTextToBytesArray = encoding.GetBytes(HtmlFile);

and vice versa :

    string str;
    System.Text.UTF8Encoding enc = new System.Text.UTF8Encoding();
    // result = the binary data i want to convert it back to string
    str = enc.GetString(result);

i used for some reason System.Text.ASCIIEncoding instead of System.Text.UTF8Encoding.

thank you Meff !