{"id":3600,"date":"2024-05-12T15:24:41","date_gmt":"2024-05-12T08:24:41","guid":{"rendered":"https:\/\/www.sqlitetutorial.net\/?page_id=3600"},"modified":"2024-10-25T20:03:10","modified_gmt":"2024-10-25T13:03:10","slug":"delete","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-csharp\/delete\/","title":{"rendered":"SQLite C#: Deleting Data"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to delete data from a table in an SQLite database from a C# program using ADO.NET.<\/p>\n\n\n\n

Steps for deleting data from a table using ADO.NET<\/h2>\n\n\n\n

Step 1.Open a new database connection<\/a>.<\/p>\n\n\n\n

Step 3. Construct a DELETE<\/a> statement that deletes on or rows from a table.<\/p>\n\n\n\n

Step 3. Create an\u00a0SqliteCommand<\/code>\u00a0object that accepts the DELETE<\/code> statement.<\/p>\n\n\n\n

Step 4. Bind values to the parameters of the DELETE<\/code> statement.<\/p>\n\n\n\n

Step 5. Execute the DELETE<\/code> statement by calling the\u00a0ExecuteNonQuery()<\/code>\u00a0method of the\u00a0SqliteCommand<\/code>\u00a0object.<\/p>\n\n\n\n

Step 6. Close the database connection.<\/p>\n\n\n\n

Deleting data from a table<\/h2>\n\n\n\n

The following program shows you how to delete a row with id 1 from the authors<\/code> table:<\/p>\n\n\n

using<\/span> Microsoft.Data.Sqlite;\n\n\nvar<\/span> sql = \"DELETE FROM authors WHERE id = @id\"<\/span>;\nvar<\/span> authorId = 1<\/span>;\n\ntry<\/span>\n{\n    \/\/ Open a new database connection<\/span>\n    using<\/span> var<\/span> connection = new<\/span> SqliteConnection(@\"Data Source=C:\\db\\pub.db\"<\/span>);\n    connection.Open();\n\n\n    \/\/ Bind parameters values<\/span>\n    using<\/span> var<\/span> command = new<\/span> SqliteCommand(sql, connection);\n    command.Parameters.AddWithValue(\"@id\"<\/span>, authorId);\n\n\n    \/\/ Execute the DELETE statement<\/span>\n    var<\/span> rowDeleted = command.ExecuteNonQuery();\n\n    Console.WriteLine($\"The author has been deleted successfully.\"<\/span>);\n\n}\ncatch<\/span> (SqliteException ex)\n{\n    Console.WriteLine(ex.Message);\n}<\/code><\/span>Code language:<\/span> C#<\/span> (<\/span>cs<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, construct an DELETE<\/code> statement that updates the last name of the author:<\/p>\n\n\n
var<\/span> sql = \"DELETE FROM authors WHERE id = @id\"<\/span>;<\/code><\/span>Code language:<\/span> C#<\/span> (<\/span>cs<\/span>)<\/span><\/small><\/pre>\n\n\n
In this statement, @id<\/code> is a parameter placeholder that represents the value of the id<\/code> column. <\/p>\n\n\n\n
When you execute this DELETE<\/code> statement in C# with a SqliteCommand<\/code>, you will need to provide a value for the @id<\/code> parameter.<\/p>\n\n\n\n
Second, declare and initialize a variable for storing the author id:<\/p>\n\n\n
var<\/span> authorId = 1<\/span>;<\/code><\/span>Code language:<\/span> C#<\/span> (<\/span>cs<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, open a database connection to the C:\\db\\pub.db<\/code> file:<\/p>\n\n\n
using<\/span> var<\/span> connection = new<\/span> SqliteConnection(@\"Data Source=C:\\db\\pub.db\"<\/span>);\nconnection.Open();<\/code><\/span>Code language:<\/span> C#<\/span> (<\/span>cs<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, create a new SqliteCommand<\/code> object and bind values to the corresponding parameters:<\/p>\n\n\n
using<\/span> var<\/span> command = new<\/span> SqliteCommand(sql, connection);\ncommand.Parameters.AddWithValue(\"@id\"<\/span>, authorId);<\/code><\/span>Code language:<\/span> C#<\/span> (<\/span>cs<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, execute the DELETE<\/code> statement by calling the ExecuteNonQuery()<\/code> method of the SqliteCommand<\/code> object:<\/p>\n\n\n
var<\/span> rowsUpdated = command.ExecuteNonQuery();<\/code><\/span>Code language:<\/span> C#<\/span> (<\/span>cs<\/span>)<\/span><\/small><\/pre>\n\n\n
The ExecuteNonQuery()<\/code> method returns the number of rows deleted.<\/p>\n\n\n\n
Sixth, show a message indicating that the program has updated the author successfully:<\/p>\n\n\n
Console.WriteLine($\"The author has been deleted successfully.\"<\/span>);<\/code><\/span>Code language:<\/span> C#<\/span> (<\/span>cs<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, display the error message if there is any exception occurred during the deletion:<\/p>\n\n\n
Console.WriteLine(ex.Message);<\/code><\/span>Code language:<\/span> C#<\/span> (<\/span>cs<\/span>)<\/span><\/small><\/pre>\n\n\n
Verifying the delete<\/h2>\n\n\n\n
First, open the terminal on the Unix-like systems or Command Prompt on Windows.<\/p>\n\n\n\n
Second, launch the sqlite3<\/code> command to connect to the pub.db<\/code> file located in the C:\\db<\/code> directory (or any directory of your choice)<\/p>\n\n\n
sqlite3 c:\\db\\pub.db<\/code><\/span>Code language:<\/span> C#<\/span> (<\/span>cs<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that the path to sqlite3<\/code> program needs to be in the PATH<\/code> environment variable:<\/p>\n\n\n\n
Third, retrieve data from the authors<\/code> table:<\/p>\n\n\n
SELECT * FROM authors\nWHERE id = 1<\/span>;<\/code><\/span>Code language:<\/span> C#<\/span> (<\/span>cs<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
<\/code><\/span>Code language:<\/span> C#<\/span> (<\/span>cs<\/span>)<\/span><\/small><\/pre>\n\n\n
The output shows an empty set, indicating that the author has been deleted successfully.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Call the ExecuteNonQuery()<\/code> method of the SqliteConnection<\/code> object to execute a DELETE<\/code> statement that deletes data from a table.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful ?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t