with
ADO
to manipulate the database method steps
some experience in learning ADO-Technology Achievement Dream-51CTO technology blog http://freetoskey.blog.51cto.com/1355382/989218
ADO
Introduction to InterfacesThe ADO library consists of three basic interfaces: The _connectionptr interface, the _commandptr interface, and the _RecordsetPtr interface. The _connectionptr interface returns a recordset or a null pointer. It is typically used to create a data connection or to execute an SQL statement that does not return any results, such as a stored procedure. Using the _connectionptr interface to return a recordset is not a good way to use it. Typically, like CDatabase, you use it to create a data connection and then use other objects to perform data-entry and output operations. The _commandptr interface returns a recordset. It provides a simple way to execute stored procedures and SQL statements that return a recordset. When using the _commandptr interface, you can take advantage of the global _connectionptr interface, or you can use the connection string directly in the _commandptr interface. If you only perform one or several data access operations, the latter is a good choice. But if you want to access the database frequently and return many recordsets, you should use the global _connectionptr interface to create a data connection and then execute the stored procedure and SQL statement using the _commandptr interface. _RecordsetPtr is a Recordset object. Compared with the above two objects, it provides more control functions to the recordset, such as record lock, cursor control, etc. As with the _commandptr interface, it is not necessarily necessary to use a data connection that has already been created, and you can use a connection string instead of a connection pointer to assign the connection member variable to the _RECORDSETPTR and let it create the data connection itself. If you are using more than one Recordset, the best approach is to use the global _connectionptr interface that already created the data connection with the Command object, and then execute the stored procedure and SQL statement using _RECORDSETPTR.
Basic Process(1) Initialize the COM library, introduce the ADO library definition file (2) Connect the database with the Connection object (3) Use the established connection, execute the SQL command through the connection, command object, or use the Recordset object to get the result recordset to query, Processing. (4) Close the connection release object after the use is complete.
"
1
"
COM
initialization of the libraryWe can use AfxOleInit () to initialize the COM library, which is usually done in the overloaded function of CWinApp::InitInstance (), see the following code: BOOL cadotest1app::initinstance () { AfxOleInit (); ......}
"
2
"with
#import
Instruction Introduction
ADO
type libraryWe add the following statement to the stdafx.h: #import "c:\program Files\Common Files\system\ado\msado15.dll" No_namespace rename ("EOF", "adoeof ") Its final function is similar to our familiar # include, when compiling the system will generate Msado15.tlh,ado15.tli two C + + header files for us to define the ADO library.
"
3
the Create
Connection
object and connect to the databaseFirst we need to add a pointer to the connection object: _connectionptr m_pconnection; bool cadotest1dlg::oninitdialog () { CDialog::OnInitDialog (); try { hresult hr = M_pconnection.createinstance ("ADODB. Connection ");//Create Connection Object if (SUCCEEDED (hr)) { hr = M_pconnection->open (" provider= Microsoft.jet.oledb.4.0;data Source=test.mdb "," "," ", admodeunknown);//Connection database // The Provider in the concatenated string in the above sentence is for the ACCESS2000 environment, and for ACCESS97, it needs to be changed to: provider=microsoft.jet.oledb.3.51; } } } catch (_com_error e)//com error, when performing COM functions, if there is an error, you can catch the _com_error exception { cstring Strcomerror; strcomerror.format ("Error number:%08lx\n error message:%s\n Error Source:%s\n Error Description:%s", e.Error (), // Error number e.errormessage (), // Error message (LPCSTR) e.source (), // error Source (LPCSTR) e.description ()); // error description :: The MessageBox (null,strcomerror, "error", Mb_iconexclamation); }} can also be used to connect using UDL files. Try{ m_pconnection.createinstance (__uuidof (Connection)); m_pconnection-> ConnectionString = "File name=e.udl"; m_pconnection->open ("", "", "", NULL);} catch (_com_error e) {...} In this code we are connected to the database by the Open method of the Connection object, the following is the prototype of the method hresult Connection15::open (_bstr_t ConnectionString, _bstr_t UserID, _bstr_t Password, long Options) connectionstring for connection string,userid is the username, password is the login password,options is the connection option that specifies the connection object's update permission to the data, options can be the following constants: admodeunknown: default. Current permissions not set adModeRead: Read Only adModeWrite: write admodereadwrite: can read and write admodesharedenyread: Prevents other connection objects from opening the connection admodesharedenywrite with Read permissions: Prevent other connection objects from opening connections with Write permissions admodeshareexclusive: Prevent other connection objects from opening the connection admodesharedenynone: Allow other programs or objects to establish connections with any permissions common database connection Methods: (1) connection to ACCESS2000 database through the Jet database engine M_pconnection->open ("Provider =microsoft.jet.oledb.4.0;data Source=c:\\test.mdb "," "," ", adModeUnknown); (2) Connect to any ODBC-enabled database through the DSN data source: m_ Pconnection->open ("Data source=adotest; Uid=sa; Pwd=, "," "," "," adModeUnknown ";//m_pconnection->open (" Dsn=test; "," "," ", 0); //Connect an ODBC data source called Test (3) Do not connect to the SQL Server database through DSN: M_pconnection->open ("Driver={sqlserver}; Server=127.0.0.1;database=vckbase; Uid=sa; pwd=139 "," "," ", admodeunknown);//where Server is the name of the SQL Server, database is the name of the library This first describes the two useful properties of the connection object ConnectionTimeout and Stateconnectiontimeout to set the time-out for the connection, which needs to be called before Open, for example: m_ Pconnection->connectiontimeout = 5; Set the timeout time to 5 seconds m_pconnection->open ("Data source=adotest;", "", "", adModeUnknown); The State property indicates that the current CONnection the state of the object, 0 means close, 1 means it is open, and we can read this property to do the corresponding processing, for example: if (m_pconnection->state) m_pconnection->close (); Close the connection if it is already open
4
the execution
SQL
command and get the result record setTo get the result recordset, we define a pointer to the Recordset object: _RecordsetPtr M_precordset; and creates an instance of the Recordset object for it: M_precordset.createinstan CE ("ADODB. Recordset "); The execution of SQL commands can take many forms, as described below.
◆
(1)
Use
Connection
object that
Execute
Method Execution
SQL
CommandThe prototype of the Execute method is as follows: _RecordsetPtr Connection15::execute (_bstr_t CommandText, VARIANT * RecordsAffected, Long Options) where & nbsp CommandText is a command string, usually a SQL command. RecordsAffected is the number of rows affected by the completion of the operation, options represents the type of content in the CommandText, and 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 execute Returns a pointer to the recordset after execution, we give the specific code and describe it. try{ _variant_t Ra; m_pconnection->execute ("CREATE TABLE" Student Information (study number integer, Name TEXT, age integer , birthday DATETIME) ", &ra,adcmdtext); m_pconnection->execute (" INSERT into student information (school number, name, age, Birthday) VALUES (112105, ' Cheng ', ' 1982-08-16 ') ", &ra,adcmdtext); m_precordset = M_pconnection->execute (" Select COUNT (*) from Student Information ", &ra,adcmdtext); / _variant_t Vcount= M_precordset->getcollect ((_variant_t) (long) (0)); Get the value of the first field into the Vcount variable m_precordset->close (); cstring message; message. Format ("Total%d records", Vcount.lval); afxmessagebox (message); } catch (_com_error e) {...}
◆
(2)
Use
Command
Object
to perform
SQL
Commandtry {_commandptr M_pcommand; M_pcommand.createinstance ("Adodb.command"); M_pcommand->activeconnection = m_pconnection; The key sentence is to assign the established connection to it m_pcommand->commandtext= "INSERT into student information (school number, name, age, Birthday) VALUES (112105, ' Cheng ', 22, ' 1982-08-16 ')" ; M_pcommand->execute (Null,null,adcmdtext); m_pcommand->commandtext= "Select COUNT (*) from student information"; M_precordset=m_pcommand->execute (Null,null,adcmdtext); _variant_t Vcount = M_precordset->getcollect ((_variant_t) (long) 0); Gets the value of the first field CString str; Str. Format ("%d records in total", vcount.lval); AfxMessageBox (str); M_precordset->close (); } catch (_com_error e) {...} In this code we just use the command object to execute the SELECT query statement, and the command object can really reflect its role in the call to the stored procedure. Next time we'll cover it in detail.
◆
(3)
directly with
Recordset
object to query get recordset For example m_precordset->open ("Select*from Student Information", _variant_t (IDispatch *) m_pconnection,true), adOpenStatic, Adlockoptimistic,adcmdtext); The prototype of the Open method is this: HRESULT recordset15::open (const _variant_t & source, Const _ variant_t & ActiveConnection, enum CursorTypeEnum CursorType, enum LockTypeEnum LockType, Long Options) Where: ①sou RCE is a data query string ②activeconnection is an already established connection (we need to construct a _variant_t object with a Connection object pointer) ③cursortype cursor type, It can be one of the following values, see this enumeration structure: enum cursortypeenum { adopenunspecified =-1,//no special designation & nbsp; adopenforwardonly = 0,//roll forward static cursor. This cursor can only move forward through the recordset, such as scrolling forward with MoveNext, this way to improve the browsing speed. However, such as bookmark,recordcount,absoluteposition,absolutepage can not be used adOpenKeyset = 1, // The recordset with this cursor does not see new and deleted actions from other users, but it is visible to you to update the original record. adopendynamic = 2, //dynamic cursor. All database operations are immediately reflected on the user record set. adOpenStatic = 3 //static cursor. It produces a static backup of your recordset, but the new, deleted, and updated actions of other users are not visible to your recordset. }; ④locktype LockType, which can be one of the following values, see the following enumeration structure: enum locktypeenum { adlockunspecified = -1, // Unspecified adlockreadonly = 1, //read-only recordset adlockpessimistic = 2, //pessimistic locking mode. Data locks all other actions on update, which is the safest locking mechanism adLockOptimistic = 3, //optimistic locking mode. The record is locked only when you call the Update method. Before this can still do data update, INSERT, delete and other actions adLockBatchOptimistic = 4,//Optimistic batch update. Records are not locked when editing, and changes, insertions, and deletions are done in batch mode. }; ⑤options refer to the introduction to the Execute method of the Connection object in this article
"
5
"Recordset traversal, updateAccording to the Student information table we just established by executing the SQL command, it contains four fields: School number, name, age, Birthday code implementation: Open the Recordset, traverse all records, delete the first record, add three records, move the cursor to the second record, change its age, save to the database. try { _variant_ T vusername,vbirthday,vid,vold; _recordsetptr m_pRecordset ; m_precordset.createinstance ("ADODB. Recordset "), m_precordset->open (" Select*from Student Information ", _variant_t (idispatch*) m_pconnection,true), Adopenstatic,adlockoptimistic,adcmdtext) while (!m_precordset->adoeof) { VID = m_ Precordset->getcollect (_variant_t ((long) 0)); //Get the value of column 1th, starting with 0, you can also give the name of the dequeue directly, such as the next line vUserName = m_precordset->getcollect ("name"), //gets the value of the name field vOld = M_precordset->getcollect ("Age"); Vbirthday = M_precordset->getcollect ("Birthday"); TRACE ("id:%d, Name:%s, age:% D, Birthday:%s\r\n ", vid.lval, (LPCTSTR) (_bstr_t) vusername, vold.lval, (LPCTSTR) (_bstr_t) vbirthday) //the OU in debug modeTput window output record set M_precordset->movenext (); //move to next record } m_ Precordset->movefirst (); //move to First record m_precordset->delete (adaffectcurrent); //Delete the current record for (int i=0;i<3;i++) //Add three new records and assign a value { m_precordset->addnew (); //Add new records M_precordset->putcollect ("study Number", _ variant_t ((Long) (I+10))); m_precordset->putcollect ("name", _variant_t ("Wang Bin Year")); m_ Precordset->putcollect ("Age", _variant_t (Long)); 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, i.e. move to the second record at m_precordset->putcollect (_variant_t ("Age"), _variant_t ((long)); //Modify its Age m_precordset->update (); //Save to library } catch (_com_error e) {}
"6
"Close Recordset and connectionThe recordset or connection can be closed m_precordset->close () using the Close method; Close record set m_pconnection->close (); Close connection macro definition in stdafx.h: #if!defined catch_error#define catch_error \ { \ CString strcomerror; \ strcomerror.format ("Error number:%08lx\n error message :%s\n Error Source:%s\n Error Description:%s ", \ e.error (), \ E.errormessage (), \ (LPCSTR) e.source (), \ (LPCSTR) e.description ()); \ :: MessageBox (Null,strcomerror, "error", mb_iconexclamation); \ } #endif使用方法: try{ ..} catch (_com_error e) { catch_error;}
How to use ADO to manipulate the database steps
