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列&
问题与这样的行有关:
string lastEdtTm = statement[21].ToString();
这里,如果statement[21]
返回一个null
值,那么它等于null.ToString()
,这将抛出异常。
简单的解决办法:
string lastEdtTm = statement[21] == null ? "" : statement[21].ToString();
如果statement[21]
解析为null
,则返回空字符串,否则返回字符串值。
请注意,对于所有可能返回null的列都应该这样做—仅仅因为您现在没有在其他地方获得该异常,并不意味着以后添加新行时可能不会获得该异常,这可能会丢失其他值。