lundi 29 février 2016

sqlite query not matching string

I have a list of about 350 music files that I store in an sqlite table. If new files get added they are added to the db. So I have a query to check if the path of the file is in the table and if not then insert it. However 3 files never match the query even though they are in the table! Therefore they are added over and over each time I start my app.

I am using SQLite for Universal Windows Platform & SQLite.Net-PCL packages for the sql content. File is my custom class that stores some strings and other file properties from each file.

 //database creation on first app launch
 using (var db = DbConnection)
 {
      var Trackdb = db.CreateTable<File>();
 }

 //where I scan all files and add to db if not already there 
 var dbconn = DbConnection;
 foreach (File file in FileList)
 {
      var existingfile = dbconn.Query<Track>("select * from File where Path = '" + file .Path.Replace("'", "''") + "'").FirstOrDefault();
      if (existingtrack == null)
      {
          file.ID = dbconn.Insert(file);
          Debug.WriteLine(file.Path + " was added to db");
      }
 }

The output everytime, from what I see there might be 2 characters which could cause this, but why? "–" vs "-" and "ë" vs "e". Is sqlite not equipped to handle these characters or is my query not robust enough? The insert statement which works fine, leading me to believe it accepts these characters as it displays my my app fine.

"C:\Data\Users\Public\Music\David Guetta & Avicii – Sunshine.mp3 was added to db"
"C:\Data\Users\Public\Music\Gotye - Somebody That I Used To Know (Tiësto Remix).mp3 was added to db"
"C:\Data\Users\Public\Music\Lana Del Rey – Summertime Sadness (Cedric Gervais Remix).mp3 was added to db"

Aucun commentaire:

Enregistrer un commentaire