Original reference: ADO interface_RecordsetPtr-CSDN blog
_RecordsetPtr smart pointer, which is a pointer specially set up for operating the database through the recordset. Through this interface, various operations can be performed on the records, fields, etc. in the database table.
To be clear: the database and the ADO record set are two different concepts, two storage spaces that exist in different physical locations.
A recordset is equivalent to a copy of the actual data. Precisely because the record set exists relatively independently of the database, there are two problems, the cursor type and the lock type involved in the Open method that will be introduced later.
_RecordsetPtrHow to use the interface
1> Create a recordset object
_ConnectionPtr m_pRecordset; m_pRecordset.CreateInstance(__uuidof(Recorset)); ?
Create a recordset object, just allocate memory space for it, the recordset does not contain any data.
2> Open the record set
The recordset object is used to obtain data in the database and operate on it, so it is necessary to open the recordset and obtain data records from the database. There are many ways to open a recordset, such as using _CommondPt::.Execute() to return a recordset, or using the _Recordset::Open() method to open a recordset;
Only the Open() method is introduced below:
The function prototype is:
HRESULT Recordset::Open(const _variant_t & amp; Source,const _variant_t & amp; ActiveConnection,enum CursorTypeEnum CursorType,enum LockTypeEnum LockType,long Options)
parameter:
Source is the data query string;
ActiveConnection is an established connection (we need to use the Connection object pointer to construct a _variant_t object);
CursorType Cursor type, which is a value in the CursorTypeEnum enumeration;
LockType lock type It is a value in the enumeration LockTypeEnum;
Options specifies the type of Source;
Cursor type CursorType, which can take one of the following values:
adOpenUnspecified=-1 No special specification
adOpenForwardOnly=0 Default value, scroll forward static cursor. (This kind of cursor can only browse the record set forward, such as using MoveNext to scroll forward. This method can save resources and improve browsing speed, but such things as BookMark, RecordCount, AbsolutePosition, and AbsolutePage cannot be used.)
adOpenKeyset=1 key set cursor, the record set using this cursor cannot see the addition and deletion operations of other users, but the operation of updating the original records is visible to you.
adOpenDynamic=2 dynamic cursor, all database operations will be immediately reflected on the user record set.
adOpenStatic=3 static cursor. It generates a static backup for records, and other users’ addition, deletion, and update operations are invisible to your record set.
LockType lock type, it can be one of the following values, please see the following enumeration structure
enum LockTypeEnum
{
adLockUnspecified=-1,
//not specified
adLockReadOnly=1,
//Read-only recordset, default value. Data cannot be changed.
adLockPessimistic=2,
//Pessimistic locking mode. The record is only locked when the Update method is called. This is the most secure locking mechanism
adLockOptimistc=3,
//Optimistic locking method, only lock the record when you call the Update method.
adLockBatchOptimistic=4
//Optimistic batch update. Records are not locked while editing, and changes, insertions, and deletions are done in batch mode.
}
Regarding the cursor and lock types, for general users, it is recommended that you only have a brief understanding, which will be further explained later.
Options can take one of the following values:
adCmdText: Indicates that CommandText is a text command.
adCmdTable: Indicates that CommandText is a table name.
adCmdProc: Indicates that CommandText is a stored procedure.
adCmdUnknown:Unknown.
For example: assuming m_pConnection is the connection we have established, we use the Open method of the _RecordsetPtr interface to open the recordset of the Employees table as follows:
m_pRecordset->Open("SELECT * FROM Employees", _variant_t((IDispatch*)m_pConnection,true), adOpenStatic, adLockOptimistic, adCmdText);
3> Pointer movement
The moving record pointer can be moved to the first record through the MoveFirst() method, the MoveLast() method to the last record, the MovePrevious() method to move to the previous record of the current record, and the MoveNext() method to move to the next record of the current record. Record. You can also use Move (record number) to move the record pointer to the desired location.
Note: The Move() method moves the pointer position relative to the current record. Positive values move backward and negative values move forward. For example, Move(3), when the current record is 3, it will move 3 record positions starting from record 3.
Two _RecordsetPtr properties:
The BOF attribute represents the cursor before the first record of the recordset and is used to determine whether the recordset is empty.
The EOF (usually renamed to adoEOF) attribute represents the cursor after the last record in the recordset and is used to determine whether it is ended.
4> Reading of data
GetCollect(field name) to get the field value pointed to by the current record pointer
The return value type is: _variant_t
5> Data update
Use PutCollect(field name, value) to write the new value and Update() to update it to the database
6> Insert record
First move the record pointer to the location to be inserted, use AddNew() to insert a record, then use PutCollect (field name, value) to write the value, and finally use Update() to update to the database;
7> Delete records
Delete a single record: first move the record pointer to the appropriate position, then use the Delete() method to delete it (take the parameter adAffectCurrent, indicating that the operation only affects the current record), and use Update() to update the database;
8> Close the record set
Directly use the Close() method to close the recordset and assign it a null value. code show as below:
m_pRecordset->Close();
m_pRecordset=NULL;
Close method similar to _ConnectionPtr;
2. Example:
The routine RecordsetPtr demonstrates the use of the _RecordsetPtr pointer to operate the database through the recordset.
Open VC++ 6.0 and create a new dialog-based project RecordsetPtr. Edit in the dialog IDD_RECORDSETPTR_DIALOG:
Three Group Boxes are used to divide it into four parts. The first part demonstrates how to read database data; the second part demonstrates how to modify the database; the third part demonstrates how to insert data into the database; and the fourth part demonstrates how to delete data in the database.
Use ClassWizard to create the CListBox variable m_list1 for the list box IDC_LIST1:
1. Double-click the IDC_BTN_READREC button and edit the OnBtnReadrec() function as follows:
void CRecordsetPtrDlg::OnBtnReadrec()
{
_ConnectionPtr m_pConnection;
_RecordsetPtr m_pRecordset;
try
{
m_pConnection.CreateInstance(__uuidof(Connection));
m_pConnection->Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Northwind.mdb","","",adModeUnknown);
}
catch(_com_error e)
{
CString errormessage;
errormessage.Format("Failed to connect to database!/rError message: %s",e.ErrorMessage());
AfxMessageBox(errormessage);
return;
}
try
{
m_pRecordset.CreateInstance("ADODB.Recordset");
m_pRecordset->Open("SELECT EmployeeID,FirstName,LastName FROM Employees WHERE City='London'", _variant_t((IDispatch*)m_pConnection,true), adOpenStatic, adLockOptimistic, adCmdText);
}
catch(_com_error &e)
{
AfxMessageBox(e.Description());
}
_variant_t vEmployeeID,vFirstName,vLastName;
try
{
while(!m_pRecordset->adoEOF)
{
vEmployeeID=m_pRecordset->GetCollect(_variant_t((long)0));
//Get the value of column 1 and start counting from 0. You can also list the column names directly, as in the following line
vFirstName=m_pRecordset->GetCollect("FirstName");
vLastName=m_pRecordset->GetCollect("LastName");
CString strtemp;
if(vEmployeeID.vt!=VT_NULL)
{
strtemp.Format("%d",vEmployeeID.lVal);
}
if(vFirstName.vt!=VT_NULL)
{
strtemp + =" ";
strtemp + =(LPCTSTR)(_bstr_t)vFirstName;
}
if(vLastName.vt!=VT_NULL)
{
strtemp + =" ";
strtemp + =(LPCTSTR)(_bstr_t)vLastName;
}
m_list1.AddString(strtemp);
m_list1.AddString("/n");
m_pRecordset->MoveNext();
}
}
catch(_com_error &e)
{
AfxMessageBox(e.Description());
}
//Release resources
m_pRecordset->Close();
m_pRecordset=NULL;
m_pConnection->Close();
m_pConnection=NULL;
}
This code demonstrates how to read data from a database table. The principle is that if the table end flag adoEOF is not encountered, use GetCollect (field name) to obtain the field value pointed to by the current record pointer, and then use the MoveNext() method to move to the next record position.
2. Double-click the IDC_BTN_CHANGE button and edit the OnBtnChange() function as follows:
void CRecordsetPtrDlg::OnBtnChange()
{
<span style="white-space:pre"> </span>...
try
{
m_pRecordset.CreateInstance("ADODB.Recordset");
m_pRecordset->Open("SELECT EmployeeID,FirstName,LastName FROM Employees WHERE (City='London') AND (EmployeeID=6)",
_variant_t((IDispatch*)m_pConnection,true), adOpenStatic, adLockOptimistic, adCmdText);
}
catch(_com_error &e)
{
AfxMessageBox(e.Description());
}
try
{
while(!m_pRecordset->adoEOF)
{
//Change record value
m_pRecordset->PutCollect("LastName",_variant_t("Jackson"));
m_pRecordset->MoveNext();
}
//Refresh to hard disk
m_pRecordset->Update();
}
catch(_com_error* e)
{
AfxMessageBox(e->ErrorMessage());
}
<span style="white-space:pre"> </span>...
}
This code demonstrates how to modify a field value in a record:
Move the record pointer to the location where the record is to be modified, directly use PutCollect (field name, value) to write the new value, and Update() to update it to the database.
3. Double-click the IDC_BTN_NEW button and edit the OnBtnNew() function as follows:
void CRecordsetPtrDlg::OnBtnNew()
{
<span style="white-space:pre"> </span>...
try
{
m_pRecordset.CreateInstance("ADODB.Recordset");
m_pRecordset->Open("SELECT * FROM Employees", _variant_t((IDispatch*)m_pConnection,true), adOpenStatic, adLockOptimistic, adCmdText);
}
catch(_com_error &e)
{
AfxMessageBox(e.Description());
}
try
{
m_pRecordset->MoveLast();
//Insert record
m_pRecordset->AddNew();
//Data input
m_pRecordset->PutCollect("EmployeeID",_variant_t((long)10));
m_pRecordset->PutCollect("FirstName",_variant_t("Mary"));
m_pRecordset->PutCollect("LastName",_variant_t("Williams"));
}
catch(_com_error *e)
{
AfxMessageBox(e->ErrorMessage());
}
//Update to external storage
m_pRecordset->Update();
...
}
This code demonstrates how to insert records:
First use the AddNew() method to add an empty record, then use PutCollect (field name, value) to enter the value of each field, and finally use Update() to update it to the database.
4. Double-click the IDC_BTN_DELETE button and edit the OnBtnDelete() function as follows:
void CRecordsetPtrDlg::OnBtnDelete()
{
...
try
{
m_pRecordset.CreateInstance("ADODB.Recordset");
m_pRecordset->Open("SELECT * FROM Employees", _variant_t((IDispatch*)m_pConnection,true), adOpenStatic, adLockOptimistic, adCmdText);
}
catch(_com_error &e)
{
AfxMessageBox(e.Description());
}
try
{
//Suppose the 10th record is deleted
m_pRecordset->MoveFirst();
m_pRecordset->Move(9);
//The parameter adAffectCurrent is to delete the current record
m_pRecordset->Delete(<span style="background-color: rgb(204, 204, 204);">adAffectCurrent</span>);
//Update to database
m_pRecordset->Update();
}
catch(_com_error *e)
{
AfxMessageBox(e->ErrorMessage());
}
...
}
This code demonstrates how to delete records:
First move the record pointer to the location of the record to be deleted, directly use the Delete() method to delete it, and use Update() to update the database.