Introduction to Visual C ++ ADO database programming (I)

 

ADO is a popular client database programming technology in windows. ADO is an advanced programming interface built on the underlying technology of ole db, so it also has powerful data processing functions (processing various types of data sources, distributed data processing, etc) and an extremely simple and easy-to-use programming interface, so it has been widely used. In addition, according to Microsoft's intention, ole db and ADO will gradually replace ODBC and Dao. There are many articles and books about various ADO applications. This article focuses on the issues of using ADO programming in VC ++ from the perspective of beginners. Before reading this article, we hope you will have some knowledge about the basic principles of ADO technology.

　　1. Use ADO programming in VC ++

ADO is actually a component composed of a group of automation objects, so you can use ADO like any other automation objects. There are three most important objects in ADO: connection, command, and recordset, which respectively indicate the connection object, command object, and record set object. If you are familiar with the ODBC class (cdatabase, crecordset) programming in MFC, it is very easy to learn ADO programming.

You can use one of the following three methods to program using ADO:

1. Run the pre-processing command # Import

# Import "C:/program files/common files/system/ADO/msado15.dll "/ No_namespace Rename ("EOF", "endoffile ")

But note that it should not be placed at the beginning of the stdafx. h file, but should be placed behind all the include commands. Otherwise, an error occurs during compilation.
During program compilation, VC ++ reads the Type Library Information in msado15.dll, automatically generate the header files and implementation files of this type of library msado15.tlh and msado15.tli (under your debug or release directory ). These two files define all the objects and methods of ADO and some enumerated constants. Our program only needs to call these methods directly, which is similar to calling automation objects using the coledispatchdriver class in MFC.

2. Use cidispatchdriver in MFC

It is to create a coledispatchdriver class derived class by reading the Type Library Information in msado15.dll, and then call the ADO object through it.

3. directly use the APIS provided by com

Use the following code:

CLSID; Hresult hR =: clsidfromprogid (L "ADODB. Connection", & CLSID ); If (failed (HR )) {...} : Cocreateinstance (CLSID, null, clsctx_server, iid_idispatch, (void **) & Pdispatch ); If (failed (HR )) {...}

The first and second methods are similar. The first method may be easier to use, and the third method may be the most troublesome. However, the third method may also be the most efficient, with the smallest program size and the strongest ADO control capability.

According to Microsoft information, the first method does not support default parameters in method calls. Of course, the second method does, but the third method does not. The level of the third method is also the highest. When you need to bypass ADO and directly call the underlying method of ole db, you must use the third method.

The key to ADO programming is to skillfully use the various objects, methods, properties, and collections provided by ADO ). In addition, if you are programming on large databases such as ms SQL or Oracle, you must be familiar with the SQL language.

　　Ii. Use # import method programming steps

We recommend that you use the # import method because it is easy to learn and use, and the code is concise.

1. Add # import command

Open the stdafx. h file and add the following content to all the include commands:

# Include <icrsint. h> // include support for VC ++ extensions # Import "C:/program files/common files/system/ADO/msado15.dll "/ No_namespace Rename ("EOF", "adoeof ")

Here, the icrsint. h file contains some pre-processing commands and macros extended by VC ++, which are used for COM programming.

2. Define the _ connectionptr variable and establish a database connection.

After you establish a connection with the database server, you can access and operate other databases. Ado uses the connection object to establish a connection with the database server, so it is equivalent to the cdatabase class in MFC. Like the cdatabase class, you can call the open method of the connection object to establish a connection with the server.

The Data Type _ connectionptr is actually a specific instance class obtained by the class template _ com_ptr_t. Its definition can be found in the msado15.tlh, comdef. h and comip. H files. In msado15.tlh, there are:

_ Com_smartptr_typedef (_ collection, _ uuidof (_ collection ));

The _ connectionptr class is obtained after macro expansion. The _ connectionptr class encapsulates the idispatch interface pointer of the connection object and some necessary operations. We use this pointer to manipulate the connection object. Similarly, the _ commandptr and _ recordsetptr types used later are also obtained. They represent the command object pointer and the record set Object Pointer respectively.

(1) connect to ms SQL Server

Note that the format of the connection string. Providing the correct connection string is the first step to successfully connect to the database server. For more information about the connection string, see the Microsoft msdn library CD.

