C + + uses ADO to connect to a database and its instances

There are many techniques for reading and writing databases, and now more ADO. ADO is provided in COM mode, so many of its behaviors follow the COM specification. First of all, to introduce ADO COM file, its location is generally "C:/Program Files/common files/system/ado/msado15.dll".

1. Introduction of ADO

Open the precompiled header file StdAfx.h, write the introduction statement:

#import "C:/Program Files/common files/system/ado/msado15.dll" no_namespace rename ("EOF", "adoeof")

Explain the previous sentence: No_namespace refers to ignoring namespaces, rename is to rename the EOF in ADO to Adoeof. It doesn't matter what the name is, but notice that the name in the declaration matches the name in the code.

2. Initialize

Before writing the code with ADO, you will initialize COM. A common approach is to add CoInitialize (NULL) and CoUninitialize () before and after the code. You can also initialize the COM library with AfxOleInit ().

3. three core objects

The 3 core objects of ADO are Connection objects (_connection), command Objects (_command), and Recordset objects (_recordset). Where the connection object is required for any operation. Many operations can be done with 3 core objects. To instantiate them and use the methods they provide, they have to be said to be smart pointers (smart Pointer). When operations such as initialization or deallocation are an object, with the dot operator, most of the other operations use the "-a" operator.

4. instantiation

_connectionptr Pconn (__uuidof (Connection));

_RecordsetPtr PRec (__uuidof (Recordset));

_commandptr Pcmd (__uuidof (Command));

If the above parameters are not added, you need to add:

Pconn.createinstance ("ADODB. Connection ");

Prec.createinstance ("ADODB. Recordset ");

Pcmd.createinstance ("Adodb.command");

5. Connect to the database

Connecting a database generally takes a string connection. The method of obtaining this string uses "secret cannot be said", that is, any new TXT file, renamed to. X.udl. Then double-click the file, and the Database Connection Properties window appears. The first tab "provider" lists all database engines, Access, SQL Server, Oracle, etc., select the next step to jump to the second tab "Connection", select the Server Name field can fill in the server's IP address, the machine will not fill or fill in the dot number You can select a database by filling in the database user name and password. Click the "Test Connection" button to succeed. Are you sure. Open x.udl with Notepad. You will see the connection string it generates. As follows:

"Provider=SQLOLEDB.1; Persist Security Info=false; User Id=sa; Password=123;initial Catalog=cfdata "

In this connection string, the Persist Security info property is true to indicate that the password is still saved after the connection is established and generally false. The ID and Password properties will only be available if you check "Allow Save password" in the Database Properties dialog box above. You can add them manually. Cfdata is the name of my database.

The connection code in C + + is as follows:

Pconn->connectionstring= "Provider=SQLOLEDB.1; Persist Security Info=false; User Id=sa; Password=123;initial Catalog=cfdata ";

6. Example

Some database operations _connection one can be completely done. such as the UPDATE statement. Because it does not need to return the result. As follows:

Pconn->connectionstring= "Provider=SQLOLEDB.1; Persist Security Info=false; User Id=sa; Password=123;initial Catalog=cfdata ";

Pconn->open ("", "", "", adconnectunspecified); Open the connection. Here the parameters are declared in the above string, so it can be null

CString strsql= "Update table1 set name= ' Richard ' where id=1 ';

_pconn->execute (_bstr_t (strSQL), null,adcmdtext);

This involves forcing type conversions. Data types in COM and general (such as MFC) types generally have corresponding, but need to be converted. such as the above _bstr_t and CString. As to what type should be converted into, look at the VC environment hints can (here recommend you add Visual assitant, so that the prompt function more perfect).

There are also operations that need to return a recordset, such as a SELECT statement. There are at least two core objects _connection and _recordset and can be executed with _command. The following shows the code implemented by the same operation with 3 different objects.

(1) Connecting objects

CString strsql= "SELECT * FROM table1"; Method 1

Prec=pconn->execute (_bstr_t (strSQL), null,adcmdtext);

(2) Recordset object

CString strsql= "SELECT * FROM table1"; Method 2

Prec->open (_variant_t (strSQL), (_variant_t) ((idispatch*) pconn), adopendynamic,adlockoptimistic,adcmdtext);

The second parameter (_variant_t) ((idispatch*) pconn) indicates the active connection, the data type conversion is more complex, the _variant_t is the type of the parameter requirement, and idispath* is the _variant_t cast type. Also use the following sentence:

Pconngetinterfaceptr ();

(3) Command object

CString strsql= "SELECT * FROM table1"; Method 3

Pcmd->put_activeconnection ((_variant_t) ((idispatch*) pconn));

pcmd->commandtext=_bstr_t (strSQL);

Prec=pcmd->execute (Null,null,adcmdtext);

7. Data Usage

When the recordset is taken, the data is fetched. Reads the name field data in a ListBox. The code is as follows:

