Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

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

    // 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 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");
    

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

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

Show:
© 2015 Microsoft