Instructor’s NotesProgramming Logic - IntermediateDatabase Applications - Phase II

Programming Logic - Intermediate

152-102

Database Applications - Phase II

Notes / Activity
Text References
  • Adding Lookup FieldsNo book references
  • Inserting a New Record
  • Catching Duplicate Records
  • Enabling / Disabling Buttons
  • Sensing Field Changes
  • Hiding Details
  • Displaying Save Status
  • Finishing the Application

Overview
  • Our current database application is great for viewing, editing and even deleting records, but it's far from being a complete database application.
  • We need to create lookup fields to simplify linking multiple tables together
  • We definitely need to provide a way for the user to add new records.
  • In additional, our application needs some fine tuning to add a professional touch.
/ Open the Unit 4 database. Note the Customer table and the link to the customer table in Orders
Note: the customer table could contain other information about customers as well (other fields)
Modify the XSD to match the database design
  • Rename table adapters (old)
  • Create tblOrders (FillByID, DeleteOrder, UpdateOrder)
  • Create qryOrderLU (Note resulting fields are the same)
  • Create tblCustomers (for size validation)
  • Create qryCustomerLU
  • Delete Old table adapters
  • Note errors

Adding Lookup Fields
  • Most databases contain many tables that are related to each other using linking fields
  • The linking field is the primary key field from one table that is also included in another table
  • These linking fields are often auto-number fields that have no meaning to users (customer number for instance)
  • Good applications allow the user to lookup values for a linking field using more natural data (customer name) and then translate the user’s selection to the appropriate linking field value (customer number)
/ Create the Customer class
Move First and Last members and properties from clsOrder.
Add customerID member and property (readonly)
Add a method to the Customer class to retrieve the lookup data
CustomerLookupTable
  • Because of C#’s data handling tools and our 3-tier architecture, implementing lookup fields is fairly straightforward.
The lookup data is probably already available. The class definition for the table we want to look up from probably already has a lookup method that was used to fill the select grid on the form for that class
We should be able to use the same query and method for our lookup field
  • Most lookup fields are implemented using a ComboBox (with SuggestAppendAutoComplete)
ListBoxes could also be used / Modify clsOrder
  • Remove first and last members
  • Replace with CustomerID
  • Remove first and last properties
  • Replace with CustomerID (validate >0)
  • Correct all other class errors.

  • To serve as a lookup field, the combo box needs to know three things:
Where is the data to be used?
Which field should be displayed in the combo box?
Which field contains the linking value?
/ Replace the customer first and last name fields with cmbCustomer.
Make SuggestAppend
Update tab order
Replace or remove all references to old text boxes
  • To provide the answers to these questions, we’ll create a LoadComboBoxmethod(pick a better name) in the GUI class which will be called from Form Load
The first task for this method is to request the lookup data from the business class (same technique used to fill the select grid)
Note: we will not be using a binding source for our lookup fields. We could, but the benefits of a binding source (filtering, resorting, positioning) are not needed, so we’ll bypass the binding source.
/ Create the LoadCustomersmethod shell.
Call it from Form Load
cmbName.DataSource = ClassName.LookupTable();
The statement calls the class LookupTable method to get a data table full of lookup data
This data table is immediately linked to the ComboBox DataSource property, effectively answering the first question (where’s the data?)
/ Define the data source for cmbCustomer
To designate which field to display and which contains the linking field we’ll set the DisplayMember and ValueMember properties of the ComboBox
cmbName.DisplayMember = "fieldname";
cmbName.ValueMember = "fieldname";
Note the field names are surrounded by quotes
The lookup table must have at least two (oftenonly two) fields.
That’s it. Now when you display the form, the ComboBox should display a list of all values in the lookup table using human-readable text
But how do you determine the lookup value for the value the user selected in the combo box?
It’s automatic. When you select a value from the combo box, the appropriate value for the ValueMember field is also set.
However, the ValueMember is the name of the field that contains the linking value. The actual value itself is stored in the ComboBox SelectedValue property (only available at runtime). / Define the DisplayMember and ValueMember for cmbCustomer
Test (cmbCustomer should contain values)
The Validating event for a ComboBox looks just like any other Validating event
The SelectedValue (converted to the appropriate type) is transferred to the appropriate field in the class.
Because we are using the ComboBox bound to a data table, validation is also easier. If the user types in a value that is not on the list, the SelectedValue property will be null.
If the ComboBox text is blank, transfer null to the business class.
If you try to convert nullto an int, a conversion error occurs. I recommend you use a nullableint type for the ID.
The business class should ensure the linking value is greater than 0 if the field is required
The business class should convert non-zero values to null (when saving) if the field is optional
/ Code cmbCustomer Validating
Test cmb validation
  • We will also set the SelectedValue property when we display a record
    cmbStudent.SelectedValue = c.StudentID;
