Share via

Save data from an object to a database

  • Article

Applies to: yesVisual Studio noVisual Studio for Mac

Note

This article applies to Visual Studio 2017. If you're looking for the latest Visual Studio documentation, see Visual Studio documentation. We recommend upgrading to the latest version of Visual Studio. Download it here

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

    Private Sub AddNewCustomer(ByVal currentCustomer As Customer)

    CustomersTableAdapter.Insert(
        currentCustomer.CustomerID,
        currentCustomer.CompanyName,
        currentCustomer.ContactName,
        currentCustomer.ContactTitle,
        currentCustomer.Address,
        currentCustomer.City,
        currentCustomer.Region,
        currentCustomer.PostalCode,
        currentCustomer.Country,
        currentCustomer.Phone,
        currentCustomer.Fax)
End Sub

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

    Private Sub UpdateCustomer(ByVal cust As Customer)

        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)
End Sub

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

    Private Sub DeleteCustomer(ByVal cust As Customer)

    CustomersTableAdapter.Delete(
        cust.origCustomerID,
        cust.origCompanyName,
        cust.origContactName,
        cust.origContactTitle,
        cust.origAddress,
        cust.origCity,
        cust.origRegion,
        cust.origPostalCode,
        cust.origCountry,
        cust.origPhone,
        cust.origFax)
End Sub

.NET security

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

See also