Shulou(Shulou.com)05/31 Report--

How to use CRecordset class in ODBC to operate the database? in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

ODBC class in 1.MFC

There are mainly CDatabase, CRecordset, CRecordview, CDBException and CFieldExchange. These classes encapsulate ODBC

SDK function, you can easily operate the database that supports ODBC.

(1) CDatabase class: a method that encapsulates the connection between the application and the database that needs to be accessed, controls the commit of transactions and executes SQL statements.

(2) CRecordset class: encapsulates most of the methods for manipulating the database, including browsing, modifying records, controlling cursor movement, sorting and other operations. The CRecordset class is the most important and powerful class in MFC's ODBC class. The CRecordset class object provides a recordset extracted from the data source. In a multitasking operating system or network environment, multiple users can share the same data source. One of the main problems of sharing data is how to coordinate the changes made by various users to the data source. CRecordset provides several different ways to deal with this problem, depending on which type of recordset the program uses.

CRecordset objects are commonly used in two forms: dynamic rowset (dynasets) and snapshot set (snapshots). A dynamic rowset can be synchronized with changes made by other users, while a snapshot set is a static view of the data. When other users change records (including modifications, additions, and deletions), the records in the snapshot are not affected, that is, snapshots do not reflect other users' changes to the data source records. The snapshot does not reflect the changes until CRecordset::Requery re-query is called. Each form provides a set of records when the recordset is opened, except that when you scroll to a record in a dynamic rowset, changes made to the record by other users or other recordsets in the application will be displayed accordingly, for example, changes in shared data should be displayed in real time in the train online sales system.

(3) CRecordView class: provides a view connected to the CRecordset object, which can establish the correspondence between the controls in the view and database data, and support cursors, modify records and other operations.

(4) CDBException class: provides exception handling for database operations.

(5) CFieldExchange class: provides data exchange between user variables and database fields.

two。 Domain data members and data exchange

The CRecordset class represents a recordset. Users usually use ClassWizard to create a derived class of CRecordset. ClassWizard can create a group of data members for derived recordset classes that correspond to the fields of the record and are called field data members or domain data members. The domain data member is similar to the field name in the table and matches the type.

For example: this is a definition of a derived class of the CRecordset class

Class CSetdata: public CRecordset

{

Public:

CSetdata (CDatabase* pDatabase = NULL)

DECLARE_DYNAMIC (CSetdata)

/ / Field/Param Data

/ / {{AFX_FIELD (CSetdata, CRecordset)

/ / define domain data member variables, which are used to exchange data with the corresponding fields of the recordset and act as a buffer or intermediate bridge. That is, when we deal with the recordset, we actually operate on the domain data members, not directly on the data in the database.

CString m_number

CString m_name

CString m_sex

Long m_age

/ /}} AFX_FIELD

CString number

/ / Overrides

/ / ClassWizard generated virtual function overrides

/ / {{AFX_VIRTUAL (CSetdata)

Public:

Virtual CString GetDefaultConnect (); / / Default connection string

Virtual CString GetDefaultSQL (); / / DefaultSQL for Recordset

Virtual void DoFieldExchange (CFieldExchange* pFX); / / RFX support

/ /}} AFX_VIRTUAL

/ / Implementation

# ifdef _ DEBUG

Virtual void AssertValid () const

Virtual void Dump (CDumpContext& dc) const

# endif

}

# endif

Domain data members are used to hold the fields of a record, which are buffers between the program and the record. The domain data member represents the current record, and when scrolling to a record in the recordset, the framework automatically copies the fields of the record to the domain data member of the recordset object. When the user wants to modify the current record or add a new record, the program first puts the new values of each field into the domain data member, and then calls the corresponding CRecordset member function to set the domain data member to the data source.

It is not difficult to see that there is a data exchange problem between the recordset and the data source. The CRecordset class uses the "Record Field Exchange" (RFX) mechanism to automatically exchange data between the domain data members and the data source. The RFX mechanism is similar to conversational data exchange (DDX). DoFieldExchange, a member function of CRecordset, is responsible for the data exchange task, in which a series of RFX functions are called. When a user joins a domain data member with ClassWizard, ClassWizard automatically establishes a RFX in DoFieldExchange.

Typical DoFieldExchange function

Void CSetdata::DoFieldExchange (CFieldExchange* pFX)

{

/ / {{AFX_FIELD_MAP (CSetdata)

PFX- > SetFieldType (CFieldExchange::outputColumn)

RFX_Text (pFX, _ T ("[number]"), m_number); / / implements data exchange between domain data members and data sources

RFX_Text (pFX, _ T ("[name]"), m_name)

RFX_Text (pFX, _ T ("[sex]"), m_sex)

RFX_Long (pFX, _ T ("[age]"), m_age)

/ /}} AFX_FIELD_MAP

PFX- > SetFieldType (CFieldExchange::param)

RFX_Text (pFX,_T ("[]"), number)

}

