Deleting database using the disconnected approach is similar to inserting a row using the DataSet and the DataAdapter classes. But instead of adding a new DataRow object, you will delete it from the DataTable of the DataSet. The following are the essential step to delete a line utilizing the disconnected methodology.

  1. Create a Connection
  2. Create a Command
  3. Create a DataAdapter
  4. Create a CommandBuilder and associate it to the DataAdapter
  5. Create a DataSet
  6. Specify connection string for the connection
  7. Specify Connection to be used by the Command
  8. Specify CommandText for the Command
  9. Add values to command parameters if any
  10. Specify the SelectCommand for the DataAdapter
  11. Fill the DataSet with the result set from the database table
  12. Find the row to delete.
  13. Delete the row from the DataTable if found.
  14. Send the changes to the database

How about we make a basic application that enables you to indicate the StudentId and delete the comparing record having that ID. Make another Windows Forms Application and name it DeletingRecordDisconnected. Include a mark and textbox for the StudentID and a catch that will execute the commands. Name the content box studentIdTextBox and the button as deleteButton.

Figure 1

Double-click the button to produce an event handler for its Click event. Make certain to import the System.Data.SqlClient namespace. Utilize the accompanying code for the event handler.

private void deleteButton_Click(object sender, EventArgs e)
{
 
    SqlConnection connection = new SqlConnection();
    SqlCommand command = new SqlCommand();
    SqlDataAdapter adapter = new SqlDataAdapter();
    SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
    DataSet dataset = new DataSet();
 
    connection.ConnectionString = @"Data Source=.SQLEXPRESS;" +
        "Initial Catalog=University;Integrated Security=SSPI";
    command.Connection = connection;
    command.CommandText = "SELECT * FROM Students";
    adapter.SelectCommand = command;
    adapter.Fill(dataset, "Students");
 
    foreach (DataRow row in dataset.Tables["Students"].Rows)       
    {                                                              
        if (row["StudentID"].ToString() == studentIdTextBox.Text)  
        {                                                          
            row.Delete();                                          
                                                                   
        }                                                          
    }                                                              
 
    try
    {
        int result = adapter.Update(dataset, "Students");
 
        if (result > 0)
            MessageBox.Show("Success!");
        else
            MessageBox.Show("Failed!");
    }
    catch (SqlException ex)
    {
        MessageBox.Show(ex.Message);
    }
}

Example 1

We created the necessary objects in lines 4-8. Note the create of SqlCommandBuilder in line 7. In its constructor, we pass the created DataAdapter object to associate the CommandBuilder to it. Lines 13-15 fills the DataSet of esteems from the Student table and adds the result to the DataTable named Students inside the DataSet. Now that we have the values of the table from the database, we can now delete a row from that DataTable. We first need to look which record to erase. We utilized a foreach loop (line 17-24) to repeat through each record in the DataSet. Line 19 tests whether the substance of the StudentID field of the current line is equivalent to what the client indicated in the studentIdTextBox. On the off chance that it matches, we utilize the DataRow.Delete() method to erase that line from the table.

Line twenty eight calls the DataAdapter’s Update() method and that we passed the DataSet and the name of the DataTable.When Update() is called, the CommandBuilder generates the required SQL articulations for each difference in adjustment it identifies. Those SQL commands are then executed to the database. In our case, since we delete a row from the DataTable, CommandBuilder generates a DELETE Statement together with parameters filled with the values you specified. After the execution of the command, the row or rows are deleted from the actual database table. The Update() method returns an integer value representing the number of rows affected. Since we 1 row was deleted, we are expecting a return value of 1. We test if the result is more noteworthy than 0 in line 30. If it is, then we show a success message. Otherwise, we tell the user that the deletion of the row has failed.

Run the program and sort a StudentId that exist in the Students table.

Figure 2

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

Figure 3