Datasystemer og nettverk. ED3  2010
Datamodellering.

Å lage eit brukarapplikasjonsprogram i Visual C++  for databasetilgang.

Vi skal lage eit Visual C++ program der vi kan bla oss gjennom innhaldet i database-tabellane, slette datapostar og leggja til nye datapostar. 
Føremålet med denne øvinga er:
Å lære om utvikling og bruk av applikasjonsprogram.
Å lære litt meir om Visual C++. I programmeringskurset hadde vi eit første møte med Visual C++ gjennom øvinga: Introduksjon til VisualC  som du finn på Førelesingar.
Stoffet her er henta frå Visual C++ documentation: “ Database application tutorial (Enrol)”  Klikk Hjelp og velg stikkord database for å få den fram.  Eg har her lagt inn Fubase som database i staden for Enrol, og lagar først ein applikasjon for tabellen sted:
To create the tutorial database application 
1. From the File menu, choose New. 
The New dialog box appears.
2. Click the Projects tab.
3. Complete the Projects dialog box as follows: 
* In the Type box, select MFC AppWizard (exe).
* In the Project Name box, type Fubase.
* In the Location box, select C:\Filer as the directory to keep your source file project directory. 
AppWizard creates a project directory with the project's name (Fubase) under the directory specified in the Location box. 
4. Click OK. 
AppWizard creates the project directory, and the MFC AppWizard – Step 1 dialog box appears.
5. Click the Single document radio button, and then click Next.
6. In the MFC AppWizard – Step 2 of 6 dialog box: 
* Click Database view without file support. 
When you create a database application without file support, AppWizard always creates it as an SDI application. 
This enables the Data Source button.
* Click Data Source. 
The Database Options dialog box appears.
* Click ODBC and, from the drop-down list box, select Fubase. Click OK. 
The Select Database Tables dialog box appears. 
* Select the table name Sted, and click OK. 
This returns you to the MFC AppWizard – Step 2 of 6 dialog box.
Depending on the data source type you are using, additional qualifiers may precede or follow the table name. 
7. Click Next to accept the options you have specified, then click Next in the AppWizard dialog boxes for Steps 3, 4, and 5 to accept the default options. 
In the MFC AppWizard – Step 6 of 6 dialog box, you must check and modify the default names that AppWizard creates for your program’s classes and files. 
Note   By default, AppWizard bases the names of classes on the project name you supply. This naming is probably fine if your application has only one recordset/view pair. If your application has multiple recordsets and record views, it’s a good idea to change the name of the first recordset/view pair created by AppWizard so the naming better reflects the name of the table in the data source. For Fubase, you’ll modify two class names and their related header and implementation filenames, even though the tutorial uses only one recordset/view pair.
8. In the MFC AppWizard – Step 6 of 6 dialog box, make the following changes to class names: 
* Select the class CFubaseSet, and change it as follows: 
In the Class name box, change its name to CStedSet. 
In the Header file box, change the header filename to StedSet.h. 
In the Implementation file box, change the name to StedSet.cpp.
The base class is CRecordset. The edit item is disabled to show that you can’t change it.
* Select the class CFubaseView,  and change it as follows: 
In the Class name box, change its name to CStedForm. 
In the Header file box, change the header filename to StedForm.h.
In the Implementation file box, change the implementation filename to StedForm.cpp.
The base class is CRecordView.
9. Click Finish. 
The New Project Information dialog box appears, summarizing the settings and features AppWizard will generate for you when it creates your project.
Take a moment to examine the application type, classes, and features that AppWizard automatically provides.
10. Click OK in the New Project Information dialog box. 
AppWizard creates all necessary files and opens the project. The next topics describe the AppWizard-createf files in more detail.
To check that evry thing is OK, Bulid and Execute the application. You should have no error and you should see an empty application window.

