1 / 50

Chapter 5

Chapter 5 . Windows Database Updates. Objectives. Update a database table in a grid and in individual controls Use the BindingSource properties for navigation and for determining the current record number Update the original data source by saving the values from a dataset

fedora
Download Presentation

Chapter 5

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 5 Windows Database Updates

  2. Objectives • Update a database table in a grid and in individual controls • Use the BindingSource properties for navigation and for determining the current record number • Update the original data source by saving the values from a dataset • Validate user input in an update program • Update related tables

  3. A Simple Update in a Grid - 1 • A DataGridView object includes features that allow the user to update the dataset • Records can be added or deleted • The user can modify data in any row in the grid • All changes are made in the dataset (the in-memory copy of the data) • When the user clicks the Save button, the BindingNavigator’s SaveItem event is fired and an attempt is made to save the data back to the original data source • The update code is automatically generated by the designer when a BindingNavigator is added to a form

  4. A Simple Update in a Grid - 2 • A dataset is a temporary set of data in memory • Changes can be made to the rows of data in the dataset • Changes are not automatically sent back to the original data source • TableAdapter serves as a go-between for the data source and the dataset • Execute the Update method of the TableAdapter or the UpdateAll method of the TableAdapterManager to send changes to the original data source

  5. A Simple Update in a Grid - 3 • TableAdapter retrieves data from the data source to create the dataset and sends back changes to the data source

  6. Database Handling inthe Visual Studio IDE • First time a program is run in the debugger, the database file is copied into the bin\Debug folder and is the file used by the program • By default, the file’s Copy to Output Directory property is set to Copy always • For database updates to show up from one run to the next, select the filename in the Solution Explorer • Change the setting for Copy to Output Directory to Copy if newer • The debugger checks the file version and copies the file when it is newer or did not exist

  7. The Data Objects,Methods, and Properties - 1 • To write more sophisticated update programs, the various methods and properties of data objects must be understood See Table 5.1 page 191 Overview of Useful Data Methods and Properties

  8. The Data Objects,Methods, and Properties - 2 • A DataSet object can consist of multiple tables • Each table can consist of multiple data rows • Data values are stored in data rows • A row’s RowState property indicates whether changes have been made to the row

  9. The Data Objects,Methods, and Properties - 3 DataRowState Enumeration Values

  10. The HasChanges Method • Call the HasChanges method to determine if any changes have been made to a dataset, returns a Boolean value If PubsDataSet.HasChanges() Then ‘ Ask the user to save the changes. End If • Specific types of changes can be checked for using an overloaded version of the HasChanges method If PubsDataSet.HasChanges(DataRowState.Deleted) Then ‘ Code to handle the deletion(s). End If

  11. The 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 Dim EmployeeChangesDataSet As DataSet EmployeeChangesDataSet = PubsDataSet.GetChanges() • Create a dataset to hold all rows marked for deletion Dim EmployeeDeletesDataSet As DataSet EmployeeDeletesDataSet = PubsDataSet.GetChanges(DataRowState.Deleted)

  12. The Edit Methods • To modify a row of data, the row must be in edit mode • Edit methods are called automatically for bound controls • BeginEdit method executes when an edit begins • EndEdit method executes when an edit terminates • CancelEdit method returns field values to their original values

  13. DataRow Versions • The DataRow object maintains several versions of its data • Current, Original, Default, and 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 AcceptChangesmethod executes

  14. The AcceptChanges Method - 1 • 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 Unchanged • Clears any RowError information and sets the HasErrors property to False

  15. The AcceptChanges Method - 2 • 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 Unchanged • The dataset is disconnected so changes are made to the dataset and not to the original data source • Execute the TableAdapter’s Update method or the TableAdapterManager’s UpdateAll method before calling the AcceptChanges method

  16. The TableAdapter andTableAdapterManager - 1 • Updates can be performed after every change or upon program termination • Combine the two techniques by providing a Save option • Prompt for unsaved changes when program terminates • TableAdapterManager manages updates to multiple related tables • TableAdapter updates single tables

  17. The TableAdapter andTableAdapterManager - 2 • Update considerations • Where does the application and data reside • How many users can make changes • Does the data source need to be up-to-date at all times • To prompt for unsaved changes, place update method in the form’s FormClosing event handler

  18. The BindingSource Object • The BindingSource object manages table data for bound controls • Controls the record position within a table • Assures all bound controls on a form display data from the same record • Uses properties to determine current record and for navigation

  19. Binding SourceProperties and Methods • Displaying the record number • The Position property holds the current row number • Add 1 to the Position property, since it is zero based • Count property indicates number of records in a table • Current property retrieves the current row

  20. Navigating UsingBindingSource Methods • MoveFirst moves to the first row • MoveNext moves to the next row • MoveLast moves to the last row • MovePrevious moves to the previous row • Do not try to move to a record beyond the last record or before the first record • Last record is at a position of Count minus one • First record is at position zero

  21. Binding Source Update Methods

  22. Binding Source Events • Two useful events for the BindingSource class • CurrentChanged event occurs when a bound value is changed • PositionChanged event occurs when a user navigates to another record • The PostionChanged event handler is a good place to display the current record number in a label or the status bar

  23. DataSet Updating • ADO.NET handles the complicated process of updating the original data source • Each row in a table has a RowState property • Can be set to Unchanged, Modified, Added, or Deleted • Execute the Update method • All of the indicated changes from the dataset are made in the original data source for all rows that have a RowState other than Unchanged

  24. SQL Statements for Updates • When a data source is added, several SQL statements are generated • SELECT, INSERT, DELETE, and UPDATE statements are created by default • Examine the CommandText properties of the DeleteCommand, InsertCommand, and UpdateCommand properties of the TableAdapter

  25. Concurrency • Concurrency problems can occur when more than one user can update a file at the same time • Concurrency control is the process of handling conflicts in updates by multiple users • Three types of concurrency controls in ADO .NET • Pessimistic—A row is unavailable from the time the record is retrieved until the update is complete • Optimistic—A row is unavailable only while an update is in progress (the default) • “Last in wins”—A row is unavailable only when the update is being made • No checks are made for multiple changes to the same record

  26. Testing Update Programs • Many types of errors may be encountered when testing an Add or Update in an update program • Must have proper rights to the database to allow writing to the data source • Be aware of constraints • Fields that contain nulls, required fields, specific values in fields • Include exception handling for all statements that access the database • Display the exception message to help determine the cause of any problems

  27. Updating a DataSet in Bound Controls • Using bound individual controls is more common than using a grid • Display dataset fields in bound text boxes so users can type in changes • Keep text boxes set to Read Only unless an Add or Edit is in progress • All of the techniques for updating a dataset apply equally to an Access database and a SQL Server database

  28. The Logic of an Update Program • Update program needs procedures to modify existing records (editing records), delete records, and add new records • Call the Update method after every change so that the data source is up-to-date • Enclose all statements that access the dataset in Try/Catch blocks

  29. User Options during an Update • Limit the options available to users during an update • While Add or Edit is in progress, the only options should be Save or Cancel • While navigating from one record to another, do not allow changes to data

  30. Adding a Record • Logic for an Add operation is more complicated than other operations • User clicks an Add button • Program must clear and unlock text boxes and allow the user to enter data for the new record • Record navigation must be disabled

  31. Pseudocode for anAdd Operation • Call the BindingSource’s AddNewmethod, which begins the Add and clears the text boxes • Set AddingBoolean to True • Set the focus to the first text box • Disable navigation • Set the text boxes’ ReadOnly Property to False • Set up the buttons for an Add: • Set up the Save and Cancel buttons • Disable the Edit button • Display “Adding” in the status bar

  32. The Edit Logic • Display data fields in bound text boxes • Set the ReadOnly property of each text box to true, locking the text box • For bound check boxes, lists, and grids set the Enabled property to false • Disable navigation so users cannot move off the record and automatically save the changes • The only choices a user should have during an Edit are Save or Cancel

  33. Pseudocode to Begin an Edit • Set EditingBoolean to True • Disable navigation • Set the text boxes’ ReadOnly property to False • Set up the buttons for an Edit: • Set up the Save and Cancel buttons • Disable the Edit button • Display “Editing” in the status bar

  34. Navigating from aCombo Box Selection - 1 • During navigation using a combo box, the text box of the combo box must not allow changes to the displayed value • Combo box text binding should be set to DataSourceUpdateMode.Never • Change the binding at run time using bang notation NameComboBox.DataBindings!text.DataSourceUpdateMode = _ DataSourceUpdateMode.OnValidation

  35. Navigating from aCombo Box Selection - 2 • During an Add or Edit a user must not be allowed to make a new selection from the combo box • Must allow text to be typed in the text portion of the combo box • Set the DropDownStyle to Simple during an Add or Edit • After a Save or Cancel change the combo box’s DropDownStyle back to DropDownList • Handle the Escape key in code

  36. Validating User Input Data • As users enter data, some fields need to be validated • Constraints, such as required fields or the data type • There may be business rules for validating data • Validating data before sending it back to the database can reduce the number of round trips between the application and the database • Perform field-level or record-level validation in the code for the form or the dataset • Recommended practice is to place it in the dataset

  37. Checking for Nulls • A common problem for programming database updates relates to null fields • If the user does not enter data in a required field and the record is sent to the database, an exception occurs if that field does not permit nulls • Check to see which fields allow nulls in two ways • In the DataSet Designer click on each individual field and view the AllowDBNull property • View settings for an entire table by using the Server Explorer • Select View/Server Explorer to see the window • Expand the connection node and find the name of the table • Right-click on table name and select Open Table Definition

  38. Adding Validation to aDetails View Program • Use the Validating event of bound controls to validate user data • Set Cancel button’s CausesValidation property to false • User can cancel an edit • In a control’s Validating event handler, set e.Cancel = True for bad data • Display an error message to the user using the ErrorProvider control • Allow the user to close an application, even when a validation error holds the focus in a control

  39. Adding Validation to the DataSetfor a DataGridView Program • Write validation code inside the dataset class (preferred approach) • Keeps the validation logic separate from the form’s logic • Write event handlers for events of the data table • ColumnChanged and TableNewRow events • Handle the Escape key

  40. Handling Data Exceptions • Never good for a program to cancel with a data exception • Prevent data input errors with validation • Catch other errors with exception handling • BindingSource for each table has a DataError event • Fires when an attempt is made to save bad data • DataGridView has a DataError event • Helps identify errors in the data in the grid

  41. The DataGridViewDataError Event • Catch an error in the data for a DataGridView • Display a message and icon in the current row • RowIndex property of the EventArgs for the DataError event handler identifies the row in error • Set e.Cancel = True to hold the user on the current row

  42. The BindingSourceDataError Event • Catch an error in data displayed in details view • Write code for the BindingSource’s DataError event • Specific information not given regarding location of the error • Write a generalized message in a message box or in an error provider

  43. Updating Related Tables • The TableAdapter.Update method issues the proper INSERT, DELETE, and UPDATE SQL commands for a single table • If updating multiple tables with parent and child relationships, the programmer must make sure the commands are executed in the correct sequence • TableAdapterManager component greatly simplifies updating related tables • Generated automatically when a table or field is dragged to a form and a new BindingSource is created

  44. Parent and Child Relationships • To add a new child and parent record, add the parent first • Only way to set relationship for child record • Cannot add child records for a parent record that has not yet been created • To delete a parent record, all child records must be deleted first • A parent record cannot be deleted if there are associated child records in existence

  45. Cascading Deletes and Updates • Help to maintain referential integrity • Set up the relationship for cascading updates and cascading deletes in the Relation dialog box

  46. Hierarchical Updates • The TableAdapterManager.UpdateAll method issues the correct updates to the original data source in the correct order • Sends all inserts (parent, then child) first • Sends all updates (parent, then child) next • Sends all deletes (child, then parent) last

  47. A Related-Table Update Program –Step-by-Step - 1 • User can add, delete, and edit store records (the parents) and add, delete and edit sales records (the child records) • Data source is updated when Save or FormClosing event handlers are executed

  48. A Related-Table Update Program –Step-by-Step - 2 • Modify the tables’ relationships • Create the data-bound controls • Modify the generated code • Commit added parent records before adding child records • Add the save query for exit • Run the application

  49. A Related-Table Update Program –Step-by-Step - 3 • Help the user enter valid data • Add exception handling • Add a table lookup column for the titles • Add validation to the dataset • Run the application

  50. Security Considerations • Do not provide a user with information that could be used to violate database security • Do not use actual field names in error messages • Use descriptive, friendly names for the data items • The practice of displaying ex.Message in a Catch clause is useful for testing and debugging a program • It should not appear in a production program because it may contain actual field names

More Related