Use VC ++ 6.0 to develop database applications connected to multiple tables

Abstract: This article describes how to use the mfc odbc programming method when developing database applications with Visual C ++ 6.0. It describes in detail how to set SQL statement parameters in the derived classes of MFC ODBC, two tables are joined.
Keyword database, multi-table join, MFC ODBC

1. Introduction
When developing a Windows application, you may need to connect to the database in many cases. The database types are diverse and its functional structures are also different. From relatively simple DBASE and FoxPro to complicated large database systems such as SYBASE and Qracle. VC ++ 6.0 provides some interfaces. Programmers can use these interfaces to easily develop database applications. One of the mfc odbc classes is useful in quickly generating simple and consistent interface applications. You can perform most database operations without having to know the detailed odbc api and SQL. However, the Database Application Generated by VC ++ Appwizard is only a database application based on a single data table. In practical applications, database applications are often required to associate two or more data tables. Many books of VC ++ are just a brief introduction to this. This article thoroughly solves this problem.

2. ODBC and MFC
2.1 ODBC
ODBC (Open Database interconnection) applications can access data in different data sources through ODBCAPI. Each data source type is supported by an ODBC driver, which completes the core of the odbc api program, and communicates with a specific database. The ODBC environment provides a Driver Manager to manage drivers connected to different data sources to be executed in ODBC32.DLL. The application only needs to connect to the driver Manager. The driver manager selects the correct Driver Based on the data source name provided by the application to access the data source.

To Use ODBC to develop database applications, you must use the ODBC Data Source manager on the Control Panel to create and prepare data sources. To meet the needs of this application, follow these steps to create the required data source.
1. Double-click the 32-bit ODBC program on the control panel and select the User DSN tab in the dialog box.
2. Click the Add button and select a Data source: Microsoft Visual FoxPro Driver. Click Finish to go to the next step.
3. In the Data Source Name field, enter the Data Source Name DB-FSB. Select the location of the Visual FoxPro database.
4. Click OK to return to the control panel.

2.2 MFC ODBC
The database Extension Section of MFC encapsulates the details of using ODBC data resources and provides a simple call interface between VC ++ and ODBC. The ODBC class of MFC mainly includes: The CDatabase class used to connect to a data source; the CRecordset class used to process a set of records returned from the database; this class simplifies the display of data obtained from the Crecordset object.

Although the Cdatabase class allows you to execute SQL statements on a database, the CRecordset class provides the essence of the interaction between applications and data. In this example, the application uses the CRecordset class to operate the data source.
The main purpose of the CRecordset class is to allow applications to access the result set returned from the database. To use the CRecordset class in an application, you can create a Crecordset derived class based on the data source and using ClassWizard in VC ++. Generally, a CRecordset derived class corresponds to a table in the user data source. Each time you generate a Crecordset derived class, you must select a data source and a table in a data source. If multiple tables in a data source are selected when a Crecordset derived class is generated, the result set in the Crecordset derived class is connected by the Carn product (dikar product) of multiple tables. Obviously, it makes no sense in actual application. Applications use the derived Crecordset class to scroll, modify, add, and delete records in a record set.