Tilpassning av dialog forma for tabellen sted. Vi skal lage eit edit-felt med ledetekst for kvar kolonne i tabelen sted.
Along with the classes, AppWizard creates a dialog template resource named IDD_FUBASE_FORM, which the CRecordView-derived class, CStedForm, uses to display its form controls. Because CRecordView is derived from CFormView, a record view’s client area is laid out by a dialog template resource. The layout of the form is up to you. AppWizard places one static text control on the dialog template resource, labeled “TODO: Place form controls on this dialog.” 
In the following procedure, you will replace this text with controls that correspond to columns in the table (via the field data members of the recordset). The table below lists the columns and their associated edit control IDs and variable names.
Fubase's Controls and Member Variables
Column name
Control ID
Variable Name
postnr
IDC_POSTNR
m_pSet->m_Postnr
poststed
IDC_POSTSTED
m_pSet->m_Poststed













To customize Fubase’s form 
1. In ResourceView, expand the Fubase resources folder.
2. Expand the Dialog folder.
3. Double-click IDD_FUBASE_FORM. 
The dialog editor opens and displays the dialog box with the corresponding ID.
4. Select and delete the static control that says TODO: Place form controls on this dialog. 
5. Design Fubase’s Sted form, using static controls and edit controls. 
Tip   You can press CTRL before you click a dialog box control; then release the CTRL key and click in the dialog box multiple times to add multiple copies of the control. For instance, if you want six edit controls, click six times. Click the selection arrow to stop adding controls.
Resize the dialog box as needed. You can either add the controls in pairs (that is, first a static text control and then the corresponding edit control, and so on) or later change the Tab order of the controls, as described at the end of this topic, so that they are paired in this way. This becomes important when you bind the controls to recordset fields, described in the next topic.
6. Choose Properties from the View menu to display the Properties window, and then pin it down so that it stays open while you add and edit the dialog box controls. 
7. For each edit control, use the ID box in the Properties window to specify an ID based on the table column names (for example, IDC_POSTSTED). This is only a convention, but it is used throughout the tutorial. 
Note   The “Edit” caption that appears in each edit control is not visible to the user at run time, so you needn’t worry about deleting it. To view the dialog as it will appear at run time, press CTRL+T to enter test mode for the dialog box. Press ESC to cancel test mode.

8. Make the Poststed edit control read-only. To do so, select the Styles tab in the Properties window and set the Read-only check box. (The other edit controls are updatable.) 
According to a common rule in the user-interface design of database forms, the user shouldn’t be able to update this key field. If users want to change a postnr of a Sted record, they must delete the old Sted record and add a new one to avoid possibly violating the referential integrity of the database. Fubase tutorial Step 3 implements Add and Delete functionality.
9. Save your work. 
It’s a good idea to periodically back up your work.
If you did not add the static text and corresponding edit controls in order, one after the other, you need to change the tab order. In either case, you can easily check the current tab order and change it if necessary.
To view or change the tab order of controls 
1. With the dialog resource open, from the Layout menu click Tab Order. 
You’ll see numbers depicting the current tab order of the controls.
2. Specify the tab order you want by clicking each control in that order. 
As you click, you’ll see the numbering change to reflect your choice. 
For Fubase, specify a tab order such that each edit control is preceded in the tab order by the static text control that describes it. By specifying this tab order, you enable ClassWizard to derive a name for the edit control when you bind it to a data member, as you’ll do in the next section.
3. Press ESC to exit Tab Order mode. 
Leave the dialog editor open for the next procedure.


