Author of VC's full introduction to database access using ADO

Author of VC's full introduction to database access using ADO

Keyword ADO database access Original Author name: xiangshengchang Introduction VC uses ADO to access the database. It introduces the various objects and methods used by VC to access the database. It is classic and practical and worth noting. Body I. Ado Overview ADO is an easy-to-use application layer interface designed by Microsoft for the latest and most powerful data access example ole db. Ado enables you to write applications to access and operate data on database servers through the OLE. DB Provider. The main advantages of ADO are ease of use, fast speed, low memory consumption, and small disk relics. Ado uses the least network traffic in key application solutions, and uses the least number of layers between the front end and the data source. All these are designed to provide lightweight, high-performance interfaces. ADO is a familiar metaphor for OLE Automation interface. Ole db is a set of "Component Object Model" (COM) interfaces. It is a new low-level database interface that encapsulates ODBC functions, access data stored in different information sources in a unified manner. Ole db is the technical basis for Microsoft UDA (Universal Data Access) policies. Ole db provides high-performance access to any data source, including relational and non-relational databases, e-mail and file systems, text and graphics, and custom business objects. That is to say, ole db is not limited to isam, Jet, or even relational data sources. It can process any type of data without considering their format and storage methods. In practical applications, this diversity means that you can access data that resides in an Excel spreadsheet, text file, email/directory service, or even a mail server, such as Microsoft Exchange. However, the ole db Application Programming Interface is designed to provide the best functions for various applications and does not meet the simplification requirements. The API you need should be a bridge between applications and ole db, Which is ActiveX Data Objects (ADO ). 2. Use ADO in VC :) 1. Introduce the ADO Library File Before using ADO, you must use the direct import symbol # import in the stdafx. h header file of the project to introduce the ADO library file so that the compiler can compile it correctly. The Code is as follows: Import ADO library files with # Import # Import "C:/program files/common files/system/ADO/msado15.dll" no_namespaces Rename ("EOF" adoeof ") This statement declares that ADO is used in the project, but the namespace of ADO is not used. To avoid constant conflicts, change the constant EOF to adoeof. Now you can use the ADO interface without adding another header file. 2. initialize the OLE/COM library Environment Note that the ADO library is a group of COM dynamic libraries, which means that the application must initialize the OLE/COM library environment before calling ADO. In an MFC application, a better method is to initialize the OLE/COM library environment in the initinstance member function of the main class of the application. Bool cmyadotestapp: initinstance () { If (! Afxoleinit () // This is the initialization com Library { Afxmessagebox ("OLE initialization error !"); Return false; } ...... } 3. Introduction to ADO Interfaces The ADO library contains three basic interfaces: _ connectionptr, _ commandptr, and _ recordsetptr. _ Connectionptr interface returns a record set or a null pointer. It is usually used to create a data connection or execute an SQL statement that does not return any results, such as a stored procedure. Using the _ connectionptr interface to return a record set is not a good method. _ Recordserptr is usually used for operations to return records. When you use the _ connectionptr operation, you need to retrieve the number of records to traverse all records, but you do not need to use the _ recordserptr operation. The _ commandptr interface returns a record set. It provides a simple method to execute stored procedures and SQL statements that return record sets. When using the _ commandptr interface, you can use the global _ connectionptr interface, or you can directly use the connection string in the _ commandptr interface. If you only perform one or several data access operations, the latter is a good choice. However, if you want to frequently access the database and return many record sets, you should use the global _ connectionptr interface to create a data connection, use the _ commandptr interface to execute stored procedures and SQL statements. _ Recordsetptr is a record set object. Compared with the above two types of objects, it provides more control functions for the record set, such as record lock and cursor control. Like the _ commandptr interface, it does not have to use a created data connection. You can use a connection string to replace the connection pointer with the connection Member variable assigned to _ recordsetptr, create a data connection by yourself. If you want to use multiple record sets, the best way is to use the global _ connectionptr interface that has created a data connection like the command object. And then use _ recordsetptr to execute stored procedures and SQL statements. 4. Use the _ connectionptr Interface _ Connectionptr is mainly a connection interface that gets a connection to the database. Its connection string can be directly written by itself or directed to an odbc dsn .. _ Connectionptr pconn; If (failed (pconn. createinstance ("ADODB. Connection "))) { Afxmessagebox ("create instance failed! "); Return; } Cstring strsrc; Strsrc = "driver = SQL Server; server = "; 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 (strsql ); _ 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 (taking connecting to SQL Server as an example) _ Recordsetptr pptr; If (failed (pptr. createinstance ("ADODB. recordset "))) { Afxmessagebox ("create instance failed! "); Return false; } Cstring strsrc; Strsrc = "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 from personal "; _ Variant_t varsrc (strsrc ); _ Variant_t varsql (strsql ); If (failed (pptr-> open (varsql, varsrc, adopenstatic, adlockoptimistic, adshorttext ))) { 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 ); Strno. trimright (); Strname. trimright (); Strsex. trimright (); Straddress. 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 The _ commandptr interface returns A recordset object and provides more record set control functions. The following code example shows how to use the _ commandptr interface: Code 11: Use the _ commandptr interface to obtain data _ Commandptr pcommand; _ Recordsetptr PRS; Pcommand. createinstance (_ uuidof (command )); Pcommand-> activeconnection = pconn; Pcommand-> commandtext = "select * from student "; Pcommand-> commandtype = ad1_text; Pcommand-> parameters-> refresh (); PRS = pcommand-> execute (null, null, adcmdunknown ); _ Variant_t varvalue = PRS-> getcollect ("name "); Cstring strvalue = (char *) _ bstr_t (varvalue ); 6. For data type conversion, because the COM object is cross-platform, it uses a common method to process various types of data, Therefore, the cstring class is incompatible with the COM Object. We need a set of APIs to convert data of the COM Object and C ++ type. _ Vatiant_t and _ bstr_t are two types of objects. They provide common methods to convert data of the COM Object and C ++ type.