C# and PostgreSQL – Deleting Data
In order to delete data in a PostgreSQL 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 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 connection variable. NpgsqlConnection connect = new NpgsqlConnection( "Server=localhost;" + "Database=Demo;" + "User Id=DemoUN;" + "Password=DemoPW"); 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 parameters. int id = 2; // Query text. string sqlText = @" DELETE FROM person WHERE id = @id "; // Query text incorporated into SQL command. NpgsqlCommand sqlDelete = new NpgsqlCommand(sqlText, connect); // Bind the parameter to the query. sqlDelete.Parameters.AddWithValue("@id", id); sqlDelete.Prepare(); // 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.
id | firstname | lastname | title | dob |
1 | Bob | Smith | Mr | 1980-01-20 |
3 | Fred | Bloggs | Mr | 1975-05-07 |
4 | Alan | White | Mr | 1989-03-20 |
5 | Fiona | Bloggs | Mrs | 1985-05-19 |