SQLite C#: Deleting Data

Summary: in this tutorial, you will learn how to delete data from a table in an SQLite database from a C# program using ADO.NET.

Steps for deleting data from a table using ADO.NET

Step 1.Open a new database connection.

Step 3. Construct a DELETE statement that deletes on or rows from a table.

Step 3. Create an SqliteCommand object that accepts the DELETE statement.

Step 4. Bind values to the parameters of the DELETE statement.

Step 5. Execute the DELETE statement by calling the ExecuteNonQuery() method of the SqliteCommand object.

Step 6. Close the database connection.

Deleting data from a table

The following program shows you how to delete a row with id 1 from the authors table:

using Microsoft.Data.Sqlite;


var sql = "DELETE FROM authors WHERE id = @id";
var authorId = 1;

try
{
    // Open a new database connection
    using var connection = new SqliteConnection(@"Data Source=C:\db\pub.db");
    connection.Open();


    // Bind parameters values
    using var command = new SqliteCommand(sql, connection);
    command.Parameters.AddWithValue("@id", authorId);


    // Execute the DELETE statement
    var rowDeleted = command.ExecuteNonQuery();

    Console.WriteLine($"The author has been deleted successfully.");

}
catch (SqliteException ex)
{
    Console.WriteLine(ex.Message);
}Code language: C# (cs)

How it works.

First, construct an DELETE statement that updates the last name of the author:

var sql = "DELETE FROM authors WHERE id = @id";Code language: C# (cs)

In this statement, @id is a parameter placeholder that represents the value of the id column.

When you execute this DELETE statement in C# with a SqliteCommand, you will need to provide a value for the @id parameter.

Second, declare and initialize a variable for storing the author id:

var authorId = 1;Code language: C# (cs)

Third, open a database connection to the C:\db\pub.db file:

using var connection = new SqliteConnection(@"Data Source=C:\db\pub.db");
connection.Open();Code language: C# (cs)

Fourth, create a new SqliteCommand object and bind values to the corresponding parameters:

using var command = new SqliteCommand(sql, connection);
command.Parameters.AddWithValue("@id", authorId);Code language: C# (cs)

Fifth, execute the DELETE statement by calling the ExecuteNonQuery() method of the SqliteCommand object:

var rowsUpdated = command.ExecuteNonQuery();Code language: C# (cs)

The ExecuteNonQuery() method returns the number of rows deleted.

Sixth, show a message indicating that the program has updated the author successfully:

Console.WriteLine($"The author has been deleted successfully.");Code language: C# (cs)

Finally, display the error message if there is any exception occurred during the deletion:

Console.WriteLine(ex.Message);Code language: C# (cs)

Verifying the delete

First, open the terminal on the Unix-like systems or Command Prompt on Windows.

Second, launch the sqlite3 command to connect to the pub.db file located in the C:\db directory (or any directory of your choice)

sqlite3 c:\db\pub.dbCode language: C# (cs)

Note that the path to sqlite3 program needs to be in the PATH environment variable:

Third, retrieve data from the authors table:

SELECT * FROM authors
WHERE id = 1;Code language: C# (cs)

Output:

Code language: C# (cs)

The output shows an empty set, indicating that the author has been deleted successfully.

Summary

• Call the ExecuteNonQuery() method of the SqliteConnection object to execute a DELETE statement that deletes data from a table.