ADO is currently a popular client database programming technology in a Windows environment. ADO is an advanced programming interface established on the underlying technology of OLE DB, so it has a powerful data processing function (handling various types of data sources, distributed data processing, etc.) and extremely simple, easy to use Programming interfaces and thus have been widely used. And according to Microsoft's intentions, OLE DB and ADO will gradually replace ODBC and DAO. There are a lot of articles and books for ADO applications, and this paper focuses on the perspective of beginners, briefly explore some of the problems when using ADO programming in VC . We wish to read this article, you have some understanding of the basic principles of ADO technology.
First, use ADO programming in VC
The ADO is actually a component consisting of a set of Automation objects, so it can be used as an ADO like any other Automation object. The most important objects in Ado have three: Connection, Command, and RecordSet, which represents the connection object, command object, and record set object, respectively. If you are familiar with the ODBASE (CRecordset) programming in the MFC, then learn ADO programming is easy.
One of the following three methods can be employed when using ADO programming:
1, use the pre-processing instruction #import
#import "c: / program files / compo files / system / ado / msado15.dll" / no_namespace rename ("eof", "endoffile")
But be careful not to put the beginning of the stdafx.h file, but should be placed behind all the include instructions. Otherwise it will be wrong when compiling.
The program reads the type library information in MSADO15.DLL during the compilation process, automatically generates the header files of this type library and implement file Msado15.TLH and Msado15.tli (in your debug or release directory). All objects and methods of ADO are defined in both files, as well as some constants of some enumerations. Just call these methods directly, we are very similar to the COLEDISPATCHDRIVER class using the COLEDISPATCHDRIVER class in the MFC.
2, use the CidispatchDriver in the MFC
Just read the type library information in Msado15.dll, create a derived class for a COLEDISPATCHDRIVER class, and then call the ADO object by it.
3, use the API provided directly with COM
If you use the following code:
CLSID clsid; HRESULT hr = :: CLSIDFromProgID (L "ADODB.Connection", & clsid); if (FAILED (hr)) {...} :: CoCreateInstance (clsid, NULL, CLSCTX_SERVER, IID_IDispatch, (void **) & pDispatch ); failed (hr)) {...}
The above three methods, the first and second similar, may be the first to use, the third programming may be the most troublesome. However, the third method is also the highest efficiency, the size is also the smallest, and the control capability of ADO is also the strongest.
According to Microsoft data, the first method does not support the default parameters in the method call, of course, the second method is the same, but the third is not the case. The level of the third method is also highest. When you need to bypass the ADO, you must use the third method when you call the OLE DB underlayer.
The key to ADO programming is to skilfully use ADO to provide various objects, methods, properties, and storage. Also, if you are programmed on a large database such as MS SQL or Oracle, you must be skilled in using the SQL language. Second, the programming step using the #import method This is recommended to use #import method because it is easy to learn, easy to use, and the code is relatively simple. 1. Add #import instruction to open the stdafx.h file, add the following to all the include instructions:
#include
_COM_SMARTPTR_TYPEDEF (_COLLECTION, __UUIDOF (_COLLECTION)); after the macro expansion, it gets the _connectionPTR class. _ConnectionPTR classes encapsulate the IDispatch interface pointer of the Connection object, and some necessary operations. We are manipulating the Connection object through this pointer. Similarly, the _commandptr and _recordsetptr types used later are also obtained, which represents a pointer to the command object pointer and record set object, respectively. (1), connect to MS SQL Server to pay attention to the format of the connection string, providing the correct connection string is the first step in successful connection to the database server, see the Microsoft MSDN Library CD for details on the connection string. This example connects Server_Name, Database_name, user_name, and password in the string to replace the actual content when programming.
_ConnectionPtr pMyConnect = NULL; HRESULT hr = pMyConnect.CreateInstance (__ uuidof (Connection))); if (FAILED (hr)) return; _bstr_t strConnect = "Provider = SQLOLEDB; Server = server_name;" "Database = database_name; uid = user_name; PWD = Password; "; // Connecting to the Database Server Now: try {PMYCONNECT-> Open (strconnect,", ", null);} catch (_COM_ERROR & E) {:: MessageBox (null, E.DESCRIPTION) ), "Warning", MB_OK │ MB_ICONWARNING); Note The connection string parameter in the open method of the Connection object must be the BSTR or _BSTR_T type. In addition, this example is to establish a connection through the OLE DB Provider, so there is no need to establish a data source. (2) Connect to the Database Server connection string format with the Database Server connection string format and direct use of ODBC programming: _BSTR_T strConnect = "DSN = Datasource_Name; Database = Database_name; uid = user_name; pwd = password;"; at this time with ODBC Like programming, you must first create a data source. 3, define the _recordsetptr type variable, and open the data set definition _RecordSetPtr type variable, and then turn on a data set by calling the Open method of the Recordset object. So the Recordset object is similar to the CRecordset class in the MFC, which also has the current record, the current record pointer concept. Such as:
_RecordsetPtr m_pRecordset; if (FAILED (m_pRecordset.CreateInstance (__uuidof (Recordset))) {m_pDoc-> m_initialized = FALSE; return;}! Try {m_pRecordset-> Open (_variant_t ( "mytable"), _ variant_t ((IDispatch *) pMyConnect , adoptimistic, adcmdtable;} catch (_COM_ERROR & E) {:: MessageBox (NULL, "Unable to open myTable table.", "Tips", MB_OK │ MB_ICONWARNING);} The Open method of the Recordset object is very important. Its first parameter can be a SQL statement, a table name or a command object, etc .; the second parameter is a pointer to the previously established connection object. In addition, the Execute method with the CONNECTION and Command can also be recorded Set, but read-only. 4, read the current recorded data I think the most convenient way to read the data is as follows:
try {m_pRecordset-> MoveFirst (); while (m_pRecordset-> adoEOF == VARIANT_FALSE) {// Retrieve column's value: CString sName = (char *) (_ bstr_t) (m_pRecordset-> Fields-> GetItem (_variant_t ( "name" ))) -> value); Short Cage = (m_precordset-> fields-> GetItem (_variant_t ("age")) -> value); // do something what you want to do: ...... m_precordset-> MoveNext ();}} // trycatch (_COM_ERROR & E) {cstring str = (char *) E.DESCRIPTION (); :: MessageBox (null, str "/ n is a problem.", "Tips", MB_OK │ MB_ICONWARNING);} The name and agents in this example are field names, and the read field values are saved in the SNAME and CAGE variables, respectively. The fields in the example are the container of the Recordset object, and the GetItem method returns the field object, and the value is an attribute of the field object (ie the value of the field). In this case, the method of manipulating the object attribute should be grasped. For example, the value to obtain the value attribute of the field object can reference it (such as above) directly with the attribute name value, but can also call the GET method, for example: cstring sname = (char *) (_ bstr_t) (m_precordset-> Fields > GetItem (_variant_t ("name")) -> getValue ()); From this example, it is also possible to see whether to reach the end of the recordset, use the AdoEOF attribute of the recordset, if it is true, it is true. Not arrived. Determine whether to reach the beginning of the recordset, the BOF property can be used. In addition, reading data has a method to define a binding class, then get field values by binding variables (see later described later). 5, modify data method one:
Try {m_precordset-> movefirst (); while (m_precordset-> adoEOf == variant_false) {m_precordset-> fields-> getItem (_variant_t ("name")) -> value = _bstr_t ("Zhao Wei"); .... ..m_precordset-> update (); m_precordset-> MoveNext ();}} // TRY changes the value of the value attribute, that is, change the value of the field. Method Two:
m_precordset-> fields-> getItem (_variant_t ("name")) -> PUTVALUE (_BSTR_T ("Zhao Wei")); Method 3: The method of defining the binding class (see later described later). 6. After adding a record new record, it is automatically recorded. There are two forms of the AddNew method, and one contains parameters, and the other does not with parameters. Method 1 (without parameters):
// Add new record Into this table: try {if (! M_precordset-> supports; m_precordset-> addnew (); m_precordset-> fields-> getItem (_VARIANT_T ("Name")) -> Value = _BSTR_T ("Zhao Wei"); m_precordset-> fields-> getItem (_variant_t ("gender")) -> value = _bstr_t ("female"); m_precordset-> fields-> GetItem (_variant_t ("age")) -> Value = _variant_t ((short) 20); m_precordset-> fields-> getItem (_variant_t ("marry")) -> value = _BSTR_T ("unmarried"); m_precordset-> Update ();} // trycatch (_Error & e ) {:: MessageBox (NULL, "I have a problem.", "Tips", MB_OK │ MB_ICONWARNING);} This method has finished the update (). Method 2 (with parameters):
_VARIANT_T VARNAME [4], Narvalue [4]; VarName [0] = L "Name"; varName [1] = L "gender"; varName [2] = L "agname [3] = l" marry " Narvalue [0] = _ BSTR_T ("Zhao Wei"); Narvalue [1] = _ BSTR_T ("Female"); Narvalue [2] = _ variant_t (short) 20); Narvalue [3] = _ BSTR_T ("Unmarried"); const int nCrit = sizeof varName / sizeof varName [0]; // Create SafeArray Bounds and initialize the arraySAFEARRAYBOUND rgsaName [1], rgsaValue [1]; rgsaName [0] .lLbound = 0; rgsaName [0] .cElements = nCrit; SAFEARRAY * psaName = SafeArrayCreate (VT_VARIANT, 1, rgsaName); rgsaValue [0] .lLbound = 0; rgsaValue [0] .cElements = nCrit; SAFEARRAY * psaValue = SafeArrayCreate (VT_VARIANT, 1, rgsaValue); // Set the values for Each element of the arrayhresult hr1 = s_ok.hr2 = s_ok; for (long i = 0; i
Try {m_precordset-> MoveFirst (); while (m_precordset-> adoEOf == variant_false) {cstring sname = (char *) (_ bstr_t) (m_precordset-> fields-> getitem (_variant_t ("name")) -> value) ; if (:: messagebox (null, "Name =" SNAME "/ N delete her?", "Tips", MB_YESNO │ MB_ICONWARNING) == iDYES) {m_precordset-> delete (adAffectCurrent); m_precordset-> Update } m_precordset-> MoveNext ();}} // trycatch (_COM_ERROR & E) {:: MessageBox (null, "," Tips ", MB_OK │ MB_ICONWARNING);} 8, use parameters The Command object is represented by a provode, such as a SQL statement, and so on. The key to using the Command object is to set the statement indicating the command to the CommandText property, and then call the Execute method of the Command object. In general, there is no need to use parameters in commands, but sometimes use parameters, you can increase its flexibility and efficiency. (1). Establish a connection, command object, and record set object this example indicating that the command is a SQL statement (SELECT statement). Question mark in the SELECT statement? On behalf of the parameters, if you want multiple parameters, put more questions, each question mark represents a parameter. _ConnectionPtr Conn1; _CommandPtr Cmd1; ParametersPtr * Params1 = NULL; // Not an instance of a smart pointer._ParameterPtr Param1; _RecordsetPtr Rs1; try {// Create Connection Object (1.5 Version) Conn1.CreateInstance (__uuidof (Connection)); Conn1 -> ConnectionString = bstrConnect; Conn1-> Open (bstrEmpty, bstrEmpty, bstrEmpty, -1); // Create Command ObjectCmd1.CreateInstance (__uuidof (Command)); Cmd1-> ActiveConnection = Conn1; Cmd1-> CommandText = _bstr_t ( " Select * from myTable WHERE AGE
CMD1-> ActiveConnection = conn1; (2). Create a parameter object and assign the parameter
// Create parameter ObjectParam1 = cmd1-> createParameter (_BSTR_T (BSTREMPTY), AdINteger, Adparaminput, -1, _variant_t ((long) 5)); param1-> value = _variant_t (long) 5); cmd1-> parameters- > Append (param1); create a parameter object with a method of command object, where the length parameter (third) is the type of fixed length, fill in -1, if it is a string, etc., fill in it. Actual length. Parameters is a container of the command object, and its Append method is to append the created parameters to the container. The parameters in which the append in the order in the SQL statement correspond to the left to right one by one in the order. (3). Implementation command Open Record // Open RecordSet Objectrs1 = CMD1-> Execute (& VTempty, & Vtempty2, AdcmdText); But note that Recordset obtained with the Execute method of Command and Connection objects is read-only. Because we can't set its LockType property before opening RecordSet (its default value is read-only). The LockType is set up after opening. I found that after the above method, after the record set RS1, not only the records in the RS1 cannot be modified, even if any records in the same table are modified directly with the SQL statement. If you want to modify the data, you still have to use Recordset's own Open method, such as:
try {m_pRecordset-> Open ((IDispatch *) Cmd1, vtMissing, adOpenStatic, adLockOptimistic, adCmdUnspecified);} catch (_com_error & e) {:: MessageBox (NULL, "mytable table does not exist.", "prompt", MB_OK │ MB_ICONWARNING );} The OPEN method of the Recordset object is great, and the first parameter can be a SQL statement, a table name, a command object pointer, and the like. 9. Responding to the ADO notification event notification event is that when a particular event occurs, inform the client, in other words, it is a specific method (ie, the process of processing function) is called by the Provider. So in response to an event, the most critical is to implement the handler of the event. (1). Delicate a class from the ConnectionEventsvt interface to respond to the notification event of _Connection, you should derive a class from the ConnectionEventsvt interface:
class CConnEvent: public ConnectionEventsVt {private: ULONG m_cRef; public: CConnEvent () {m_cRef = 0;}; ~ CConnEvent () {}; STDMETHODIMP QueryInterface (REFIID riid, void ** ppv); STDMETHODIMP_ (ULONG) AddRef (void) ; STDMETHODIMP_ (ULONG) Release (void); STDMETHODIMP raw_InfoMessage (struct Error * pError, EventStatusEnum * adStatus, struct _Connection * pConnection); STDMETHODIMP raw_BeginTransComplete (LONG TransactionLevel, struct Error * pError, EventStatusEnum * adStatus, struct _Connection * pConnection) ;. .. * pConnection) {* adstatus = adstatusunwantedEvent; returnidEvent; returnidEvent; Return S_OK;}; Some methods Although you don't need it, you must implement it, just simply return a S_OK. However, if you want to avoid frequently being called, you should also set the adstus parameter to AdstatusunWantedEvent, then it will not be called later after this call. There must also be three ways of queryinterface, addRef, and release:
STDMETHODIMP CConnEvent :: QueryInterface (REFIID riid, void ** ppv) {* ppv = NULL; if (riid == __uuidof (IUnknown) ││ riid == __uuidof (ConnectionEventsVt)) * ppv = this; if (* ppv == NULL) return ResultFromScode (E_NOINTERFACE); AddRef (); return NOERROR;} STDMETHODIMP_ (ULONG) CConnEvent :: AddRef () {return m_cRef;};! STDMETHODIMP_ (ULONG) CConnEvent :: Release () {if (0 = - M_CREF) Return M_Cref; delete this; return 0;} (3). Start response notification event
// Start using the Connection eventsIConnectionPointContainer * pCPC = NULL; IConnectionPoint * pCP = NULL; hr = pConn.CreateInstance (__ uuidof (Connection)); if (FAILED (hr)) return; hr = pConn-> QueryInterface (__ uuidof (IConnectionPointContainer) (void **) & pcpc); if (FAILED (HR)) Return; HR = PCPC-> FindConnectionPoint (__ uuidof (connectionectionevents), & PCP); PCPC-> Release (); if (Failed (HR)) Return; PConnevent = new cconnevent (); hr = pConnevent-> queryinterface (__ uuidof (iUidof (iUndnown), (void **) & punk; if (failed (hr)) Return RC; HR = PCP-> Advise (Punk, & DWConnevt); PCP- > Release (); if (FAILED (HR)) Return; PCONN-> Open ("DSN = Pubs;", "SA", ",", "", adConnectunSpecified); that is, do these things before the connection (Open). (4). Stop response notification event PConn-> close (); // stop using the connection Eventshr = pconn-> queryinterface (__ uuidof (iconnectionPointContainer), (void **) & pcpc); if (Failed (HR)) return; HR = PCPC-> FindConnectionPoint (__ uuidof (connectionectionevents), & pcp); PCPC-> Release (); if (Failed (HR)) Return RC; HR = PCP-> Unadvise (dwconnevt); PCP-> Release (); if (FAILED (HR)) Return; Do this after the connection is closed.
