How to: Delete Records in a Database

Note

This article applies to Visual Studio 2015. If you're looking for Visual Studio 2017 documentation, use the version selector at the top left. We recommend upgrading to Visual Studio 2017. Download it here.

To delete records from a database, use the TableAdapter.Update method or the TableAdapter.Delete method. Or, if your application does not use TableAdapters, you can use command objects to delete records from a database (for example, ExecuteNonQuery).

The TableAdapter.Update method is typically used when your application uses datasets to store data, whereas the TableAdapter.Delete method is typically used when your application uses objects to store data.

If your TableAdapter does not have a Delete method, it means either the TableAdapter is configured to use stored procedures, or its GenerateDBDirectMethods property is set to false. Try setting the TableAdapter's GenerateDBDirectMethods property to true from within the Dataset Designer and then save the dataset to regenerate the TableAdapter. If the TableAdapter still does not have a Delete method, then the table probably does not provide enough schema information to distinguish between individual rows (for example, no primary key is set on the table).

Delete Records Using TableAdapters

TableAdapters provide different ways to delete records from a database depending on the requirements of your application.

If your application uses datasets to store data you can simply delete records from the desired DataTable in the DataSet, and then call the TableAdapter.Update method. The TableAdapter.Update method takes any changes in the data table and sends those changes to the database (including inserted, updated, and deleted records).

To delete records from a database using the TableAdapter.Update method

  • Delete records from the desired DataTable by deleting DataRow objects from the table. For more information, see How to: Delete Rows in a DataTable. After the rows are deleted from the DataTable, call the TableAdapter.Update method. You can control the amount of data to update by passing in an entire DataSet, a DataTable, an array of DataRows, or a single DataRow. The following code shows how to delete a record from a DataTable and then call the TableAdapter.Update method to communicate the change and delete the row from the database. (This example uses the Northwind database's Region table.)

    // Locate the row to delete.
    NorthwindDataSet.RegionRow oldRegionRow;
    oldRegionRow = northwindDataSet.Region.FindByRegionID(5);
    
    // Delete the row from the dataset
    oldRegionRow.Delete();
    
    // Delete the row from the database
    this.regionTableAdapter.Update(this.northwindDataSet.Region);
    
    ' Locate the row to delete.
    Dim oldRegionRow As NorthwindDataSet.RegionRow
    oldRegionRow = NorthwindDataSet._Region.FindByRegionID(5)
    
    ' Delete the row from the dataset
    oldRegionRow.Delete()
    
    ' Delete the row from the database
    Me.RegionTableAdapter.Update(Me.NorthwindDataSet._Region)
    

    If your application uses objects to store the data in your application, you can use the TableAdapter's DBDirect methods to delete data directly from the database. Calling the Delete method removes records from the database based on the parameter values passed in.

To delete records from a database using the TableAdapter.Delete method

  • Call the TableAdapter's Delete method, passing in the values for each column as parameters of the Delete method. (This example uses the Northwind database's Region table.)

    Note

    If you do not have an instance available, instantiate the TableAdapter you want to use.

    NorthwindDataSetTableAdapters.RegionTableAdapter regionTableAdapter = 
        new NorthwindDataSetTableAdapters.RegionTableAdapter();
    
    regionTableAdapter.Delete(5, "NorthWestern");
    
    Dim regionTableAdapter As New NorthwindDataSetTableAdapters.RegionTableAdapter
    
    regionTableAdapter.Delete(5, "NorthWestern")
    

Delete Records Using Command Objects

The following example deletes records directly from a database using command objects. For more information on using command objects to execute commands and stored procedures, see Fetching Data into Your Application.

To delete records from a database using command objects

  • Create a new command object, set its Connection, CommandType, and CommandText properties. (This example uses the Northwind database's Region table.)

    System.Data.SqlClient.SqlConnection sqlConnection1 = 
        new System.Data.SqlClient.SqlConnection("YOUR CONNECTION STRING ");
    
    System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
    cmd.CommandType = System.Data.CommandType.Text;
    cmd.CommandText = "DELETE Region WHERE RegionID = 5 AND RegionDescription = 'NorthWestern'";
    cmd.Connection = sqlConnection1;
    
    sqlConnection1.Open();
    cmd.ExecuteNonQuery();
    sqlConnection1.Close();
    
    Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection("YOUR CONNECTION STRING")
    
    Dim cmd As New System.Data.SqlClient.SqlCommand
    cmd.CommandType = System.Data.CommandType.Text
    cmd.CommandText = "DELETE Region WHERE RegionID = 5 AND RegionDescription = 'NorthWestern'"
    cmd.Connection = sqlConnection1
    
    sqlConnection1.Open()
    cmd.ExecuteNonQuery()
    sqlConnection1.Close()
    

.NET Framework Security

You must have access to the database you are trying to connect to, as well as permission to delete records from the desired table.

See Also

TableAdapter Overview
Insert new records into a database
How to: Update Records in a Database
Save data from an object to a database
Overview of Data Applications in Visual Studio
Connecting to Data in Visual Studio
Preparing Your Application to Receive Data
Fetching Data into Your Application
Bind controls to data in Visual Studio
Editing Data in Your Application
Validating Data
Saving Data