How to save a filename with Chinese characters in SQL Server - Varchar(100)?

Question

I have filename like this: 一個例子.pdf

I want to save the filename in a SQL Server table and read it from the SQL Server table. How do you do that?

This is the code:

class Bestand
{
        public void Add(string filePath, DateTime fileDate, string fileName = "")
        {
            Bestand bestand = this.CurrentBestand;

            if (filePath.Length > 0 && CanReadFile(filePath))
            {
                Binary bin = new Binary();
                bin.Data = new System.Data.Linq.Binary(File.ReadAllBytes(filePath));

                bestand.BestandsDatum = fileDate;
                bestand.BestandsNaam = String.IsNullOrEmpty(fileName) ? Path.GetFileName(filePath) : Encoding.UTF8.GetBytes(fileName)[0].ToString();  

                bestand.Binary = bin;
            }
        }

        public void Save(string filePath)
        {
            byte[] buffer = Data.ToArray();
            System.IO.File.WriteAllBytes(filePath, buffer);
        }
…
}

and call this to save the file:

documents[0].Add(beFile.Value, dtpDate.Value);

and call this to open the file:

public static void ViewBestand(IBestand bestand)
{
    string orgFilepath = Path.Combine(TempDocumentFolder, bestand.FileName);
    string filepath = orgFilepath;

    int tmpCounter = 0;
    while (File.Exists(filepath) && tmpCounter < 100)
    {
        tmpCounter++;
        filepath = Path.Combine(TempDocumentFolder, Path.GetFileNameWithoutExtension(orgFilepath) + "_" + tmpCounter.ToString() + Path.GetExtension(orgFilepath));
    }

    bestand.Save(filepath);
    ViewFile(filepath);
}

Why `VARCHAR` and not `NVARCHAR`? The latter is made for UNICODE character storage. — Lloyd, Oct 04 '17 at 12:42
Because it is decided and I want to save it in a field BestandsNaam. — user1531040, Oct 04 '17 at 12:44
Is there something to encode / decode to write and read these characters in sql server from a win application? — user1531040, Oct 04 '17 at 12:46
Don't try to work around it, use `NVARCHAR`. Your client code won't need to change at all because C# is already sending the string over as `NVARCHAR` -- strings in .NET are always Unicode. — Jeroen Mostert, Oct 04 '17 at 12:55
There is no "workaround". You can't save Chinese characters into a varchar field. You need to change the field to nvarchar in the *database* if you want to store Chinese characters in that field. Not change your C# application. — Jacob H, Oct 04 '17 at 13:03

score 5 · Accepted Answer · answered Oct 04 '17 at 12:44

5

Change the type of your column to nvarchar(100). varchar cannot store Unicode characters, cause it's single byte, Unicodeis double byte.

How to save a filename with Chinese characters in SQL Server - Varchar(100)?

1 Answers1