How to: Delete Records in a Database
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).
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.)
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.)
If you do not have an instance available, instantiate the TableAdapter you want to use.
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();