The CRecordView class has several enhancements that allow the use of the dialog box function (DoDataExchange () to directly display data from the record set, making it easier to display data from the record set. It also provides operations such as record movement.

3. database applications connected to multiple tables
3. 1 functions of this program:
Use the BZM field of the FSB table and the hh field of the DBK1 table to associate the FSB table of Visual FoxPro with the DBK1 table (the structure is as follows. Interface 1 appears when the program runs. Click the toolbar with the mouse? ,? , (, (The records in the FSB table are moved, but those in the DBK1 table are not moved. Click the "join" button, and the records in the DBK1 table are synchronized with those in the FSB table (the content in the BZM editing box is the same as that in the HH editing box ). If you want to query related records in two tables based on the specific keyword value, enter the specific keyword value in the "locate" edit box. Then, click the join button to associate the two tables with the new record set.

Table 1: FSB table structure
Field name
Type
Remarks
BZM
C
Index keywords
DGDL1
N

DGZD1
N

Other fields

Table 2: DBK1
Field name
Type
Remarks
HH
C
Index keywords
BL
N

ZZCM
C

Other fields

Database applications developed based on the mfc odbc class use SQL statements to manipulate data tables. In the database, the SQL statement used to associate the table FSB with the table DBK1 is:
SELECT * from fsb, DBK1 where fsb. BZM = DBK1.HH
Generally, only one table in a data source is selected when you create a CRecordset derived class. Therefore, two table associations must be implemented for database applications developed based on the mfc odbc class, use the m _ strFilter parameter of the CRecordset class. It is equivalent to the WHERE clause in an SQL statement. The m _ strSort parameter is equivalent to the group by clause in an SQL statement. Note that the m_strFilter string does not contain the "WHERE" keyword. In this example, two m_strfilter parameters, mbzm and mhh, are used in the CRecordset derived class corresponding to table FSB and table DBK1. When you click the "join" button, the program first uses the content in the "locate" edit box as the mbzm value and retrieves the result set in the table FSB. The CRecordset derived class corresponding to table DBK1 is retrieved Based on the m_bzm value of the current record of the CRecordset derived class corresponding to table FSB as the mhh value, in this way, we can obtain the table DBK1 record corresponding to the table's FSB keyword field BZM. The association between table FSB and table DBK1 is realized. Therefore, the key to table-2 Association is the setting of the m _ strFilter parameter.

Figure 1

3.2 Create a database application
3.2. 1 Create a database application for a single form document
Use VC ++ Appwizard to generate a single form, single document database application based on the data source DB-FSB established earlier. Select FSB. DBF when selecting a data table for the data source DB-FSB. The Application name is ZF0001 (for detailed steps, see the relevant VC ++ documents ). The ZF0001 application creates derivative classes such as CZf0001Doc, CZf0001Set, and CZf0001View.
3.2.2 set the m_strfilter Parameter
In the CZf0001Set class generated in the previous step, set the m _ strFilter parameter in program ① (the statements in the black body are manually added to the parameter mbzm of CZf0001Set) as follows ). Part of the program list is omitted to save space.
①. The fields in the joined data source table are described in the definition of the Crecordset derived class, you are prompted to define parameters here in the "// Field/Param Data" annotation of the program generated by VC ++ Appwizard.
CRecordset derived class: Definition of CZf0001Set
Class CZf0001Set: public Crecordset // Crecordset derived class CZf0001Set
{
Public:
CZf0001Set (CDatabase * pDatabase = NULL );
DECLARE_DYNAMIC (CZf0001Set)
// Field/Param Data
// {AFX_FIELD (CZf0001Set, CRecordset) // The bound field
CString m_bzm;

... // To save space, omit some fields
CString m_bz;
//} AFX_FIELD
CString mbzm; // parameter mbzm
// Overrides
// Classwizard generated virtual function overrides
......
Virtual void dump (cdumpcontext & DC) const;
# Endif
};

②. Crecordset: constructor of czf0001set
The memory variables of the bound fields are initialized.
Czf0001set: czf0001set (cdatabase * PDB): crecordset (PDB)
{
// {Afx_field_init (czf0001set)
M_bzm = _ T ("");
M_dgqd1 = _ T ("");
......
M_bz = _ T ("");
M_nFields = 16; // number of record fields in the data source table
//} AFX_FIELD_INIT
M_nDefaultType = snapshot;
M_nParams = 1; // Number of CZf0001Set Parameters
Mbzm = ""; // parameter initialization
}

③ Record field exchange (RFX)
Through RFX, the MFC framework can exchange between database and CRecordset variables. Exchange is created by executing the DoFieldExchange () function.
Void CZf0001Set: DoFieldExchange (CFieldExchange * pFX)
{
// {AFX_FIELD_MAP (CZf0001Set)
PFX-> SetFieldType (CFieldExchange: outputColumn );
RFX_Text (pFX, _ T ("[bzm]"), m_bzm );
RFX_Text (pFX, _ T ("[dgqd1]"), m_dgqd1 );
......
RFX_Text (pFX, _ T ("[dgdl2]"), m_dgdl2 );
RFX_Text (pFX, _ T ("[bz]"), m_bz );
//} AFX_FIELD_MAP
PFX-> SetFieldType (CFieldExchange: param );
// Set the field type to CFieldExchange: param
RFX_Text (pFX, "mbzm", mbzm );
// Set the RFX macro for the parameter. If multiple parameters exist, they must be set in the order of the location flag in the SQL statement,
// The parameter name in the RFX macro, such as "mbzm", is not used to match the parameter and can be customized.
}

3.2.3 add the second table and set parameters for the second table

In 3. 2. 1. Based on the database application created, enter ClassWizard and click Add Class... in the pop-up menu, select New ..., enter CZf1001 in the Name column of the Create New Class dialog box, select CRecordset in the Base class column, and click Create.

In the pop-up Database Options dialog box, select the DB-FSB data source in the ODBC combo box. Then press OK. In the pop-up Select Database Tables dialog box, Select the DBK1 table. Press OK to confirm. Add # include "CZf1001.h" to all files that exist in # include "CZf0001Set. h ". In this way, the Crecordset derived class corresponding to the DBK1 table is created. In the CZf0001Doc class created in step 1, add the pointer variable m_zf1002 (that is, CZf1001 * m_zf1002) of the CZf1001 object ).

Follow the setting method of the m _ strFilter parameter in the CZf0001Set class described in 3.2.2. in the CZf1001 class, set the mhh parameter.

3. Use the mhh parameter and mbzm parameter in the CZf0001View derived class of CrecordView

3.3.1 The parameter is used in the CZf0001View: OnInitialUpdate () function.

At the beginning of the czf0001view: oninitialupdate () function, call czf0001view: getdocument () to return pointers of two crecordset classes (czf0001set and czf1001) from the czf0001doc class of the document class. Based on the returned pointer, Set M _ strfilter (equivalent to the WHERE clause of the SQL statement) and determine the initial values of the two parameters. One thing to note:
M_pset-> m_strfilter = "BZM like? ";
M_pset2-> m_strfilter = "HH like? ";

"?", When calling open or requery, "? "Will be automatically replaced by the czf0001set: mbzm and czf1001: mhh values. For example, if mbzm is set to "31001", m_pset-> m_strfilter will be changed to "BZM = 31001 ". In this way, you only need to specify mbzm to obtain the required record set. The program list of czf0001view: oninitialupdate () is as follows (the statements in the black body are manually added ):

Void czf0001view: oninitialupdate ()
{
M_pset = & getdocument ()-> m_zf0001set;
M_pset2 = & getdocument ()-> m_zf1002;
If (! M_pset2-> open ())
Return;
M_pset-> m_strfilter = "BZM like? ";
M_pset-> mbzm = "%"; // initially select all records
M_pset-> m_strsort = "";
M_pset2-> m_strfilter = "HH like? ";
M_pset2-> mhh = m_pset-> m_bzm;
// Use the m_bzm value of the crecordset derived class corresponding to the table FSB as the value of the mhh Parameter
M_pSet2-> m_strSort = ""; // the search results are not sorted.
M_pSet-> m_pDatabase = m_pSet2-> m_pDatabase; // share CDatabase

CRecordView: OnInitialUpdate ();
GetParentFrame ()-> RecalcLayout ();
ResizeParentToFit ();
}

3.3.2 Add the edit box to the dialog box
In the resource view Dialog idd_zf000terraform form, add the required edit box. Use ClassWizard to select related fields in the first table FSB and connect them. However. you cannot find the DBK1 field variable in the second table using ClassWizard. Therefore, you must manually modify the DoDataExchange () of the CRecordView class in the edit box that is scheduled to be connected to the DBK1 field in the second table () (dialog box data exchange function ). Add the relevant content after the DoDataExchange () function "//} AFX_DATA_MAP. See the simhei section of the program below. If some statements in the black body are added before "//} AFX_DATA_MAP", ClassWizard cannot be used to modify the idd_zf000terraform again.

Void czf0001view: dodataexchange (cdataexchange * PDX)
{
Crecordview: dodataexchange (PDX );
// {Afx_data_map (czf0001view)
Ddx_control (PDX, idc_combo1, m_comb );
Ddx_control (PDX, idc_edit4, m_ss );
Ddx_fieldtext (PDX, idc_edit2, m_pset-> m_bl1, m_pset );
Ddx_fieldtext (PDX, idc_edit3, m_pset-> m_dgdl1, m_pset );
DDX_FieldCBString (pDX, IDC_COMBO1, m_pSet-> m_bzm, m_pSet );
DDX_FieldText (pDX, IDC_EDIT5, m_pSet-> m_dgqd1, m_pSet );
//} AFX_DATA_MAP
DDX_FieldText (pDX, IDC_EDIT1, m_pSet2-> m_bl, m_pSet2 );
DDX_FieldText (pDX, IDC_EDIT6, m_pSet2-> m_hh, m_pSet2 );
DDX_FieldText (pDX, IDC_EDIT7, m_pSet2-> m_zzcm, m_pSet2 );
}

3.3.3 Add a button in the dialog box
To demonstrate the association effect of two tables, add an association button in the dialog box and a positioning edit box for input parameters. Add the following Event code to the button:

Void CZf0001View: OnButton1 ()
{
// TODO: Add your control notification handler code here
Char ll [11];
Int nn = 0;
M_SS.GetLine (0, ll); // read the content in the "locate" edit box.
Nn = m_SS.LineLength (0); // read the length of the characters in the "locate" edit box.
If (nn) // enter content in the "locate" edit box, and a new record set is created based on Content Retrieval.
{
M_pset-> mbzm = (cstring) ll;
M_pset-> mbzm = m_pset-> mbzm. Left (NN) + "% ";
// "%" Indicates a string of any length (the length can be 0) according to the SQL syntax.
M_pset-> requery (); // re-search in the table FSB
If (m_pset-> iseof ())
{
MessageBox ("the search result is blank! ");
UpdateData (FALSE );
M_pSet-> mbzm = "% ";
M_pSet-> Requery ();
}
M_pSet2-> Requery (); // retrieve the value of BZM in the current record of Table 1 in Table 2: DBK1
Updatedata (false); // refresh the Form Content
M_ss.setsel (0,-1 );
M_ss.replacesel ("");
// Clear the content in the "locate" edit box so that the next time you click the "associate" button, the search will not be repeated (even if nn = 0)
}
Else // if the "locate" edit box is empty, associate the existing result set in table 1 with table 2: dbk1.
{
M_pset2-> mhh = m_pset-> m_bzm; // use the BZM value currently recorded in Table 1 as the search parameter of table dbk1.
M_pset2-> requery ();
Updatedata (false );
M_pset-> movenext ();
}
}

4. Summary
This example shows that although VC ++ provides Appwizard and classwizard, it saves a lot of work for programmers, but wants to compile excellent VC ++ applications, it is far from enough. You must be familiar with the knowledge of MFC class libraries and ActiveX controls. This document describes how to join two tables as an example. In practical applications, you can refer to the example below to develop more sophisticated database applications.