When you set the SelectedValue property, the ComboBox displays the corresponding DisplayMember value for this record
/ Update DisplayRecord
Test
Lab for Part 4
Inserting a New Record
  • Because our form (and class) allows the user to edit existing records and add new records, we’ll have to add code to ensure we can handle both.
  • As it turns out, the code for adding (saving) a new record is very similar to saving the changes for an existing record.
  • What we’ll need first is a way to determine whether we’re adding a new record or editing an existing record
Most tables use an auto-number field as the primary key. These tables only need a Boolean method that determines if the ID field (auto-number) is still set to the default value set in the constructor.
NOTE: a little later we’ll be setting the ID number to a new value, basically clearingisNewRecord.
If the primary key is not auto-number, we’ll add a new data member to the business class: _newRecord (Boolean)
We’ll also add a read only property for this data member so the GUI can ask the business class if a new record is being added (isNewRecord).
True designates that we’re adding a new record (False means the current record is being edited).
When to set this flag to True is pretty easy to figure out—whenever a new instance of the class is created.
We’ll also have to reset the flag to False when appropriate
▪After a successful Save
▪Whenever an existing record is retrieved
(handles Cancel after New record) / Create isNewRecord (method, no data member)
(optional not needed with this technique)
Set to True in New
Set to False in GetRecordSet to False in SaveRecord
  • The GUI needs to clear the form whenever the user requests a new record
The easy way to do this is to ensure the constructor method of the business class sets each field to the appropriate initial value.
Then, in the GUI btnNew, instantiate a new record.
After the instantiation, call DisplayRecord to transfer the record contents to the form. / In btnNew, instantiate a new record (with currRecord pointing to it)
Display Record
Set focus to first input field
Test
(Test Cancel too)
Clear radio buttons
Only calc if not new
  • Things get a little more complicated when the user saves the new record.
  • To start with, we’ll need a new Insert query that allows us to add a new record into the database.
As it turns out, the primary table adapter automatically created an Insert query when we initially built the adapter. However, that query seems to confuse doubles and decimals.
The easiest solution is to add our own Insert query to the table adapters (Add, Query, Insert) / Add InsertOrder query
  • We need to modify the Save Record method of the business class to distinguish between the need to insert a new record or update an existing record.
Add an If statement to check the status of isNewRecord.
When True, insert a new record
The Insert query requires you provide values for all fields in the database.
Watch the Intellisense popup to help you remember the order.
▪Alternatively, simply copy the Update command and change the query name
If there is an auto-number field (there often is), it is NOT included in the Insert query.
Note, you could simply copy the parameters from the Update command and remove the last one.
When false, use the Update command that is already in the method.
/ Update the Save Record method
If the table has an auto-number key field (which is always included in the grid, but hidden).
How do we know what auto-number value was assigned to the new record?
We’ll create a new query in the primary table adapter (single value query), using the following SQL command:
Select Max(tblID) From tblTable
Replace tblID with the auto-number field name and tblTable with your table name.
This command returns the maximum ID number in the table. Because all auto-number fields never recycle the numbers of deleted records, the LastId (my name for the query) must be the ID assigned to the latest record.
After the new record has been inserted (in business class SaveRecord), set the _myID = ta.LastID().Value; / Create the LastId query
  • Now, the GUI’s btnSave event needs to be updated as well.
When changing the grid contents to reflect the changes in the database, the code now needs to distinguish between new and changed records
Add an If statement that checks the class isNewRecord status (property).
When false, simply use the existing code that updates the grid. / Add the If statement before the statements that update the grid
Move existing code inside the Else clause
When isNewRecord is true, we’ll have to add a new record to the grid.
We could simply reload the grid (binding set), but for large tables, this is time consuming and impractical.
I’ve found it’s easier to add a new row to the grid’s binding set, after which the new row will automatically be displayed in the grid.
To create a new record in the binding set
varnewRec = (DataRowView)bsSelect.AddNew();
bsSelect.AddNew tells the binding set we’re adding a new row
newRec is a pointer to that new row
Because binding sets can display different kinds of rows, we have to define the row as a generic row (DataRowView) and convert the newly added row to this type. / Create the new row in the binding set
Now, we can transfer the new field data to the new binding set record in the GUI
newRec["IDfield"] = currRecord.OrderID;
newRec["otherfield"] = cmbField.Text; / We will also need to remember if this is a new record because SaveRecord resets the flag
Fill the appropriate columns with data from the new record
The field values are added to the appropriate fields in the new record.
Note, you could also use the current values in currRecord instead of the values in the ComboBoxes and TextBoxes, but our calculated field is not available in currRecord, so we have to get the customer’s name from the combo box.
Because the row that newRec points to has already been added to the binding source, it will automatically appear in the grid.
Your code to sort the grid and locate the most recent record should still be applicable.
/ Test
Note errors (continue below)
  • Modifying grdSelect_SelectionChanged
