即使插入了行,SQLite也会报告插入查询错误
本文关键字:插入 报告 查询 错误 SQLite | 更新日期: 2023-09-27 17:49:40
我使用的是http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki 1.0.82.0版本
当我像这样插入一行时:
"INSERT INTO [testtable] (col1,col2) values ('','')"
我总是从SQLiteCommand.ExecuteNonQuery();
得到1的结果,它应该返回0 (OK)或101 (DONE)。我知道行插入得很好,因为每次运行该方法时,自动增量值都会增加。
readonly object _threadlock = new object();
readonly string _file;
public CSQLite(string file)
{
_file = file;
}
private SQLiteConnection _sqlCon;
private SQLiteCommand _sqlCmd;
private SQLiteDataAdapter _db;
private void SetConnection()
{
lock (_threadlock)
{
_sqlCon = new SQLiteConnection(String.Format("Data Source={0};Version=3;New=False;Compress=True;", _file));
}
}
public int SimpleInsertAndGetlastID(out int id)
{
lock (_threadlock)
{
SetConnection();
_sqlCon.Open();
//Execute
_sqlCmd = _sqlCon.CreateCommand();
_sqlCmd.CommandText = "INSERT INTO [testtable] (col1,col2) values ('','')";
var res = _sqlCmd.ExecuteNonQuery();
//Get id
_db = new SQLiteDataAdapter("select last_insert_rowid();", _sqlCon);
var ds = new DataSet();
_db.Fill(ds);
DataTable dt = ds.Tables[0];
var val = dt.Rows[0][0].ToString();
Int32.TryParse(val, out id);
_sqlCon.Close();
return res;
}
}
测试:/// <summary>
///A test for SimpleInsertAndGetlastID
///</summary>
[TestMethod()]
public void SimpleInsertAndGetlastIDTest()
{
var file = "dbs''test.db";
var target = new CSQLite(file);
var id = -1;
var res = -1;
try
{
res = target.SimpleInsertAndGetlastID(out id);
}
catch (Exception ex){/*Breakpoint*/}
Assert.IsTrue(id > 0); //id gets +1 every time the test is run so the row *is* getting inserted
Assert.IsTrue(res==0||res==101); //Res is always 1 for some reason
}
表创建(如果有这个问题):
public List<string> Columns { get; set; }
if (!File.Exists(_dbFile))
SQLiteConnection.CreateFile(_dbFile);
var fieldqry = "";
var count = 0;
Columns.ForEach((field) =>
{
count++;
fieldqry += String.Format("[{0}] TEXT NULL", field);
if (count < Columns.Count)
fieldqry += ",";
});
var qry = String.Format("CREATE TABLE IF NOT EXISTS [{0}](" +
"[ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," +
"{1}" +
");", TableName, fieldqry);
var sql = new CSQLite(_dbFile);
var res = sql.Execute(qry);
if(res!=SqLiteErrorCodes.SQLITE_OK)
throw new SqLiteImplementationException("Query failed.");
列为new List<string> { "col1", "col2" } };
ExecuteNonQuery()
不返回SQLite错误代码,它返回受查询影响的行数。如果要插入一行,则1听起来像是操作成功时的预期结果。
ExecuteNonQuery的结果被认为是"受影响的行数",而不是错误代码:-)