Checking for a record in Sqlite + C #

Question

I am trying to check if a record exists in a table.

How could I do this?

I have already written 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')"; 

Answer 1

To check if this entry exists, you can 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 in the first row returned by your query.
(The link is for SqlServer, but it is identical for SQLite, because SQLiteCommand must implement the IDbCommand interface )

Another approach to use is as follows

 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 are using one query and not two (although the difference in the local database should be minimal)

Answer 2

  insert into wordlist(word) select 'foo' where not exists ( select 1 from wordlist where word = 'foo') 

Answer 3

If you are using sqlite-net-pcl , you can write the following.

I have a base class for multiple tables, and in it I have a RowExists method. The corresponding source code is as follows:

 public abstract class BaseSQLiteAccess { protected SQLiteConnection _databaseConnection; protected String TableName { get; set; } //... protected bool RowExists(int id) { bool exists = false; try { exists = _databaseConnection.ExecuteScalar<bool>("SELECT EXISTS(SELECT 1 FROM " + TableName + " WHERE ID=?)", id); } catch (Exception ex) { //Log database error exists = false; } return exists; } }