In this example, SERVER_NAME, database_name, user_name, and password in the connection string should be replaced with the actual content during 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 that the connection string parameter in the open method of the connection object must be of the BSTR or _ bstr_t type. In addition, in this example, the connection is established directly through ole db provider, so no data source needs to be created.

(2) connect to the database server using ODBC driver. The format of the connection string is similar to that used for ODBC programming:

_ Bstr_t strconnect = "DSN = performance_name; database = database_name; uid = user_name; Pwd = password ;";

As with ODBC programming, you must first create a data source.

3. Define the _ recordsetptr variable and open the dataset.

Define the _ recordsetptr variable and call the open method of the recordset object to open a dataset. Therefore, the recordset object is similar to the crecordset class in MFC. It also has the concept of the current record and the current record pointer. For example:

_ 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, true), adopenkeyset, Adlockoptimistic, adcmdtable ); } Catch (_ com_error & E) { : MessageBox (null, "mytable cannot be opened. "," Prompt ", Mb_ OK │ mb_iconwarning ); }

The open method of the recordset object is very important. Its first parameter can be an SQL statement, a table name, or a command object; the second parameter is the pointer to the previously established connection object. In addition, use the execute method of the connection and command object to obtain the record set, but read-only.

4. Read the data of the current record

I think the most convenient way to read 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 = (short) (m_precordset-> fields-> getitem (_ Variant_t ("Age")-> value ); // Do something what you want to do: ...... M_precordset-> movenext (); } } // Try Catch (_ com_error & E) { Cstring STR = (char *) E. Description (); : MessageBox (null, STR + "/n again. "," Prompt ", Mb_ OK │ mb_iconwarning ); }

In this example, both name and age are field names. The read Field Values are stored in the sname and cage variables respectively. In this example, fields is the container of the recordset object. The getitem method returns the Field object, while the value is an attribute of the Field object (that is, the value of this field ). In this example, you should know how to manipulate object attributes. For example, to obtain the value of the Field object's value attribute, you can directly use the attribute name value to reference it (in the preceding example), but you can also call the get method, for example:

Cstring sname = (char *) (_ bstr_t) (m_precordset-> fields-> getitem (_ Variant_t ("name")-> getvalue ());

From this example, we can also see whether to determine whether it reaches the end of the record set and use the adoeof attribute of the record set. If the value is true, the end is reached, and vice versa, the end is not reached. You can use the BOF attribute to determine whether it has reached the beginning of the record set.

In addition, another way to read data is to define a bound class, and then obtain the field value through the bound variable (see the description below ).

5. modify data

Method 1:

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

Changed the value of the value attribute, that is, the value of the field.

Method 2:

M_precordset-> fields-> getitem (_ Variant_t ("name")-> putvalue (_ bstr_t ("Zhao Wei "));

Method 3: Define the binding class (see the following description ).

6. Add records

After the new record is successfully added, it automatically becomes the current record. The addnew method has two forms: one containing parameters, and the other without parameters.

Method 1 (without parameters ):

// Add new record into this table: Try { If (! M_precordset-> supports (adaddnew) return; 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 (); } // Try Catch (_ com_error & E) { : MessageBox (null, "another problem occurs. "," Prompt ", mb_ OK │ mb_iconwarning ); }

After this method is completed, you must call Update ().

Method 2 (with parameters ):

_ Variant_t varname [4], narvalue [4]; Varname [0] = l "name "; Varname [1] = l "gender "; Varname [2] = l "Age "; Varname [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 Array Safearraybound 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 array Hresult HR1 = s_ OK .hr2 = s_ OK; For (long I = 0; I <ncrit & succeeded (HR1) & succeeded (HR2); I ++) { HR1 = safearrayputelement (psaname, & I, & varname [I]); HR2 = safearrayputelement (psavalue, & I, & narvalue [I]);} // Initialize and fill the safearray Variant vsaname, vsavalue; Vsaname. Vt = vt_variant │ vt_array; Vsavalue. Vt = vt_variant │ vt_array; V_array (& vsaname) = psaname; // & vsaname-> parray = psaname; // See definition in oleauto. h file. V_array (& vsavalue) = psavalue; // Add a new record: M_precordset-> addnew (vsaname, vsavalue );

This method does not need to call update, because after it is added, ADO will automatically call it. This method is difficult to use safearray.

Method 3: Define the binding class (see the following description ).

7. delete records

You can call the delete method of recordset to delete the current record. For more information about Delete usage, see references.

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? ", "Prompt", mb_yesno │ mb_iconwarning) = idyes) { M_precordset-> Delete (adaffectcurrent ); M_precordset-> Update (); } M_precordset-> movenext (); } } // Try Catch (_ com_error & E) { : MessageBox (null, "another problem occurs. "," Prompt ", mb_ OK │ mb_iconwarning ); }

8. Use a command with Parameters

The command object represents commands that a provider can understand, such as SQL statements. The key to using the command object is to set the statement indicating the command to the commandtext attribute, and then call the execute method of the command object. In general, you do not need to use parameters in the command, but sometimes you can use parameters to increase the flexibility and efficiency.

(1) Establish connections, command objects, and record set objects

In this example, the command statement is an SQL statement (select statement ). Question mark in the SELECT statement? It indicates a parameter. If multiple parameters are required, multiple question marks are placed. 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,-1 ); // CREATE command object Statement 1.createinstance (_ uuidof (command )); Cmd1-> activeconnection = conn1; Cmd1-> commandtext = _ bstr_t ("select * From mytable where age <? "); } // Try

Note that the command object must be associated with the connection object to take effect. In this example, set the activeconnection attribute of the command object to the pointer of the connection object, that is, for this purpose:

Cmd1-> activeconnection = conn1;

(2) create a parameter object and assign a value to the parameter

// Create parameter object Param1 = 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 using the command object method. If the length parameter (third) is of a fixed length type, enter-1, for variable lengths such as strings, enter the actual length. Parameters is a container of the command object. Its append method is to append the created parameter object to the container. The append parameters correspond to the question marks in the SQL statement from left to right in sequence.

(3) run the command to open the record set

// Open recordset object RS1 = cmd1-> execute (& vtempty, & vtempty2, ad1_text );

Note that the recordset obtained using the command and connection object execute method is read-only. Before enabling recordset, we cannot set its locktype attribute (its default value is read-only ). However, setting locktype does not take effect after it is enabled.

I found that after getting the record set RS1 using the above method, not only the records in RS1 cannot be modified, even if you directly use SQL statements to modify any records in the same table.

To modify the data, you must use the recordset open method, for example:

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 really good. The first parameter can be an SQL statement, table name, command object pointer, and so on.

9. Respond to ADO notification events

A notification event means that when a specific event occurs, the provider notifies the customer program. In other words, the provider calls a specific method (that is, the event processing function) in the customer program ). To respond to an event, the most important thing is to implement the event processing function.

(1). derive a class from the connectioneventsvt Interface

To respond to notification events of _ connection, a class should be derived from the connectioneventsvt interface:

Class cconnevent: Public connectioneventsvt { PRIVATE: Ulong m_cref; Public: Cconnevent () {m_cref = 0 ;}; ~ Cconnevent (){}; Stdmethodimp QueryInterface (refiid riid, void ** GMM ); 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 ); ...... };

(2). Implement the processing function of each event (all methods with the raw _ prefix implement it ):

Stdmethodimp cconnevent: raw_infomessage ( Struct Error * perror, Eventstatusenum * adstatus, Struct _ connection * pconnection) { * Adstatus = adstatusunwantedevent; Return s_ OK; };

Some methods are not required, but must be implemented. Simply return a s_ OK value. However, to avoid frequent calls, set the adstatus parameter to adstatusunwantedevent. After this call, it will not be called.
You must also implement the QueryInterface, addref, and release methods:

Stdmethodimp cconnevent: QueryInterface (refiid riid, void ** GMM) { * GMM = NULL; If (riid = _ uuidof (iunknown) │ Riid = _ uuidof (connectioneventsvt) * GMM = this; If (* GMM = 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 responding to notification events

// Start using the Connection events Iconnectionpointcontainer * 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 (connectionevents), & PCP ); Pcpc-> release (); If (failed (HR) return; Pconnevent = new cconnevent (); HR = pconnevent-> QueryInterface (_ uuidof (iunknown), (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 to say, do these things before the connection (open.

(4). stop responding to notification events

Pconn-> close (); // Stop using the Connection events HR = pconn-> QueryInterface (_ uuidof (iconnectionpointcontainer ), (Void **) & pcpc ); If (failed (HR) return; HR = pcpc-> findconnectionpoint (_ uuidof (connectionevents), & 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.