sqlite查询不匹配字符串 [英] sqlite query not matching string

问题描述

我有一个大约350个音乐文件的列表，我存储在sqlite表中。如果添加了新文件，它们将添加到数据库。所以我有一个查询，以检查文件的路径是否在表中，如果没有，然后插入它。然而，3个文件从不匹配查询，即使他们在表中！因此，每次启动我的应用程序时，它们都会一次次添加。

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.

我使用SQLite for Universal Windows Platform& SQLite.Net-PCL包为sql内容。 File是我的自定义类，它存储每个文件中的一些字符串和其他文件属性。

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<Query>();
 }

 //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");
      }
 }

输出每次，从我看到的可能是2个字符，可能会导致这个，但为什么？ - vs - 和ë对e。 sqlite没有装备处理这些字符或者我的查询不够健壮？插入语句工作正常，导致我相信它接受这些字符，因为它显示我的应用程序罚款。

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"

编辑：由chue x解决

edit: solved by chue x

var existingfile = dbconn.Query<File>("SELECT * FROM Track WHERE Path = ?", file.Path.Replace("'", "''")).FirstOrDefault();

推荐答案

由chue x解决

         var existingfile = dbconn.Query<File>("SELECT * FROM File WHERE Path = ?", file.Path.Replace("'", "''")).FirstOrDefault();

这篇关于sqlite查询不匹配字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！