How to: Delete Records in a Database

Article
08/19/2012

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. 
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)
```
```
// 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);
```

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.

Dim regionTableAdapter As New NorthwindDataSetTableAdapters.RegionTableAdapter

regionTableAdapter.Delete(5, "NorthWestern")

NorthwindDataSetTableAdapters.RegionTableAdapter regionTableAdapter = 
    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.)

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()

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();

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.

How to: Delete Records in a Database

Delete Records Using TableAdapters

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

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

Delete Records Using Command Objects

To delete records from a database using command objects

Security

See Also

Tasks

Concepts

Other Resources

Additional resources