Using VC 6.0 Developing Multi-Table Database Application Summary This article describes the programming method of using the MFC ODBC class when developing database applications with Visual C 6.0, detailing the SQL statement in the MFC ODBC derived class. The method of the parameter implements the association of two tables. Keyword database, multi-table joint, MFC ODBC1. 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 MFC2.1 ODBCODBC (Open Database Interconnection) application can access data in different data sources via ODBCAPI, each different data source type supported by ODBC driver, this driver completed the core of the ODBC API program, And communicate with the specific database. 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 of the MFC ODBCMFC 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 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 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 in new record set implementation. Table 1: FSB Table Structure Field Name Type Remark BZMC Index Keyword DGDL1NDGZD1N Other Field Table 2: DBK1 Table Field Name Type Remarks HHC Index Keyword BLNZZCMC Other Fields Based on the MFC ODBC class development, the SQL statement is manipulated by the SQL statement by the MFC ODBC class. Database Database Database SQL statement is: Select * from FSB, dbk1 where fsb.bzm = dbk1 .HH Since a CRecordSet is generated, only one table in a data source is selected, so the database application developed based on the MFC ODBC class is to implement two table associations, and the parameter m _strfilter of the CRecordset class is used. It is quite The WHER clause in the SQL statement. Parameter m _STRSORT is equivalent to the Group By clause in the SQL statement. Pay attention to the "WHERE" keyword in the m_strfilter string. This example is derived in the table FSB and the table DBK1 Crecordset derived class MBZM and MHH two m _Strfilter parameters are used. When you click the "associated" button with the mouse, the program first uses the contents of the MBZM according to the contents of the "Position" editing box, and the result set is retrieved in the table FSB. Table DBK1 CRecordset The derived class is retrieved as the value of the CRecordset derived class corresponding to the table FSB, and the value of the table DBK1 corresponding to the table FSB key field BZM is obtained. The Table FSB is associated with the table DBK1. By This opinion, the key to the two tables is the setting of the M_Strfilter parameter. Figure 13.2 Database application creates 3.2. 1 Create a single table document database application using VC AppWizard generated according to the data source DB-FSB established in front A single form, a single document database application. Select the data table of the DB-FSB to select the fsb.dbf. Applications are ZF0001 (see the specific steps for VC data). ZF0001 application created CZF0001DOC, CZF0001SET, C ZF0001View et al.
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; cstract m_dgqd1; ,,,,, / / / For salary space, omitted part of the field cstring m_bz; //}} AFX_FIELDCSTRING MBZM; // Parameter MBZM // Overrides // ClassWizard generated Virtual Function overrides ,,,,,,, Virtual Void Dump (CDumpContext & DC) const; #ENDIF}; 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_INITM_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_MAPPFX-> setfieldType (cfieldExchange :: param); // Set the field type to cfieldexchange :: paraMrfx_text (PFX, "MBZM", MBZM); / / Set RFX macro for parameters, if there are multiple parameters , Must be set in the order of the location flag in SQL statements, 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 cdatabasecrecordView :: OnInitialUpdate (); getParentFrame () -> recalcLayout (); resizeParentToft (); resizeParentToft ();} 3.3.2 Add edit box in the Dialog In the 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", if you want to modify the IDD_ZF0001_form form again, you will not be able 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_MAPDDX_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.
