VC Development Database Basite ADO Part 1, ADO Introduction ADO (ActiveX Data Object) is a new interface developed by Microsoft Database Application. It is a high-level database access technology built on OLE DB. Please don't have to worry about it, even for OLE DB, COM does not understand the ADO, because it is very easy to use, even more likely 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 command We add the following statement in stdafx.h: (STDAFX.H This file can you find? You can find it in the Header Files in FileView) #import "C: / Program Files / Common files / system / ado / msado15.dll "No_namespace rename" 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
[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 open connection adModeShareDenyNone : Allow other programs or objects to establish connections in any permission
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) Connection to any support ODBC's database via the DSN data source: m_pConnection-> Open ("data source = adotest; UID = SA; PWD =;", ",", ",", ",", ",", ",",
(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 make a corresponding process, for example: if (m_pconnection-> state) m_pconnection-> close (); // If the connection has been opened, turn it off.
[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: Stored Procedure AdcmDunkNown: Unknown Execute End Returns a pointer to record sets, 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 birthdaym_pConnection-> Execute ( "CREATE TABLE users (ID INTEGER, username TEXT, Old Integer, Birthday DateTime) ", & Recordsaffected, AdcmdText); /// Add record M_PConnection-> Execute (" Insert Into Users (ID, Username, Old, Birthday) VALUES (1, '' '' ') 'Washington' '' '' '' ', 25,' '' '' '' '1970/1/1' '' '' '' ') ", & RecordsAffected, adCmdText); /// old records all fields The value is added to one m_pconnection-> execute ("Update Users Set Old = OLD 1", & RecordSaffected, AdcmdText); // / / executes the SQL statistical command to get record set M_PRecordset = m_pconnection-> Execute ("Select Count) (" 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 vcount variable m_precordset-> Close ); /// Close record set cstring message; "" a total of% D record ", vcount.lval); afXMESSAGEBOX (Message); /// Display the current record number
m_pCommand.CreateInstance ( "ADODB.Command");; (2) to SQL commands executed using the Command object _CommandPtr m_pCommand _ variant_t vNULL; vNULL.vt = VT_ERROR; vNULL.scode = DISP_E_PARAMNOTFOUND; /// no parameters defined m_pCommand-> ActiveConnection = m_pconnection; /// Very critical sentence, assign the established connection to it m_pcommand-> commandtext = "select * from users"; /// command string m_precordset = m_pcommand-> Execute (& Vnull, & vnull, adcmdtext) ; // / execute the command, obtain the recordset In this code, we just use the Command object to execute the SELECT query statement, and the Command object actually reflects 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_precordset-> pu TCOLLECT, _ VARIANT_T ((i 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 record Move a record down, that is, move 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] Closing the record set and connection record set or connection can use the Close method to turn off m_precordset-> close (); /// Close record set m_pconnection-> close (); /// Close connection to this, I think you have already Familiar with the approximate process of the ADO operation database, maybe you have already bamboo, maybe you still have a bit of Hu, don't be tight! It is recommended that you try to write a few examples, which will better familiarize yourself with ADO. Finally, I wrote a small example for everyone, and read all records in the list control, and can add, delete, and modify the record. Click here to download sample code
Postscript: Limited to many of the contents of the space ADO have not yet been introduced, next time we will introduce the property, method of the Recordset object, method and solve several critical technologies: binding mode handle record set data, stored procedure call, transaction processing, diagram Save and read in the database, use with the table control, etc.
/ / -------------------------------------------------------------------------------------------- -------------------------------------- yet: How do I use ADO to access the database in VC? ?
1. # Import "msado15.dll" NO_NAMESPACE RENAME ("EOF", "ADOEOF") introduces "Msado15.dll" dynamic library.
2. Define a variable _ConnectionPtr M_adocon; represents a single session that represents a representative and data source
3, open the database _BSTR_T Connstring; // Connect the string
Try {connString = _t ("provider = msdasql.1; data source = kong; persist security info = false");
M_adocon.createInstance (__ uuidof (connection)); //???
M_adocon-> open (connString, "", ", -1);} catch (_com_error) {iERRORINFO * Perrorinfo = Comerr.ErrorInfo (); HRESULT HR = Comerr.Error ();
IF (Perrorinfo) {BSTR BSDESC = NULL;
PERRORINFO-> getDescription (& BSDESC); _ BSTR_T SDESC (BSDesc, false);
AfxMessageBox (SDESC.Operator LPCTSTR ());
PERRORINFO-> Release ();}}
4, use CString Strip, SQL_STR, STRTEMP, STRRESULT;
Uint port;
_RecordSetPtr Adorst; // Recordset instance _streamptr adoStream; // ADO data stream
SQL_STR = m_STRTEXT; / / SQL statement
If (m_adocon-> state == adStateclosed) // determines whether the connection has been turned off {straTemp = "ADO Connection Hasn't set up."; m_pWnd-> SendMessage (WM_FRESHWATCHLIST, (WPARAM));
Sendmsg (Strtemp, False);
Return;}
try {// Create Recordset InterfaceAdoRst.CreateInstance (__ uuidof (Recordset)); AdoRst-> Open ((LPCTSTR) SQL_STR, m_AdoCon.GetInterfacePtr (), adOpenDynamic, adLockOptimistic, adCmdUnknown); // open and execute SQL (SQL_STR) Statement
IF (strret! = "SELECT") Strresult = _t ("ok!"); else {// save the xmlresult to strresultadostream.createInstance (__ uuidof (stream)); //
AdoRst-> Save (AdoStream.GetInterfacePtr (), adPersistXML); strResult = ((BSTR) AdoStream-> ReadText (adReadAll)); // convert XML format}} catch (_com_error & comerr) {// catch the COM exceptionIErrorInfo * PERRORINFO = Comerr.ErrorInfo (); hResult HR = Comerr.Error ();
IF (Perrorinfo) {BSTR BSDESC = NULL; Perrorinfo-> GetDescription (& BSDESC); _BSTR_T SDESC (BSDesc, false);
Strresult = SDESC.Operator LPCTSTR (); perrtinfo-> release ();}}
5. Obtain field GetCollect table () int nItem; _variant_t vUsername, vBirthday, vID, vOld; try {m_pRecordset.CreateInstance ( "ADODB.Recordset"); m_pRecordset-> Open ( "SELECT * FROM users", _ variant_t (( IDispatch *) theApp.m_pConnection, true), adOpenStatic, adLockOptimistic, adCmdText); m_bSuccess = TRUE;! while (m_pRecordset-> adoEOF) {vID = m_pRecordset-> GetCollect ( "ID"); vUsername = m_pRecordset-> GetCollect ( " username "); vOld = m_pRecordset-> GetCollect (" old "); vBirthday = m_pRecordset-> GetCollect (" birthday "); nItem = m_userlist.InsertItem (0xffff, (_ bstr_t) vID); m_userlist.SetItem (nItem, 1, 1, (_ BSTR_T) VUSERNAME, NULL, 0, 0, 0); M_UserList.SetItem (Nitem, 2, 1, (_ BSTR_T) VOLD, NULL, 0, 0); M_UserList.SetItem (NITEM, 3, 1, (_BSTR_T) VBIRTHDAY, NULL, 0, 0, 0);
m_precordset-> moandenext ();}}
6. Add data PutCollect () if (m_pRecordset-> adoEOF && m_nCurrentSel> = 0 && m_bAutoSave!) {VUserID = (long) m_nUserID; vUsername = m_sUsername; vOld = (long) m_nOld; vBirthday = m_tBirthday; m_pRecordset-> PutCollect ( "ID", vuserid; m_precordset-> Putcollect ("username", vusername); m_precordset-> Putcollect ("old", verset); m_precordset-> Putcollect ("birthday", vbirdhday} // ----- -------------------------------------------------- ----------------------------- Information on the ACCESS in ADO
Void OpenSchemax (Tchar * Tablename) {HRESULT HR = S_OK;
Iadorecordbinding * picrs = null;
_RecordSetptr pristSchema ("AdoDb.Recordset"); _ ConnectionPtr PConnection ("AdoDb.Connection");
PConnection-> Connectionstring = Tablename; PConnection-> provider = "Microsoft.jet.OleDb.4.0";
Try {PCONNECTION-> Open (PConnection-> Connectionstring, "," ", AdmodeunkNown); prtention, (_UUIDOF (il), (lpvoid *) & picrs;
PRSTSCHEMA = PConnection-> OpenSchema (adschematables); // enumerated table name processing
While (! (prschema-> endoffile) {CSTRING STRTABLETYPE
_BSTR_T TABLE_NAME = prStschema-> Fields-> GetItem ("Table_name") -> value; // Get the name of the table
_BSTR_T TABLE_TYPE = prStschema-> Fields-> GetItem ("Table_Type") -> value; // Get the type of table
StrtableType.Format ("% s", (lpcstr) table_type);
IF (! LSTRCMP ("" "))) {m_cbteam.addstring (lpcstr) Table_name); // Add table name}
PRSTSCHEMA-> MOVENEXT (); // Clean Up Objects Before Exit.
PRSTSCHEMA-> Close (); pConnection-> close ();
Catch (_COM_ERROR & E) {// Notify the user of errors if any. // pass a connection .printProvideRerror (PConnection); PrintComerror (E);}}
-------------------------------------------------- ------------------------------ 9CBS VC programming experience summary
ADO 2.8 Samples
See AlsoClear Method | Command Object | Connection Object | Errors Collection | Execute Method (ADO Command) | Execute Method (ADO Connection) | Requery MethodExecute, Requery, and Clear Methods Example (VC ) This example demonstrates the Execute method when run from both a Command object and a Connection object. It also uses the Requery method to retrieve current data in a recordset, and the Clear method to clear the contents of the Errors collection. The ExecuteCommand and PrintOutput functions are required for this example to run.
// beginexecutecpp # incrude
#import "c: / program files / compo files / system / ado / msado15.dll" / no_namespace rename ("eof", "endoffile")
// Function declarationsinline void TESTHR (HRESULT x) {if FAILED (x) _com_issue_error (x);}; void ExecuteX (void); void ExecuteCommand (_CommandPtr pCmdTemp, _RecordsetPtr pRstTemp); void PrintOutput (_RecordsetPtr pRstTemp); void PrintProviderError (_ConnectionPtr PConnection; Void Printcomerror (_COM_ERROR & E);
// main function ////////main
Void main () {if (failed (:: coinitialize (null)) Return;
EXECUTEX ();
:: Couninitialize ();
//// Executex function /////
Void Executex (Void) {HRESULT HR = S_OK;
// Define string variables _bstr_t strSQLChange ( "UPDATE Titles SET Type =" " 'self_help' WHERE Type = 'psychology'");. _Bstr_t strSQLRestore ( "UPDATE Titles SET Type =" " 'psychology' WHERE Type = 'self_help'" ); _bstr_t strCnn ( "Provider = 'sqloledb'; Data Source = 'MySqlServer';" "Initial Catalog = 'pubs'; Integrated Security = 'SSPI';");. // Define ADO object pointers // Initialize pointers on Define. // THESE ARE IN The adoDB :: Namespace. _ConnectionPtr PConnection = NULL; _COMMANDPTR PCMDCHANGE = NULL; _RECORDSETPTR PRSTTITLES = NULL;
Testhr (PConnection.createInstance); PConnection-> Open (strCN, ",", ", adConnectunSpecified);
// Create Command Object. Testhr (PCMDChange.createInstance); pcmdchange-> activeConnection = PConnection; pcmdchange-> commandtext = strsqlchange;
// Open titles table, casting Connection pointer to an // IDispatch type so converted to correct type of variant TESTHR (pRstTitles.CreateInstance (__ uuidof (Recordset)));. PRstTitles-> Open ( "Titles", _variant_t ((IDispatch * ) PConnection, True, AdopenStatic, Adopient, AdcmdTable;
// Print Report of ORIGINAL DATA. Printf ("/ N / NData In Titles Table Before Executing The Query: / N");
// Call Function to Print Loop RecordSet Contents; PrintOutput (PRSTTTIS);
// Clear Extraneous Errors from the Errors Collection. PConnection-> Errors-> CLEAR (); // Call ExecuteCommand Subroutine to Execute Pcmdchange Command. ExecuteCommand (PCMDChange, PRSTTIGES);
// Print Report of New Data. Printf ("/ N / N / TData In Titles Table After Executing The Query: / N"); PrintoutPut (PRSTTITLES);
// Use the connection object's execute method to restore data. PConnection-> Execute (strsqlrestore, null, adexecutenorecords);
// Retrieve The Current Data by Requerying The Recordset. Prsttitles-> Requory (AdcmDunknown);
// Print report of restored data printf ( "/ n / n / tData after exec query to restore original info:. / N");. PrintOutput (pRstTitles);} catch (_com_error & e) {PrintProviderError (pConnection); PrintComError ( e);
// Clean Up Objects Before EXIT. IF (PRSTTIGES-> State == AdStateOpen) PRSTTITITLES-> Close (); if (PConnection) IF (PConnection-> State == AdStateOpen) PConnection-> Close (); }
//// executeCommand function ////
Void ExecuteCommand (_RecordPtr PCMDTEMP) {Try {// Commandtext Property Already Set Before Function Was Called. PcmdTemp-> Execute (Null, Null, AdcmdText);
// Retrieve The Current Data by Requerying The RecordSet. PrStTemp-> Requery (AdcmDunkNown);
catch (_com_error & e) {. // Notify user of any errors that result from // executing the query // Pass a connection pointer accessed from the Recordset PrintProviderError (pRstTemp-> GetActiveConnection ());. PrintComError (e);}} ///Printoutput function ///
Void PrintputPut (_RecordSetPtr PRSTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTEMP-> MOVEFIRST ();
// if EOF is true, the no data and skip print loop. If (printf ("/ limited {"} else {// define temporary strings for output conversions. // Initialize To First Record's Values. _BSTR_T BSTRTILE; _BSTR_T BSTRTYPE
. (! (PRstTemp-> EndOfFile)) // Enumerate Recordset and print from each while {// Convert variant string to convertable string type bstrTitle = pRstTemp-> Fields-> GetItem ( "Title") -> Value;. BstrType = PRSTTEMP-> Fields-> GetItem ("Type") -> Value; Printf ("/ T% S,% S / N", (LPCSTR) BSTRTILE, (LPCSTR) bstrtype); prStTemp-> MoveNext ();}} }
//// PrintProvideRerror function /////
Void PrintProvideRerror (_ConnectionPtr PConnection) {//print provider errors from connection object. // perr is a record object in the connection's error collection. errorptr perr = null;
IF ((PConnection-> ErrorS-> Count> 0) {long ncount = pConnection-> errors-> count; // collection ranges from 0 to ncount -1. for (long i = 0; i
Void Printcomerror (_COM_ERROR & E) {_BSTR_T BSTRSOURCE (E.Source ()); _bstr_t bstrdescription (E.Description ()); // print com errors. Printf ("error / n"); printf ("/ tcode =% 08lx / N ", E.Error ()); Printf (" / Tcode Meaning =% S / N ", E.ErrorMessage ()); Printf (" / Tsource =% S / N ", (LPCSTR) BSTRSOURCE); Printf ("/ tdescription =% s / n", (lpcstr) bstrdescription;} // endexecuteCPP
Sender: Wesley (Pine ~ Forest in the heart), the letter area: Visualc Title: ADO is Aok (ADO is actually very simple) (1) (reproduced) Sending Station: BBS Shuimu Tsinghua Station (Fri Nov 19 11:30: 18 1999) The following text is reproduced from the Database discussion area] [Originally published by Wesley] This article is taken from http://www.codeguru.com/mfc_database/ado_aok.shtml. Author: Bob Place rewrite translation: wesley at Tsinghua. Email: wesley@video.mdc.tsinghua.edu.cn. Foreword: Some people often ask: What is the best data access method now? Answer is of course ADO! M $ spent a lot of time, launched a stuff called UDA (Universal Data Access), and a set of very simple data access object ADO (ActiveX Data Object), used to replace the Overspelling DAO (Data Ac) CESS Object), RDO (Remote Data Object). The bottom layer of the DAO is the JET engine, mainly to provide access to the Access database, and the new version also supports access to other databases, but for other databases, the Jet intermediate layer is required, the access speed is relatively poor. Jet is the fastest in all access methods for Access database. The latest Jet Engine version is 4.0, the corresponding DAO version is 3.6, which can access the database of Access 2000. The cdaodatabase and cdaorecordset are MFC packages for DAO. The bottom layer of RDO is ODBC. RDO is just a thin packaging layer for the ODBC API, and it is a bit unlike, many people have written by ODBC API (my first SQL Server client program is an ODBC API. A console application written, which can discover those APIs with RDO, and there is no professional spirit. It is also because of thin, so the speed is faster, before ADO, the fastest way to access MS SQL Server is RDO (don't tell me, there are DB-Library, whether I look at MS documents, or I personally experiment, DB -Library has no ODBC / RDO fast). The latest RDO version is 2.0, saying is the latest, and it seems that it has not been updated for several years, because MS has long decided to eliminate it. But unfortunately, now everyone is still using C Database, CRecordset is the RDO MFC packaging. Now use these two classes, like BMW Mercedes, but also to get off work, just because they will not drive. :-( The bottom of the ADO is OLE DB, not only can access the relational database, but also access non-relational databases, this is now the fastest database accessing the intermediate layer! ADO to the OLE DB packaging can be said to be quite successful, object model Concise, there is no more than a little more, the function is still far super Dao, RDO. But at this time, I am a little admiring MS. Dozens of dense Ma Ma in Ole DB is too horrible to me. Use ADO.
One thing is very unfortunate, Microsoft's ADO document has little about the content of VC, like a VC rookie like me, sitting into the comfortable driving position of BMW, I don't know how to go to my hands, depressed! This article is really saving the Lifen in the water, can't help me to contribute it to let everyone share. Start: Before using ADO, you must let your program know where to find ADO. In the stdafx.h file, you need to add the following code: #import "c: / program files / common files / system / ado / msado15.dll" no_namespace rename ") The role of this line code Yes, tell the compiler to find the ADO library file (may vary on your machine), then explain you don't have Namespace, and rename the EOF to AdoEOF (if you don't do it, it is very likely to touch constant conflicts ). Just add this sentence, prepare for work, is it simple? No need to include any header file, don't specify any lib file for link, I also feel a little magical. // shrug _connectionptr, _commandptr, and _recordsetptr (not mentioned in this article): ADO, and CDAODATABASE, CDATABASE is very similar, and it is divided into such a few, and the ADO is based on CoM, these blocks are Standard COM components, and CDATABASE, etc. are MFC classes. One thing to pay attention to, to learn ADO programming, learn COM is inevitable, but this is a good thing, now there is a little COM, OLE, it is estimated that it is difficult to adapt to the situation of Windows programming. The three components of ADO are three COM components: Connection, Command, RecordSet. (There are two temporary use :) Connection is used to establish a database connection, and the SQL statement does not return any result sets. Command is used to return a result set and provide a simple method to perform a stored procedure or any S QL statement that returns the result set. Recordset is the result set that can be accessed, scrolling operations. If you give Command and Recordset correct Connection String instead of a CONNECTION object's pointer, you can open the recordset, which is suitable for single database operations. When you need frequent database operations in the program, it is best to predefine a Connection object, use it to connect to the database, and use the RE CORDSET to process data. This two objects will be discussed in this article. _ConnectionPtr is a connection interface, similar to CDatabase and CDaoDatabase, and actual working principle is similar. Create its instance in the program, point to a data source via an OLE DB Provider and open the connection.
The following code is CDAODATABASE and _CONNECTIONPTR to open instances: Dao: cdaodatabase mydb = new cdaodatabase (); m_daoserverdb.open (null, false, false, "ODBC; DSN = SAMS_SVR; UID = admin; pwd = adm in"); ADO: _CONNECTIONPTR MYDB; Mydb.createInstance (__ uuidof (connection)); mydb-> open ("DSN = SAMS_SVR; UID = admin; pwd = admin", ",", - 1); _RecordSetPtr is a recordset clip, Similar to CDaorecordset. Take a look at it and open the way CDAO Recordset how similar: DAO: CDaoRecordset MySet = new CDaoRecordset (MyDb); MySet-> Open (AFX_DAO_USE_DEFAULT_TYPE, "SELECT * FROM some_table"); ADO: _RecordsetPtr MySet; MySet.CreateInstance (__ uuidof (RECORDSET); MySet-> Open ("Select * from home_table", mydb.getinterfaceptr (), adoPENDY NAMIC, ADLOCKTIMISTIC, ADCMDTEXT); (Translator Note: Please Note ADO In Open Recordset, use mydb.getInterfacePtr () As one of the parameters, I used _ConnectionPtr, & (_ connectionptr) no, it was so used to use it, it is really unknown. :() ADO is just a little bit slightly, but it will give this effort to get ADO a lot of benefits. Still very valuable. Now there is a connection and a recordset, which should be used in these two Dongdong points. Maybe it is assumed that there is a listbox named M_List, we take the data out of the longevity.
DAO: VARIANT * vFieldValue; COleVariant covFieldValue; CString Holder; while {MySet-> GetFieldValue ( "FIELD_1", covFieldValue); vFieldValue = (LPVARIANT) covFieldValue; if (vFieldValue-> vt - (MySet-> IsEOF ()!)! Vt_null) {Holder.Format ("% s", vfieldvalue-> pbval); m_list.addstring (Holder);} myset.movenext ();} ado: _variant_t Holder While (! Myset-> adoEof) {Holder = MySet- > GetCollect ("Field_1"); if (Holder.Vt! = Vt_null) m_list.addstring ((char *) _ BSTR_T (Holder)); MySet-> MoveNext ();} Note: Nothing in all documents in Microsoft Find a getCollect method. I found all the places, and I also mentioned from no one. The way in the document is like this: Holder = MySet-> getfields-> field -> (_ variant_t (fieldnumber)) -> value; Which one do you like? Anyway, I like getCollect. (Translator Note: Ghost knows where he can get this method, is it from MS own procedures? I serve HIM.) Dynamic binding VS DFX (Crecordset and CDaorecordSet pre-field binding, Data Field Exchan GE : Dynamic Binding that is, using the SQL statement dynamically constructed result field instead of using DFX in CDAORECordSet to map all original fields into member variables. Dynamic binding example: SELECT (SUM (Field_1) SUM (Field_2)) AS Answer from Some_Table If you use DFX, you must write yourself in the program: m_answer = m_field_1 m_field2; (Translator Note: Although many people I like DFX's field pre-bound, I would rather write code, but I rarely use DFX, of course, I never used in VB, it seems to be used in Delphi, it is more cool. Probably the documentation of MS makes me don't use it, but didn't say it in Borland. :-) In contrast, dynamic binding does have a superior place, reduce the amount of code, and make the program more Small, easier to maintain.
Besides, this is also the recommended data method recommended by MS, more flexible, faster, more easy to maintain, what can we ask? For most programs, it is created a global connection, and then uses RecordSet to handle data. If the number of RecordSet is a lot, you can imagine how much light is spent on the DFX code. If you use dynamic binding, these are saved. _Variant_t and _bstr_t What is the same? Unfortunately, our favorite CString class can not be used in COM (CSTRINGEX), because COM must set up a cross-platform, it requires a more common way to process strings and other data. This is the origin of the Variant data type, and the BSTR type. Variant is a huge union, which contains all the data types you can want, except for char *, but it is ok, BSTR replaces char *. (Translator Note: It seems that Variant is a very slow thing. Everyone is not willing to make it, but according to my opinion, the situation is not so bad, Union takes you should not go slow, to say slow, it is slow to give Variant Allocated on the address space, this is a good horror than VB, but it doesn't want to be afraid. After you are familiar with these two things, you will soon like it) Simply, _Variant_t is a class, packaged in the Variant data type, and allows us to make mandatory type conversion to it (I believe everyone like this), _ BSTR_T gives the same thing to BSTR. In the following example, you will see how to take the data into the variant, how to put it in _BSTR_T, and finally enforce it into a char *, and convert _variant_t to force into long, double or everything else Things: _VARIANT_T HOLDER; // First Get The Variant and Put IT ITO THE _VARIANT_T HOLDER = MySet-> getCollect ("Field_1"); // NOW PUT IT ITO A _BSTR_T AND CAST IT TO A CHAR * M_LIST.Addstring ((charr *) _ bstr_t (Holder)); _variant_t compare with and without _bstr_t code: COleVariant covFieldValuel VARIANT vFieldValue CString Holder; MySet-> GetFieldValue ( "FIELD_1", covFieldValue); vFieldValue = (LPVARIANT) covFieldValue; Holder.Format ( "% s", vfieldvalue-> pbval); M_List.Addstring (Holder); difference! (To be continued) - The world does not matter if there is no reason, there is only one situation and another situation and another situation is only experienced with extreme unfortunate talents to taste extreme happiness ※ Source: · BBS Shuimu Tsinghua Station BBS.NET. Tsinghua.edu.cn · [from: 166.111.159.21]
Sender: Wesley (Pine ~ Forest in the heart), the letter area: Visualc Title: ADO IS Aok (ADO is actually very simple) (2) (reproduced) Sending station: BBS Shuimu Tsinghua Station (Fri Nov 19 11:30: 29 1999) Reprinted from the Database discussion area] [Originally published by Wesley] Update, Insert, Delete: When I do Update, INSERT, DELETE operation, I usually like to use Connection and Command objects because of CSTRING. To construct some SQL statements simple, then use connections.execute. Of course, it is also possible to dry with Recordset. The UPDATE method has the following three ways to call: 1: assign a value for a field object (or some), then call the UPDATE method; 2: Pass the field name and field value to the UPDATE method as the UPDATE method; 3: Will the field The array of name and field values is transmitted to the UPDATE method as a parameter. The AddNew method is called as follows: 1: Call directly, then call method one with Update; 2: Pass the field name, field value array to the AddNew method. The delete method is the easiest and directly call it, delete the current record! After doing these things, you may need to call the Requory method to see the effect. The following sample code requires us to create a simple MFC Application, and then declare three objects in the CWinApp class interface inside: // Global ADO Objects // connection _ConnectionPtr m_pConnection; _CommandPtr m_pCommand; _RecordsetPtr m_pRecordset; in VC6 there is very interesting, If you knock "M_PConnection.", You will see a list of methods and properties, if you knock "M_PConnection->", you will also see a list of methods and properties, of course, the content inside is completely different, because you are actually pointing two different things. Below is the two mixed code: _ConnectionPtr MyDB; mydb.createInstance (__ uuidof (connection)); mydb-> open ("DSN = SAMS_SVR; UID = admin; pwd = admin", ",", - 1) Back to the sample code, in the Application's InitInstance method, I turn on the data connection, point to a database on my machine, you need to change Connectionstring, use your own ODBC data source or specify an OLE DB Provider.
// WE open the application we will open the ado connection m_pConnection.createInstance (__ uuidof (connection)); m_pconnection-> open ("DSN = adotest", ",", - 1); if you open the About dialog I will see a ListBox, and a button called Button1, which contains the core code of the ADO call. I created an instance of the _RecordSetPtr interface, open the record set I need, then traversed all records, plug them to listbox: _variant_t thevalue; THEAPP.M_PRECORDSET.CREATEINSTANCE (__ uuidof (recordset); try {theapp.m_precordset -> Open ( "SELECT DISTINCT FLDESC FROM tblFALines", theApp.m_pConnection.GetInterfacePtr (), adOpenDynamic, adLockOptimistic, adCmdText); while {TheValue = theApp.m_pRecordset-> GetCollect ( "FLDESC" (theApp.m_pRecordset-> adoEOF!) ); If (thevalue.vt! = Vt_null) m_list.addstring ((char *) _ bstr_t (thevalue)); theapp.m_precordset-> MoveNext ();} theapp.m_precordset-> close ();} catch (_Error * e ) {Cstring error (); AFXMESSAGEBOX (E-> ErrorMessage ());} catch (...) {MessageBox ("WHOA this is bad");} Remember to use try and catch, otherwise ADO call errors may make your program crash, you must always remember to capture _com_ERROR exceptions and other errors. I am so simple that the code is simple, so many details, especially for many good program habits (such as checking the HRESULT values returned by most COM methods). The purpose of this article is to say that ADO is not difficult, COM is the same, not all things that ADO can do. I didn't even think about what ADO can bring for you, but I am sure, that is, ADO is faster, more easily, and is much more powerful than DAO, RDO. Take a look at this site other article, you will know how easy it is to call the stored process through the ADO! Finally, I want to recommend two books to you, one is completely free, I can find electronic version at www.informit.com, and another must pay for it, but I still recommend two books, unless In your housing, the bed is also put on the head.
:) The free is the
Formal code: _RecordSetPtr M_PRS; _ConnectionPtr M_PCONN; Try {Coinitialize (NULL);
m_pConn.CreateInstance (__ uuidof (Connection)); m_pRs.CreateInstance (__ uuidof (Recordset)); // set the server side cursor m_pConn-> CursorLocation = adUseServer; // adUseClient; adUseNone // ORACLE database connection m_pConn-> Open (L " Provider = oraoledb.racle.1; user ID = pkuwh_za; password = a; data source = 213; Persist security info = false ", l", l ", adopenuspecified); // The above statement is written You don't have to configure Oracle Data Source //m_PCONN />Open (L "Provider= Oraoledb.racle.1;User ID = PkuWh_ZA; Password = a; data source = (deScription = (protocol = tcp) (Host = 192.168 .3.213) (port = 1521)) (Connect_Data = (Service_name = ZAGL))); Persist Security Info = FALSE ", L" ", L" ", AdopenunSpecified; M_PRS-> PutreFactiveConnection (m_pconn); cstring t =" select * from case_acceptinfo where rownum <= 3 "; m_pRs-> Open (_variant_t (t), _ variant_t ((IDispatch *) m_pConn, true), adOpenDynamic, adLockOptimistic, adCmdUnknown); while (! m_pRs-> adoEOF) {CString m_s1 = _COM_UTIL :: ConvertBSTRTSTRING ((_ bstr_t) m_prs-> getCollect ("ReportTime")); messagebox (m_s1); m_prs-> moand ();
M_PRS-> close (); m_pconn-> close ();
M_PRS = NULL; m_pconn = null; couninitialize ();} catch (_com_error & e) {afxMessageBox (E.DESCRIPTION ());}