while (!prec->adoeof)

{

The _bstr_t type can be seen as a bridge between a COM type string and an MFC type string

CString str=lpstr (_bstr_t (Prec->getcollect ("name"));

((clistbox*) GetDlgItem (idc_list1))->addstring (str);

Prec->movenext ();

}

The above code is used in the adoeof, to note that the direct copying of third-party code may be renamed to Rseof, and so on, you need to make the corresponding changes. Also, do not forget the MoveNext () in the while loop, or it will become a dead loop. The following methods can also be used to obtain and convert field values:

_variant_t var=prec->getcollect ("name");

Var. ChangeType (VT_BSTR);

CString Str=var.bstrval;

8. Close and release

After you run out of objects, you need to close and release the code as follows:

Prec->close ();

Pconn->close ();

Prec.release ();

Pcmd.release ();

Pconn.release ();

9. Error Trapping

Database operation is unavoidable error, connection string error, SQL statement error, or return null you are hard to plug in the table (this error can be var.vt!=vt_null after the removal), so we need to put them into the Try...catch section. ADO throws an _com_error type exception after catching the error, and we can do this:

Try

{

Pconn->connectionstring= "Provider=SQLOLEDB.1; Persist Security Info=false; User Id=sa; Password=123;initial Catalog=cfdata ";

Pconn->open ("", "", "", adconnectunspecified);

....//code omitted./

}

catch (_com_error& e)

{

AfxMessageBox (E.errormessage ());

AfxMessageBox (E.description ());

}

Here's a doubt, after catching the error, E.errormessage () and E. Description () in the different information, sometimes the former is clear, some of the latter said clearly, not clear, simply add it. Finally, you can add a catch (...), after all, there may be errors outside of ADO.

Here, I have made a mistake, and it has been a whole n long. I put _com_error& E in the back of _com_error* e also corresponding to the operator, and compiled through, the result of a running program crashes, and it does not tell me where the error, because then the error is _com_error at this time but the _com_ Error* to catch, of course, not to catch. Here & is just a quote, writing does not matter, * is absolutely not. (Many of the master code in the online book is used *, misleading AH).

Other interfaces

In addition to the 3 core objects in ADO, we should also understand fieldsptr, Fieldptr, Streamptr and other interfaces. For example, in the example above, we can use Fieldsptr's GetCount () method to get the number of fields, use Fieldptr to receive specific fields, and so on. Binary data like files will be used for streamptr.

Instance code:

#include <iostream> #include "vector" #import "C:\Program Files\Common Files\system\ado\msado15.dll" No_ Namespace rename ("EOF", "adoeof") using namespace Std;int main (int argc, char* argv[]) {CoInitialize (NULL); _ Connectionptr Pconn (__uuidof (Connection)); _RecordsetPtr PRec (__uuidof (Recordset));////or in the following manner://_connectionptr Pconn=null;//_recordsetptr prec=null;//_commandptr pcmd=null;//pconn->createinstance ("ADODB. Connection ");//prec->createinstance (" ADODB.    Recordset ");//pcmd->createinstance (" Adodb.command "); try{_bstr_t strconnect = "provider=sqloledb; Persist Security info=true; User Id=sa; Password=123456;initial catalog=test;data source=localhost ";//_bstr_t strconnect =" Provider=SQLOLEDB; server=localhost;database=test;uid=sa;pwd=123456 ";p Conn->open (StrConnect," "," ", adModeUnknown);} catch (_com_error &e) {cout<< "Initial failed!" <<endl;cout<<e.description () <<endl;cout<<e.helpfile () <<endl;return 0;} Try{prec = Pconn->execute ("SELECT *From Correspondfield ", null,adcmdtext); if (!PREC-&GT;BOF) {Prec->movefirst ();} else{cout<< "Data is empty!" <<endl;return 0;} vector<_bstr_t> column_name;for (int i=0;i<prec->fields->getcount (); i++) {cout<<pRec-> Fields->getitem (_variant_t ((long) i))->name<< ""; Column_name.push_back (Prec->fields->getitem (_ variant_t ((long) i))->name); Cout<<endl;} while (!prec->adoeof) {for (Vector<_bstr_t>::iterator Itr = Column_name.begin (); Itr!=column_name.end (); Itr+ +) {if (Prec->getcollect (*ITR). vt! = vt_null) {cout<< (_bstr_t) prec->getcollect (*ITR) << ";} else{cout<< "NULL" <<endl;}} Prec->movenext (); Cout<<endl;}} catch (_com_error &e) {cout<<e.description () <<endl;cout<<e.helpfile () <<endl;return 0;} Try{prec->close ();p conn->close ();p rec->release ();p conn->release (); catch (_com_error &e) {cout<<e.description () <<endl;cout<<e.helpfile () <<endl;return 0;} CoUninitialize (); return 0;}

　　

C + + uses ADO to connect to a database and its instances