grdSelect_SelectionChangedfires (executes) as soon as your code adds a new row to the grid because the current row changes to the new blank row
This causes undesirable side effects (grdSelect_SelectionChangedtries to Display the new blank record).
grdSelect_SelectionChangedfires again when your code executes EndEdit (again, undesirable)
Because of all this extra code execution, the new record is not selected in the grid after the new record has been saved.
To fix this problem we’ll implement a form-level flag (Boolean variable) that I call insertingRecord.
btnNew: set flag to true
btnCancel: set flag to false (first line)
btnSave: after binding.Position set to false
grdSelect_SelectionChanged: surround all code with an if statement:
if(!insertingRecord) {
/ Update grdSelect_
SelectionChanged
Remove isNew variable in btnSave. Use insertingRecord.
Test
  • Finally, to make life a little easier for the user, we’ll let the user click the Delete button to delete a new record even if it hasn’t been saved yet.
In btnDelete, see if the new record flag is set. If so, click btnCancel.
/ Update btnDelete
Test
Catching Duplicate Records
  • The last thing you want in a database table is duplicate records.
  • If the table does not use an auto-number field, attempting to add a duplicate record causes an error in the database program which also causes your program to crash.
  • If the table does include an auto-number field, you still need to check for duplicate records to maintain the integrity of the data.
Check for duplicates in the logical key fields—the fields whose combination of values should not include duplicates.
Note: the logical keys are often the same fields you have showing in your lookup grid (but not always).
  • The first thing we’ll need to do is provide a means of determining if the key fields have changed.
If the key fields haven’t changed we shouldn’t check for duplicate records.
If we do, we will always get a duplicate record error because the current field combination is already in the database.
This will be a business class responsibility (part of validation), so we’ll add a new data member, _orgKeys, that will be a concatenated string of all the logical key values when the record was originally retrieved from the database. / Create _orgKeys
Because concatenating the fields might be complicated (and to provide similarity between all my business classes), we’ll also create a privatemethod (KeysNow) to concatenate all the logical key values.
This method returns a string / Write KeysNow to concatenate order date, customer ID
  • To determine if a record already exists in the database, we’ll obviously need database access and a new query.
  • The new query (DuplicatesFound) counts the number of records that have the exact combination of our current logical key fields.
  • To create the query:
Double-click XSD
Right-click primary table adapter (not the lookup)
Add Query
Use SQL
Select which returns a single value
Select statement should already contain Count(*).
Count(*) creates one value that counts the number of records that meet your criteria
Add parameter for each logical key field:
Where fieldname=? [And fieldname2=?]
Change name to DuplicatesFound
Finish
/ Add DuplicatesFound query to tblOrders Table Adapter
  • Next, we’ll need a method (private) in the business class that invokes this query.

privateboolisDuplicateRecord() {
varta = newdsOrdersTableAdapters.tblOrdersTableAdapter();
returnta.DuplicatesFound(OrderDate, CustomerId) > 0;
}
  • I define the method as a Boolean method (true represents a duplicate record).
  • As always, we define a table adapter variable (ta) to provide access to the query.
  • The return statement invokes the query and immediately compares the number sent by the query to zero.
/ Type or copy the above method (Data Access)
  • Checking for duplicate records is a consistency check type of validation, (most likely) involving multiple fields. So, we’ll postpone checking for duplicate records until we’re trying to save the record (InvalidFields)
In InvalidFields, after checking all the field validity, check for duplicate records / Update InvalidFields. Sense changes in keys
//Check for duplicate record, but only if the logical keys have changed.
if (badFields == "") {//Only do consistency check if no other others
if (_orgKeys != KeysNow()isDuplicateRecord()) {
badFields += " Duplicate customer order";
}
}
First, we check to see if the logical keys have changed: are the original key values different than the keys now?