ADO's first intimate contact - One of Ado Development Practice 1. ADO Introduction ADO (ActiveX Data Object) is a new interface developed by Microsoft Database Application, which is a high-level database access technology built on OLE DB, please do not have to be Worried, even if you have the OLE DB, COM doesn't understand, you can easily deal with ADO because it is very easy to use, even more easy to use than the ODBC API, DAO, RDO you have contacted in the past, and does not lose flexibility. This article will detail how to use ADO to develop database applications in VC, and give sample code. Sample code
Second, the basic process is difficult to start, any new technology is the most important thing for beginners or "Getting Started", mastering its points. Let's take a look at the basic procedures developed by the ADO database! (1) Initialize the COM library, introduce the ADO library definition file (2) Connect the database (3) with the Connection object to use the established connection, execute the SQL command via the Connection, Command object, or use the Recordset object to obtain the results record set for querying, processing . (4) Turn off the connection release object after use.
Preparation: For everyone to test the examples provided herein, we use the Access database, you can also find this Test.mdb directly in the sample code we provide. Below we will introduce the above steps and give relevant code. [1] Initialization of the COM library We can use Afxoleinit () to initialize the COM library, which is usually done in the overload function of cwinapp :: initInstance (), please see the following code:
Bool Cadotest1App :: InitInstance () {AFXOLINIT (); ......
[2] To introduce the ADO type library with a #import instruction We add the following statement in stdafx.h: (STDAFX.H Where can this file you can find? You can find it in the Header Files in FileView)
#import "c: / program files / common files / system / ad / msado15.dll" NO_NAMESPACE RENAME ("EOF", "EOF", "ADOEOF") What is the role of this statement? Its ultimate role is similar to our familiar #include. When compiling, the system will generate msado15.tlh, ado15.tli two C header files to define the ADO library.
Some descriptions: (1) Msado15.dll in your environment is not necessarily in this directory, please modify (2) When compiling, it will appear as follows, and the Microsoft is in MSDN. And it is recommended that we don't pay attention to this warning. Msado15.tlh (405): Warning C4146: Unary Minus Operator Applied To Unsigned Type, Resound STILL
[3] Create a Connection object and connect the database First we need to add a pointer to the Connection object: _ConnectionPtr M_PConnection; the following code demonstrates how to create a Connection object instance and how to connect the database and perform an exception capture.
BOOL CADOTest1Dlg :: OnInitDialog () {CDialog :: OnInitDialog (); HRESULT hr; try {hr = m_pConnection.CreateInstance ( "ADODB.Connection"); /// create Connection objects if (SUCCEEDED (hr)) {hr = m_pConnection -> Open ("provider = microsoft.jet.Oledb.4.0; data source = test.mdb", ",", ", admodeunknown); //// Connect the database // The top of the connection string in the connection string is For Access2000 environments, for Access97, it is necessary to change to: provider = microsoft.jet.Oledb.3.51;}} catch (_COM_ERROR E) /// capture exception {cString ErrorMessage; errorMAGE.Format ("Connection database failed! / R / n Error Information:% S ", E.ErrorMessage (); AFXMessageBox; /// Display Error Information} In this code we are connecting the database through the open method of the Connection object, the following is the method Prototype HRESULT Connection15 :: Open (_bstr_t userid, _bstr_t password, long option "Connectionstring is a connection string, userid is a username, Password is a login password, Options is a connection option, which is used to specify the Connection object to update the data. Permissions, Options can be as follows: AdmodeunkNown: Default. The current license is not adModeRead settings: read-only adModeWrite: write only adModeReadWrite: can read and write adModeShareDenyRead: Connection object to prevent other read permissions to open a connection adModeShareDenyWrite: Connection object to prevent other write permissions to open a connection adModeShareExclusive: Connection object to prevent other read and write Permissions Open Admodesharedenynynone: Block Other Connection objects from open in any permissions
We give some commonly used ways to provide you with reference: (1) Connection to Access2000 database via Jet database engine
m_pConnection-> Open ("provider = microsoft.jet.Oledb.4.0; data source = c: //test.mdb", ",", admodeunknown);
(2) Connect any database that supports ODBC through the DSN data source:
M_PConnection-> Open ("Data Source = adotest; UID = SA; PWD =;", "", "", Admodeunknown);
(3) Connection to the SQL Server database without DSN: m_pconnection-> open ("driver = {SQL Server}; 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.
In addition to the Open method, we will first introduce two useful attributes in the Connection object ConnectionTimeout and StateConnectionTimeout to set the timeout time, you need to call before Open, for example: m_pConnection-> ConnectionTIMEOUT = 5; // / Set the timeout time of 5 second m_pConnection-> Open ("Data Source = adotest;", ",", "" "" ",", ",", ",", ",", ",", Read this property to work accordingly, for example:
IF (m_pconnection-> state) m_pconnection-> close (); // If the connection has been opened, close it
[4] Execute the SQL command and acquire the result record set to obtain the result record set, we define a pointer to the Recordset object: _RecordSetPtr m_precordset; and create an instance of the Recordset object for it: m_precordset.createInstance ("adodb.recordset"); SQL The execution of the command can be used in a variety of forms, and we will explain it.
(1) using the Connection object's Execute method executes the SQL command prototype Execute method is as follows: _RecordsetPtr Connection15 :: Execute (_bstr_t CommandText, VARIANT * RecordsAffected, long Options) wherein CommandText is a command string, usually SQL commands. Parameter recordsaffected is the number of rows affected after the operation is completed. The parameter Options represents the type of content in CommandText. Options can take one of the values: AdcmdText: Indications CommandText is a text command adcmdtable: indicate that CommandText is a table name AdcmdProc: indicating that CommandText is a table name AdcmdProc: Store procedure adcmdunknown: unknown
EXECUTE is executed, return a pointer to the record set, below we give specific code and instructions. _variant_t RecordsAffected; /// execute SQL commands: CREATE TABLE create table users, users contains four fields: plastic ID, string username, plastic old, date-type birthday m_pConnection-> Execute ( "CREATE TABLE users (ID INTEGER, username TEXT , old INTEGER, birthday DATETIME) ", & RecordsAffected, adCmdText); /// Add a record to a table inside m_pConnection-> Execute (" INSERT iNTO users (ID, username, old, birthday) VALUES (1, 'Washington', 25, '1970/1/1') ", & recordsaffected, adcmdtext); // /" d onn n ion> ("Update Users Set Old = OLD 1", & Recordsaffected, AdcmdText); // / execute SQL command to get statistical record set comprising a number of records m_pRecordset = m_pConnection-> execute ( "SELECT COUNT (*) FROM users", & RecordsAffected, adCmdText); _variant_t vIndex = (long) 0; _variant_t vCount = m_pRecordset-> GetCollect (vindex); /// get the value of the first field is placed in a vcount variable m_precordset-> close (); /// Close record set cstring message; message.format ("a total of% D record", vcount.lval) AFXMessageBox (Message); /// Displays the current record number (2) Use the Command object to execute the SQL command
_CommandPtr m_pCommand; m_pCommand.CreateInstance ( "ADODB.Command"); _variant_t vNULL; vNULL.vt = VT_ERROR; vNULL.scode = DISP_E_PARAMNOTFOUND; /// no parameters defined m_pCommand-> ActiveConnection = m_pConnection; /// a critical , The established connection assignments to it m_pcommand-> commandtext = "select * from users"; /// command string m_precordset = m_pcommand-> execute (& vnull, & vnull, adcmdtext); // / execution command, acquisition record set
In this code, we just use the Command object to perform the SELECT query statement, and the Command object can really reflect its role in the call to the stored procedure. Next time we will introduce it.
(3) Searching directly with the Recordset object, for example
M_PRecordset-> Open ("Select * from users", _ variant_t (idispatch *) m_pconnection, true), adopenStatic, AdlockOptimistic, AdcmdText);
Open prototype method is such that: HRESULT Recordset15 :: Open (const _variant_t & Source, const _variant_t & ActiveConnection, enum CursorTypeEnum CursorType, enum LockTypeEnum LockType, long Options) wherein: ①Source query string data is already established connections ②ActiveConnection (We need to construct a _variant_t object) 3CURSORTYPE cursor type, which can be one of the following values, please see this enumeration structure: enum cursortypeenum {adopenunSpecified = -1, /// Do not specify adopenforwardonly = 0 , // prior rolling static cursor. This cursor can only browse the record in front, such as scrolling forward with MoveNext, this way can improve the browsing speed. But such as Bookmark, RecordCount, AbsolutePosition, AbsolutePosition, AbsolutePage can't use AdopenKeyset = 1, /////> Records that use this cursor can not see new, delete operations, but for updating the original records, you are visible to you of. AdoPENDYNAMIC = 2, // / Dynamic Cursor. The operation of all databases will be immediately reacted on each user recordset. AdopenStatic = 3 /// Static cursor. It produces a static backup for your recordset, but the new, delete, and update operations of other users are invisible to your record set. }; 4LockType lock type, it can be one of the following values, please see the following enumeration: enum locktypeenum {adlockunSpecified = -1, /// Non-specified AdlockReadOnly = 1, /// read-only record set AdlockPESSIMISTIC = 2, pessimistic Locking method. Data locks all other actions at the time of update, this is the safest lock mechanism AdlockOptimistic = 3, optimistic locking mode. Lock the record only when you call the UPDATE method. You can still do data update, insert, delete, etc. before this, AdlockBatchOptimistic = 4, optimistic batch update. When editing, the record does not lock, change, insert, and delete it is done in batch mode. }; 5Options Please refer to the introduction of the EXECUTE method of the Connection object [5] record set in this article, the update is built according to the use of the SQL command, which contains four fields: ID, Username, Old, BIRTHDAY The following code implementation: Open the record set, traverse all records, delete the first record, add three records, move the cursor to the second record, change its age, save 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_pConnection, true), adOpenStatic, adLockOptimistic, Adcmdtext); While (! m_precordset-> adoEOf) // This is AdoEOF instead of EOF? Remember Rename ("EOF", "AdoEOF") This sentence? {vid = m_precordset-> getCollect (_variant_t ((_VARIANT_T) LONG) 0)); // Narrows the value of the first column, start counting from 0, you can also give a list of columns, such as the next line vusername = m_precordset-> getCollect ("username"); /// get Username Field value VOLD = m_precordset-> getCollect ("old"); vbirthday = m_precordset-> getCollect ("birthday"); //////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// The Output window output record IF in the DEBUG mode (VID.VT! = VT_NULL && vusername.vt! = vt_null && vold.vt! = vt_null && vbirdday.vt! = vt_null) Trace ("ID:% D, Name:% s, Age:% D, birthday:% s / r / n", Vid.lval, (lpctstr) (_ BSTR_T) VUSERNAME, VOLD.LVAL, (LPCTSTSTR); m_precordset-> MoveNext (); /// Move to Next Record} m_precordset-> MoveFirst (); // / Move to the first record m_precordset-> delete (adAffectCurrent); /// Remove the current record // Add three new records and assign the value for (int i = 0; i <3; i ) {m_precordset-> addnew (); /// Add new record m_pre Cordset-> Putcollect ("id", _ variant_t ((^ 10))); m_precordset-> Putcollect ("Username", _ variant_t ("Yeltsin")); m_precordset-> Putcollect ("OLD", _ variant_t ( (long) 71)); m_precordset-> Putcollect ("birthday", _ variant_t ("1930-3-15"));} m_precordset-> Move (1, _variant_t ((long) adbookmarkfirst); /// from the first A record moves down to move a record, that is, moved to the second record m_precordset-> Putcollect (_VARIANT_T ("OLD"), _ variant_t (long) 45)); // / / modify its age m_precordset-> Update () ; // Save to the library
[6] Close the recording set and the connection record set or connection can use the Close method to turn the m_precordset-> close (); /// Close record set m_pConnection-> close (); /// Close connection postscript: limited to space ADO Many of the contents have not yet been introduced. Next time we will detail the properties, methods of the Recordset object, how to solve several critical technologies: binding mode handle record set data, stored procedures, transaction processing, image in the database With reading, use with the table control. Let's see it next time!
