如何解决SQLite查询的空绑定？ [英] How to resolve null bindings on SQLite query?

问题描述

我使用SQLite库和SQLite.Net查询我的Windows Phone 8.1项目中的现有数据库。到目前为止，到db的连接正在执行正常，但返回的记录为null。

我采取了通常的调试步骤 -

1.检查映射到我的db模式中的名称的绑定类型和名称。

2.验证数据

3.通过查询数据库的代码，发现数据库绑定为空。 （这意味着我在我的POCO中的字段映射的问题）

我没有得到任何编译时错误，返回的记录为null。

问题：
有人知道为数据库提供的绑定有什么问题映射？

当我遍历SQLite.cs类时，发现绑定计数为0：

a href =https://i.stack.imgur.com/ITzp3.png =nofollow noreferrer>

查询代码：

  use（var dbConn = new SQLiteConnection（Path.Combine（ApplicationData.Current.LocalFolder.Path，AppDBPath），true））
 {
 List< ZoneInfo> zoneInfo = dbConn.Query< ZoneInfo>（select * from+ tableName）.ToList< ZoneInfo>（）; 
 ObservableCollection< ZoneInfo> zoneInfoCollection = new ObservableCollection< ZoneInfo>（zoneInfo）; 
 return zoneInfoCollection; 
} 
  

DB映射POCO：

  public class ZoneInfo 
 {
 
 // ObjectId属性标记为主键
 [SQLite.PrimaryKey] 
 [Column（objectId）] 
 public string ObjectId {get;组; } 
 
 [Column（zone）] 
 public string ZoneName {get;组; } 
 
 [Column（tariff_ph）] 
 public int？ TariffPH {get;组; } 
 
 [Column（tariff_pd）] 
 public int？ TariffPD {get;组; } 
 
 [Column（restrictions）] 
 public string限制{get;组; } 
 
 [Column（days_of_operation）] 
 public string DaysOpen {get;组; } 
 
 [Column（hours_of_operation）] 
 public string HoursOpen {get;组; } 
 
 
 
 public ZoneInfo（）
 {
 
} 
 
 
 public ZoneInfo string objectName，string zoneName，int tariffPH，int tariffPD，
字符串限制，字符串daysOpen，string hoursOpen）
 {
 
 ObjectId = objectId; 
 ZoneName = zoneName; 
 TariffPH = tariffPH; 
 TariffPD = tariffPD; 
限制=限制; 
 DaysOpen = daysOpen; 
 HoursOpen = hoursOpen; 
} 
 
 
 
} 
  

数据库模式 -

解决方案

您的DB Mapping POCO与您的数据库架构不匹配。

  [Column（tariff_ph）] 
 public int？ TariffPH {get;组; } 
 
 [Column（tariff_pd）] 
 public int？ TariffPD {get;组; } 
  

应该

  [Column（tariff_ph）] 
 public float TariffPH {get;组; } 
 
 [Column（tariff_pd）] 
 public int TariffPD {get;组; } 
  

因为在数据集中有浮点值，而且两者都不是NULLABLE。 >

这里有一个最低例子在Sqlite Window Phone 8中更新记录，创建数据库，插入一些数据并更新数据库。看看是否可以帮助你，但我很确定你的数据不匹配。

I'm using the SQLite libs and SQLite.Net to query an existing database in my Windows Phone 8.1 project. So far the connection to the db is executing fine but the records being returned are null.

I took the usual debugging steps -

1.Checked the binding types and names which map to the names in my db schema.

2.Verified the data exists in the DB.

3.Stepped through the code that queries the database, found that the db bindings are null. (This suggests to me an issue with the field mappings in my POCO)

I don't get any compile time error as such but the records being returned are null.

Question: Does anyone know why there is an issue with the bindings provided for the database mapping?

When I stepped through the SQLite.cs class I found the binding count is 0:

Query Code:

            using (var dbConn = new SQLiteConnection(Path.Combine(ApplicationData.Current.LocalFolder.Path, AppDBPath), true))
            {
                List<ZoneInfo> zoneInfo = dbConn.Query<ZoneInfo>("select * from " + tableName).ToList<ZoneInfo>();
                ObservableCollection<ZoneInfo> zoneInfoCollection = new ObservableCollection<ZoneInfo>(zoneInfo);
                return zoneInfoCollection;
            }

DB Mapping POCO:

public class ZoneInfo
{

    //The ObjectId property is marked as the Primary Key  
    [SQLite.PrimaryKey]
    [Column("objectId")]
    public string ObjectId { get; set; }

    [Column("zone")]
    public string ZoneName { get; set; }

    [Column("tariff_ph")]
    public int? TariffPH  { get; set; }

    [Column("tariff_pd")]
    public int? TariffPD { get; set; }

    [Column("restrictions")]
    public string Restrictions { get; set; }

    [Column("days_of_operation")]
    public string DaysOpen { get; set; }

    [Column("hours_of_operation")]
    public string HoursOpen { get; set; }



    public ZoneInfo() 
    {

    }


    public ZoneInfo(string objectId, string zoneName, int tariffPH, int tariffPD, 
        string restrictions, string daysOpen, string hoursOpen )
    {

        ObjectId = objectId;
        ZoneName = zoneName;
        TariffPH = tariffPH;
        TariffPD = tariffPD;
        Restrictions = restrictions;
        DaysOpen = daysOpen;
        HoursOpen = hoursOpen;
    }



}

Database schema -

解决方案

Your "DB Mapping POCO" does not match your Database schema.

[Column("tariff_ph")]
public int? TariffPH  { get; set; }

[Column("tariff_pd")]
public int? TariffPD { get; set; }

Should probably be

[Column("tariff_ph")]
public float TariffPH  { get; set; }

[Column("tariff_pd")]
public int TariffPD { get; set; }

Since you have floating point values in your dataset and both of them are NOT NULLABLE.

I have an minimum example here Update Record in Sqlite Window Phone 8, that creates the database, inserts some data and updates the database. See if that can help you, but I'm pretty sure your data doesn't match correctly.

这篇关于如何解决SQLite查询的空绑定？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！