Introducing the VC to access the database Raiders with ADO, introduce VC to access the database and various methods of accessing the database, very classic, very practical, very worth seeing.
Text 1, ADO Overview ADO is Microsoft designed for the latest and most powerful data access example OLE DB, is an application layer interface that is easy to use. ADO allows you to write applications to access and operate data in the database server via OLE. DB provider. The most important advantages of ADO are easy to use, fast, and less memory spending and small disk remains. ADO uses minimal network traffic in a critical application, and uses the least number of layers between the front and data sources, all of which are to provide lightweight, high-performance interfaces. The reason is called ADO, which has used a familiar metaphor, OLE automation interface.
OLE DB is a set of "Component Object Model" (COM) interfaces, a new database low-level interface, which encapsulates the function of ODBC and access data stored in different information in a unified manner. OLE DB is the technical foundation of the Microsoft UDA policy. OLE DB provides high performance access for any data source, including relationships and non-relational databases, email, and file systems, text, and graphics, custom business objects, and more. That is, OLE DB is not limited to ISAM, JET, even relational data sources, which can handle any type of data regardless of their format and storage methods. In practical applications, this diversity means accessing data residing in an Excel electronic data sheet, a text file, an email / directory service, and even a mail server, such as Microsoft Exchange. However, the purpose of the OLE DB application programming interface is to provide the best features for a variety of applications, which do not meet simplified requirements. The API you need should be a bridge that connects to applications and OLE DB, which is ActiveX Data Objects (ADO).
Second, use ADO in VC (Development steps :)
1. Introduce the ADO library file
Before using ADO, you must introduce the STDAFX.H header file to introduce the ADO library file in direct introduction symbol #import to enable the compiler to compile correctly. The code is as follows:
Introducing an ADO library file with #import
#import "c: / program files / commit files / system / ado / msado15.dll" no_namespaces rename ")
This list of statements declares that use ADO in the project, but does not use the namespace of ADO, and rename constant EOFs to AdoEOF in order to avoid constant conflicts. Now you don't need to add additional header files, you can use the ADO interface.
2. Initializing the OLE / COM library environment must note that the ADO library is a set of COM dynamic libraries, which means that the application must initialize the OLE / COM library environment before calling ADO. In the MFC application, a better way is to initialize the OLE / COM library environment in the initInstance member function of the application primary class.
Bool cmyadotestapp :: InitInstance () {if (! Afxoleinit ()) // This is initializing the COM library {AFXMESSAGEBOX ("OLE initialization error!"); Return False;}
......
}
3. Introduction to the ADO interface
The ADO library contains three basic interfaces: _ConnectionPTR interface, _commandptr interface and _RecordSetPtr interface. _ConnectionPTR interface returns a recordset or an empty pointer. It is usually used to create a data connection or perform a SQL statement that does not return any result, such as a stored procedure. Returning a recordset using the _ConnectionPTR interface is not a good method. For operations to return records, it is usually implemented with _RecordSerptr. When using _ConnectionPTR operation, you should want to get the number of records to traverse all records, and use _RecordSerptr. _CommandPTR interface returns a recordset. It provides a simple way to perform the stored procedures and SQL statements that returns the record set. When using the _CommandPTR interface, you can use the global _connectionptr interface, or you can use the connection string directly in the _commandptr interface. If you only perform one or more data access, the latter is a better choice. But if you want to access the database frequently and return a lot of records, you should use the global _connectionptr interface to create a data connection, then use the _CommandPTR interface to perform the stored procedure and SQL statements.
_RecordSetPtr is a recordset object. Compared with the above two objects, it provides more control functions to records, such as record lock, cursor control, and the like. Like the _CommandPTR interface, it does not have to use a data connection that has been created, you can use a connection string instead of the Connection member variable assigned to _RecordSetptr, let it create data connections yourself. If you want to use multiple record sets, the best way is to use the Command object to use the global _connectionptr interface that has created a data connection, and then use the _recordsetPTR to perform the stored procedure and SQL statements.
4, use _ConnectionPTR interface _ConnectionPTR is mainly a connection interface, acquiring connection with the database. Its connection string can be written directly, or you can point to an ODBC DSN. . _ConnectionPtr PCONN; IF ("AdoDb.Cronection"))) {AFXMESSAGEBOX ("Create Instance Failed!"); Return;}
CString strsrc; striRC = "driver =" driver = "; strsrc =" suppersoft "; strsrc ="; dataBase = "; strsrc =" mYDB "; strsrc ="; uid = sa; pwd = ";
CString strsql = "INSERT INTO Student (No, Name, SEX, ADDRESS) VALUES (3, 'AAA', 'Male', 'beijing')
_variant_t varsrc (strsrc); _ variant_t varsql; _ bstr_t bstrsrc (strsrc);
IF (Failed (PConn-> Open (BSTRSRC, "," ", - 1))) {AFXMessageBox (" Can not open database! "); pconn.release (); return;}
Colevariant VTOPTIONAL ((long) DISP_E_PARAMNOTFOUND, VT_ERROR);
PCONN-> EXECUTE (_BSTR_T (strsql), & vtoptional, -1); pconn.release ();
AfxMessageBox ("OK!");
5, use the _recordsetPTR interface (to connect SQL Server as an example) _RecordSetPtr pptr; if (pptr.createinstance))) {AFXMESSAGEBOX ("CREATE INSTANCE FAILED!"); Return False;}
CString strsrc; striRC = "driver = SQL Server; Server ="; strsrc = "210.46.141.145"; strsrc = "; dataBase ="; strsrc = "MYDB"; strsrc = "; uid = sa; pwd ="; strsrc = "sa";
CString strsql = "SELECT ID, NAME, GENDER, ADDRESS ADM PERSONAL";
_variant_t varsrc (strsrc); _ variant_t varsql (strsql);
IF (Failed (PPTR-> Open (Varsql, Varsrc, AdopenStatic, AdlockOptimistic, Adptext)) {AFXMessageBox ("Open Table Failed!"); pptr.release (); return false;}
While (! pptr-> getadoeof ()) {_ variant_t varno; _variant_t varname; _variant_t varsex; _variant_t varaddress;
Varno = PPTR-> getCollect ("ID"); varName = pptr-> getCollect ("name"); VARSEX = PPTR-> getCollect ("gender"); varAddress = pptr-> getCollect ("address");
CString strNo = (char *) _ bstr_t (varNo); CString strName = (char *) _ bstr_t (varName); CString strSex = (char *) _ bstr_t (varSex); CString strAddress = (char *) _ bstr_t (varAddress);
Strname.trimright (); straddress.trimright (); straddress.trimright (); STRSEX.TRIMRIGHT ();
Int ncount = m_list.getitemcount (); int nitem = m_list.insertitem (ncount, _t (")); m_list.setitemtext (nitem, 0, strno); m_list.setitemtext (Nitem, 1, strname); m_list.setitemtext (Nitem, 2, Strsex); M_List.SetItemtext (NITEM, 3, Straddress);
PPTR-> MOVENEXT ();
PPTR-> Close (); pptr.release ();
6. Use the _commandptr interface _commandptr interface to return a RecordSet object, and provide more recordset control functions, the following code samples use the _commandptr interface method: code 11: Using the _CommandPTR interface to get data _Commandptr pCommand; _recordsetptr pRs; pCommand.CreateInstance (__ uuidof (Command)); pCommand-> ActiveConnection = pConn; pCommand-> CommandText = "select * from student"; pCommand-> CommandType = adCmdText; pCommand-> Parameters-> Refresh (); pRs = PCOMMAND-> EXECUTE (NULL, NULL, AdcmDunkNown); _ variant_t varValue = PRS-> getCollect ("name"); cstring strval = (char *) _ bstr_t (varvalue);
6. About data type conversion Since the COM object is a cross-platform, it uses a generic method to handle various types of data, so the CSTRING class and COM object are incompatible, we need a set of APIs to convert COM objects And C types of data. _vatiant_t and _bstr_t are the other objects. They provide a general method to convert COM objects and C types of data.
................................................ ........................................
1. Generate an application framework and initialize the OLE / COM library environment to create a standard MFC AppWizard (EXE) application, then initialize the OLE / COM library in the initInstance function using the ADO database (because the ADO library is a COM DLL library). This example is:
Bool Cadotestdlg :: OnNitdialog () {:: Coinitialize (null); // Initializing OLE / COM Library Environment}
The program is finally called :: Couninitialize (); // Release the program occupied COM resources.
In addition:
M_PRecordset-> Close (); note! ! ! Don't close multiple times! ! ! ! ! ! ! ! ! ! ! ! m_pConnection-> close (); m_precordset = null; m_pconnection = null; 2. Introduced ADO library file
Before using ADO, you must introduce the ADO library file with the direct introduction symbol #import with the direct introduction symbol #import so that the compiler can be compiled correctly. The code is as follows: #Import "c: / program files / common files / system / ado / msado15.dll" No_Namespace Rename ") The definition of the ADO class is stored as a resource in ADO DLL (MSADO15. DLL is called a type library in it. The type library describes the autonomous interface, and the COM VTable interface used by C . When using the #import command, Visual C needs to be read from the ADO DLL when running, and create a set of C header files. These header files have .TLI and .TLH extensions, readers can find these two files in the project's directory. The ADO class called in the C program code is defined in these files. The third line of the program indicates that the ADO object does not use the namespace. In some applications, naming conflicts may occur due to objects in the application in the application, there is a need to use the namespace. If you want to use a namespace, you can modify the third line program to: Rename_NameSpace ("Adons"). The fourth line of code is renamed the EOF in the ADO (end) to AdoEOF to avoid conflicts with other library defined for your EOF. 3. Database operation using intelligent pointers
Define two ADO intelligence pointer instances in the CaboutDLG header and add a listCtrl in the dialog.
Class Cadotestdlg: Public CDialog {_ConnectionPtr M_PCONNECTION; _RecordSetPtr M_PRecordset; ClistCtrl M_List; ......}
The ADO library contains three intelligent pointers: _ConnectionPtr, _Commandptr, and _recordsetptr.
_ConnectionPtr is usually used to create a data connection or perform a SQL statement that does not return any result, such as a stored procedure. _Commandptr returns a recordset. It provides a simple way to perform the stored procedures and SQL statements that returns the record set. When using the _CommandPTR interface, you can use the global _connectionptr interface, or you can use the connection string directly in the _commandptr interface. _RecordSetPtr is a recordset object. Compared with the above two objects, it provides more control functions to records, such as record lock, cursor control, and the like.
Inbutton1 Add the following code in the event response using the ADO program:
void CAdotestDlg :: OnButton1 () {m_List.ResetContent (); m_pConnection.CreateInstance (_uuidof (Connection)); // initialize pointer Connection m_pRecordset.CreateInstance (_uuidof (Recordset)); // initialize pointer Recordset try {m_pConnection-> Open ("DSN = Adotest", "," ", 0); // Connection is an ODBC data source // read: This is an open function of the connection that does not require a user ID or password, otherwise the form is -> Open ("DSN = TEST; UID = SA; PWD = 123;", ",", ", 0); // Execute SQL statement to get a recordset to assign its pointer to m_precordset cstring strsql =" select * from middle "; BSTR bstrSQL = strSql.AllocSysString (); m_pRecordset-> Open (bstrSQL, (IDispatch *) m_pConnection, adOpenDynamic, adLockOptimistic, adCmdText); // adOpenDynamic:! adLockOptimistic optimistic blockade dynamic method adCmdText: text query while (m_pRecordset-> adoEOF ) // Traverse all records {// Take a record field value method _variant_t thevalue; // variant data type Thevalue = m_pRecordset-> getCollect ("BIG_NAME"); // Get the value of the field BIG_NAME (thevalue.vt! = Vt_null) m_list.addstring ((char *) _ bstr_t (thevalue))); // Add this value to list control // _BSTR_T thevalue1 = m_precordset-> fields-> GetItem ("BIG_NAME ") -> value; // cstring temp = thevalue1.copy (); // m_list.addstring (Temp); // Data Type Conversion _Variant_t Vusername, Vbirthday, VID, VOLD; Trace ("ID:% D, Name:% s, Age:% D, Birthday:% S / R / N" , Vid.lval, (lpctstr) (_ BSTR_T) VUSERNAME, VOLD.LVAL, (LPCTSTR) (_ bstr_t) vbirthday; m_precordset-> MoveNext (); // Go to Next Record} m_precordset-> close (); m_pconnection- > Close ();} catch (_COM_ERROR E) // Abnormally handled {AFXMessageBox (E.ErrorMessage ());} m_precordset-> close (); // Note! ! ! Don't close multiple times! ! ! ! Otherwise it will be error M_PCONNECTION-> Close (); m_precordset = null; m_pconnection = null;}
The program is converted from _variant_t and _bstr_t to the COM object and the C type data, and _variant_t classes encapsulate the OLE Autonomous Variant data type. Using the _variant_t class in C is much easier than using the Variant data type. Ok, after compiling the program runs, remember to create an ODBC data source called Adotest before running. The program will display the BIG_NAME field value in the table Middle in the list control.
Database programming (medium) 4 in Visual C . Execute the SQL command and acquire the result record set
In order 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: m_precordset.createInstance ("adodb.recordset"); SQL command execution can take a variety of forms, below An explanation.
(1) Execute SQL commands using the Execute method of the Connection object
The prototype of the Execute method is as follows:
_RecordSetPtr Connection15 :: EXECUTE (_BSTR_T Commandtext, Variant * Recordsaffected, Long Options)
Where the CommandText is a command string, usually the SQL command. 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 record M_PConnection-> Execute ("INSERT INTO USERS (ID, Username, Old, Birthday) VALUES (1, '' '' '1970/1 / 1 '' '') ", & Recordsaffected, AdcmdText);
/// add a value of all the records of the OLD field ("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 to put in the vcount variable 2 sentences can be written - _variant_t vcount = m_precordset-> getCollect ((_ variant_t) ((long) 0)); m_precordset-> close ); /// Close the record set cstring message; "a total of% D record", vcount.lval); AFXMessageBox (Message); // / display The current record number (2) Use the Command object to perform 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) Directly use the RecordSet object to obtain record sets
Example -
Void cgmsadlg :: OndbSelect () {// Todo: add your control notification handler code he_RecordSetPtr rs1; // Define RecordSet object_BSTR_T Connect ("DSN = GMS; UID = SA; PWD =;"); / / Define Connection Characters String _BSTR_T SOURCE ("SELECT Count (*) from Buaa.mdb010"); // To execute SQL statement :: Coinitialize (null); // Initialize RS1 object hresul hr = rs1.createInstance (__UUIDOF (Recordset); / / Omitting the return value HR RS1-> Open (Source, Connect, Adopenforwardonly, AdlockRectonly, -1); _VARIANT_T TEMP = RS1-> getCollect (_variant_t (long) 0); cstring straTemp = (char *) _BSTR_T) TEMP; MessageBox ("OK!" strTemp);
E.g. 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
among them:
1Source is a data query string 2ActiveConnection is a established connection (we need to use the Connection object pointer 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, /// does not specify adopenforwardonly = 0, /// front rolled 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 structure:
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 can take one of the values: AdcmdText: Indicates that CommandText is the text command adcmdtable: indicates that CommandText is a table name AdcmdProc: indicating that CommandText is a stored procedure AdcmDunkNown: unknown
10, bonded data
Define a binding class to bind its member variables to a specified recordset to facilitate access to the field value of the recordset.
(1). Delicate a class from cadorecordbinding:
class CCustomRs: public CADORecordBinding {BEGIN_ADO_BINDING (CCustomRs) ADO_VARIABLE_LENGTH_ENTRY2 (3, adVarChar, m_szau_fname, sizeof (m_szau_fname), lau_fnameStatus, false) ADO_VARIABLE_LENGTH_ENTRY2 (2, adVarChar, m_szau_lname, sizeof (m_szau_lname), lau_lnameStatus, false) ADO_VARIABLE_LENGTH_ENTRY2 (4, adVarChar, m_szphone, sizeof (m_szphone), lphoneStatus, true) END_ADO_BINDING () public: CHAR m_szau_fname [22]; ULONG lau_fnameStatus; CHAR m_szau_lname [42]; ULONG lau_lnameStatus; CHAR m_szphone [14]; ULONG lphoneStatus;};
Where the field to be bound to the variable name is used with begin_ado_binding macro. Each field corresponds to two variables, a value of a field, and the status of the field stores. The field is represented by the serial number starting from 1, such as 1, 2, 3, etc.
It is important to note that if the field to be bound is a string type, the number of elements of the corresponding character array must be larger than the field length 2 (such as m_szau_fname [22], the length of the field Au_FNAME is actually actually Is 20), not so binding will fail. I analyzed more 2 may be a word in the end of the empty character NULL and the BSTR string at the end of the string (indicating the length of the BSTR). This issue may be an unexpected problem for beginners.
The definition of the Cadorecordbinding class is in the ICRSINT.H file, the content is:
Class Cadorecordbinding {public: stdmethod_ (const ado_binding_entry *, getadobindingintries) (void) pure;};
BEGIN_ADO_BINDING macro definition also icrsint.h document, content: #define BEGIN_ADO_BINDING (cls) public: / typedef cls ADORowClass; / const ADO_BINDING_ENTRY * STDMETHODCALLTYPE GetADOBindingEntries () {/ static const ADO_BINDING_ENTRY rgADOBindingEntries [] = {
ADO_VARIABLE_LENGTH_ENTRY2 macro definitions are icrsint.h file: #define ADO_VARIABLE_LENGTH_ENTRY2 (Ordinal, DataType, Buffer, Size, Status, Modify) / {Ordinal, / DataType, / 0, / 0, / Size, / offsetof (ADORowClass, Buffer ), / offsetof (adorowclass, status), / 0, / classoffset (cadorecordbinding, adorowclass), / modify},
#define end_ado_binding macro is also in ICRSINT.H file: #define end_ado_binding () {0, Adempty, 0, 0, 0, 0, 0, 0, 0, False}}; / Return RgadobindinTries;}
(2). Bind
_RecordSetPtr RS1; Iadorecordbinding * picrs = null; ccustomus Rs; ... RS1-> queryinterface (__ uuidof (il), (lpvoid *) & picrs); picrs-> bindtorecordset (& RS); Delivery class must pass The IadoRecordbinding interface can be bound to call its Bindtorecordset method.
(3). The variable in the RS is the value of the current record field.
// set sort and filter condition: // step 4: manipulate the datars1-> fields-> GetItem ("au_lname") -> Properties-> GetItem ("Optimize") -> value = true; rs1-> sort = " AU_LNAME ASC "; RS1-> filter =" Phone Like '415 5 *' "
RS1-> MoveFirst (); while (variant_false == rs1-> endoffile) {printf ("Name:% S / T% S / TPHONE:% S / N", (rs.lau_fnamestatus == adfldok? Rs.m_szau_fname: "" ", (RS.LAU_LNAMESTATUS == Adfldok? rs.m_szau_lname:" "), (rs.lphoneStatus == Adfldok? rs.m_szphone:")); if (rs.lphoneestatus == adfldok) STRCPY (RS. " M_SzPhone, "777"); testhr (Picrs-> Update (& RS)); // add change to the bathchrs1-> MoveNext ();} RS1-> filter = (long) Adfilternone; ... IF Picrs) Picrs-> Release (); rs1-> close (); PCONN-> Close ();
As long as the status of the field is Adfldok, you can access it. If you modify the field, don't forget to call the Picrs's Update (notice that the recordset's Update), then close, don't forget to release Picrs (ie picrs-> release ();).
(4). You can also add a new record with the IadorecordBinding interface.
IF (failed (picrs-> addnew (& rs)) ......
11. Access long data
Long data in Microsoft SQL includes TEXT, Image, etc. Type of data, treated as binary bytes.
You can use the GetChunk and Appendchunk methods of the field object. Each time you can read or write a portion of all data, it remembers the location of the last accessed. But if you have access to other fields, you have come from the beginning.
Please see the example below:
// Write a photo to the database: Variant Varchunk; SafeArray * PSA; SafeArraybound Rgsabound [1];
// vt_Array │ vt_ui1cfile f ("h: //aaa.jpg", cfile :: modeRead; byte bval [chunksize 1]; uint uisread = 0; // Create a Safe Array to Store the array of bytes while ( 1) {uisread = f.read (bval, chunksize); if (uisread == 0) Break; rgsabound [0] .CELEMENTS = uisread; rgsabound [0] .llbound = 0; PSA = SafeArrayCreate (vt_ui1, 1, rgsabound) ); for (long index = 0; index
_variant_t varChunk; BYTE buf [ChunkSize]; while (lPhotoSize> 0) {lIsRead = lPhotoSize> = ChunkSize ChunkSize:? lPhotoSize; varChunk = m_pRecordset-> Fields-> Item [ "photo"] -> GetChunk (lIsRead); for ( LONG INDEX = 0; Index 12. Use the SafeArray problem It is also important to learn to use SafeArray because it is often used in ADO programming. Its main purpose is to pass the passage of array parameters in Automation. Because in a network environment, arrays cannot be delivered directly, but they must be packaged into safgonaray. Essentially SafeArray adds a descriptor to add a description of the usual array, indicating that its dimension, length, boundary, element type, etc. SafeArray is not used alone, but is then packaged in a variable of the Variant type, and then transfer it as a parameter. The value of VARIANT's VT member if it contains VT_Array│ ..., then it is packaged is a SafeArray, and its Parray member is a pointer to SafeArray. The type of element in SafeArray can be any type of Variant's encapsulation, including the Variant type itself. Specific steps using SafeArray: method one: Packing a SafeArray: (1). Define variables, such as: Variant Varchunk; SafeArray * PSA; SafeArrayBound Rgsabound [1]; (2). Create a SafeArray descriptor: Uisread = f.read (bval, chunksize); // read array from a file.if (uisread == 0) Break; rgsabound [0] .CELEments = uisread; rgsabound [0] .llbound = 0; PSA = SafeArrayCreate Vt_ui1, 1, rgsabound; (3). Place the data element to SafeArray: For (long index = 0; index A one is released, it is very troublesome. (4). Encapsulate to Variant: VARCHUNK.VT = Vt_Array│VT_UI1; VARCHUNK.PARRAY = PSA; This allows VARCHUNK as a parameter. Steps to read the data in SafeArray: (1). Read by SafeArraygetElement BYTE BUF [Lisread]; for (long index = 0; Index Entry to the buffer BUF. Method Two: Read and write SafeArray buffer using SafearrayAccessData: (1). Read the buffer: BYTE * BUF; SafeArrayAccessData (Varchunk.Parray, (void **) & buf); F.Write (BUF, Lisread); SafearrayunAccessData (Varchunk.Parray); (2). Write buffer: BYTE * BUF; :: SafeArrayAccessData (PSA, (void **) & buf; for (long index = 0; index VARCHUNK.VT = VT_Array│VT_UI1; VARCHUNK.PARRAY = PSA; This method reads and writes SafeArray, it can actually manipulate SafeArray's data buffer, which is fast than SafeArrayGetElement and SafeArrayPutelement. It is especially suitable for reading data. But don't forget to call :: SafeArrayunAccessData (PSA), otherwise it will be wrong. 13. Use bookmarks (Bookmark) Bookmarks can uniquely identify a record in the record set, for quickly transferring the current record back to have accessed records, and filtering, etc. Provider will automatically generate a bookmark for each record in the recordset, and we only need to use it. We can't try to display, modify or compare bookmarks. The ADO's bookmark property represents the current recorded bookmark. Usage steps: (1). Create a variable of a Variant type _variant_t varbookmark; (2). Save the current recorded bookmark value into the variable That is, the current value of the Bookmark property of the record set. Varbookmark = RST-> Bookmark; (3). Return to previous records Set saved bookmarks to the bookmark properties of the recordset: // Check for WHETHER BOOKMARK SET for a Recordif (varbookmark.vt == vt_empty) Printf ("No Bookmark Set! / N"); Else Rst-> Bookmark = varbookmark; After the setup, the current record will move to the record pointed to the bookmark. 14, set filter conditions The Filter property of the Recordset object represents the current filtering condition. Its value can be a conditional expression (excluding WHERE keyword) that is connected by AND or OR, which is provided by a group of bookmarks or a FiltergroupENum enumeration value provided by ADO. After setting a new value for the Filter property, the current record pointer of the Recordset will be automatically moved to the first record that meets the filter criteria. E.g: RST-> filter = _bstr_t ("Name = 'Zhao Wei' and Gender = 'Female'"); Pay attention to the following questions when using conditions expressions: (1), can form complex expressions with parentheses E.g: RST-> filter = _bstr_t ("(Name = 'Zhao Wei' AND Gender = 'Female') ORE <25"); However, Microsoft does not allow in brackets to be used in brackets, and then uses AND, for example: RST-> filter = _bstr_t ("(Name = 'Zhao Wei' Or Gender = 'Female') And Age <25"); Must be modified to: RST-> filter = _bstr_t ("(Name = 'Zhao Wei' And Age <25) OR (Gender = 'Female' And Age <25)") (2), the comparison operators in the expression can be like LIKE After the Like is compared, a string containing wildcard *, as an asterisk represents several arbitrary characters. The head and tail of the string can be taken as an asterisk * RST-> filter = _bstr_t ("Name Like '* Zhao *'); It can also be just the tail with star: RST-> filter = _bstr_t ("Name Like 'Zhao *'); The type of Filter property is Variant. If the filtering condition is an array composed of bookmarks, you need to convert the array to SafeAry, and then package it into a variant or _variant_t type variable, then assign it to the Filter property. 15, index and sort (1) Establish an index When searching for a Find method with a field, in order to speed up the speed, you can temporarily establish an index in the recordset in this field. As long as the Optimize property of this field is set to TRUE, for example: PRST-> FIELDS-> GetItem ("Name") -> Properties-> GetItem ("Optimize") -> PutValue ("true"); prSt-> Find ("Name = 'Zhao Wei'", 1, AdSearchForward); ... prSt-> Fields-> GetItem ("Name) -> Properties-> GetItem (" Optimize ") -> PutValue (" false "); prSt-> Close (); Description: The Optimize property is the property provided by the Provider (called dynamic properties in ADO), and the ADO itself does not have this property. (2), sort To sort it very simple, just set the keyword list to be sorted to the sort property of the Recordset object, for example: pRstAuthors-> CursorLocation = adUseClient; pRstAuthors-> Open ( "SELECT * FROM mytable", _ variant_t ((IDispatch *) pConnection), adOpenStatic, adLockReadOnly, adCmdText); ...... pRst-> Sort = "name DESC, ASC "; The keyword (ie the field name) is separated by a comma. If you want to sort in a keyword descending order, you should add a null after the keyword, add DESC (such as above). Asc adding does not care when ascending. This operation is made by the index and does not perform physical sorting, so the efficiency is higher. But note that set the CursorLocation property of the record set to AduseClient before opening the record set, as shown in the example. The sort attribute value can be modified at any time when needed. 16, transaction processing The transaction processing in the ADO is also very simple, and only three methods of the Connection object are called in the appropriate location, these three methods are: (1), call at the beginning of the transaction PCNN-> Begintrans (); (2), call when the transaction is over and successfully PCNN-> CommitTrans (); (3) When the transaction ends and failures PCNN-> rollbacktrans (); When using transaction processing, you should minimize the range of transactions, that is, reduce the time interval between transactions until the end (submission or rollback) to improve system efficiency. You can also set the ISOLATIONLEVEL attribute value of the Connection object before you call the Begintrans () method, and see the technical information about ADO in MSDN for details. Third, using ADO programming FAQ The following is discussed for the issues of MS SQL 7.0 programming. 1. Connection failure possible cause In Enterprise Managemer, open the property dialog box, in the Security tab, there is an option Authentication. If this option is Windows NT Only, the connection string used by your program must include the trusted_connection parameter, and its value must be yes, such as: "Provider = SQLOLEDB; Server = 888; trusted_connection = yes" "; database = master; uid = lad;"; If you do not follow the above, the program is running will inevitably failed. If the Authentication option is SQL Server and Windows NT, the connection string used by your program may not contain a trusted_connection parameter, such as: "Provider = SQLOLEDB; server = 888; database = master; uid = lad; pwd = 111;" Because the default value given by the ADO is NO, it can be omitted. I think it is still safe to take default. 2, change the method of the current database Use the USE statement in tansct-sql. 3. How to determine if a database exists (1) You can open a view called Schemata in the Master database, which lists all of the database names on the server. (2), the easier method is to use the USE statement, the existence is successful; unsuccessful, there is no existence. E.g: try {m_pConnect-> Execute (_bstr_t ( "USE INSURANCE_2002"), NULL, adCmdText│adExecuteNoRecords);} catch (_com_error & e) {blSuccess = FALSE; CString str = "database INSURANCE_2002 absence / n!"; str = e. Description (); :: MessageBox (NULL, STR, "Warning", MB_OK │ MB_ICONWARNING); 4. Judge whether a table exists (1), also determine whether a table is present, or it can also be used to successfully open it to determine, very convenient, for example: try {m_pRecordset-> Open (_variant_t ( "mytable"), _ variant_t ((IDispatch *) m_pConnection, true), adOpenKeyset, adLockOptimistic, adCmdTable);} catch (_com_error & e) {:: MessageBox (NULL, "it does not exist "," Tips ", MB_OK │ MB_ICONWARNING); (2), otherwise the method can be used, that is, there is a table named SysObjects in each database on the MS-SQL server, and see if the contents of this table know if the specified table is in the database. (3), in the same, there is a view called Tables (View) in each database, see if the content of this view is known to be specified in the database. 5, type conversion problem (1), type variant_bool Type variant_bool equivalent to the short type. THE VARIANT_BOOL IS Equivalent to short. See it's definition BELOW: TYPDEF Short Variant_BOOL (2), _ COM_PTR_T class Type Conversion _ConnectionPTR can be automatically converted into idspatch * type, because _ConnectionPtr is actually an instance of the _com_ptr_t class, and this class has this type of conversion function. Similarly, _RecordSetPtr and _CommandPTR can also be converted. (3), _ BSTR_T and _variant_t class When ADO programming, _bstr_t and _variant_t are useful, save a number of BSTR and Variant type conversion. 6. Open the recording scale When the recording set is turned on, when the RECORDSET's Open method is called, the last parameter must not contain AdAsynceXecute, otherwise, because it is an asynchronous operation, data cannot be read when reading data. 7, abnormal processing problem The statement for all calling ADOs must be captured with TRY and CATCH statements, otherwise the program will exit when an exception occurs. 8, use the SafeArray problem In the initial use, I have encountered a problem of hurting my brains, I must pay attention to: After defining the SafeArray pointer, if you are intended to repeat multiple times, you can call :: SafeArrayDestroyData release data, but never call: SafeArrayDestroyDescriptor, otherwise it is wrong, even if you call SafeArrayCreate. E.g: SafeArray * psa; ... // when the data area ::: SafeArrayDestroydata (PSA); When I analyzed when defining the PSA pointer, an example of a SafeArray (that is, the SafeArray descriptor) is also automatically created. But as long as one call :: SafeArrayDestroyDescriptor, the descriptor is destroyed. So I think :: SafeArrayDestroyDescriptor can not be called at all, even if the call must also be called. 9, repeat the command object problem A command object If you want to repeat multiple times (especially with parameter commands), you should set it to true before the first execution is executed. This will slow down the first execution, but it can be accelerated in the future implementation. 10, binding string string field problem If the field to be bound is a string type, the number of elements of the corresponding character array must be larger than the field length 2 (such as m_szau_fname [22], the length of the field Au_FNAME is actually 20), not like this Binding will fail. 11. Problems using appendchunk When a new record is just added to the recordset using the AddNew method, it is not possible to write data to a long data field (Image Type), and you must first write the data to the data to write this field, otherwise an error . That is, Appendchun cannot be followed immediately after addNew. In addition, after writing other fields, you must also call Appendchunk, and after calling the UPDATE method of the record set, the appendchunk is called, otherwise it will be wrong when you call appendchunk. In other words, it is necessary to appendchunk before, update is behind. Thus, this time, I can't use AddNew, because the addNew with parameters will automatically call the record set Update, so appendchunk runs back to Update, only wrong! Therefore, this should be used with ADDNEW without parameters. I speculate that this may be a problem with MS SQL 7.0, and there is no problem in MS SQL 2000, but Appendchunk still cannot be after Update. Fourth, small knot Under normal circumstances, the execute of Connection and Command is used to perform commands that do not generate the record set, and the RECORDSET's OPEN is used to generate a recordset, of course, is not absolute. Special Command is mainly used to perform parameterized commands, can be executed directly by the Command object, or the Command object can be passed to the RECORDSET Open. The code snippet in this article is debugged in VC 6.0, Windows NT 4.0 SP6 and MS SQL 7.0. I believe that you should have a simple database program after you read it. Of course, you need to write more practical, complicated programs, you need to know more about Ole DB, ADO, and database platforms, I hope you will continue to work, you will be successful! For details, please refer to the Microsoft MSDN July 2000 CD or MS SQL 7.0 Online Data (Books Online). It is inevitable that there is a mistake and wrong in the article. ................................................ .......................................... ADO Database Connection - Example Analysis - VC Article Source: Bean Technology Network Release time: 2005-12-27 These days have been learning ADO database connections, which feel more complicated, so they learn, while doing, do some notes, below, some things I have summed up about ADO database connection, because it is notes, so I don't necessarily Very orderly, the meaning of the stickers, I can give you a reference. I also hope that everyone will improve together, and the mistakes and deficiencies, I hope everyone can inform me in time, when I came, I have An idea, because I am in the process of use, I always encounter some Ming's wonderful mistakes, so I think everyone will meet, so I gave an error summary, of course, you need everyone here. Let's be complete, if you have any experiences and insights, I hope to leave a message in the comments, I will accept everyone's opinion and add them in time (of course, it is allowed to get your permission) 1, import library file Before using ADO, you must introduce the ADO library file with the direct introduction symbol #import with the direct introduction symbol #import so that the compiler can be compiled correctly. The code is as follows: #import "c: program filescommon filesysystemadomsado15.dll" NO_NAMESPACE RENAME ("EOF", "Endoffile") Rename ("Bof", "Firstoffile") The definition of the ADO class is to be stored in ADO DLL (MSADO15.DLL) as a resource in its inside. The type library describes the autonomous interface, and the COM VTable interface used by C . When using the #import command, Visual C needs to be read from the ADO DLL when running, and create a set of C header files. These header files have .TLI and .TLH extensions, readers can find these two files in the project's directory. The ADO class called in the C program code is defined in these files. The third line of the program indicates that the ADO object does not use the namespace. In some applications, naming conflicts may occur due to objects in the application in the application, there is a need to use the namespace. If you want to use a namespace, you can modify the third line program to: Rename_NameSpace ("Adons"). The fourth line of code is renamed the EOF in the ADO (end) to AdoEOF to avoid conflicts with other library defined for your EOF. 2, initialize the COM environment (1) :: Coinitialize (null); // Initialize the OLE / COM library environment :: Couninitialize (); // Since initializing the environment, there is of course necessary to release him (2) You can also call MFC global functions Afxoleinit (); 3, the definition of the three major objects and the creation of instances (1) _ConnectionPtr PConnection ("AdoDb.Connection"); _RecordSetPtr Prcordset ("AdoDb.Recordset"); _Commandptr PCOMMAND ("Adodn.command"); (2) _CONNECTIONPTR PCONNECTION; _RecordSetPtr PRecordset; _Commandptr pCommand; PCONNECTION.CREATEINSTANCE (__ uuidof (connection)); PRecordSet.createInstance (__ uuidof (recordset); PCOMMAND.CREATEINSTANCE (__ uuidof (command)); (3) _CONNECTIONPTR PCONNECTION; _RecordSetPtr PRecordset; _Commandptr pCommand; PConnection.createInstance ("AdoDb.Connection"); PRecordSet.createInstance ("AdoDb.Recordset"); PCOMMAND.CREATEINSTANCE ("AdoDb.command"); 4, open a connection PConnection-> Open (connectionstring, ",", admodeunknown); /// connection database The above connection string connectstring corresponds to different ways according to different data sources. 1) Access Access 2000 "provider = microsoft.jet.Oledb.4.0; data source = databasename; user id = username; password = userpassword" 2) Access ODBC data "Provider = madasql; dsn = dsnname; uid = username; pwd = userpassword;" 3) Access Oracle Database "Provider = msdara; data source = servername; user id = username; password = userpassword;" 3) Access MS SQL Database "Provider = SQLOLEDB, DATA SOURCE = ServerName; Initial Catalog = DatabaseName; User ID = UserName; Password = UserPassword;" 4. Execute SQL commands There are more SQL commands, but don't consider the details, here only say the general method CString strsql; / / Define the SQL command string to save the SQL statement STRSQL.FORMAT ("SQL Statement"); Then use strsql.allocsystring () to make type conversion using strsql.allocsystring in methods to use the SQL command string. 5, custom data type of COM Variant, BSTR, SafeArray The range of Variant variables includes a lot, using _variant_t management BSTR is a string variable, manages _BSTR_T 6, turn off the connection If (m_pconnection-> state) // cannot be closed multiple times, it will eventually occur m_pConnection-> close (); 7, structured abnormal processing ADO encapsulates the COM interface, so you need to make an error handling Such examples: HRESULT HR; Try { HR = m_pconnection.createInstance ("AdoDb.Connection"); // Create a Connection object En (ac)) { HR = m_pconnection-> open ("provider = microsoft.jet.Oledb.4.0; data source = test.mdb", ",", ", admodeunknown); /// connection database /// The provider in the connection string in the above sentence is for the Access2000 environment, for Access 97, it needs to be changed to: provider = microsoft.jet.Oledb.3.51;} } Catch (_COM_ERROR E) /// Capture Exception { CString ErrorMessage; ErrorMessage.Format ("Connection Database Failed! Error Message:% S", E.ErrorMessage ()); AFXMessageBox; /// Display error message } 8. Analysis of the cause of error (1) Do not support the interface, may not be inserted into null values
