Sun Xin VC Study Notes: Lecture 20th (ii) ADO database programming

Database Programming: COM [count] extension of small executable programs, serial communication port [domain] commercial organizations, business organization, the connection object of the three core objects of the Component Object Model ADO of the company's component object model represents the connection to the database, which manages the communication between applications and databases. Both the recordest and command objects have an activeconnection attribute, which is used to consume the connection object. The command object is used to process repeated queries, or to query the output or return parameter values that need to be checked in Stored Procedure calls. The recordset object is used to obtain data. The recordset object stores the query results. These results are composed of rows (records) and columns (fields) of data. Each column is stored in a filed object in the fields set of the recordset.

 

Summary of ADO database connection knowledge 1. Before importing and importing files to the database, you must use stdafx in the project. the H file is finally introduced with a direct symbol # import to introduce the ADO library file, so that the compiler can compile correctly. The Code is as follows: # import "C:/program files/common files/system/ADO/msado15.dll" no_namespace Rename ("EOF", "endoffile") Rename ("Bof ", "firstoffile") ADO class is defined as a kind of resource stored in ado dll (msado15.dll), which is internally called a Type Library. The Type Library describes the autonomous interface and the com vtable interface used by C ++. When using the # import command, Visual C ++ needs to read this type of library from ado dll at runtime and create a set of C ++ header files. These header files have the. TLI and. tlh extensions. # After the import introduces the code of the ADO library file, these two files are generated under the project directory. The ADO class called in the C ++ program code should be defined in these files. The third line of the program indicates that the ADO object does not use the namespace. In some applications, because the objects in the application and the objects in the ADO may have a name conflict, it is necessary to use the namespace. If you want to use a namespace, you can change the third-line program to rename_namespace ("adons "). The fourth line of code renamed the EOF In ADO to adoeof, because the end of the file is also ended with EOF, in order to avoid conflicts with other libraries that define their own EOF. You can determine the reason for changing the name based on your naming conventions. 2. initialize the com environment. ole db is compiled based on COM technology, ADO is a user program based on ole db, and ole db is a COM component, to access the COM component, initialize the com library as follows: (1): coinitialize (null ); // initialize the OLE/COM database environment // write the access to the database between upstream and downstream code. The third step should be written here: couninitialize (); // Since the environment is initialized, of course, remember to release it. (2) You can also call the global function afxoleinit () of MFC. (3) define the three pointer objects and create instances. (1) _ connectionptr pconnection ("ADODB. connection "); _ recordsetptr precordset (" 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); to generate a smart pointer object, you can also initialize it while defining it, for example, _ connectionptr pconnection (_ uuidof (connection )); _ connectionptr is a smart pointer _ uuidof () used to obtain the Globally Unique Identifier (3) _ connectionptr pconnection; _ recordsetptr precordset; _ commandptr PC Ommand; pconnection. createinstance ("ADODB. connection "); precordset. createinstance ("ADODB. recordset "); pcommand. createinstance ("ADODB. command "); 4. Open a connection pconnection-> connectionstring =" the strings here are written in the following four ways "; // assign a value to the connection string pconnection-> open (connectionstring, "", "", admodeunknown); // The second three parameters for connecting to the database are the user ID and password respectively, because the connection string connectioncstring has been set, which can be empty here. The fourth parameter can be set to the following two parameters: adasyncconnect asynchronously opens the database, and uses 16 adconnectunspecified to directly open the database in ASP. In ASP,-1 connectionstring is used to directly open the database based on different data sources, corresponding to different writing methods (it is very difficult to remember it. You can use the ADO control in VB to connect it first, and then copy it to VC, which is not prone to errors) 1) 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 the Oracle Database" provider = msdaora; Data sourse = Serv Ername; user id = username; Password = userpassword; "4) Access ms SQL database" provider = sqloledb, data source = servername; initial catalog = databasename; user id = username; password = userpassword; "5. Run the SQL command to obtain data method 1: precordset = pconnection-> execute (" select * from authors ", null, ad1_text); Method 2: precordset-> open ("select * from authors", _ variant_t (idispacth *) pconnection), // sets the active connection adopendynamtic, // The cursor type adlockoptimistic, // The lock class Type adshorttext); Method 3: pcommand-> put_activeconnection (_ variant_t (idispatch *) pconn); pcommand-> commandtext = "select * from authors "; precordset = pcmd-> execute (null, null, ad1_text); after obtaining the data, create a loop to get the data: While (! Precordset-> adoeof) {STR = precordset-> getcollect ("au_lname"); PRST-> movenext ();} There are many SQL commands, but do not consider the details, here, we only use the common method cstring strsql; // defines the SQL command string to save the SQL statement strsql. format ("SQL statement"); then, use strsql in each method that uses the SQL command string. allocsysstring () method for type conversion 6. The specific data types of com include variant, BSTR, and safearray variant. It is a variant type, it is mainly used to support automated language access, which is very convenient to use in VB. However, VC is complicated. It uses _ variant_t to manage BSTR as a string variable, use _ bstr_t for management. This class reloads the char * operator 7 and closes the connection. If (pconnection-> state); // It cannot be closed multiple times; otherwise, the error pconnection-> close (); precordset-> close (); pcommand may occur. release (); pconnection. release (); // release reference count precordset. release (); Note: Use "->" when calling close (), and use "when calling release ". ", why? Because of smart pointers, _ connectionptr is a class _ connectionptr with the-> operator reloaded: it is an interface pointer template. '.' Is the function of the template _ com_ptr. -> It is called by 'interface functions. // Forexample: _ connectionptr m_conn; m_conn.createinstance (....); // createinterfaceinstance. m_conn-> open (...); // openaconnectiontodatabase. '->' is the operator that is overloaded by _ com_ptr. the purpose is to allow you to call the function of template parameters. 8. structured exception handling ADO encapsulates the COM interface, so you need to handle the following errors: hresult hr; try {hR = m_pconnection.createinstance ("ADODB. connection "); // create a connection object if (succeeded (HR) {hR = m_pconnection-> open (" provider = Microsoft. jet. oledb.4.0; Data Source = test. mdb ","","", Admodeunknown); // connect to the database // The provider in the connection string in the above sentence is for the Access2000 environment. For Access97, you must change it to provider = Microsoft. jet. oledb.3.51 ;}} catch (_ com_error e) // catch exception {cstring errormessage; errormessage. format ("failed to connect to the database! /R/n error message: % s ", E. errormessage (); afxmessagebox (errormessage); // Display error message} three objects are introduced to access the database through ADO. They can all execute SQL statements to obtain data, however, instead of using that method to obtain data, the data will eventually be placed in the record set object.