Edit

Save data from an object to a database in .NET Framework applications

  • Article

Note

Datasets and related classes are legacy .NET Framework technologies from the early 2000s that enable applications to work with data in memory while the applications are disconnected from the database. They are especially useful for applications that enable users to modify data and persist the changes back to the database. Although datasets have proven to be a very successful technology, we recommend that new .NET applications use Entity Framework Core. Entity Framework provides a more natural way to work with tabular data as object models, and it has a simpler programming interface.

You can save data in objects to a database by passing the values from your object to one of the TableAdapter's DBDirect methods (for example, TableAdapter.Insert). For more information, see TableAdapter.

To save data from a collection of objects, loop through the collection of objects (for example, a for-next loop), and send the values for each object to the database by using one of the TableAdapter's DBDirect methods.

By default, DBDirect methods are created on a TableAdapter that can be run directly against the database. These methods can be called directly and don't require DataSet or DataTable objects to reconcile changes in order to send updates to a database.

Note

When you're configuring a TableAdapter, the main query must provide enough information for the DBDirect methods to be created. For example, if a TableAdapter is configured to query data from a table that does not have a primary key column defined, it does not generate DBDirect methods.

TableAdapter DBDirect method Description
TableAdapter.Insert Adds new records to a database and enables you to pass in individual column values as method parameters.
TableAdapter.Update Updates existing records in a database. The Update method takes original and new column values as method parameters. The original values are used to locate the original record, and the new values are used to update that record.

The TableAdapter.Update method is also used to reconcile changes in a dataset back to the database by taking a DataSet, DataTable, DataRow, or an array of DataRows as method parameters.
TableAdapter.Delete Deletes existing records from the database based on the original column values passed in as method parameters.

To save new records from an object to a database

  • Create the records by passing the values to the TableAdapter.Insert method.

    The following example creates a new customer record in the Customers table by passing the values in the currentCustomer object to the TableAdapter.Insert method.

    private void AddNewCustomers(Customer currentCustomer)
{
    customersTableAdapter.Insert( 
        currentCustomer.CustomerID, 
        currentCustomer.CompanyName, 
        currentCustomer.ContactName, 
        currentCustomer.ContactTitle, 
        currentCustomer.Address, 
        currentCustomer.City, 
        currentCustomer.Region, 
        currentCustomer.PostalCode, 
        currentCustomer.Country, 
        currentCustomer.Phone, 
        currentCustomer.Fax);
}

To update existing records from an object to a database

  • Modify the records by calling the TableAdapter.Update method, passing in the new values to update the record, and passing in the original values to locate the record.

    Note

    Your object needs to maintain the original values in order to pass them to the Update method. This example uses properties with an orig prefix to store the original values.

    The following example updates an existing record in the Customers table by passing the new and original values in the Customer object to the TableAdapter.Update method.

    private void UpdateCustomer(Customer cust)
{
    customersTableAdapter.Update(
        cust.CustomerID,
        cust.CompanyName,
        cust.ContactName,
        cust.ContactTitle,
        cust.Address,
        cust.City,
        cust.Region,
        cust.PostalCode,
        cust.Country,
        cust.Phone,
        cust.Fax,
        cust.origCustomerID,
        cust.origCompanyName,
        cust.origContactName,
        cust.origContactTitle,
        cust.origAddress,
        cust.origCity,
        cust.origRegion,
        cust.origPostalCode,
        cust.origCountry,
        cust.origPhone,
        cust.origFax);
}

To delete existing records from a database

  • Delete the records by calling the TableAdapter.Delete method and passing in the original values to locate the record.

    Note

    Your object needs to maintain the original values in order to pass them to the Delete method. This example uses properties with an orig prefix to store the original values.

    The following example deletes a record from the Customers table by passing the original values in the Customer object to the TableAdapter.Delete method.

    private void DeleteCustomer(Customer cust)
{
    customersTableAdapter.Delete(
        cust.origCustomerID,
        cust.origCompanyName,
        cust.origContactName,
        cust.origContactTitle,
        cust.origAddress,
        cust.origCity,
        cust.origRegion,
        cust.origPostalCode,
        cust.origCountry,
        cust.origPhone,
        cust.origFax);
}

.NET security

You must have permission to perform the selected INSERT, UPDATE, or DELETE on the table in the database.

Related content