Knytte skjemafelta til felta i recordsettet.
With the form designed, it’s time to indicate which edit controls map to which table columns — or, more precisely, which controls map to which recordset field data members. To perform this task, you use ClassWizard’s “foreign object” mechanism.
Normally, you use ClassWizard to bind controls in a dialog box or form to member variables of your CDialog- or CFormView-derived class. In the case of CRecordView, though, you bind the form’s controls not to data members of the record view class but to data members of the recordset class associated with the record view. 
Your CRecordView-derived class — CStedForm in this case — has a data member called m_pSet. You can view m_pSet using ClassView. This data member is a pointer to CStedSet, Fubase’s recordset class. Recall that you viewed this recordset class on the Member Variables tab of ClassWizard (see the figure Table Columns Mapped to Recordset Data Members, earlier in this lesson).
The control bindings go through m_pSet to the corresponding field data members of CStedSet. For example, in the following procedure, you will bind the Poststed edit control to: 
m_pSet->m_Poststed



To bind a form control to a recordset data member 
1. If necessary, go to ResourceView, expand the Dialog folder, and double-click IDD_FUBASE_FORM. This opens the dialog box inside the dialog editor.
2. In the dialog editor window, hold down the CTRL key and double-click the Poststed edit control. 
ClassWizard’s Add Member Variable dialog box appears, with a proposed field name selected for you in the Member variable name box. ClassWizard chooses this name based on the caption of a static text control that falls previous to the edit control in the tab order. 
For example, for IDC_POSTSTED, the control’s caption is “Poststed,” and the Member variable name box should display:
m_pSet->m_Poststed
3. Click OK in the Add Member Variable dialog box to accept the name.
4. Repeat steps 2 and 3 for each of the other edit controls on the form. 
It isn’t necessary to create mappings for the static text controls.
5. Save your work. 
Note   Using CTRL+double-click in the dialog editor is a ClassWizard shortcut for mapping form controls to members of the associated dialog, form view, or record view class. You can use it on a pushbutton to create a command handler function for the button. You can use it on other controls to create a class member variable. 
You can view the complete mappings in the Class Wizard Member Variables tab for class CStedForm. For example, where IDC_POSTSTED appears in the Control IDs column, you’ll see ->m_Poststed in the corresponding Member column.


Applikasjonen er no klar for Build og Execute. Du skal kunne få tilgang til lesing av dataene i tabell Sted i Fubase. Prøv også å endre data. Det skal ikkje kunne gå an. Seinare skal vi lære å slette datafelt og leggja til nye.

Studer også Class-View og File-View, ekspander klassane og filene. Her ser vi også variablane som er knytta til recordsett-felta. Seinare skal vi gå inn i programkoden og leggja inn tal (for eksempel frå ein sensor) inn i variablane, og vidare inn i databasen.

Add Menu Items for Add, Refresh, and Delete
In the following procedure, you will add three new menu items to Fubase’s Record menu: Add, Refresh and Delete. The command IDs you assign to the new menu items are application-specific IDs, not predefined by the framework as are ID_RECORD_FIRST and the other commands on the Record menu.
Tip   You may find it helpful to “pin down” the Menu Item Properties dialog box during the following procedure.
To add menu items for the commands 
1. In ResourceView, expand the Fubase resources folder, and then expand the Menu folder.
2. Open the IDR_MAINFRAME menu resource.
3. Right clich the Record menu, click Properties to open the Menu Item Properties dialog box. Pin it to keep it open.
4. At the top of the existing Record menu items, add an “Add” menu item with the following caption, resource ID, and command prompt: 
* &Add
* ID_RECORD_ADD
* Legg til ein ny poststad
5. Below the new Add menu item, add a “Refresh” menu item with the following caption, resource ID, and command prompt: 
* &Refresh \tEsc
* ID_RECORD_REFRESH
* Kanseler endringar i skjema, eller kanseler Legg til. 
The “\t Esc” coding specifies that the ESC key can be used as an accelerator. If you do not complete the next procedure, Add an Accelerator for the Refresh Command, don’t include this code.
6. Below the new Refresh menu item, add a “Delete” menu item with the following caption, resource ID, and command prompt: 
* &Delete
* ID_RECORD_DELETE
* Slett ein poststad
7. Below the new Delete menu item, add a separator: 
* Insert a new menu item and, in the Menu Item Properties dialog box, click the Separator checkbox. 
8. Save your work and leave the menu editor window open. 