3. Establishment and closure of recordset

To use the ODBC class of MFC to operate the database, you should first establish a derived class of CRecordset to associate the corresponding data table, and then call the Open () function to query the data in the data source and establish the recordset. In addition, the GetDefaultConnect and GetDefaultSQL functions may also be called in the Open function.

(1) first, there is a parameter pointing to a CDatabase object in the constructor of the derived class, which is used to get the data source. Functions are as follows:

CRecordset (CDatabase* pDatabase = NULL)

If pDatabase is NULL, a CDatabase object is automatically built in the Open function. If the CDatabase object is not already connected to the data source, a connection is established in the Open function, and the connection string is provided by the member function GetDefaultConnect.

(2) virtual CString GetDefaultConnect ()

This function returns the default connection string. The Open function calls this function when necessary to get the connection string to establish a connection to the data source. You generally need to override this function in the CRecordset derived class and provide the connection string in the new version of the function.

For example: CSetdata is a derivative of the CRecordset class

CString CSetdata::GetDefaultConnect ()

{

Return _ T ("ODBC;DSN=my data")

}

This function gets the database connection string, including the connection method and the name of the data source.

(3) virtual CString GetDefaultSQL ()

The Open function calls this function when necessary to return the default SQL statement or table name to query the records in the data source. It is generally necessary to override this function in a CRecordset derived class and provide a SQL statement or table name in the new version of the function.

For example:

CString CSetdata::GetDefaultSQL ()

{

Return _ T ("[bingli]")

}

This class returns the name of the data table bingli in the data source.

(4) virtual BOOL Open (UINT nOpenType = AFX_DB_USE_DEFAULT_TYPE, LPCTSTR lpszSQL = NULL, DWORD dwOptions = none)

Throw (CDBException, CMemoryException)

This function queries records in the data source using the specified SQL statement and establishes a recordset based on the specified type and options. The parameter nOpenType describes the type of recordset, as shown in the following table. If the required type driver does not support it, the function will generate an exception. The parameter lpszSQL is a SELECT statement for SQL, or a table name. The function uses lpszSQL to query, and if the parameter is NULL, the function calls GetDefaultSQL to get the default SQL statement. The parameter dwOptions can be a combination of options, and commonly used options are listed in the following table. If the creation is successful, the function returns TRUE. If the function calls CDatabase::Open and returns FALSE, the function returns FALSE.

Type of recordset

Types

Meaning

AFX_DB_USE_DEFAULT_TYPE uses the default value.

CRecordset::dynaset a dynamic set that scrolls in both directions.

CRecordset::snapshot A snapshot that can be scrollable in both directions.

CRecordset::dynamic provides better dynamic characteristics than dynamic sets, which are not supported by most ODBC drivers.

A read-only recordset that CRecordset::forwardOnly can only scroll forward.

Common options when creating recordset

Option

Meaning

CRecordset::none has no option (default).

CRecordset::appendOnly does not allow you to modify and delete records, but you can add records.

The CRecordset::readOnly recordset is read-only.

Some CRecordset::skipDeletedRecords databases (such as FoxPro) do not actually delete records when they are deleted, but instead make a delete mark that skips the deleted records when scrolling.

If all parameters are empty, for example:

CSetdata * pset = new CSetdata ()

Pset- > Open ()

Then the Open () function will call the GetDefaultSQL () function to get the data from the specified data table and build the dataset. In fact, if only the table name is provided, the CRecordset class constructs a SELECT statement to query the data source. For example: SELECT

(field name that can be provided) FROM bingli (that is, the table name returned by the GetDefaultSQL () function).

(5) after establishing the recordset, the user can call the Requery member function at any time to re-query and establish the recordset. Requery has two important uses:

Enable the recordset to reflect the user's changes to the data source

Query the records and recreate the recordset according to the new filtering or sorting method.

Before calling Requery, you can call CanRestart to determine whether the recordset supports Requery operations. Remember that Requery can only be called after a successful call to Open, so the program should call IsOpen to determine whether the recordset has been established. the declaration of the function is

Virtual BOOL Requery ()

Throw (CDBException, CMemoryException)

Returning TRUE indicates that the recordset was established successfully, otherwise FALSE is returned. An exception occurs if there is an error within the function.

BOOL CanRestart () const; / / returns TRUE if Requery is supported

BOOL IsOpen () const; / / returns TRUE if the recordset has been established

The CRecordset class has two common data members, m_strFilter and m_strSort, to set up filtering and sorting of records. If filtering or sorting is specified among the two data members before calling Open or Requery, Open and Requery will query the data source according to the filtering and sorting specified by the two data members.

Member m_strFilter is used to specify the filter. M_strFilter actually contains the contents of the WHERE clause of SQL, but it does not contain the WHERE keyword. An example of using m_strFilter is:

Select only records whose CourseID is MATH101.

If (masked pset-> Open (CRecordset::snapshot, Section))

. . . . . .

