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:

Check if a value exists
If it exist, give a message that it already exists
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

Crashes or throws an exception tell you the insert failed due to a constraint violation ? — John3136, Apr 14 '23 at 07:52

davmos · Accepted Answer · 2023-04-17T21:17:12.740

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

edited Apr 17 '23 at 21:17

answered Apr 14 '23 at 17:30

davmos

9,324
4
40
43

Don't want to ignore it! I want to catch it if exists and tell the user about it without it crashing. How to do it? – DanneManne Apr 14 '23 at 23:23
OK, see updated answer, solution 1 – davmos Apr 15 '23 at 22:04
I don't think you understand what I want to do... I want: 1. Check if a value exists 2. If there is, give a message that it already exists 3. If it doesn't exist add it How should I do it? – DanneManne Apr 17 '23 at 11:57
Solution 1 does that! If it inserts the value then it won't go into the catch. If it doesn't insert the data because of a unique constraint violation, then it will go into the catch, skip over the if & print the "...value already exists" message. For clarity, I've removed the other solutions. The main reason I left them there was for future readers. – davmos Apr 17 '23 at 21:22
1

Now i tested your solution and i worked without the app crashed. Thanks! :) – DanneManne Apr 18 '23 at 17:03

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

1 Answers1