Add an Accelerator (Hurtigtast) for the Refresh Command
To add an accelerator 
1. In ResourceView, expand the Accelerator folder.
2. Open the IDR_MAINFRAME accelerator resource. 
Note   The name for this resource doesn’t need to match the menu resource name, so long as the ID you assign to an accelerator matches the ID for the corresponding menu item.
3. Create a new accelerator with the following ID: ID_RECORD_REFRESH.
4. From the Key drop-down list, choose, or type in, VK_ESCAPE. 
5. Clear the “Ctrl” modifier box.
6. Save your work but leave the accelerator editor open. 
You will need this editor or the menu editor open to establish a context when you use ClassWizard to create command handler functions for the menu commands.
Create Handlers for Add, Refresh, and Delete
Each of the new Record menu commands needs a command handler function in the CStedForm class. Since the Fubase menu resource is associated with the CMainFrame class, you must make an association between the menu IDs and the CStedForm class. You do this by giving focus to the IDR_MAINFRAME resource (accelerator or menu) so ClassWizard can glean the available command IDs from the resource.
 To create handlers for the commands 
1. With focus on the IDR_MAINFRAME resource, from the View menu, click ClassWizard and click the Message Maps tab. 
Notice that the CMainFrame class is automatically selected. 
2. In the Class Name box, select CStedForm.
3. In the Object IDs list, select the ID_RECORD_ADD command ID; in the Messages box, select COMMAND; and then click Add Function to create a command handler function. 
Accept the default handler name: OnRecordAdd.
4. Repeat step 3 for the ID_RECORD_DELETE and ID_RECORD_REFRESH command IDs.
5. Click OK to exit ClassWizard. 
You can also close the resource editors at this point. You will fill in the command handlers in later topics.
The Basics of Adding, Editing, and Deleting Records
Before you implement the new command handlers, you should know some basic facts about how the framework supports database updating: 
* CRecordView automatically updates the current record when the user moves to another record.
* CRecordView takes three steps to modify an edited record in the associated recordset when the user moves to another record. The record view: 
* Prepares the current record for updating by calling the recordset’s Edit member function.
* Calls the UpdateData member function derived from CFormView, which changes the recordset’s member variables, usually by getting the new values from the form’s controls.
* Calls the recordset’s Update member function to actually update the data source with the modified values. 
* CRecordView does not provide a default implementation for Add, since user interfaces for Add functionality vary widely among database applications.
* The steps for adding a new record parallel the steps for updating a modified record: 
* Prepare a new record by calling the recordset’s AddNew member function. The fields of the new record are initially Null. (In database terminology, Null means “having no value” and is not the same as NULL in C++.)
* Change the recordset’s member variables, usually by getting the new values from the form’s controls with UpdateData.
* Call the recordset’s Update member function to actually update the data source with the values for the new record. 
* Deleting a record is simpler than adding or editing one. The record view simply calls the recordset’s Delete member function. 
There are two main concerns when you delete a record. First, if you delete a record from one table and there are related records in other tables, you may damage the integrity of your database. For example, deleting a sted record for which there are records in the student table makes the sted and student tables inconsistent.
Second, after deleting a record, you or the user must move off the deleted record to another record.

