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.
Your first task is to create the dialog template and the dialog class for the Query dialog box.
Figure 7.17 The Query Database dialog box
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.
const CFundDialog * GetFundDialog() {return &m_wndFundDialog;} |
#include "Mainfrm.h" #include "STUploadDoc.h" #include "FundDialog.h" |
(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:
CString m_strFund; |
int nChoice = m_cbFund.GetCurSel(); if(nChoice >= 0) m_cbFund.GetLBText(nChoice, m_strFund); |
You will now create the dialog template and dialog class for the Query Results dialog box.
Figure 7.18 The Query Results dialog box
SELECT * FROM pricehistory |
Press CTRL+T to test the Query Results dialog box. Check that records from the price history table of the Stocks database are displayed.
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 |
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.
#include "_recordset.h" |
(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); |
You will now add the command handler function and the user-interface update handler function for the Query Database option on the Data menu.
#include "QueryDialog.h" #include "StockDataList.h" #include "ResultsDialog.h" |
(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.
(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.