The member m_strSort is used to specify the sort. 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

CourseID-> mendstrSort = "CourseID DESC"; / / sorts records in descending order

Open _ set-> MSPET ()

. . . . . .

In fact, when constructing a SELECT statement, the Open function puts the contents of m_strFilter and m_strSort into the WHERE and ORDER of the SELECT statement

In the BY clause. If WHERE and ORDER BY clauses are already included in the lpszSQL parameter of Open, then m_strFilter and m_strSort must be empty.

Call the no-parameter member function Close to close the recordset. After calling the Close function, the program can call Open again to create a new recordset. The destructor of CRecordset calls the Close function, so the recordset is closed when the CRecordset object is deleted.

4. Scrolling record

CRecordset provides several member functions to scroll through the recordset, as shown below. When you scroll to a new record with these functions, the framework automatically copies the contents of the new record to the domain data member.

Void MoveNext (); / / advance a record

Void MovePrev (); / / back up 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 to the absolute position specified by the parameter nRows. If nRows is negative, scroll back and forward. For example, when nRows is-1, the function scrolls to the end of the recordset. Note that this function does not skip deleted records.

Virtual void Move (long nRows, WORD wFetchType = SQL_FETCH_RELATIVE)

This function is powerful. You can complete the functions of the above five functions by specifying wFetchType parameters as SQL_FETCH_NEXT, SQL_FETCH_PRIOR, SQL_FETCH_FIRST, SQL_FETCH_LAST and SQL_FETCH_ABSOLUTE. If wFetchType is SQL_FETCH_RELATIVE, it will move relative to the current record, if nRows is positive, move forward, if nRows is negative, move backward.

If the CRecordset::skipDeletedRecords option is selected when creating a recordset, deleted records will be skipped when scrolling records, in addition to SetAbsolutePosition, which is important for a database such as FoxPro.

If the recordset is empty, calling the above function will generate an exception. In addition, you must ensure that scrolling does not exceed the boundaries of the recordset. This can be detected by calling IsEOF and IsBOF.

BOOL IsEOF () const; / / if the recordset is empty or scrolls past the last record, the function returns TRUE, otherwise it returns FALSE.

BOOL IsBOF () const; / / if the recordset is empty or scrolls past the first record, the function returns TRUE, otherwise it returns FALSE.

Here is an example of using IsEOF:

While (! mroompset-> IsEOF ())

MoveNext _ set-> MSPET ()

Call GetRecordCound to get the total number of records in the recordset, which is declared as:

Long GetRecordCount () const

Note that this function actually returns the furthest distance the user scrolls through the recordset. To really return the total number of records, you have to call MoveNext to move to the end of the recordset (not MoveLast).

5. Modify, add, and delete records

To modify the current record, follow these steps:

Call Edit member function. After calling this function, you enter edit mode, and the program can modify domain data members. Be careful not to call Edit in an empty recordset, otherwise an exception will be generated.

The Edit function saves the contents of the current domain data members in a buffer for two purposes: one is to compare with the domain data members to determine which fields have been changed, and the other is to restore the original values of the domain data members if necessary. If Edit is called again, the domain data members will be restored from the buffer The program is still in edit mode after calling. Call Move (AFX_MOVE_REFRESH) or Move (0) to exit edit mode (the value of AFX_MOVE_REFRESH is 0), and this function will recover the domain data members from the buffer.

Sets the new value of the domain data member.

Call Update to finish editing. Update writes the changed record to the data source and ends the editing mode.

To add a new record to the recordset, follow these steps:

Call the AddNew member function. After calling this function, you enter add mode, which sets all domain data members to NULL (note that in database terminology, NULL means no value, which is different from C++ 's NULL). Like Edit, AddNew stores the contents of the current domain data members in a buffer, if necessary The program can call AddNew again to cancel the add operation and restore the original value of the domain data member, and the program is still in the add mode after the call. Call Move (AFX_MOVE_REFRESH) to exit the add mode, and the function will restore the domain data member from the buffer.

Set domain data members.

Call Update.Update to write the contents of the domain data member to the data source as a new record, thus ending the addition.

If the recordset is a snapshot, then after adding a new record, you need to call Requery to re-query, because the snapshot does not reflect the add operation.

To delete the current record of a recordset, follow these two steps:

Call the Delete member function. This function will delete the current record in both the recordset and the data source. Be careful not to call Delete in an empty recordset, or an exception will be generated.

Scroll to another record to skip deleting the record.

The function mentioned above is declared as:

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 with an exception.

Before making changes to the recordset, the program may call the following functions to determine whether the recordset can be changed, because modifying, adding, or deleting in an immutable recordset will result in an exception.

BOOL CanUpdate () const; / / returns TRUE indicating that records can be modified, added, and deleted.

BOOL CanAppend () const; / / returns TRUE to indicate that records can be added.

This is the answer to the question about how to use the CRecordset class to operate the database in ODBC. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.