Implementing the Add Command
Step 3 implements a user interface for Add that closely parallels CRecordView’s default user interface for modifying an existing record. The user starts a new record with the Add command on the Record menu. 
In response to the Add command, the record view calls its OnRecordAdd member function and enters an “add mode” by setting an m_bAddMode data member to TRUE. The add mode is completed when the user moves off the record. The Step 3 implementation overrides the record view’s OnMove member function to implement completion of the add mode. The following procedures implement the add mode, and create a CEdit member variable used to turn on and off the read-only style of the Postnr edit control.
To implement the Add mode 
1. In the Attributes section of file StedForm.h, add the protected m_bAddMode data member:
2. protected:
3. BOOL m_bAddMode;
4. Initialize m_bAddMode in the CStedForm constructor in file StedForm.cpp. (You can jump directly to the constructor from ClassView.) Add the following line after the //}}AFX_DATA_INIT line:
5. m_bAddMode = FALSE;
In Steps 1 and 2 of the tutorial, the Postnr control was read-only because it was necessary to prevent the user from changing this primary key value of the Sted record. In Step 3, you need to turn off the read-only style of the Postnr control when the user is in add mode. The control is still read-only if the user is in browse/update mode rather than add mode.
To change the read-only style, you must call the CEdit member function SetReadOnly with the appropriate parameter. This requires a member variable of type CEdit in CStedForm. At this point, the class has a CString data member representing the Postnr control, but you need a CEdit member variable as well.
To define the CEdit member variable 
1. From the View menu, click ClassWizard.
2. Click the Member Variables tab.
3. In the Class Name box, select CStedForm.
4. In the Control IDs box, select IDC_POSTNR, which is already associated with a CString member.
5. Click Add Variable to open the Add Member Variable dialog box.
6. In the Member Variable Name box, type the name m_ctlPostnr.
7. In the Category box, select Control. 
Notice that the Variable Type box changes appropriately to CEdit.
8. Click OK to close the Add Member Variable dialog box. 
Notice that a second member variable is now associated with the IDC_POSTNR control ID. You access the control’s value through m_pSet->m_Postnr. You access the control itself, to call its member functions, through m_ctlPostnr.
9. Click OK to close ClassWizard. 
The Add command initiates add mode and calls the recordset’s AddNew function to prepare a new record, but doesn’t add the record to the data source. The record isn’t actually added to the data source until a subsequent call to OnMove calls the recordset’s Update function.
To implement the OnRecordAdd command handler function 
1. Use ClassView to jump to the OnRecordAdd starter handler that ClassWizard created in StedForm.cpp.
2. Add the following code to implement the handler (Copy from thid document):
// If already in add mode, complete the previous new record
if (m_bAddMode)
OnMove(ID_RECORD_FIRST);

CString strCurrentPoststed = m_pSet->m_poststed;
m_pSet->AddNew();
m_pSet->SetFieldNull(&(m_pSet->m_poststed), FALSE);
m_pSet->m_poststed = strCurrentPoststed;
m_bAddMode = TRUE;
m_ctlPostnr.SetReadOnly(FALSE);
UpdateData(FALSE);
The most important line of this code is the call to CRecordset::AddNew, which prepares a new record. The rest of the code does the following: 
* If the user is already in add mode, complete the current record by simulating the user’s moving to another record. Moving to another record is the normal user interface for completing a record.
* Save the Poststed for the current record and use it as the default for the new record, based on the assumption that more often than not the user will want to add another postnr for the poststed currently being viewed.
* In add mode, change the Postnr control to read/write rather than read-only, so the user can enter a new Postnr.
* If you are working through the Fubase tutorial, proceed to the topic Updating the Data Source with the Added Record

Updating the Data Source with the Added Record
Add mode is completed when the user moves off the record. Enroll implements this by overriding the CRecordView::OnMove member function. 
 
