Edit

Share via

Edit data in datasets using the .NET Framework

  • 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. The technologies 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.

This article describes how to query and edit data in tables in .NET Framework datasets. You can edit data in data tables much like you edit the tables in any database. You can insert, update, and delete records in the table. In a data-bound form, you can specify which fields are user-editable.

In those cases, the data-binding infrastructure handles all the change tracking so that the changes can be sent back to the database. If you edit data programmatically and want to send the changes back to the database, you must use objects and methods that do the change tracking.

In addition to changing the actual data, you can also query a DataTable to return specific rows of data. For example, you can query for individual rows, original or proposed versions of rows, rows that changed, or rows that contain errors.

Prerequisites

To use Visual Studio to query and edit data in .NET Framework datasets, you need:

Edit rows in a dataset

To edit an existing row in a DataTable, you need to locate the DataRow you want to edit, and then assign the updated values to the desired columns.

If you don't know the index of the row you want to edit, use the FindBy method to search by the primary key.

NorthwindDataSet.CustomersRow customersRow = 
    northwindDataSet1.Customers.FindByCustomerID("ALFKI");

customersRow.CompanyName = "Updated Company Name";
customersRow.City = "Seattle";

Once you know the row index, you can use it to access and edit the row.

northwindDataSet1.Customers[4].CompanyName = "Updated Company Name";
northwindDataSet1.Customers[4].City = "Seattle";

Insert new rows into a dataset

Applications that use data-bound controls typically add new records by using the Add New button on a BindingNavigator control.

You can also add a new record to a dataset by calling the NewRow method on the DataTable. Then, add the row to the DataRow collection (Rows).

NorthwindDataSet.CustomersRow newCustomersRow = 
    northwindDataSet1.Customers.NewCustomersRow();

newCustomersRow.CustomerID = "ALFKI";
newCustomersRow.CompanyName = "Alfreds Futterkiste";

northwindDataSet1.Customers.Rows.Add(newCustomersRow);

Delete records from a data table

You can delete a DataRow by calling the Remove method of the DataRowCollection, or by calling the Delete method of the DataRow object.

The Remove method removes the row from the collection. If your application doesn't need to send updates back to a data source, you can remove records by directly accessing the data row collection with Remove.

Conversely, the Delete method doesn't actually remove the DataRow, but marks it for deletion. The actual removal occurs when you call AcceptChanges method. You can programmatically check which rows are marked for removal before actually deleting them.

To retain the information that the dataset needs to send updates to the data source, use the Delete method to remove the DataRow from the data table. If your application uses a TableAdapter or DataAdapter, the Update method deletes rows that have a RowState of Deleted.

The following example shows how to call the Delete method to mark the first row in the Customers table as deleted:

northwindDataSet1.Customers.Rows[0].Delete();

Note

If you get the count property of a DataRowCollection, the resulting count includes records that are marked for deletion. To get an accurate count of records not marked for deletion, you can loop through the collection and look at the RowState property of each record. Records marked for deletion have a RowState of Deleted. Alternatively, you can create a data view of a dataset that filters based on row state and get the count property from it.

Determine whether there are changed rows

When you make changes to records in a dataset, information about those changes is stored until you commit them. You commit the changes when you call the AcceptChanges method of a dataset or data table, or when you call the Update method of a TableAdapter or data adapter.

Changes are tracked two ways in each data row:

  • Each data row contains information related to its RowState, for example Added, Modified, Deleted, or Unchanged.

  • Each changed data row contains multiple versions. The DataRowVersion includes the original version before changes and the current version after changes. While a change is pending and you can respond to the RowChanging event, a third proposed version is also available.

To check for changed rows, call the HasChanges method of a dataset. The method returns true if changes were made in the dataset. After determining that changes exist, you can call the GetChanges method of a DataSet or DataTable to return a set of changed rows.

The following example shows how to check the return value from the HasChanges method to detect whether there are any changed rows in NorthwindDataset1.

if (northwindDataSet1.HasChanges()) 
{
    // Changed rows were detected, add appropriate code.
}
else
{
    // No changed rows were detected, add appropriate code.
}

Determine the type of changes

You can check to see what type of changes were made in a dataset by passing a value from the DataRowState enumeration to the HasChanges method.

The following example shows how to check the NorthwindDataset1 dataset to determine whether any new rows were added.

if (northwindDataSet1.HasChanges(DataRowState.Added)) 
{
    // New rows have been added to the dataset, add appropriate code.
}
else
{
    // No new rows have been added to the dataset, add appropriate code.
}

Locate rows that have errors

When you work with individual columns and rows of data, you might encounter errors. You can check the HasErrors property to determine whether errors exist in a DataSet, DataTable, or DataRow.

If the HasErrors property for the dataset is true, iterate through the collections of tables, and then through the rows, to find the rows with the errors.

private void FindErrors() 
{
    if (dataSet1.HasErrors)
    {
        foreach (DataTable table in dataSet1.Tables)
        {
            if (table.HasErrors)
            {
                foreach (DataRow row in table.Rows)
                {
                    if (row.HasErrors)
                    {
                        // Process error here.
                    }
                }
            }
        }
    }
}

Related content