Data Manipulation with ADO.NET

Editing Data With ADO .NET

In this tutorial you will learn about Editing Data With ADO .NET - Updating Data, Adding Data, Typed data set, Untyped data set, Deleting Data, Editing with a DataGrid.

Updating Data

The SqlDataAdapter’s update method is called whenever the changes made to a DataSet has to be applied to the underlying table. The SqlDataAdapter is instantiated using eh SELECT statement and the compiler generates the statements for UPDATE, INSERT and DELETE. The changes made to the DataSet are then applied to the Database. However considerations for automatic update will fail if there is no primary key assigned to the data in the Database. The update is also likely to fail if the AcceptChanges method of the DataSet is called before the Update method of the SqlDataAdapter is called. The dataset maintains the row versions like Deleted rows, modified rows, inserted row and so on. When the UPDATE method is called this version information is used to update the relevant changes to the database. The AcceptChanges method removes all the versions and hence the UPDATE will fail. AcceptChanges is usually called after Update method.

The Update method of the Adapter will be called within a Try Catch block. When two tables are updated in a dataset, the following sequence is advised as a best practice:

(a) Update Child table – Delete records. The code sample is given below

(b) Dim DeletedChildRecords as DataTable = _

(c) DSet.Table.GetChanges(DataRowState.Deleted) DataAdapter.Update(DeletedChildRecords)

(d) Parent Table – Insert, Update, and delete records

(e) Child Table – Insert and update records.

Adding Data

Adding new rows to the DataSet can be performed in any one of the following methods:

Typed data set

Dim newCustomersRow as DataSetClass.Customers.CustomersRow

newCustomersRow = DataSetInstance.Customers.NewCustomersRow()

newCustomersRow.CustomerID = "ALFKI"

newCustomersRow.CompanyName = "Alfreds Futterkiste"

DataSetInstance.Customers.Rows.Add(newCustomersRow)

Untyped data set

Dim newCustomersRow As DataRow = DataSet1.Tables("Customers").NewRow()

newCustomersRow("CustomerID") = "ALFKI"

newCustomersRow("CompanyName") = "Alfreds Futterkiste"

DataSet1.Tables("Customers").Rows.Add(newCustomersRow)

Deleting Data

In order to retain the information that the dataset needs to send updates to the data source, the System.Data.DataRow.Delete method is used to remove rows in a data table. For example, if the application uses a TableAdapter (or DataAdapter), the adapter's Update method will delete rows in the database that have a RowState of DataRowState.Deleted.

If the application does not need to send updates back to a data source, then it is possible to remove records by directly accessing the data row collection DataRowCollection.Remove

To delete records from a data table call the DataRow.Delete method of a DataRow. This method does not physically remove the record; instead, it marks the record for deletion.

If the count property of a DataRowCollection is obtained, the resulting count includes records that have been marked for deletion. To get an accurate count of only records that are not marked for deletion, the user can loop through the collection looking at the RowState property of each record (records marked for deletion have a RowState of DataRowState.Deleted).

The following line of code is used to delete nth row.

DataSetXX.TableYYY.Rows(n).Delete()

Editing with a DataGrid

A DataGrid can be edited using the BeginEdit and EndEdit methods of the DataGrid. The following illustration clarifies the concepts of editing a DataGrid.

1. Identify the current cell by its ColumnNumber and RowNumber properties and call the method BeginEdit of the DataGrid .
2. Create the DataTable and DataRow Objects.
3. Assign the edited values to the DataRow.
4. Accept changes by calling the AcceptChanges method of DataRow.
5. Call the EndEdit method of the DataGrid.

Click here to view sample code