Introduction to Visual C + + ADO Database programming (top)

ado|c++|visual| Programming | data | Database ADO is the most popular client database programming technology in the Windows environment at present. ADO is a high-level programming interface built on the OLE DB underlying technology, so it has powerful data processing functions (dealing with various types of data sources, distributed processing, etc.) and extremely simple and easy-to-use programming interface, so it has been widely used. And according to Microsoft's intentions, OLE DB and ADO will gradually replace ODBC and DAO. Now introduce ADO various applications of articles and books have a lot of, this article focus on the beginner's point of view, briefly discuss the use of ADO programming in VC + + some problems. We hope that before reading this article, you have some understanding of the fundamentals of ADO technology.

I. Use ADO programming in VC + +

ADO is actually a component of a set of automation objects, so you can use ADO just as you would with any other automation object. The most important objects in ADO are three: Connection, command, and Recordset, which represent the connection object, the Commands object, and the Recordset object, respectively. If you are familiar with the use of ODBC classes in MFC (CDatabase, CRecordset) programming, then learning ADO programming is very easy.

You can use ADO programming with one of the following three methods:

1, the use of preprocessing instructions #import

#import "C:\Program files\common Files Ystem\ado\msado15.dll" \
No_namespace rename ("EOF", "Endoffile")
Note, however, that you cannot put it at the beginning of the StdAfx.h file, but you should put it behind all include directives. Otherwise, there will be errors at compile time.
Program in the compilation process, VC + + will read out the type library information in Msado15.dll, automatically generate two of the type library header files and implementation files Msado15.tlh and Msado15.tli (in your debug or release directory). All the objects and methods of ADO are defined in these two files, as well as the constants of some enumerated types. Our programs just call these methods directly, which is very similar to calling Automation objects using the COleDispatchDriver class in MFC.

2, the use of MFC Cidispatchdriver

is by reading the type library information in the Msado15.dll, creating a derived class of the COleDispatchDriver class, and then invoking the ADO object through it.

3, directly with the API provided by 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);
if (FAILED (HR))
{...}
The above three methods, first and second similar, may be the first to use some, the third programming may be the most troublesome. But perhaps the third method is also the most efficient, the program is the smallest size, and the ability to control ADO the most powerful.

According to Microsoft information, 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 third approach is also the highest level. When you need to bypass ADO and call the OLE DB bottom method directly, you must use a third method.

The key to ADO programming is to skillfully use the various objects (object), methods (method), properties, and Containers (collection) provided by ADO. Also, if you are programming on a large database such as MS SQL or Oracle, you need to be proficient with the SQL language.


Second, the use of the #import method of programming steps

It is recommended that you use the #import method, because it is easy to learn, Easy-to-use, and the code is relatively concise.

1, add #import instructions

Open the StdAfx.h file and add the following content to all include directives:

#include <icrsint.h>//include Support for VC + + Extensions
#import "C:\Program files\common Files Ystem\ado\msado15.dll" \
No_namespace rename ("EOF", "adoeof")
The icrsint.h file contains the definition of some preprocessing instructions, macros, etc. of VC + + extension, which is used in COM programming.

2, the definition of _connectionptr variables, and establish a database connection

After you have established a connection to the database server, you can access and manipulate other related databases. ADO uses the Connection object to establish a connection to the database server, so it is equivalent to the CDatabase class in MFC. As with the CDatabase class, calling the open method of the Connection object establishes a connection to the server.

The data type _connectionptr is actually a concrete instance class derived from the class template _com_ptr_t, and its definition can be found in the three files Msado15.tlh, comdef.h, and Comip.h. In the MSADO15.TLH, there are:

_com_smartptr_typedef (_collection, __uuidof (_collection));
After macro expansion, we get the _connectionptr class. The _connectionptr class encapsulates the IDispatch interface pointers for connection objects, and some of the necessary operations. This is the pointer that we use to manipulate the connection object. Similarly, the following _commandptr and _RecordsetPtr types are also obtained, which represent pointers to command object pointers and Recordset objects, respectively.

(1), connecting to MS SQL Server

Note The format of the connection string, providing the correct connection string is the first step to successfully connect to the database server, see the Microsoft MSDN Library CD for more information on connection strings.

In this example, the Server_name,database_name,user_name and password in the connection string should be replaced with 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 a BSTR or _bstr_t type. In addition, this example establishes a connection directly through the OLE DB provider, so there is no need to establish a data source.

(2) The connection to the database server connection string via ODBC driver is similar to that used when programming directly with ODBC:

_bstr_t strconnect= "Dsn=datasource_name; Database=database_name; Uid=user_name; Pwd=password; ";
At this point, as with ODBC programming, you must first establish a data source.

3, the definition of _recordsetptr variables, and open the dataset

You can open a dataset by defining the _RECORDSETPTR variable, and then by calling the Recordset object's Open method. So the Recordset object is similar to the CRecordset class in MFC, and it has the current record and 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,true), adOpenKeyset,
adLockOptimistic, adCmdTable);
}
catch (_com_error &e)
{
:: MessageBox (NULL, "Unable to open mytable table.") "," hint, "
mb_ok│mb_iconwarning);
}
The Open method of the Recordset object is very important, its first argument can be an SQL statement, a table name or a command object, and so on; the second parameter is a pointer to the connection object that was established previously. In addition, the connection and command object's Execute method also gets the recordset, but is read-only.