To implement Add functionality in the OnMove function override 
1. From the View menu, click ClassWizard.
2. In the Class name box, click CStedForm.
3. In the Object IDs list, select CStedForm.
4. In the Messages box, select OnMove.
5. Click Add function.
6. Click Edit code to jump to the starter OnMove function, and replace the highlighted //TODO comment with the following code (Copy from here):
if (m_bAddMode) 
{
if (!UpdateData())
return FALSE;
TRY
{
m_pSet->Update();
}
CATCH(CDBException, e)
{
AfxMessageBox(e->m_strError);
return FALSE;
}
END_CATCH

m_pSet->Requery();
UpdateData(FALSE);
m_ctlPostnr.SetReadOnly(TRUE);
m_bAddMode = FALSE;
return TRUE;
}
else
{
return CRecordView::OnMove(nIDMoveCommand);
}
Catch any exceptions thrown by the recordset’s Update function so that errors are reported to the user. The CDBException data member m_strError is a fairly user-friendly error message, prepared by the underlying ODBC driver.
In its default CRecordView implementation, OnMove moves to the next, previous, first, or last record. If the application has changed the recordset field data members for the current record before the move, the framework updates the data source before moving to another record.
Note   Some ODBC drivers do not reflect newly added records in the recordset; others do. For those drivers that don’t display newly added records, to make the added records visible you must requery the database.
Step 3 augments the default CRecordView user interface for updating the current record. If the user is in add mode and then moves off the new record, Fubase adds the newly prepared record to the data source before moving to another record. But you must decide whether it’s important for added records to be immediately visible. For the tutorial, the decision is to requery the recordset (evident in the code above) after each add operation so the newly added record is included in the recordset.
Normally, the move commands behave as you might expect: Move Next moves to the next record, and so on. But as a consequence of the decision to requery during the add operation, when the user clicks any move command when adding a record, Enroll always effectively moves to the first record. That’s because requerying the recordset automatically sets the recordset to the first record.

Implementing the Delete Command
In response to a Delete command, the record view deletes the current record by calling the Delete member function of its associated recordset. 
To implement the Delete command 
1. Use ClassView to jump to the OnRecordDelete starter function in class CSectionForm.
2. Implement the handler with the following code (Copy from here):
TRY
{
m_pSet->Delete();
}
CATCH(CDBException, e)
{
AfxMessageBox(e->m_strError);
return;
}
END_CATCH

// Move to the next record after the one just deleted
m_pSet->MoveNext();

// If we moved off the end of file, move back to last record
if (m_pSet->IsEOF())
m_pSet->MoveLast();

// If the recordset is now empty, clear the fields left over
// from the deleted record
if (m_pSet->IsBOF())
m_pSet->SetFieldNull(NULL);
m_pSet->Requery(); //Denne er sett inn av meg, får oppdatert alt
UpdateData(FALSE);
Catch any exceptions thrown by the recordset’s Delete function so that errors are reported to the user. The CDBException data member m_strError is a fairly user-friendly error message, prepared by the underlying ODBC driver.
If you want to customize the error message, you can force the error condition, then examine m_strStateNativeOrigin for a particular state or native value. You can look up error messages in the ODBC Programmer’s Reference, Appendix A, ODBC Error Codes. Fubase takes the easy approach by displaying m_strError.
For Fubase, the decision was to move to the record following the deleted record. You could move to the previous record after a delete operation or anywhere else as long as you, or the user, moves off the deleted record.

Implementing the Refresh Command
The Refresh command cancels add mode, if the user had previously chosen Add, or it discards any changes the user may have made on the form for the current record. In the first case, Enroll cancels the add mode by calling:
CRecordset::Move(0);
When you call AddNew to begin the add operation, the framework stores a copy of the current record’s fields before allowing the user to enter new values in the record view’s controls. Calling Move as shown here “refreshes” the current record — and effectively cancels the add operation. It restores the record that was current before add mode began. This also works if you called Edit instead of AddNew.
When the user cancels add mode, Fubase makes the Postnr control read-only again, for reasons explained earlier.
To implement the Refresh command 
1. Use ClassView to jump to the OnRecordRefresh starter handler in class CSectionForm.
2. Implement the handler function with the following code (Copy from here):
if (m_bAddMode)
{
m_pSet->Move(0);
m_ctlSection.SetReadOnly(TRUE);
m_bAddMode = FALSE;
}
// Copy fields from recordset to form, thus
// overwriting any changes the user may have made
// on the form
UpdateData(FALSE);



Test ut ADD, Delete og Refresh menyvalga.