We will now delete a database record using the connected approach. The steps are the same as inserting a record while connected to a database which uses the DbCommand.ExecuteNonQuery() method.

  1. Create a Connection
  2. Create a Command
  3. Specify connection string to Connection
  4. Specify Connection that the Command will use
  5. Specify the DELETE Statement for the CommandText of the Command
  6. Add values to command parameters if any
  7. Open Connection
  8. Execute the command
  9. Close Connection

Let’s create a simple application that allows you to specify the StudentId and delete the corresponding record having that ID. Create a new Windows Forms Application and name it DeletingRecordsConnected. Include a label and text box for the StudentID and a button that will execute the commands. Name the text box studentIdTextBox and also the button as deleteButton.

Figure 1

Double click the button to come up with an event handler for its Click event. take care to import the System.Data.SqlClient namespace. Use the subsequent code for the event handler.

private void button1_Click(object sender, EventArgs e)
{
    SqlConnection connection = new SqlConnection();
    SqlCommand command = new SqlCommand();
 
    connection.ConnectionString = @"Data Source=.\SQLEXPRESS;" + 
        "Initial Catalog=University;Integrated Security=SSPI";
    command.Connection = connection;
    command.CommandText = "DELETE FROM Students WHERE [email protected]";
    command.Parameters.AddWithValue("@StudentID", studentIdTextBox.Text);
 
    try
    {
        connection.Open();
        int result = command.ExecuteNonQuery();
        if (result > 0)
            MessageBox.Show("Student was removed!");
        else
            MessageBox.Show("Can't find student.");
    }
    catch (SqlException ex)
    {
        MessageBox.Show("An error has occured.");
    }
    finally
    {
        connection.Close();
    }
}

Example 2

Line 9 specified a DELETE SQL Statement which has 1 command parameter, the StudentID to be deleted. Line 10 add’s the text of the studentIdTextBox as a value to that command parameter. Inside the try block, we opened the connection and Line 15 uses the ExecuteNonQuery() method to execute the DELETE command. The technique returns a whole number which is the quantity of row influenced. Line 16 tested if at least 1 row was affected since we delete 1 record. If so, we show a message showing that the deletion was successful. If no records were affected, it simply means the record was not found and no records were deleted.Run the program and type a StudentId that exist in the Students table.

If a matching row is found, it is removed from the table.

Written by compitionpoint

Leave a Comment

Your email address will not be published. Required fields are marked *