4, read the current record of data

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 your want to do:
......
M_precordset->movenext ();
}
}//try
catch (_com_error &e)
{
CString str= (char*) e.description ();
:: MessageBox (null,str+ "\ n) It's gone wrong again." "," hint, "
mb_ok│mb_iconwarning);
}
The name and age in this example are field names, and the Read field values are stored in the sname and cage variables respectively. The fields in the example is the container for the Recordset object, the GetItem method returns a Field object, and value is a property of the Field object (that is, the value of the fields). Through this example, we should master the method of manipulating object properties. For example, to get the value of a Field object, you can refer to it directly by using the property name value (as in the previous example), but you can also call the Getting method, for example:

CString sname= (char*) (_bstr_t) (M_precordset->fields->getitem
(_variant_t ("name"))->getvalue ());
From this example, you can also see whether the end of the recordset is reached, the Adoeof property of the recordset is used, and the value is true to the end and vice versa. The BOF property is available when determining whether to start the recordset.

In addition, there is another way to read data, which is to define a bound class and then get the value of the field through the bound variable (described later).

5, modify the 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
Changed the value of the values property, that is, the value of the field changed.

Method Two:

M_precordset->fields->getitem
(_variant_t ("name"))->putvalue (_bstr_t ("Zhao Wei"));
Method Three: is to define the binding class method (see later).

6, add the record

When a new record is added successfully, it becomes the current record automatically. There are two forms of the AddNew method, one with parameters and the other without parameters.

Method one (with no parameters):

ADD new record to 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, "wrong again.") "," hint ", mb_ok│mb_iconwarning);
}
This method is finished and calls update ().

Method two (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;
The 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 ADO will call it automatically when it is added. This method is mainly to use SAFEARRAY very troublesome.

Method Three: is to define the binding class method (see later).

7, delete the record

The Delete method of the recordset is called, and the current record is deleted. For additional uses of Delete, please refer to the 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+" \ nthe deletion of her?) ",
"Hint", mb_yesno│mb_iconwarning) ==idyes)
{
M_precordset->delete (adaffectcurrent);
M_precordset->update ();
}
M_precordset->movenext ();
}
}//try
catch (_com_error &e)
{
:: MessageBox (NULL, "wrong again.") "," hint ", mb_ok│mb_iconwarning);
}
8, using the command with parameters

The Command object represents a provider can understand commands, such as SQL statements. The key to using the command object is to set the statement that represents the command into the CommandText property, and then call the command object's Execute method. In general, you do not need to use parameters in a command, but sometimes you can increase its flexibility and efficiency by using parameters.

(1). Establish connections, command objects, and Recordset objects

The statement in this example that represents a command is an SQL statement (a SELECT statement). The question mark in the SELECT statement is the argument, and if you want multiple arguments, put a few more question marks, and each question mark represents an argument.

_connectionptr Conn1;
_commandptr Cmd1;
Parametersptr *params1 = NULL; Not a 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 Object
Cmd1.createinstance (__uuidof (Command));
Cmd1->activeconnection = Conn1;
Cmd1->commandtext = _bstr_t ("select * from MyTable WHERE age<?");
}//try
To be aware that a command object must be associated with a connection object to work, this example sets the ActiveConnection property of the Command object to a pointer to the connection object, 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 by using the method of the Command object, where the length argument (the third) is a fixed-length type, fill in 1, and if the variable length is a string, fill in the actual length. Parameters is a container for a command object, and its Append method is to append the created Parameter object to the container. The parameters in the append correspond sequentially to the question mark in the SQL statement from left to right.

(3). Execute command to open a recordset

Open Recordset Object
RS1 = Cmd1->execute (&vtempty, &vtempty2, adCmdText);
Note, however, that the recordset obtained with the command and connection object's Execute method is read-only. Because before we open the recordset, we cannot set its LockType property (its default value is read-only). Setting LockType after opening does not work.

I found that by using the above method to get the recordset Rs1, not only the records in RS1 cannot be modified, even if you modify any record in the same table directly with SQL statements.

If you want to be able to modify the data, use the 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. "," hint ", mb_ok│mb_iconwarning);
}
The Open method of the Recordset object is so good that the first argument can be an SQL statement, a table name, a command object pointer, and so on.

9, in response to ADO notification events

A notification event is when a particular event occurs and the client is notified by provider, in other words, a specific method (that is, the handler function of the event) that is invoked by provider in the client program. So in response to an event, the most important thing is to implement the handler function of the event.

(1). Derive a class from the CONNECTIONEVENTSVT interface

To respond to _connection notification events, 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);
......
};
(2). The processing function for each event is implemented (the method with the RAW_ prefix is implemented):

STDMETHODIMP Cconnevent::raw_infomessage (
struct Error *perror,
EventStatusEnum *adstatus,
struct _connection *pconnection)
{
*adstatus = adStatusUnwantedEvent;
return S_OK;
};
Some methods you don't need, but you must implement it, simply return to a S_OK. However, if you want to avoid being invoked frequently, you should also set the adstatus parameter to adStatusUnwantedEvent, which will not be invoked after this call.
It is also necessary to implement the QueryInterface, AddRef, and release three methods:

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 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 * * *) &AMP;PCPC);
if (FAILED (HR)) return;
hr = Pcpc->findconnectionpoint (__uuidof (connectionevents), &AMP;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 means doing this before you connect (Open).

(4). Stop responding to notification events

Pconn->close ();
Stop using the Connection events
hr = Pconn->queryinterface (__uuidof (IConnectionPointContainer),
(void * * *) &AMP;PCPC);
if (FAILED (HR)) return;
hr = Pcpc->findconnectionpoint (__uuidof (connectionevents), &AMP;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.