I'm trying to check if a record in a table already exists.
How could I do that?
I already wrote the following code:
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')";
The EXISTS operator is a logical operator that checks whether a subquery returns any row. In this syntax, the subquery is a SELECT statement that returns zero or more rows. If the subquery returns one or more row, the EXISTS operator return true. Otherwise, the EXISTS operator returns false or NULL .
The SQLite EXISTS condition is used in combination with a subquery and is considered "to be met" if the subquery returns at least one row. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.
SQLite doesn't support native variable syntax, but you can achieve virtually the same using an in-memory temp table.
SQLite does not have an ALL operator. You might be tempted to write something like this: select ...
To check if that record exists you could simplify your code
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 will return the first column on the first row returned by your query.
(The link is for SqlServer, but it is identical for SQLite, because the SQLiteCommand should implement the IDbCommand interface)
Another approach to use is the following
cmd.CommandText = "INSERT INTO wordlist (word)
SELECT ('word')
WHERE NOT EXISTS
(SELECT 1 FROM wordlist WHERE word = 'word');";
cmd.ExecuteNonQuery();
This is even better because you use a single query and not two (albeit the difference in a local db should be minimal)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With