Use MFCODBC to write database applications

3.1 Overview
The Visual C ++ MFC class library defines several database classes. When using ODBC programming, CDatabase, CRecordSet, and CRecordView are often used ). Where:
The CDatabase object provides a connection to the data source, through which you can operate on the data source.
The CRecordView class object can display database records in a controlled manner. This view is directly connected to the table view of a CRecordSet object.
The CRecordSet Class Object provides a set of Records extracted from the data source. The CRecordSet object is usually used in two forms: dynamic row set (dynasets) and snapshot set (snapshots ). Dynamic datasets can be kept in sync with changes made by other users. A snapshot set is a static view of data. Each form provides a set of records when the record set is opened. The difference is that when you roll to a record in a dynamic row set, changes made to this record by other users or other record sets in your application are displayed accordingly.

Visual C ++ provides several record sets that can be used to customize how applications work. The fastest way to view these different options requires both speed and feature. You will find that in many cases, if you want to add features, you have to pay the cost of reduced program execution speed. The following describes the record set options that can be freely controlled. More importantly, tell you if you can get faster or more features from this option.
1. the Snapshot option requires Visual C ++ to download the entire query from a Snapshot. In other words, you can quickly take a photo of the database content and use it as the basis for future work. This method has three disadvantages. First, you cannot see updates made by others on the network. This may mean that your decision is based on old information. Second, download all these records at a time, which means that the network is heavily burdened during the download process. Third, the user stops waiting when the record is downloaded, which means the network call performance has become lower. However, this method also has two advantages. First, once the record is downloaded, the network activity required by the workstation is almost lost, which releases the bandwidth for other requests. In short, you will see an increase in network throughput. Second, because all the applied records are on the user's machine, the user will actually get better overall performance of the application. You may want to restrict the snapshot method to a small database because the snapshot is applicable to user request information and not to data editing sessions.
2. When Dynaset (dynamic set) uses this option, Visual C ++ creates an actual pointer pointing to each record in the request. In addition, only the records actually needed when filling the screen are downloaded from the server. The benefits of this method are obvious. You can see records on the screen almost immediately. The changes made by other users to the database are also displayed. Finally, other users will also see your changes, because the dynamic set is uploaded to the server when you change the record. Obviously, this method requires real-time access to the server, which reduces the total network throughput and application performance. This option is suitable for creating applications that take a lot of time to edit data. At the same time, it is also the best choice for large databases, because you only need to download the information you actually need.
3.2 Use ODBC programming
AppWizard can be used to establish an ODBC application framework, or ODBC can be used directly for database programming. In this case, the header file afxdb. h should be included.
The two most important classes for application ODBC programming are CDatabase and CRecordSet. However, in applications, the CRecordSet class should not be used directly, but an export class must be generated from the CRecordSet class, and add member variables corresponding to fields in the database table. Then, reload the member function DoFieldExchange of the CRecordset class. This function uses the RFX function to exchange data between the database field and the member variable of the record set field. The RFX function exchanges data (DDX) in the same dialog box) similar mechanism, responsible for data exchange between databases and member variables.

The following is an example of ODBC programming skills in the Visual C ++ environment:
3.21 database connection
In the CRecordSet class, a variable m_pDatabase is defined:
CDatabase * m_pDatabase;
It is a pointer to the object database class. If you pass an opened CDatabase Class Object Pointer to m_pDatabase before the CRecordSet Class Object calls the Open () function, you can share the same CDatabase class object. For example:

