CODEWITHSUNDEEP
c#sqlitec#-4.0
Helio Gracie
Helio Gracie

Reputation: 173

checking if record exists in Sqlite + C#

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

Upvotes: 13

Views: 17996

Answers (4)

Mustafa Özçetin
Mustafa Özçetin

Reputation: 2994

For newcomers, with the introduction of the Upsert feature, we can insert rows (if they exist or not) in one shot. Consider the following SQLite table:

CREATE TABLE "Person" (
    "Id"    INTEGER NOT NULL UNIQUE,
    "Name"  TEXT NOT NULL,
    PRIMARY KEY("Id")
)

Note that the Id column has a Unique constraint and hence cannot be duplicated.

And assume the Person table has the rows:

Id Name
-- ----
1  John
2  Bill

Now consider the following upsert statements:

INSERT INTO Person(Id, Name) VALUES (2,'Mike') ON CONFLICT DO NOTHING;
INSERT INTO Person(Id, Name) VALUES (3,'Hans') ON CONFLICT DO NOTHING;

When these statements are executed, the excution will be successful and only 1 row (namely 'Hans') will be inserted. The significant part is the

ON CONFLICT DO NOTHING

clause and it ignores any conflicts ('Mike' above is not inserted since his Id already exists) with the Insert statement. Without it we would get a unique constraint error.

SQLite page for the upsert feature:

https://www.sqlite.org/draft/lang_UPSERT.html

C# sample:

    public void Upsert()
    {
        string dbFilePath = "Test.db";
        string connectionString = $"Data Source={dbFilePath}";
        using (var connection = new SqliteConnection(connectionString))
        {
            connection.Open();
            using (var command = connection.CreateCommand())
            {
                command.CommandText = $"INSERT INTO Person(Id, Name) VALUES(2, 'Mike') ON CONFLICT DO NOTHING;";
                int rowsAffected = command.ExecuteNonQuery(); // is 0
                System.Console.WriteLine("rowsAffected: " + rowsAffected);
            }
        }
    }

Note that I used the Microsoft.Data.Sqlite library (not System.Data.Sqlite) in the C# sample.

Upvotes: 0

Bongo
Bongo

Reputation: 3153

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

I have a base class for several tables and in it, I have a RowExists method. The relevant source code looks 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;
    }
}

Upvotes: 1

Steve
Steve

Reputation: 216243

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)

Upvotes: 23

Tim
Tim

Reputation: 8921

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

Upvotes: 0

Related Questions