Lab 7: Querying a Database

In this lab, you will implement the Query Database option for the STUpload application. This option allows the user to submit a simple ad hoc query to the central database. The user specifies a fund name, a start date, and an end date in a Query dialog box; and is returned price history data for the specified fund between the two dates. The user browses the query results in the Results dialog box, which contains a read-only DataGrid Control bound to an ADO Data Control.

The Query Database option is available only if the STUpload application is currently displaying price history data for a fund. The Query Database option is generally used to check that the records the user is about to upload do not already exist on the server. Therefore, the Query dialog box appears displaying the current fund and date range as default values.

This lab assumes that you have installed SQL Server and set up the Stocks database as directed in the "Getting Started" section of About This Book.

Implementing the Query Dialog Box

Your first task is to create the dialog template and the dialog class for the Query dialog box.

  • To create the Query dialog template
    1. Using Figure 7.17 as a guide create the Query Database dialog box template. The resource has the ID IDD_QUERYDIALOG, and contains a combo box control and two Date Time Picker controls. (Use the ToolTip feature to locate the buttons for these controls on the Controls toolbar.)
    2. Figure 7.17 The Query Database dialog box

    3. Assign the combo box control the ID IDC_QUERY_FUND. On the Styles page, ensure that the Sort check box is selected. In the Type box, select Drop List.
    4. Click the drop-down arrow of the combo box. The selection handles change to show the extent of the drop-down list. Drag the handle of the drop-down list so that it extends to just above the OK and Cancel buttons.
    5. Name the first Date Time Picker control IDC_FROMDATE. On the Styles page, set the Format option to Short Date. Select the Use Spin Control check box.
    6. Repeat the procedure for the second Date Time Picker control, naming it IDC_TODATE.

  • To create the CQueryDialog dialog class
    1. Press CTRL+W to open ClassWizard. When prompted, create the CQueryDialog dialog class.
    2. On the Member Variables tab, add the member variables shown in Table 7.5.
    3. Table 7.5 CQueryDialog Member Variables

      Resource ID Category Variable type Variable name
      IDC_FUND Value int m_nFund
      IDC_FUND Control CComboBox m_dtFund
      IDC_FROMDATE Value CTime m_fromdate
      IDC_FROMDATE Control CDateTimeCtrl m_dtFrom
      IDC_TODATE Value CTime m_todate
      IDC_TODATE Control CDateTimeCtrl m_dtTo

    You will now implement the CQueryDialog::OnInitDialog() function to initialize the Query Dialog box. This function will fill the combo box with the funds currently on file, and set the current selection. It will also initialize the Date Time Picker controls with the first and last dates on file.

  • To initialize the Query Dialog box
    1. Your first task is to make the Select Fund dialog box available to the CQueryDialog::OnInitDialog() function. Open the Mainfrm.h file. To the public section of the CMainFrame class definition, add the following inline function:
    2.  const CFundDialog * GetFundDialog() {return &m_wndFundDialog;} 

    3. Add the following lines to the top of the QueryDialog.cpp file:
    4.  #include "Mainfrm.h" #include "STUploadDoc.h" #include "FundDialog.h" 

    5. Use ClassWizard to create the CQueryDialog::OnInitDialog() function (handle the WM_INITDIALOG message in the CQueryDialog class). Replace the // TODO comment in the generated function with the following code:
    6. (This code can be found in CH7_01.cpp, installed from the companion CD.)

       CMainFrame * pWnd = dynamic_cast<CMainFrame *> (AfxGetAPP()->m pMainWnd(); ASSERT_VALID(pWnd); CSTUploadDoc * pDoc =      dynamic_cast<CSTUploadDoc *>(pWnd->GetActiveDocument()); ASSERT_VALID(pDoc); const CFundDialog * pFD = pWnd->GetFundDialog(); ASSERT_VALID(pFD); // Fill combo box with current fund names for(int n = 0; n < pFD->m_listBox.GetCount(); n++) {      CString strBuf;      pFD->m_listBox.GetText(n, strBuf);      m_cbFund.AddString(strBuf); } // Set listbox selection to strCurrentFund parameter int iPos =      m_cbFund.FindStringExact(-1, pDoc->GetCurrentFund());      m_cbFund.SetCurSel(iPos); // Setup Date Time Pickers m_dtFrom.SetFormat("d MMM yyy"); m_dtTo.SetFormat("d MMM yyy"); 

    You will need to overload the OnOK() function for the CQueryDialog class to retrieve the fund selected by the user:

  • To implement the CQueryDialog::OnOK() function
    1. Open the QueryDialog.h file, and add the following variable to the public section of the CQueryDialog class definition:
    2.  CString m_strFund; 

    3. Use ClassWizard to create the OnOK() function to handle the BN_CLICKED message for the IDOK object ID. Replace the // TODO comment with this code:
    4.  int nChoice = m_cbFund.GetCurSel(); if(nChoice >= 0)      m_cbFund.GetLBText(nChoice, m_strFund); 

    Implementing the Query Results Dialog Box

    You will now create the dialog template and dialog class for the Query Results dialog box.

  • To create the Query Results dialog template
    1. Using the Components and Controls Gallery, insert the Microsoft ADO Data Control, version 6.0 (OLEDB) and the Microsoft DataGrid Control, Version 6.0 (OLEDB) into the project. Make sure that you create all the classes associated with these controls.
    2. Using Figure 7.18 as a guide, create the Results dialog box template. The resource has the ID IDD_RESULTSDIALOG, and contains an ADO Data Control with the default ID IDC_ADODC1, and a DataGrid Control with the default ID IDC_DATAGRID1. The OK button has been given the caption Close. The Cancel button has been removed.
    3. Figure 7.18 The Query Results dialog box

    4. On the Control page of the ADO Data Control Properties dialog box, build a connection string to connect to the Microsoft OLE DB Provider for SQL Server. On the Connection page, specify the (local) SQL Server and select the Stocks database.
    5. Reopen the ADO Data Control Properties dialog box. On the RecordSource page, select 1 - adCmdText from the Command Type drop-down list. In the Command Text (SQL) box, type the following command:
    6.  SELECT * FROM pricehistory 

    7. Edit the DataGrid Control properties. On the Control page, deselect the AllowUpdate check box. On the All page, set the DataSource property to the ID of the ADO Data Control IDC_ADODC1.

    Press CTRL+T to test the Query Results dialog box. Check that records from the price history table of the Stocks database are displayed.

  • To create the CResultsDialog dialog class
    1. Press CTRL+W to open ClassWizard. When prompted, create the CResultsDialog dialog class.
    2. On the Member Variables tab, add the member variables shown in Table 7.6.
    3. Table 7.6 CResultsDialog Member Variables

      Resource ID Category Variable type Variable name
      IDC_ADODC1 Control CAdodc m_adodc
      IDC_DATAGRID1 Control CDataGrid m_datagrid

    4. Close ClassWizard. Open the ResultsDialog.h file, and add the following variables to the public section of the CResultsDialog class definition:
    5.  CString m_strQuery; CString m_strCaption; 

    You will now implement the CResultsDialog::OnInitDialog() function to initialize the Query Results dialog box. This function will fill the combo box with the funds currently on file, and set the current selection. It will also initialize the Date Time Picker controls with the first and last dates on file.

  • To initialize the Query Results Dialog box
    1. Add the following line to the top of the ResultsDialog.cpp file, to include the C_Recordset class, which was generated by inserting the ADO Data Control into the project:
    2.  #include "_recordset.h" 

    3. Use ClassWizard to create the CResultsDialog::OnInitDialog() function. Replace the // TODO comment in the generated function with the following code:
    4. (This code can be found in CH7_02.cpp, installed from the companion CD.)

       m_adodc.SetRecordSource(m_strQuery); m_adodc.Refresh(); C_Recordset cRS = m_adodc.GetRecordset(); long lRecs = cRS.GetRecordCount(); if(lRecs < 1) {      AfxMessageBox("No records match this query");      EndDialog(IDCANCEL); } m_datagrid.SetCaption(m_strCaption); 

    Handling the Query Database Menu Command

    You will now add the command handler function and the user-interface update handler function for the Query Database option on the Data menu.

  • To add the OnDataQuerydatabase() command handler
    1. To the top of the STUploadDoc.cpp file, add the following:
    2.  #include "QueryDialog.h" #include "StockDataList.h" #include "ResultsDialog.h" 

    3. Use ClassWizard to add a command handler for the ID_QUERY_DATABASE ID to the CSTUploadDoc class. The function should be called OnDataQuerydatabase().
    4. Replace the // TODO comment with the following code:
    5. (This code can be found in CH7_03.cpp, installed from the companion CD.)

       CQueryDialog aQDlg; // Set the default values for the Date Time Picker controls // with first and last date on file (all funds) CStockData sdFirst = m DocList.GetHead(); CStockData sdLast = m DocList.GetTail(); aQDlg.m fromdate = sdFirst.GetDate(); aQDlg.m todate = sdLast.GetDate(); if(aQDlg.DoModal() == IDOK) {      // Construct query      CString strQuery =            "select * from PriceHistory where ph_fund = '";      strQuery += aQDlg.m strFund;      strQuery += "' and ph date between '";      strQuery += aQDlg.m fromdate.Format("%Y/%m/%d");      strQuery += "' and '";      strQuery += aQDlg.m todate.Format("%Y/%m/%d");      strQuery += "'";      // Construct caption string      CString strCaption = aQDlg.m strFund;      strCaption += " Prices ";      strCaption += aQDlg.m fromdate.Format("%d/%m/%Y");      strCaption += " - ";      strCaption += aQDlg.m todate.Format("%d/%m/%Y");      CResultsDialog rd;      rd.m strQuery = strQuery;      rd.m strCaption = strCaption;      rd.DoModal(); } 

    The user-interface update command handler will ensure that the Query Database option is available only if the application is currently displaying price history data for a fund.

  • To add the user-interface update command handler
    1. Use ClassWizard to add an UPDATE_COMMAND_UI handler for the ID_QUERY_DATABASE ID to the CSTUploadDoc class. The function should be called OnUpdateDataQuerydatabase().
    2. Replace the // TODO comment with the following code:
    3. (This code can be found in CH7_04.cpp, installed from the companion CD.)

       // Enable the Query Database command only if there is // data on file and a fund currently selected for viewing BOOL bEnable = FALSE; bEnable = m strCurrentFund.IsEmpty() ? FALSE : TRUE; pCmdUI->Enable(bEnable); 

    You can now build and run the STUpload application. Import the Ch7Test.dat file from the \Chapter 7\Data folder. Select a fund to view and select the Query Database command that is now enabled. The values in the Ch7Test.dat file correspond to records that have already been uploaded to the Stocks database. Accept the default values in the controls and click OK to browse the records on the database. You can experiment by submitting queries that will retrieve some of the records or none of them.



    Microsoft Press - Desktop Applications with Microsoft Visual C++ 6. 0. MCSD Training Kit
    Desktop Applications with Microsoft Visual C++ 6.0 MCSD Training Kit
    ISBN: 0735607958
    EAN: 2147483647
    Year: 1999
    Pages: 95

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net