checking if record exists in Sqlite + C#

c#, c#-4.0, sqlite

Solution

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)

Problem

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')"; ```

Original source