First, the implementation method
Everything is difficult at the beginning. The most important thing for beginners of any new technology is to "get started" and master its main points. Let's take a look at the basic process of ADO database development! The basic steps are as follows:
(1) Initializes the COM library and introduces the ADO library definition file.
(2) Connect the database with the connection object.
(3) Using the established Connection, execute SQL commands through the connection and command objects, or use the Recordset object to obtain the result record set for query and processing.
(4) Close the connection and release the object after use.
Below we will introduce the above steps in detail and give the relevant code.
1 and initialization of COM library
We can use AfxOleInit () to initialize the COM library, which is usually done in the overloaded function of CWinApp::InitInstance (). Please look at the following code:
BOOL cado test 1 app::init instance()
{
AfxOleInit()；
......
}
2. Introduce ADO type library with #import instruction.
In order to introduce the ADO type library, you need to add the following statement to the stdafx.h file of the project:
#import "c: program file public file system adomsado 15.dll "
no_namespace rename("EOF "，" adoEOF ")
What does this sentence do? Its final function is similar to the familiar #include. At compile time, the system will generate two C++ header files, MSADO 15. TLH and ADO 15. TLI to define ADO libraries.
Readers should pay attention to the following points: msado 15.dll in your development environment is not necessarily in this directory, please modify it according to the actual situation; During compilation, the following warning may appear. Microsoft explained this in MSDN and suggested that we ignore this warning: MSADO 15. TLH (405): Warning C4 146: The unary subtraction operator is applied to unsigned types, and the result is still unsigned.
3. Create a connection object and connect to the database.
To first add pointer _ connectionptrm _ pconnection to the connection object, the following code demonstrates how to create an instance of the connection object and how to connect to the database and catch an exception:
BOOL cado test 1 DLG::OnInitDialog()
{
CDialog::OnInitDialog()；
HRESULT hr
attempt
{
Hr = m _ connection. CreateInstance("ADODB。 Connection "); ///Create a connection object
If (Success (hours))
{
HR = m _ p connection-Open(" Provider = Microsoft。 Jet . OLEDB.4.0
Data source =test.mdb ","","",admodeunknown); ///Connect to the database
//The provider in the connection string in the above sentence is for the ACCESS2000 environment, while for ACCESS97,
//needs to be changed to: provider = Microsoft. jet . oledb . 3.5 1；
}
}
Catch(_ com _ errore)////catch an exception.
{
CString errormessage
Error message. Format ("Failed to connect to the database! Error message: %s ",e.errormessage ());
AfxMessageBox(error message)； ///Display an error message
}
In this code, we connect to the database through the Open method of the Connection object. The following is the prototype of this method:
Hresult connection15:: open (_ bstr _ tconnectionstring, _ bstr _ tuseid, _bstr_t Password, long option);
The parameter ConnectionString in the above function is the connection string; Parameter UserID is the user name; Parameter Password is the login password; The parameter Options is a connection option, which is used to specify the permission of the connection object to update data. Typically, the options can be the following constants:
AdModeUnknown: default. The current permission is not set.
AdModeRead: read-only
AdModeWrite: write only.
AdModeReadWrite: you can read and write.
AdModeShareDenyRead: Prevents other connection objects from opening connections with read permission.
AdModeShareDenyWrite: Prevents other connection objects from opening connections with write permission.
AdModeShareExclusive: Prevents other connection objects from opening connections with read and write permissions.
AdModeShareDenyNone: Prevents other connection objects from opening connections with any permissions.
We give some common connection methods for your reference:
(1) Connect to the ACCESS2000 database through the JET database engine;
m _ p connection-Open(" Provider = Microsoft。 Jet . OLEDB.4.0
Data source =C:est.mdb ","","",admodeunknown);
(2) Connect any database supporting ODBC through DSN data source:
m _ p connection-Open(" Data Source = ado test； UID = saPWD =； ”，""，""，admode unknown)；
(3) Connect to the SQL SERVER database without DSN:
m _ p connection-Open(" driver = { SQL Server }； Server =127.0.0.1; DATABASE = vckbase
UID = saPWD= 139 "，""，""，admode unknown)；
Where Server is the name of the SQL server and DATABASE is the name of the library.
In addition to the Open () method, there are many methods in the Connection object. Let's first introduce two useful properties in the connection object, ConnectionTimeOut and State. ConnectionTimeOut is used to set the timeout of the connection and needs to be called before opening, for example:
m _ p connection-connection time out = 5； ///Set the timeout to 5 seconds.
m _ p connection-Open(" Data Source = adotest；" ，""，""，admode unknown)；
The State property indicates the state of the currently connected object, 0 means closed, and 1 means open. We can read this property to make corresponding processing, such as:
If(m _ p connection status)
m _ p connection-Close()； ///If the connection is already open, please close it.
4. Execute the SQL command and get the result record set.
In order to get the result recordset, we define a pointer to the recordset object: _ RecordsetPtr m _ pRecordset.
And create an instance of the Recordset object for it: m _ p Recordset. Create an instance ("adodb. Recordset "). The execution of SQL commands can take many forms, which we will explain one by one.
(1) Use the Execute method of the Connection object to execute the SQL command.
The prototype of the Execute () method is as follows:
_ RecordsetPtr connection 15::Execute(_ bstr _ t CommandText，VARIANT * RecordsAffected，long Options)
Where CommandText is a command string, usually an SQL command. The parameter RecordsAffected is the number of rows affected after the operation is completed, and the parameter Options indicates the type of content in CommandText. Options can take one of the following values: adCmdText indicates that CommandText is a text command; AdCmdTable indicates that CommandText is the table name; AdCmdProc indicates that CommandText is a stored procedure; AdCmdUnknown means that the CommandText content is unknown. The Execute () function returns a pointer to the recordset after execution. Let's give the specific code and explain:
_variant_t record is affected;
///Execute the SQL command: CREATE TABLE to create a table user, which contains four fields: plastic ID, string user name, plastic old, date and birthday.
M _ p connection-execute ("create table users (id integer, user name.
TEXT，old INTEGER，birthday DATETIME)"，RecordsAffected，ADC mdtext)；
///Add a record to the table.
M _ p connection-execute ("insert into users (ID, user name, old, birthday))
VALUES ( 1，' Washington '，25，' 1970/ 1/ 1 ')"，RecordsAffected，ADC mdtext)；
///Old field values of all records plus 1.
m _ p connection-Execute(" UPDATE users SET old = old+ 1 "，RecordsAffected，ADC mdtext)；
///Execute the SQL statistics command to obtain a record set containing the number of records.
m _ pRecordset = m _ p connection-Execute(" SELECT COUNT(*)FROM
users”，RecordsAffected，ADC mdtext)；
_ variant _ t vin dex =(long)0；
_ variant _ t vCount = m _ pRecordset-get collect(vin dex)； ///Get the value of the first field and put it in the vCount variable.
m _ pRecordset-Close()； ///Close the recordset
CString message;
News. Format ("* * * has %d records", vcount.lval);
AfxMessageBox (message); ///Displays the current number of records. (2) Use the Command object to execute SQL commands.
_ CommandPtr m _ pCommand
m_pCommand。 CreateInstance("ADODB。 Command ");
_ variant _ t vNULL
vNULL.vt = VT _ ERROR
v null . scode = DISP _ E _ param not found； ///is defined as parameterless.
m _ p command-active connection = m _ p connection； ///A key sentence, and assign the established connection to it.
m _ p command-CommandText = " SELECT * FROM users "； ///command string
m _ pRecordset = m _ p command-Execute(vNULL，v null，ADC mdtext)；
//Execute the command to get the recordset.
In this code, we only use the Command object to execute the SELECT query statement, and the Command object can really reflect its role in calling the stored procedure. We will introduce it in detail next time.
(3) directly query the recordset object to obtain the recordset, for example:
m _ pRecordset-Open(" SELECT * FROM users "，_ variant _ t((IDispatch *)m _ p connection，true)，
adOpenStatic，adLockOptimistic，ADC mdtext)；
The prototype of the Open () method is as follows:
HRESULT recordset15:: open (const _ variant _ t
Source, constant _ variable _t
ActiveConnection, enumerating CursorTypeEnum CursorType, enumerating lock types enumerating lock types, long option)
The parameter source in the above function is a data query string; Parameter ActiveConnection is an established connection (we need to construct a _variant_t object with the connection object pointer); Parameter CursorType cursor type, which can be one of the following values. Look at this enumeration structure:
Enumeration CursorTypeEnum
{
AdOpenUnspecified =-1,//not specified.
Adopenforwardly = 0,///Scrolls the static cursor forward. This cursor can only browse the recordset forward, such as scrolling forward with MoveNext, which can improve the browsing speed. But you can't use such things as bookmark, recordcount, absoluteposition and absolutepage.
Adopenkeyset = 1,//The record set with this cursor can't be seen by other users, but you can see the operation of updating the original record.
AdOpenDynamic = 2,///dynamic cursor. All database operations will be immediately reflected in each user recordset.
AdOpenStatic = 3/// static cursor. It makes a static backup of your recordset, but other users' add, delete and update operations are invisible to your recordset.
};
The parameter LockType indicates the lock type of the database and can be one of the following values. See the following enumeration structure:
Enumeration lock type enumeration
{
AdLockUnspecified =-1,//Not specified.
AdLockReadOnly = 1,//read-only recordset
Adlock pessimism = 2, pessimistic locking mode. When the data is updated, all other operations are locked, which is the safest locking mechanism.
AdLockOptimistic = 3, optimistic locking mode. Lock records only when the Update method is called. Until then, you can still update, insert and delete data.
AdLockBatchOptimistic = 4, optimistic batch update. Records are not locked when editing, and changes, insertions and deletions are done in batch mode.
};
For the meaning of parameter options, please refer to the introduction of the Execute () method of the Connection object in this article.
5. Traverse and update the recordset
According to the users table we just established by executing SQL commands, it contains four fields: ID, user name, age and birthday.
Realize the following code: open the record set, traverse all records, delete the first record, add three records, move the cursor to the second record, change its age data and save it to the database.
_variant_t vUsername，vBirthday，vID，vOld
_ RecordsetPtr m _ pRecordset
m_pRecordset。 CreateInstance("ADODB。 Recordset ");
m _ pRecordset-Open(" SELECT * FROM users "，_ variant _ t((IDispatch *)m _ p connection，true)，
adOpenStatic，adLockOptimistic，ADC mdtext)；
And (! m_pRecordset-adoEOF)
///Why is this adoEOF instead of EOF? Remember the words rename(“EOF ","adoEOF ")?
{
vID = m _ pRecordset-get collect(_ variant _ t((long)0))； ///Get the value of 1 column, counting from 0. You can also name the column directly;
vUsername = m _ pRecordset-get collect(" username ")； ///Get the value of the user name field.
vOld = m _ pRecordset-get collect(" old ")；
Vbirthday = m _ p recordset-get collect ("birthday");
///The output window in debug mode outputs the records in the recordset.
if(vID.vt！ = VT_NULL
vUsername.vt！ = VT_NULL
vOld.vt！ = VT_NULL
vBirthday.vt！ = VT_NULL)
TRACE("id:%d, name: %s, age: %d, birthday:% s ",vid. lval，(lpctstr) (_ bstr _ t) vusername，vold。 lval，(lpctstr)(_ bstr _ t)vbirthday)；
m _ pRecordset-MoveNext()； ///Move to the next record
}
m _ pRecordset-move first()； ///Move to the first record
m _ pRecordset-Delete(adAffectCurrent)； ///Delete the current record
///Add three new records and assign values.
for(int I = 0； i3； i++)
{
m _ pRecordset-add new()； ///Add a new record
m_pRecordset-PutCollect("ID "，_ variant _ t((long)(I+ 10)))；
M _ p recordset-putcollect ("user name", _ variant _ t ("Yeltsin"));
m_pRecordset-PutCollect("old "，_ variant _ t((long)7 1))；
M_pRecordset-PutCollect ("birthday", _ variant _ t ("1930-3-15"));
}
m_pRecordset-Move( 1，_ variant _ t((long)adBookmarkFirst))； ///Move down one record from the first record, that is, move to the second record.
m _ pRecordset-put collect(_ variant _ t(" old ")，_ variant _ t((long)45))； ///Modify its age.
m _ pRecordset-Update()； ///Save to the library
6. Close the recordset and connection.
You can close a recordset or connection with the close () method:
m _ pRecordset-Close()； ///Close the recordset
m _ p connection-Close()； ///Close the connection
At this point, I think readers are familiar with the general process of ADO operating database. Maybe you have a plan, maybe you are still a little confused, never mind! I suggest you try to write a few examples, which will make you better familiar with ADO. Finally, I'll write you a small example. The function of this example is to read all records and put them into the list control, and at the same time, you can add, delete and modify records.
Second, the programming steps
1, start Visual C++6.0, generate an application program based on dialog box, and name the program ADOTest1;
2. Put the controls for displaying the record list and editing, and the button controls for adding and deleting records on the dialog interface, and specifically set the dialog resource part in the participation code;
3. Use the class wizard to add a message response function for the button to add or modify database records;
4. Add it to the program code, compile and run the program.