WPU SQLite.Net.SQLiteConnection.Query encoding issue

Question

I have a encoding problem with the Query of SQLite.Net. Everything works fine if i only use the column names in the SQL String, but if i write the SQL on my own, every special char like ä,ü,ö,ß will not be encoded correctly. Here are two easy examples, one working, one not.

   public class   ass   {
        [PrimaryKey, AutoIncrement]
        public int _id { get; set; }
        [MaxLength(255)]
        public string sortname { get; set; }
    }

    dbConn = new SQLiteConnection(new SQLitePlatformWinRT("testpasswort"),DB_PATH);
    dbConn.CreateTable<ass>(SQLite.Net.Interop.CreateFlags.None);

    //add a test entry with special chars
    ass asss = new ass();
    asss.sortname = "oe=öae=äszett=ß";
    dbConn.Insert(asss);

    //now select the test entry to an ass object
    List<ass> getass =  dbConn.Table<ass>().ToList<ass>();
    //the list is filled and sortname = "oe=öae=äszett=ß"   

    //now fake a object with
    List<ass> sqlass = dbConn.Query<ass>("SELECT 'oe=öae=äszett=ß' as sortname FROM ass").ToList<ass>();
    //the List is filled and sortname = "oe=�ae=�szett=�"

I know the query is useless and the following will work:

List<ass> sqlass = dbConn.Query<ass>("SELECT sortname as FROM ass").ToList<ass>();

But the problem is, that the .Query funktion have a encoding issue, this will NOT work:

List<ass> sqlass = dbConn.Query<ass>("SELECT sortname FROM ass WHERE sortname LIKE '%ä%'").ToList<ass>();

But this will work:

List<ass> sqlass = dbConn.Query<ass>("SELECT sortname FROM ass).ToList<ass>().Where(v => v.sortname.Contains("ä"));

everytime i have any special char in the sqlcode it will not work, this is fatal for my needs, because i have a lot of replace(column,find,replace) statements and all of them failed if the find or replace String contains any ü,ö,ä [...]

Did anyone know how to sove this?

Did you try to use parameters something like this List<ass> sqlass = dbConn.Query<ass>("SELECT sortname FROM ass WHERE sortname LIKE ?", @"ä").ToList<ass>(); — jomsk1e
– jomsk1e, Commented May 20, 2016 at 15:54

Ton Plooij · Accepted Answer · 2016-05-20 15:19:44Z

1

+200

A possible solution would be to use @params instead of direct string request. And use the UTF-8 encoding pragma, which you can also use to check your existing database encoding. A helpful description for this issue can be found here.

answered May 20, 2016 at 15:19

Ton Plooij

2,65114 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

WPU SQLite.Net.SQLiteConnection.Query encoding issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related