Develop multi-table joint database applications with VC 6.0
Abstract This article describes the programming method of using the MFC ODBC class using the Visual C 6.0 development database application, and the method of setting the SQL statement parameters in the Debut class of the MFC ODBC is described in detail. The association of the two tables is implemented. Keyword database, multi-table joint, MFC ODBC 1. Introduction When developing a Windows application, in many cases, you may have to connect to a database. Database types are varied, and the functional structure is different. From a relatively simple DBASE, FoxPro, such as complex Sybase, Qracle, etc. VC 6.0 provides some interfaces. Programmers can use these interfaces to easily develop database applications. The MFC ODBC class is one of them, which is very useful in rapid generation of simple and consistent interface applications. Users don't have to know the details of ODBC API and SQL, using ODBC classes to complete most of the operations of the database. However, VC AppWizard generated database applications are just a database application based on a single data table. In practical applications, the database application can often be required to associate two or more data sheets. A lot of books for VC is just a brief introduction. This paper solves this problem. 2. ODBC and MFC 2.1 ODBC ODBC (Open Database Interconnect) application can access data in different data sources via ODBCAPI, each different data source type supports by an ODBC driver, this driver completed the ODBC API program Core and communicate with specific databases. The ODBC environment provides Driver Manager, which manages those drivers connected to different data source to execute in ODBC32.DLL. The application only needs to be connected to the driver manager. The driver manager selects the correct driver to access the data source according to the data source provided by the application. To develop database applications using ODBC, you must use the ODBC Data Source Manager at the Control Panel to create and formulate data sources. In this case, the application needs to be applied, and the required data source is established as follows. 1. Double-click the 32-bit ODBC program at the control panel, 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. Enter the data source name in the Data Source Name: DB-FSB. Then select the location of the Visual FoxPro database. 4. Click the OK button to return to the control panel. 2.2 The database extension section of the MFC ODBC MFC encapsulates the details of the ODBC data resource, providing a simple call interface between VC and ODBC. The ODBC class of the MFC mainly includes: CDATABASE class used to connect to a data source; use the CRecordset class used from a set of records returned from the database; simplifies the CRecordView class that is displayed from the CRecordset object. Although the CDatabase class allows you to perform SQL statements to a database, the CRecordset class provides the essence of application and data interaction. This application uses the CRecordset class to operate the data source. The main purpose of the CRecordset class is to allow the application to access the result set returned from the database. To use the CRecordset class in your application, create CRecordset derived classes according to the data source and use ClassWizard in VC . Typically, a CRecordset is derived with a table corresponding to the user data source.
Each time a CRecordset derived class is generated, you should select a table of data sources and a table in a data source. When generating a CRecordset derived class, select a plurality of tables in a data source, then the result set in the CRecordset derived class is a plurality of tables (Dikalji) connections, obviously, there is no meaning in practical applications. . The application can scroll, modify, increase, and delete the records in the record set by derived CRecordset class. The CRecordView class has several enhancements that allow the dialog box mode (DODATAEXChange () function to display data directly from the recordset, making it easier to display data from the record set. And provide operations such as recording movement. 3. Multi-Table Database Application 3. 1 This program function: The FSB table of Visual FoxPro is associated with the DBK1 table (the structure below) via the BZM field of the FSB table and the HH field of the DBK1 table. The program running appears as shown in Figure 1. Click the tool bar with the mouse?, (, (, The FSB table records moves the DBK1 table record is not moved. Click the "associated" button with the mouse, then the record of the DBK1 table and the FSB table record synchronous movement (BZM edit box content The same is the same as the HH edit box). If, request the relevant record in the two tables according to the specific keyword value, then enter the specific keyword value in the "Position" editing box, then click "association with the mouse click" "Buttons, you will see two tables implementation in new record sets. Table 1: FSB table structure field name type Remark BZM C index keyword DGDL1 N DGZD1 N other field table 2: DBK1 Table field name Type Remarks HH C Index Keyword BL N ZZCM C Other fields The database application developed by the MFC ODBC class is to manipulate the data table by using the SQL statement by the MFC ODBC class. Database Database Database SQL statement is: SQL statement: SELECT * FROM FSB , DBK1 WHERE FSB.BZM = DBK1.HH When you create a CRecordset derived class, only one table in a data source is generally selected, so the database application developed based on the MFC ODBC class is to implement two table associations, as CRecordset The parameter m_strfilter of the class is equivalent to the WHERE clause in the SQL statement. Parameter m _STRSORT is equivalent to the Group By clause in the SQL statement. Be aware of the "WHERE" keyword in the m_strfilter string. This example is in Table FSB MBZM and MHH two m _strfilter parameters are used in the CRecordset derived class corresponding to Table DBK1. When clicking the "Associated" button with the mouse, the program first uses the content in the "Position" edit box as the value of MBZM, in the table FSB search The result set. Table DBK1 CRECORDSET derived class is implemented by the current record M_bzm value corresponding to the CRecordset corresponding to the table FSB, and the value of MHH is retrieved, thereby obtaining a record of table DBK1 corresponding to the table FSB key field BZM. Implement the table The FSB is associated with the table DBK1. This shows that the key to the two tables is the setting of the M_Strfilter parameter. Figure 1 3.2 Database application creates 3.2. 1 Create a single table document database application based on the data established in front Source DB-FSB, generate a single form, a single document database application using VC AppWizard. Select the data sheet of the DB-FSB to select the data sheet for DB-FSB. Applicate name is ZF0001 (see the specific steps for VC data) .ZF0001 Classic categories such as CZF0001DOC, CZF0001SET, CZF0001View, etc. were created in the program.
3.2.2 Setting the M_Strfilter parameter In the previously generated CZF0001SET class, set the m _Strfilter parameter in the 1 2 3 program (the statement of the black body portion is manually increased in the 1 2 3 program. In order to save space, some of the contents of the program list is omitted. 1. In the definition of CRecordset derived class, the fields of the connected data source table are described and prompts this to define parameters in the program "// field / param data" generated in VC AppWizard. CRecordset derived classes: CZf0001Set defined class CZf0001Set: public Crecordset // Crecordset derived class CZf0001Set {public: CZf0001Set (CDatabase * pDatabase = NULL); DECLARE_DYNAMIC (CZf0001Set) // Field / Param Data // {{AFX_FIELD (CZf0001Set, CRecordset) // Binded field cstring m_bzm; cstring m_dgqd1; ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, Virtual function overrides ,,,,,,, VIRTUAL VOID DUMP (CDUMPCONTEXT & DC) const; #ndif}; 2. CRecordset derived class: CZF0001SET constructor is initialized in which the corresponding memory variables of the bound field are initialized. CZF0001SET :: CZF0001SET (CDATABASE * PDB): CRecordset (PDB) {// {{AFX_FIELD_INIT (CZF0001SET) m_bzm = _t (""); m_dgqd1 = _t (""); ,,,,,, M_BZ = _t (" "); m_nfields = 16; // Data source table record field number //}} AFX_FIELD_INIT M_NDEFAULTTYPE = SNAPSHOT; M_NPARAMS = 1; // CZF0001SET parameter MBZM =" "; // Parameter Initialization} 3. Recording Field Exchange (RFX) By using RFX, the MFC framework can be exchanged between databases and CRecordset variables. The exchange is created by executing a 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); / / Setting RFX macro for parameters, if there are more Parameters, must set up the order flag in the SQL statement, the name of the parameters in the RFX macro, such as "MBZM", is not used to match the parameters, you can define it yourself. } 3.2.3 Increase the second table, and set the parameters of the second table on the base application created by 3.2.1, enter the classwizard, click the Add Class ... button and in the pop-up menu. Select New ..., then enter the CZF1001 in the NAME column in the Create New Class dialog, select CRecordset in the Base Class box, press the CREATE button. In the pop-up Database Options dialog box, select the DB-FSB data source in the ODBC combo box. Then press the OK button. Select the DBK1 table in the SELECT DATABASE TABLES dialog box that pops up. Press OK to confirm. And in all files that exist #include "czf0001set.h", #include "czf1001.h" is added. This creates CRecordset derived classes corresponding to the DBK1 table. In the CZF0001Doc class created in the first step, the pointer variable M_ZF1002 (ie: CZF1001 * M_ZF1002) of a CZF1001 object is added. Set the parameter MHH in the CZF1001 class according to 3.2.2. In the CZF1001 class. 3. 3 Parameters MHH and Parameters MBZM Use 3.3.1 Parameters in CRECORDVIEW 3.3.1 Parameters in the CZF0001View :: OnInitialUpdate () function using the beginning of the CZF0001View :: OnInitialUpdate () function, call CZF0001View :: getDocument () In the document class CZF0001Doc class, the pointers of the two CRecordset classes (CZF0001SET, CZF1001) are returned. Set m _strfilter according to the returned pointer, and determine the initial value of the two parameters of the two parameters. Here is a point: m_pset-> m_strfilter = "bzm like?"; M_pset2-> m_strfilter = "?" "?" In the statement, "?" When calling Open or Requory, "?" Will be automatically CZF0001Set :: Mbzm and CZF1001 :: MHH values.
For example, specifying MBZM "31001", then m_pset-> m_strfilter will become "BZM = 31001". This way the user can get the required recordset as long as the MBZM is specified. CZF0001View :: OnInitialUpdate () Program list is as follows (the statement of the black body is hand-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 = "%"; // Initial selection all record m_pset-> m_strsort = ""; m_pset2-> m_strfilter = "hh Like? "; m_pset2-> MHH = m_pset-> m_bzm; // Put the value of the CRecordset derived class of the CRecordset corresponding to the table FSB, as the value m_pset2-> m_strsort =" "; // retrieval of the value MHH m_pset-> m_pdatabase = m_pset2-> m_pdatabase; // Share CDatabase CRecordView :: OnInitialUpdate (); getParentFrame () -> recalclayout (); resizeParentToft ();} 3.3.2 Add edit box in the dialog box in the resource view Dialog's IDD_ZF0001_Form form, join the edit box required by the user. Select the relevant fields in the first table FSB with ClassWizard. However, using classwizard could not find the second table DBK1 field variable, so for the edit box connected to the second table DBK1 field, you must modify the DODATAEXChange () (dialog data exchange function) of the CRecordView class with manually. Add relevant content after DODATAEXCHANGE () function "//}} AFX_DATA_MAP". See the black body part of the program below.
If the black body part statement is added in front of "//}} AFX_Data_map", it is not possible to use ClassWizard. 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_ZCM, M_PSET2);} 3.3.3 Add a button in the dialog box to demonstrate the effect of two tables, Add a "associated" button and a "positioning" edit box for an input parameter in the dialog box.
