CRecordset class
The CRecordset class represents a recordset. This class is the most important and powerful class in the ODBC class of the MFC.
10.5.1 Dynamic Set, Snapshot, Cursor and Cursor Library
In multi-task operating systems or network environments, multiple users can share the same data source. One of the main issues of sharing data is how to coordinate the modifications to the data sources. For example, when an application changes the record in the data source, other applications should be processed to the application. For this problem, MFC-based ODBC applications can take several different ways, which will be determined by the program.
The record set is mainly divided into two Snapshot and Dynast (Dynaset), and the CRecordset class supports both. Different performances of these two records have taken different processing methods in their other application change data source records.
The snapshot record set provides a static view of the data. Snapshot is a very image term, just like a photo of some records of the data source. When other users have changed records (including modification, adding, and delete), the records in the snapshot are unaffected, that is, the snapshot does not reflect the change in the data source record. The snapshot will reflect changes until the CRecordset :: Requery reconfers. Snapshots are useful for jobs that do not want the middle of changes in the cause of reporting or execution calculations. It should be pointed out that this static feature of the snapshot is relative to other users, it will correctly reflect the modification and deletion of the records of the record, but for the newly added record until Requuery can be reflected in the snapshot .
Dynamic sets provide data dynamic. When other users modify or delete records in the record set, they will reflect in the dynamic set: When scrolling to the modified record, the modification it makes it immediately reflected in the dynamic set, when the record is deleted, the MFC code will Skip the delete part of the recordset. For other users added records until Requuery is called, they will be reflected in the dynamic set. It is reflected in the dynamic set of modifications, add, and deleting records. When the data must be dynamic, use the dynamic set is the most suitable. For example, in a train ticket ticketing system, it is clear that the dynamic set should be used to reflect changes in shared data at any time.
Scroll in the record set, you need to have a flag to indicate the position after the scroll (current location). The ODBC driver maintains a cursor to track the current record of the record set, which can understand the cursor into a mechanism of tracking the recordset location.
Cursor Library is a dynamic link library (ODBCCR32.DLL) between the ODBC driver manager and driver. The main function of the optical library is to support snapshots and provide bidirectional scrolling capabilities for the underlying driver, and high-level drivers do not require optical bid libraries because they are scrolling. The cursor library management snapshot recorded buffer, the buffer reflects the modification and deletion of the record, but does not reflect the change of other users to record, which is visible, and the snapshot is actually equivalent to the current cursor buffer buffer.
It should be noted that a snapshot is a static cursor. The static cursor until scrolling to a record to acquire the data. Therefore, to ensure that all records are snapshots, you can scroll to the end of the record set, and then scroll to the first record of interest. The disadvantage of this is that the additional overhead is required to scroll to the end, which will reduce performance.
Unlike snapshots, the dynamic set does not have the buffer to maintain the buffer to store records. In fact, the dynamic set does not use the optical library because the optical label will block some underlying driver functions that support the dynamic set. The dynamic set is a key set drive cursor. When a dynamic set is turned on, the driver saves the records of each record in the record. As long as the cursor scrolls in the dynamic set, the driver will check the current record from the data source by key, so that the selected record is synchronized with the data source. As can be seen from the above analysis, the snapshot and dynamic set has a common feature, that is, after the establishment of the recordset, the members in the record set have been determined. This is why two records cannot reflect the reason for other users to add records.
10.5.2 Domain Data Members and Data Exchange
The CRecordset class represents a recordset. Users generally need to create a CRecordset derived class with ClassWizard. ClassWizard creates a batch of data members for derived records, which correspond to the fields of records, called field data members or domain data members. For example, the ClassWizard will join 6 domain data members in the derived class for table 10.2, and ClassWizard will join 6 domain data members in the derived class, as shown in Listing 10.1. It can be seen that the domain data member is similar to the field name in the table, and the type matches.
Table 10.2 SECTION Table in STDREG32.MDB
Courseid (Text) sectionno (text) Instructorid (text) roomno (text) schedule (text) Capacity (int) math101 1 klausenj Ken-12 MWF10-11 40 Math101 2 Rogersn WIL-1088 TTH3: 30-5 15 Math201 1 Rogersn WIL -1034 MWF2-3 20 MATH201 2 Smithj WIL-1054 MWF3-4 25 Math202 1 KLA WIL-1054 MWF9-10 20 MATH202 2 Rogersn Ken-12 TTH9: 30-11 15 Math202 3 Klausenj WIL-2033 TTH3-4: 30 15
Listing 10.1 Domain data in derived class
class CSectionSet: public CRecordset {public: // {{AFX_FIELD (CSectionSet, CRecordset) CString m_CourseID; CString m_SectionNo; CString m_InstructorID; CString m_RoomNo; CString m_Schedule; int m_Capacity; //}} AFX_FIELD......... };;
Domain data members are used to save each field of a record, which is a buffer between the program and the record. Domain data members represent the current record, when scrolling to a record in the record set, the framework automatically copies the recorded fields into the domain data member of the recordset object. When the user wants to modify the current record or add new records, the program first places the new value of each field into the domain data member, and then call the corresponding CRecordset member function Set the domain data member to the data source.
It is not difficult to see that there is a data exchange problem between the record set and the data source. The CRecordset class automatically exchanges data between domain data members and data sources using the "Record Field Exchange, abbreviated as RFX) mechanism. The RFX mechanism is similar to the dialog data exchange (DDX). CRecordSet member function DOFIELDEXCHANGE is responsible for data exchange tasks, and a series of RFX functions are called in this function. When the user joins domain data members with ClassWizard, ClassWizard will automatically establish RFX in DofieldExchange. Typical DOFIELDEXCHANGE as shown in Listing 10.2: Listing 10.2 Typical DOFIELDEXCHANGE functions
void CSectionSet :: DoFieldExchange (CFieldExchange * pFX) {// {{AFX_FIELD_MAP (CSectionSet) pFX-> SetFieldType (CFieldExchange :: outputColumn); RFX_Text (pFX, _T ( "[CourseID]"), m_CourseID); RFX_Text (pFX, _T ("[Sectionno]", m_sectionno); RFX_Text ("[InstructorId]"), M_instructorID; RFX_Text (PFX, _T ("[Roomno]"), M_ROOMNO; RFX_Text (PFX, _t "[Schedule]", m_schedule); RFX_INT (PFX, _T ("[Capacity]"), M_Capacity); //}} AFX_FIELD_MAP}
10.5.3 SQL query
The establishment of the record set is actually mainly a query process, and the SQL SELECT statement is used to query the data source. When establishing a recordset, CRecordSet constructs a SELECT statement based on some parameters to query the data source and create a recordset with the results of the query. Understand this point is critical to understand CRecordset. The syntax of the SELECT statement is as follows:
SELECT RFX-Field-List from table-name [where m_strfilter] [Order By M_STRSORT]
Where Table-Name is a table name, RFX-Field-List is a selected column (field). WHERE and ORDER BY are two clauses, which are used to filter and sort, respectively. Below is some examples of SELECT statements:
Select Courseid, Instructorid from section
Select * from section where courseid = 'math202' and capacity = 15
Select Instructorid from section section Order by Courseid ASC
The first statement selects the CourseID and Instructorid fields from the section table. The second statement selects the CourSeID from the section table to Math202 and the Capacity equal to 15, in which the logical connectors like "AND" or "OR" are used. Note that the single quotes are enclosed in a single quotes when references the strings, date or time, and the like data in the SQL statement, and the numerical data is not available. The third statement selects the InstructorID column from the section table and is arranged in ascending order of CourseI, and uses the keyword DESC. Tip: If there is a space in the column name or table name, you must pack the name with square brackets. For example, if there is a column name "Client Name", "[Client Name]" should be written.
10.5.4 Record Settings and Closing To create a recordset, first construct a CRecordset derived class object, then call the Open member function query the record in the data source and establish a recordset. In the Open function, the getDefaultconnect and getDefaultsql functions may be called. Function declaration
CRecordset (CDATABASE * PDATABASE = NULL); parameter pDatabase points to a CDATABASE object to get the data source. If PDATABASE is NULL, a CDATABASE object is automatically built in the Open function. If the CDATABase object has not been connected to the data source, the connection is established in the Open function (see 10.3.1) is provided by the member function getDefaultConnect.
Virtual cstring getDefaultConnect (); This function returns the default connection string. The Open function will call the function to obtain the connection string to establish a connection to the data source. It is generally necessary to override this function in the CRecordset derived class and provide a connection string in the new version.
virtual BOOL Open (UINT nOpenType = AFX_DB_USE_DEFAULT_TYPE, LPCTSTR lpszSQL = NULL, DWORD dwOptions = none); throw (CDBException, CMemoryException); This function records the SQL statements specified query the data source and with a specified set of record types and options to establish . The parameter NOPENTYPE illustrates the type of record set, as shown in Table 10.3, if the required type driver does not support, the function will produce an exception. The parameter LPSZSQL is a SQL SELECT statement or a table name. The function is queried with LPSZSQL. If this parameter is null, the function calls getDefaultsql to get the default SQL statement. Parameters DwOptions can be a combination of options, and common options are listed in Table 10.4. If the function returns true if the function is called, if the function calls CDatabase :: Open and returns FALSE, the function returns false.
Table 10.3 Type of record set
Type meaning AFX_DB_USE_DEFAULT_TYPE uses the default value. CRecordset :: Dynaset can be two-way scrolling dynamic sets. CRecordset :: Snapshot can be rolled two-way snapshot. CRecordset :: Dynamic provides better dynamic characteristics than dynamic sets, and most ODBC drivers do not support this recordset. CRecordset :: Forwardonly can only swipe the read-only record set. Table 10.4 Creating a record set
Option Meaning CRecordset :: None No Options (Default). CRecordset :: Appendonly does not allow modification and deletion records, but you can add a record. CRecordset :: Readonly record set is read-only. CRecordset :: SkipDeletedRecords Some databases (such as foxPro) are not deleted when deleting records, but do a delete tag, which will skip these deleted records when scrolling.
Virtual cstring getDefaultsql (); Open function calls this function to return to the default SQL statement or table name to query the record in the data source. It is generally necessary to overwrite this function in the CRecordset derived class and provide SQL statements or table names in the new function. Here are some examples of returning strings. "Section" // Select all records in the section table to record the "section, course" // combined section and the columns of the course table to record
/ / Sort all records in the section table, order in ascending order, and establish record sets
"Select * from section Order by Courseid ASC"
The above example illustrates that the Open function can be very flexible to query the records in the data source by reasonably schedule SQL statements and table names. The user can combine the fields of multiple tables, or only some of the records in the record can also be filtered and sorted.
The last section said that when establishing a recordset, CRecordSet constructs a SELECT statement to query the data source. If you only provide a table name when calling Open, the SELECT statement also lacks the selection parameter RFX-Field-List (see 10.5.3). The framework specifies that if only the table name is provided, the selection of the column is extracted from the RFX statement in DofieldExchange. For example, if you only provide the "section" table name when calling Open, then the following SELECT statement will be constructed:
Select Courseid, SectionNo, InstructorId, Roomno, Schedule, Capacity from Section
After establishing a recordset, the user can call the Requery member function at any time to re-query and establish a recordset. Requuery has two important purposes:
Make records to reflect the user's changes to the data source (see 10.5.1). Inquiry records and re-establish records in accordance with new filtration or sorting methods.
Before calling Requory, you can call CANRESTART to determine if the recordset supports the Requory operation. To remember that Requery can only call the Open after successful call, the program should call the ISOpen to determine if the recordset has been established. Function declaration
Virtual Bool Requory (); throw (CDBException, cMemoryException); Returns True indicates that the recordset is successful, otherwise it returns false. An exception is generated if the function is internally errors. Bool canRestart () const; // Return true if Requuery
Bool isopen () const; // Returns true if the record set has been established
The CRecordset class has two public data members M_STRFILTER and M_STRSORT to set filtering and sorting recorded. Before calling Open or Requuery, if you specify filtering or sort in these two data members, Open and Requery will query the data source according to the filtering and sorting of these two data members.
Members m_strfilter is used to specify a filter. M_Strfilter actually contains the content of the WHERE clause of SQL, but it does not contain WHERE keywords. An example of using m_strfilter is:
m_pset-> m_strfilter = "courseid = 'math101'"; // only select the courseid for Math101
IF (m_pset-> open (crecordset :: snapshot, "section"))
..........
Members M_STRSORT is used to specify sorting. M_STRSORT actually contains the contents of the Order By clause, but it does not contain the Order By keyword. An example of M_STRSORT is
m_pset-> m_strsort = "courseid desc"; / / Press CourSeid's descending order
m_pset-> open ();......
In fact, the Open function is constructing the SELECT statement, puts the contents of M_Strfilter and M_STRSORT in the WHERE and Order By clause of the SELECT statement. If the WHERE and ORDER BY clauses have been included in the Open's LPSZSQL parameters, then m_strfilter and m_strsort must be empty.
Call the non-parameter member function Close to close the recordset. After the CLOSE function is called, the program can call the Open to establish a new recordset again. The destructor of CRecordset calls the Close function, so the recordset is also closed when the CRecordset object is deleted.
10.5.5 Rolling Record
CRecordset provides several member functions to scroll in the recordset, as shown below. When scrolling into a new record with these functions, the framework automatically copies the newly recorded content into domain data members.
Void MoveNext (); // For a record
Void moveprev (); // Back a record
Void movefirst (); // Scroll to the first record in the recordset
Void movelast (); // Scroll to the last record in the recordset
Void SetabSolutePosition (long nrows); This function is used to scroll at an absolute position specified by the parameter nROWS. If NROWS is negative, scroll from the previous. For example, when NROWS is -1, the function scrolls to the end of the recordset. Note that this function will not skip the deleted records.
Virtual Void Move (Long NROWS, Word WFETCHPE = SQL_FETCH_RELATIVE); the function is powerful. You can complete the function of the above five functions by specifying the WFETCHTYPE parameter as SQL_FETCH_NEXT, SQL_FETCH_PRIOR, SQL_FETCH_FETCH_PL_FETCH_LAST, and SQL_FETCH_ABSOLUTE. If wfetchtype is SQL_FETCH_RELATIVE, then move relative to the current record, if nrows is positive, then move forward, if NROWS is negative, then moves backward. If you select the CRecordset :: SkipDeletedRecords option when establishing a recordset, then in addition to SetabsolutePosition, you will skip the deleted records when scrolling records, this object FoxPro is very important.
If the record set is empty, then the above function will produce an exception. In addition, it must be ensured that the scrolling does not exceed the boundary of the recordset. Calling ISEOF and ISBOF can perform this detection.
Bool ISEOF () const; if the recordset is empty or scrolling over the last record, then the function returns true, otherwise returns false.
Bool isbof () const; if the recordset is empty or scrolling through the first record, the function returns true, otherwise returns false.
Here is an example of using ISEOF:
While (! m_pset-> ISEOF ()) m_pset-> moext ();
Calling getRecordCound to get the total number of records in the record set, the declaration of this function is
LONG GETRECORDCOUNT () Const; Note that this function returns to the farthest distance that the user is scrolling in the recordset. To really return to the total number of records, only call MOVENEXT to move to the end of the recordset (MoveLast is not line).
10.5.6 Modify, add, and delete records
To modify the current record, you should follow the steps:
Call the Edit member function. When you call this function, you entered the edit mode, and the program can modify the domain data member. Be careful not to call Edit in an empty recordset, otherwise an exception will occur. The EDIT function saves the content of the current domain data in a buffer, and there are two purposes, one is to compare with domain data members to determine which fields have been changed, and the other is necessary when necessary. Domain data member original value. If you call Edit again, a domain data member will be recovered from the buffer, and the program is still in editing mode. Call MOVE (AFX_MOVE_REFRESH) or MOVE (0) can exit edit mode (the value of AFX_MOVE_REFRESH is 0), and the function restores domain data from the buffer.
Set up the new value of domain data members.
Call Update to complete the editing. Update writes the changed record into the data source and ends the edit mode.
To add a new record to the record, you should follow the steps:
Call the AddNew member function. After calling this function, enter the add mode, which sets all domain data members into NULL (note that in the database term, NULL means no value, which is different from C NULL). Like Edit, AddNew saves the contents of the current domain data in a buffer. When necessary, the program can call the AddNEW again to cancel the add operation and restore the original value of the domain data member, and the program is still adding mode. . Call MOVE (AFX_MOVE_REFRESH) can exit the add mode while the function recovering domain data from the buffer. Set domain data members.
Call Update. Update writes the content in the domain data as a new record to the data source, thereby ending the addition.
If the recordset is a snapshot, then after adding a new record, you need to call the Requery re-query because the snapshot cannot reflect the addition of the addition.
To delete the current record of the record set, you should follow these steps below:
Call the DELETE member function. This function will simultaneously add the delete tag to the record set and data source. Be careful not to call Delete in an empty recordset, otherwise an exception will be generated.
Scroll to another record to skip the delete record.
The function mentioned above is:
Virtual void edit (); throw (cdbexception, cmemoryexception); Virtual void addnew (); throw (cdbexception); virtual void delete (); throw (cdbexception);
Virtual Bool Update (); throw (cdbexception); If the update fails, the function returns false and generate an exception.
Before you make changes to the record set, the program may call the following function to determine if the recordset can be changed because if you modify it in a recordset that cannot be changed, add or delete will result in abnormal generation.
Bool canupdate () const; // Returns TRUE indicates that the record can be modified, added, and deleted.
Bool canappend () const; // Returns TRUE indicates that a record can be added.
