How to handle SQLite Unique Constraints error when the value already exists?

Question

Have created a database and use unique constraints to avoid duplicate values. When I try to add a value that violates that rule, the app crashes.

How would you solve it? Should I first make a SQL query and see if the value already exists and if it doesn't exist add it? Does it feel a bit unnecessary to have two questions against the database, can I do it one query?

I want:

1. Check if a value exists
2. If it exist, give a message that it already exists
3. If it doesn't exist add it How should I do it?

Using Nuget Package: System.Data.SQLiteLogin

C# Code

    // Connection String
    string DbFile = @"URI=file:Data\LearnWords.db";
    int status = 0;
    
    // Db Connection
    SQLiteConnection DbConnectiom = new SQLiteConnection(DbFile);
    DbConnectiom.Open();
    
    // SQL Command
    SQLiteCommand Cmd = new SQLiteCommand("INSERT INTO Word (WordClass, WordEng) VALUES('Test','Hello');", DbConnectiom);
    status = Cmd.ExecuteNonQuery();
    Console.WriteLine($"Status: {status}");
    
    DbConnectiom.Close();

Tried to find some documentation but couldn't find it

Answer 1

You can use try/catch, but you will want to be sure that the issue is a unique constraint violation, so you will need the extended result code. So after you call .Open() on the connection, do this...

DbConnectiom.SetExtendedResultCodes(true);

Then you can have a try/catch that checks for SQLiteErrorCode.Constraint_Unique like so...

try
{
    status = Cmd.ExecuteNonQuery();
    Console.WriteLine($"Status: {status}");
}
catch (SQLiteException se)
{
    if (se.ResultCode != SQLiteErrorCode.Constraint_Unique)
        throw;

    Console.WriteLine("Unique constraint violated - value already exists");
}