CDatabase m_db;
CRecordSet m_set1, m_set2;
M_db.Open (_ T ("Super_ES"); // create an ODBC connection
M_set1.m_pDatabase = & m_db; // m_set1 reuse m_db object
M_set2.m_pDatabse = & m_db; // m_set2 reuse m_db object
Or:

Cdatabase db;
Db. Open ("Database"); // establish an ODBC connection
CrecordSet m_set (& db); // construct a record set object to direct the database to the database
3.22 query records
The query records use the CRecordSet: Open () and CRecordSet: Requery () member functions. Before using the CRecordSet class object, you must use the CRecordSet: Open () function to obtain a valid record set. Once you have used the CRecordSet: Open () function, you can apply the CRecordSet: Requery () function when querying again. When calling the CRecordSet: Open () function, if you have passed an opened CDatabase object pointer to the m_pDatabase member variable of the CRecordSet class object, use the database object to establish an ODBC connection; otherwise, if m_pDatabase is empty, create a CDatabase Class Object and connect it to the default data source, then initialize the CRecordSet class object. The default data source is obtained by the getdefaconnect connect () function. You can also provide the required SQL statement and use it to call the CRecordSet: Open () function, for example:
M_Set.Open (AFX_DATABASE_USE_DEFAULT, strSQL );
If no parameter is specified, the program uses the default SQL statement to operate on the SQL statements specified in the getdefasql SQL () function:

CString CTestRecordSet: getdefasql SQL ()
{Return _ T ("[BasicData], [MainSize]");}
For the table name returned by the getdefasql SQL () function, the default operation is the SELECT statement, that is:
SELECT * FROM BasicData, MainSize

You can also use the member variables m_strFilter and m_strSort of the CRecordSet during the query to execute conditional query and result sorting. M_strFilter is a filter string that stores the WHERE condition strings in the SQL statement; m_strSort is a Sort string that stores the ORDERBY character strings in the SQL statement. For example:
M_Set.m_strFilter = "TYPE = motor ";
M_Set.m_strSort = "VOLTAGE ";
M_Set.Requery ();
The corresponding SQL statement is:
SELECT * FROM BasicData, MainSize
Where type = Motor
ORDER BY VOLTAGE
In addition to directly assigning values to m_strFilter, you can also use parameterization. Parameterization makes conditional query tasks more intuitive and convenient. To use parameterization, follow these steps:

(1). Declare the parameters:

Cstring p1;
Float p2;
(2) initialize the parameters in the constructor.
P1 = _ T ("");
P2 = 0.0f;
M_nParams = 2;
(3) bind the variable to the corresponding column
PFX-> SetFieldType (CFieldExchange: param)
RFX_Text (pFX, _ T ("P1"), p1 );
RFX_Single (pFX, _ T ("P2"), p2 );
After completing the preceding steps, you can use the parameters for conditional query:
M_pSet-> m_strFilter = "TYPE =? ANDVOLTAGE =? ";
M_pSet-> p1 = "Motor ";
M_pSet-> p2 = 60.0;
M_pSet-> Requery ();
The value of the Parameter Variable replaces "?" In the query string in the order of binding. Adapter.
If the query results contain multiple records, you can use the CRecordSet functions to Move (), MoveNext (), MovePrev (), MoveFirst (), and MoveLast () to Move the cursor.
3.23 add record
The AddNew () function is used to add records, which requires that the database be opened in the allowed way:

M_pSet-> AddNew (); // Add a new record to the end of the table
M_pSet-> SetFieldNull (& (m_pSet-> m_type), FALSE );
M_pSet-> m_type = "Motor ";
... // Enter a new field value
M_pSet-> Update (); // Save the new record to the database
M_pSet-> Requery (); // refresh the record set
3.24 delete records
Directly use the Delete () function, and you do not need to call the Update () function after calling the Delete () function:

M_pSet-> Delete ();
If (! M_pSet-> IsEOF ())
M_pSet-> MoveNext ();
Else
M_pSet-> MoveLast ();

3.25 modify records
Use the Edit () function to modify records:

M_pSet-> Edit (); // modify the current record
M_pSet-> m_type = "generator"; // modify the field value of the current record
...
M_pSet-> Update (); // Save the Modification result to the database
M_pSet-> Requery ();
3.26 statistical records
Statistical records are used to count the total number of record sets. You can declare a CRecordse first.