Chapter 5 Windows Database UpdatesThe Data AdapterThe DataSet Methods and PropertiesThe DataRowState EnumerationThe HasChanges MethodThe GetChanges MethodThe Edit MethodsDataRow VersionsThe AcceptChanges MethodSlide 10Changing the Data SourceThe Data Adapter Update MethodWhen to Update?The Form’s Closing EventModifying DataDeleting RowsAdding RowsConstraintsThe BindingContext ObjectBindingManagerBase ObjectsThe Binding managerDeclaring a Binding Manager ObjectBinding Manager PropertiesThe Current PropertyThe Position PropertyNext RecordPrevious RecordBinding Manager MethodsBinding Manager EventsAdding an Event HandlerDisplaying the Record Number and Record CountSQL Statements for UpdatesConcurrencyConcurrency ControlTesting Update ProgramsThe Logic of an Update ProgramDeleting RecordsAdding RecordsSlide 39Saving an Added RecordThe Binding BugEditing RecordsBinding to Combo BoxesSample Combo Box SettingsParent and Child RelationshipsSlide 46Cascading Deletes and UpdatesThe Related-Table Update ProgramFiltering the Child TableThe Update Commands – Save the Child Record DeletesThe Update Commands – Save all Parent Record UpdatesThe Update Commands – Save the Child Adds and EditsChapter 5 Windows Database UpdatesAdvanced Programming Using Visual Basic.NET © 2003 by The McGraw-Hill Companies, Inc. All rights reserved.© 2003 by The McGraw-Hill Companies, Inc. All rights reserved.5- 2The Data Adapter•A dataset is a temporary source of data•A dataset is disconnected from the original data source–Changes are not automatically sent back to datasource•The data adapter is used to send changes back to the data sourceThe Data ObjectsThe Data ObjectsDatabase(Original data source)Data Adapter DatasetRetrieve andupdate dataCreate datasetand save updates© 2003 by The McGraw-Hill Companies, Inc. All rights reserved.5- 3The DataSet Methods and Properties•Each row of data has a RowState property which indicates whether changes have been madeThe Data ObjectsThe Data ObjectsDataSetDataTablesCollectionDataTable(0)DataRowsCollectionDataRow(0)DataRow(1)DataRow(n)© 2003 by The McGraw-Hill Companies, Inc. All rights reserved.5- 4The DataRowState EnumerationThe Data ObjectsThe Data ObjectsDataRowState Enumeration PurposeAdded Indicates that this is a new row.Deleted The row is marked for deletion.Detached The row is not part of a collection. A row has the detached value before it is added or after it has been removed.Modified Changes have been made to the row.Unchanged No changes have been made to the row.© 2003 by The McGraw-Hill Companies, Inc. All rights reserved.5- 5The HasChanges Method•Determines if changes have been made to a dataset•Returns a boolean valueThe Data ObjectsThe Data ObjectsIf DsEmployee1.HasChanges() Then ‘Query the user to save the changesEnd IfIf DsEmployee1.HasChanges() Then ‘Query the user to save the changesEnd IfIf DsEmployee1.HasChanges(DataRowState.Deleted) Then ‘Code to handle the deletion(s)End IfIf DsEmployee1.HasChanges(DataRowState.Deleted) Then ‘Code to handle the deletion(s)End If© 2003 by The McGraw-Hill Companies, Inc. All rights reserved.5- 6The GetChanges Method•Use to retrieve the rows that have changes•Use an empty argument to retrieve all changed rows OR specify type of changes using enumeration values•Create a new dataset to hold the change rowsThe Data ObjectsThe Data ObjectsDim dsEmployeeChanges As DataSetdsEmployeeChanges = DsEmployee1.GetChanges()Dim dsEmployeeChanges As DataSetdsEmployeeChanges = DsEmployee1.GetChanges()Dim dsEmployeeDeletes As DataSetdsEmployeeDeletes = DsEmployee1.GetChanges(DataRowState.Deleted)Dim dsEmployeeDeletes As DataSetdsEmployeeDeletes = DsEmployee1.GetChanges(DataRowState.Deleted)© 2003 by The McGraw-Hill Companies, Inc. All rights reserved.5- 7The Edit Methods•To modify a row of data, the row must be in edit mode•Edit methods are called automatically for bound controls•The BeginEdit is called when an edit begins•The EndEdit executes when the method terminates•Use the CancelEdit method to return field values to their original values–Be sure to call before EndEdit is executedThe Data ObjectsThe Data Objects© 2003 by The McGraw-Hill Companies, Inc. All rights reserved.5- 8DataRow Versions•The DataRow object maintains several versions of its column data–Current, Original, Default, Proposed•If no changes have been made, the Current and Original versions are the same•When EndEdit executes, the Current version is replaced by the Proposed version•The EndEdit method confirms the changes•Changes are made when the AcceptChanges method executes–To the dataset© 2003 by The McGraw-Hill Companies, Inc. All rights reserved.5- 9The AcceptChanges Method•Calls the EndEdit method of the data row•Removes all rows marked for deletion•Makes the adds and edits indicated for the table•Sets the Original version of each changed row to the Current version•Sets the RowState of each row to UnchangedThe Data ObjectsThe Data Objects© 2003 by The McGraw-Hill Companies, Inc. All rights reserved.5- 10The AcceptChanges Method•The AcceptChanges method commits all changes to the dataset•The RejectChanges method rolls back all changes that have been made by replacing Current versions with the Original versions•After AcceptChanges or RejectChanges executes, all RowState properties are reset to UnchangedThe Data ObjectsThe Data Objects© 2003 by The McGraw-Hill Companies, Inc. All rights reserved.5- 11Changes are made to the dataset,not to the original data source.Execute the data adapter’sUpdate method before callingthe AcceptChanges method.Changing the Data SourceThe Data ObjectsThe Data Objects© 2003 by The McGraw-Hill Companies, Inc. All rights reserved.5- 12The Data Adapter Update Method•Execute the update method after every change or when the program terminates•General form•ExamplesThe Data ObjectsThe Data ObjectsDataAdapter.Update(DataSet)DataAdapter.Update(DataSet, “Table”)DataAdapter.Update(DataSet)DataAdapter.Update(DataSet, “Table”)daBook.Update(DsBooks1)daBook.Update(DsBooks1, “Books”)daBook.Update(DsBooks1)daBook.Update(DsBooks1, “Books”)© 2003 by The McGraw-Hill Companies, Inc. All rights reserved.5- 13When to Update?•Every time an add, edit, delete occurs?•When the program terminates?•Provide a Save option on a menu and prompt for unsaved changes when the program terminates?The Data ObjectsThe Data