C# and MySQL

[Evgeni57]

Hi all. I have some issue with data encoding. I have a txt file, which was open like this: StreamWriter m_ComissionShopWriter = new StreamWriter(Directory.GetCurrentDirectory() + "\\logs\\1.txt", false, Encoding.GetEncoding(1251)); then I user WriteLine method to put there some data, which was taken from some stream: sr = new StreamReader(stream, Encoding.GetEncoding(1251)); After, I try to load it into table, which has the following collumn: varchar(100) cp1251_general_ci Insertion goes great! But when I run select query on it, I got some junk data instead of readable, 1251 encoded, data. What is wrong, and how can I fix it? Thanks!

[Luc Pattyn]

When you open a stream with a specific encoding, it applies to the stream, not the data in memory; in .NET strings in memory are using Unicode no matter what. So you are reading/writing Unicode strings from/to the database, which requires nvarchar fields. If you insist on having a particular encoding in the database, then you must read/write byte arrays, which you convert to/from Unicode strings using Encoding.GetString()/GetBytes(). :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

---

I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that.

---

[Evgeni57]

Well, I tried it but it didn't work. Here is a part of my code, can you tell me, please, what I did wrong?

        MySqlConnection connection = new MySqlConnection(MyConString);
        MySqlCommand command = connection.CreateCommand();
        try
        {
            connection.Open();
            Console.WriteLine("Connected to DB");
        }
        catch (Exception e)
        {
            Console.WriteLine("Failed to connect to DB");
            return;
        }
        command.CommandText = "truncate table test\_tb";
        command.ExecuteNonQuery();
        connection.Close();


        StreamReader sr = new StreamReader("somefile.txt", Encoding.GetEncoding(1251));
        string line, query;
        string\[\] data;
        byte\[\] temp = new byte\[10000\];
        int j = 0;
        while ((line = sr.ReadLine()) != null && j < 10)
        {
            data = line.Split('~');

            query = "insert into test\_tb (name, id) values(N'" + data\[2\] + "'," + j.ToString() + ")";
            /\*Some parsing was done here, data\[2\] contains 1251 encoded string\*/
            temp = ASCIIEncoding.GetEncoding(1251).GetBytes(query);
            command.CommandText = ASCIIEncoding.GetEncoding(1251).GetString(temp);
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
            ++j;
        }

The column in table os varchar(1000) type, with windows-1251 encoding thanks a lot.