Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

checking if record exists in Sqlite + C#

Tags:

c#

sqlite

c#-4.0

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')";
like image 938
Helio Gracie Avatar asked Mar 28 '13 14:03

Helio Gracie


People also ask

How to check if record EXISTS in SQLite?

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 .

Does exist in SQLite?

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.

Does SQLite have variables?

SQLite doesn't support native variable syntax, but you can achieve virtually the same using an in-memory temp table.

Does all work in SQLite?

SQLite does not have an ALL operator. You might be tempted to write something like this: select ...


1 Answers

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)

like image 154
Steve Avatar answered Oct 15 '22 05:10

Steve