Windows XAML中的SQLite (PCL)查询不允许超过21列

本文关键字:不允许 查询 21列 PCL XAML 中的 SQLite Windows | 更新日期: 2023-09-27 18:04:14

我在我的通用Windows 8.1运行时应用程序中使用了SQLite-PCL。
有一个大表与23列在我的项目。问题是-虽然表创建&数据插入代码运行没有任何错误,但在全列查询(SELECT * FROM table)不给我超过21列。

下面是我的create-table方法(它运行正常):

    private void CreateTable()
    {
        string CREATE_TABLE_SQL = @"CREATE TABLE IF NOT EXISTS "
                + TABLE_NAME
                + "( "
                + KEY_ID + " INTEGER PRIMARY KEY, " // 0
                + KEY_UPDATED_AT + " TEXT, "        // 1
                + KEY_CREATED_AT + " TEXT, "        // 2
            // ... all other column names are stated here in the similar way
                + KEY_LAST_EDITED_BY + " INTEGER, " // 20
                + KEY_LAST_EDIT_TIME + " TEXT, "    // 21
                + KEY_IS_LD + " INTEGER "        // 22
                + ");";
        using (var connection = new SQLiteConnection(DB_NAME))
        {
            using (var statement = connection.Prepare(CREATE_TABLE_SQL))
            {
                statement.Step();
            }
        }
    }

这是我的行插入SQL查询(它运行正常):

string insQuery = 
       @"INSERT INTO " + TABLE_NAME
          + " ( " + KEY_ID + ", " //1
          + KEY_UPDATED_AT + ", "//2
          + KEY_CREATED_AT + " , "//3
          // all other col. names are stated here
          + KEY_LAST_EDITED_BY + ", "//21
          + KEY_LAST_EDIT_TIME + ", "//22
          + KEY_IS_LD + " "//23
          + " ) " 
          + " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";

但是如果我查询所有行或具有所有列的特定行,则准备好的语句不能超过索引20,因此我假设它包含的列不超过21列。这是不能运行OK &在访问第21项时给出NullReferenceException:

public List<MyModel> GetAll()
{
    List<MyModel> objList = new List<MyModel>();
    string query = @"SELECT * FROM " + TABLE_NAME
                        + " ORDER BY " + KEY_ID + " DESC;";
    using (var connection = new SQLiteConnection(DB_NAME))
    {
        using (var statement = connection.Prepare(query))
        {
            // The next line prints this: "Statement data-count=0, ColumnCount=23"
            ALog.d("Statement data-count=" + statement.DataCount + 
                    ", ColumnCount=" + statement.ColumnCount); 
            while (statement.Step() == SQLiteResult.ROW)
            {
                try
                {
                    int id = Int32.Parse(statement[0].ToString());
                    string updatedAt = statement[1].ToString();
                    string createdAt = statement[2].ToString();
                    // ... all other values are extracted here, then I'm building
                    //  my user object & the next line prints my expected values
                    ALog.d("User: " + user.ToString());
                    // ... the remaining columns are got here nicely
                    string imgUrl = statement[19].ToString();
                    int lastEdt = Int32.Parse(statement[20].ToString());
                    // This line is the culprit giving out NullReferenceException >_<
                    string lastEdtTm = statement[21].ToString();
                    bool isLd = Int32.Parse(statement[22].ToString()) > 0;
                    objList.Add(new MyModel(
                        // Params. of the constructor goes here ...
                     ));
                }
                catch (Exception e)
                {
                    ALog.d("Db - GetAll() : Exception:: " + e.ToString());
                }
            }
            // This line prints as: "Statement data-count=23, ColumnCount=23"
            // That means - all my data & columns have been queried,
            // but I couldn't read values from statement[21] & statement[22]
                ALog.d("Statement data-count=" + statement.DataCount + 
                        ", ColumnCount=" + statement.ColumnCount);
                statement.Reset();
                statement.ClearBindings();
        }
    }
    return objList;
}

请注意,我在项目中有超过10个表,少于17列&

Windows XAML中的SQLite (PCL)查询不允许超过21列

问题与这样的行有关:

string lastEdtTm = statement[21].ToString();

这里,如果statement[21]返回一个null值,那么它等于null.ToString(),这将抛出异常。

简单的解决办法:

string lastEdtTm = statement[21] == null ? "" : statement[21].ToString();

如果statement[21]解析为null,则返回空字符串,否则返回字符串值。

请注意,对于所有可能返回null的列都应该这样做—仅仅因为您现在没有在其他地方获得该异常,并不意味着以后添加新行时可能不会获得该异常,这可能会丢失其他值。

相关文章: