我正在尝试检查表中的记录是否已存在.
我怎么能这样做？
我已经写了以下代码：
- string dbName = "Data Source=searchindex.db";
- sqliteConnection con = new sqliteConnection(dbName);
- con.Open();
- sqliteCommand cmd = new sqliteCommand(con);
- // If this sql request return false
- cmd.CommandText = "SELECT rowid FROM wordlist WHERE word='word'";
- cmd.ExecuteNonQuery();
- // then add record in table
- cmd.CommandText = "INSERT INTO wordlist(word) VALUES ('word')";
要检查该记录是否存在,您可以简化代码
- cmd.CommandText = "SELECT count(*) FROM wordlist WHERE word='word'";
- int count = Convert.ToInt32(cmd.ExecuteScalar());
- if(count == 0)
- {
- cmd.CommandText = "INSERT INTO wordlist(word) VALUES ('word')";
- cmd.ExecuteNonQuery();
- }
ExecuteScalar将返回查询返回的第一行的第一列.
(该链接适用于sqlServer,但它与sqlite完全相同,因为sqliteCommand应该实现IDbCommand接口)
另一种使用方法如下
- cmd.CommandText = "INSERT INTO wordlist (word)
- SELECT ('word')
- WHERE NOT EXISTS
- (SELECT 1 FROM wordlist WHERE word = 'word');";
- cmd.ExecuteNonQuery();
