none
retrieving special characters from sqlite in c# RRS feed

  • Question

  • Hi all,

    I have a column in a table in sqlite database, which contains special characters like ä,ö,ü,...

    Text-Encoding in the database is UTF8. I would like to retrieve data from sqlite in c# and show them in console. but I see ? instead of special characters in console. For example insted of Käse I see k?se

    here is my code:

     dt2.Load(readerData);
    
                    
    
                        foreach (DataRow row in dt2.Rows)
                        {
                            Encoding isot = Encoding.GetEncoding("iso-8859-1");
                            Encoding utf8t = Encoding.UTF8;
                            byte[] utfBytest = utf8t.GetBytes(row["column1"].ToString());
                            byte[] isoBytest = Encoding.Convert(utf8t, isot, utfBytest);
                            string msg = isot.GetString(isoBytest);
                            Console.WriteLine(msg);
    
                        }

    Do you have any idea what should I do to solve this problem?


    • Edited by rahaba Saturday, May 11, 2019 12:50 PM
    Saturday, May 11, 2019 12:49 PM

Answers

  • Hi rahaba,

    Thank you for posting here.

    Based on your description, you want to retireve special characters and show it in console.

    You could try the following code.

     static void Main(string[] args)
            {
                SQLiteConnection.CreateFile("MyDatabase.sqlite");
                SQLiteConnection connection = new SQLiteConnection(@"Data Source = MyDatabase.sqlite");
                connection.Open();
                string sql = "CREATE TABLE IF NOT EXISTS highscores (name varchar(20), score int)";
    
                SQLiteCommand command = new SQLiteCommand(sql, connection);
                command.ExecuteNonQuery();
    
                string sqlInsert = "INSERT INTO highscores (name,score) values(@name,@score)";
                command = new SQLiteCommand(sqlInsert, connection);
                command.Parameters.AddWithValue("@name", "Käse");
                command.Parameters.AddWithValue("@score", 3);
                command.ExecuteNonQuery();
    
                SQLiteCommand sqlCom = new SQLiteCommand("Select * From highscores", connection);
    
                SQLiteDataReader sqlDataReader = sqlCom.ExecuteReader();
                while (sqlDataReader.Read())
                {
                    string m = sqlDataReader.GetValue(0).ToString();
                    string n = sqlDataReader.GetValue(1).ToString();
                    Console.WriteLine(m);
                    Console.WriteLine(n);
                }
    
                Console.ReadKey();
    
            }

    Result:

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 13, 2019 6:29 AM
    Moderator

All replies

  • You can change the output encoding of your Console with: 

    Console.OutputEncoding = System.Text.Encoding.UTF8;

    in your code.

    wizend

    Saturday, May 11, 2019 2:37 PM
  • I have tested it but doesnt help
    Saturday, May 11, 2019 2:41 PM
  • Maybe, it doesn't work because you first convert the bytes into UTF-8 and then once more convert them into Latin1. So, you either shouldn't use UTF-8 as output encoding, but Latin1:

    Console.OutputEncoding = Encoding.GetEncoding("ISO-8859-1");

    or you should skip the second converting.

    wizend

    Saturday, May 11, 2019 4:08 PM
  • Thank you but also not the solution :/
    Saturday, May 11, 2019 4:35 PM
  • Did you check if ‘string s = row[“Column1”].ToString()’ contains a valid text using Debugger and breakpoints?

    Saturday, May 11, 2019 5:31 PM
  • It seems to me you got your code snippet from this StackOverflow thread. But that guy had special needs. He wanted to convert the Utf-8 string into  Iso-8859-1 (that's the reason for this line:
    string msg = isot.GetString(isoBytest);
    But you said you only wanted to write the column string out correctly in your Console. When that is correct, you need to modify your code:
    Console.OutputEncoding = Encoding.UTF8;
    foreach (DataRow row in dt2.Rows)
    {
    	//Encoding utf8t = Encoding.UTF8;
    	Console.WriteLine(row["column1"].ToString());
    
    }
    
    
    wizend
    Saturday, May 11, 2019 6:01 PM
  • Did you check if ‘string s = row[“Column1”].ToString()’ contains a valid text using Debugger and breakpoints?

    I have checked it, and s contain also the words with ?. For example K?se instead of Käse
    Saturday, May 11, 2019 7:00 PM
  • Yes you are right, I have my code from there. 

    I have also tested your code, and see in the consol ? for special characters like ä,Ä,Ö,....

    As I said my problem ist the the words is correctly in sqlite, but I can not retrieve them in correct format in c#. I think I have the same problem like stackoverflow post

    Saturday, May 11, 2019 7:04 PM
  • Did you check if ‘string s = row[“Column1”].ToString()’ contains a valid text using Debugger and breakpoints?

    I have checked it, and s contain also the words with ?. For example K?se instead of Käse

    Then maybe the code that reads or writes the text to database is not good. Show some details related to database access. Are you sure that database contains the right letters?


    Saturday, May 11, 2019 7:14 PM
  • I use DB Browser (Sqlite) to see data in my table. Actually I should change the encoding to CP850 to see the correct text in this table:

    Saturday, May 11, 2019 9:08 PM
  • Hi rahaba,

    Thank you for posting here.

    Based on your description, you want to retireve special characters and show it in console.

    You could try the following code.

     static void Main(string[] args)
            {
                SQLiteConnection.CreateFile("MyDatabase.sqlite");
                SQLiteConnection connection = new SQLiteConnection(@"Data Source = MyDatabase.sqlite");
                connection.Open();
                string sql = "CREATE TABLE IF NOT EXISTS highscores (name varchar(20), score int)";
    
                SQLiteCommand command = new SQLiteCommand(sql, connection);
                command.ExecuteNonQuery();
    
                string sqlInsert = "INSERT INTO highscores (name,score) values(@name,@score)";
                command = new SQLiteCommand(sqlInsert, connection);
                command.Parameters.AddWithValue("@name", "Käse");
                command.Parameters.AddWithValue("@score", 3);
                command.ExecuteNonQuery();
    
                SQLiteCommand sqlCom = new SQLiteCommand("Select * From highscores", connection);
    
                SQLiteDataReader sqlDataReader = sqlCom.ExecuteReader();
                while (sqlDataReader.Read())
                {
                    string m = sqlDataReader.GetValue(0).ToString();
                    string n = sqlDataReader.GetValue(1).ToString();
                    Console.WriteLine(m);
                    Console.WriteLine(n);
                }
    
                Console.ReadKey();
    
            }

    Result:

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 13, 2019 6:29 AM
    Moderator