C# and SQLite – Deleting Data

In order to delete data in an SQLite database, the SQL ‘Delete’ statement needs to be used. The following example deletes a record with a specific ‘id’ from the ‘person’ table, which was used in the examples for selecting, inserting and updating data.

Deleting data works in a similar way to the other examples, where a parameterised query is used. Firstly, a check is made to see if the database file actually exists. If it doesn’t, a message is displayed and execution of the program is halted. If successfully found, a connection to the database is established, then the query parameter for the ‘id’ of the record to be deleted is defined as a variable, which is bound in to the following SQL statement. The SQL statement is then executed and a confirmation message is displayed. The declaration and execution of the SQL statement is wrapped in a ‘try-catch-finally’ block to catch any errors that may arise and close the database connection at the end.

// Database.
string database = @"C:\Demo\DemoDB.db";
string password = @"DemoPW";
SqliteConnection connect;

// Check if the database exists.
if (!File.Exists(database))
{

    // Message confirming incorrect database location.
    Console.WriteLine("Error locating database.");

    // Stop program execution.
    System.Environment.Exit(1);

}

// Database connection.
connect = new SqliteConnection("Data Source=" + database + ";" +
                                "Password=" + password);

try
{

    // Connect to database.
    connect.Open();

}
catch (Exception e)
{

    // Message confirming unsuccessful database connection.
    Console.WriteLine("Database connection unsuccessful.");

    // Stop program execution.
    System.Environment.Exit(1);

}

try
{

    // Query parameter.
    int id = 2;

    // Query text incorporated into SQL command.
    var sqlDelete = connect.CreateCommand();
    sqlDelete.CommandText = @"
        DELETE FROM person 
        WHERE id = $id
    ";

    // Bind the parameter to the query.
    sqlDelete.Parameters.AddWithValue("$id", id);

    // Execute SQL.
    sqlDelete.ExecuteNonQuery();

    // Confirm successful deletion of person information.
    Console.WriteLine("Person information deleted successfully.");

}
catch (Exception e)
{

    // Confirm error deleting person information and exit.
    Console.WriteLine("Error deleting person information.");
    System.Environment.Exit(1);

}
finally
{

    // Close the database connection.
    connect.Close();

}

The contents of the ‘person’ table now looks as follows.

Further Resources

• Environment Class.
• File Class.