The first intimate contact with ADO-one of ADO development practices

I. Introduction to ADO ADO (ActiveX Data Object) is a new interface for Microsoft database application development. It is a high-level database access technology built on top of ole db. Do not worry about this, even if you are, COM can also easily deal with ADO without understanding it, because it is very easy to use, and even easier to use than the ODBC APIs, DAO, and RDO you used in the past. This article describes in detail how to use ADO for database application development under VC and provides sample code. Sample Code Ii. Basic Process Everything is difficult at the beginning. Any new technology is the most important thing for beginners to learn about is getting started. Let's take a look at the basic process of ADO database development! (1) initialize the COM library and introduce the ADO library definition file. (2) connect to the database using the Connection object (3) Use the established Connection to execute SQL commands through the Connection and Command objects, or use the Recordset object to obtain the result record set for query and processing. (4) Close the connection and release the object after use. Preparations: For everyone to test the example provided in this article, we use the Access database, you can also find this test. mdb directly in the sample code we provide. Next we will introduce the above steps in detail and provide relevant code. [1]COM library Initialization We can use AfxOleInit () to initialize the COM library, which is usually completed in the overload function of CWinApp: InitInstance (). See the following code: BOOL CADOTest1App::InitInstance() { AfxOleInit(); ...... [2]Use the # import command to introduce the ADO Type Library We add the following statement to stdafx. h: (where can I find the stdafx. h file? You can find it in Header Files in FileView) #import "c:/program files/common files/system/ado/msado15.dll" no_namespace rename("EOF","adoEOF") What is the role of this statement? The final function is similar to # include, which we are familiar with. during compilation, the system will generate msado15.tlh and ado15.tli C ++ header files to define the ADO library. Notes: (1) msado15.dll may not be in this directory in your environment. modify it as needed. (2) The following warning may appear during compilation. Microsoft explained this in MSDN and suggested that we ignore this warning. Msado15.tlh (405): warning C4146: unary minus operator applied to unsigned type, result still unsigned [3]Create a Connection object and connect to the database First, we need to add a pointer to the Connection object: _ ConnectionPtr m_pConnection; The following code demonstrates how to create a Connection object instance, connect to a database, and capture exceptions. BOOL CADOTest1Dlg: OnInitDialog () {CDialog: OnInitDialog (); 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 need to change it: 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} In this Code, we connect to the database through the Open method of the Connection object. The following is a prototype of this method. HRESULT Connection15: Open (_ bstr_t ConnectionString, _ bstr_t UserID, _ bstr_t Password, long Options) ConnectionString is the Connection string, UserID is the user name, Password is the login Password, Options is the Connection option, used to specify the Connection object to update the data permission, Options can be the following constants: AdModeUnknown: default. The current permission is not set AdModeRead: Read-Only AdModeWrite: Write only AdModeReadWrite: read/write AdModeShareDenyRead: blocks other Connection objects and opens the Connection with the read permission. AdModeShareDenyWrite: blocks other Connection objects and opens the Connection with write permission. Admodemo-exclusive: prevents other Connection objects from opening connections. AdModeShareDenyNone: allows other programs or objects to establish connections with any permissions. We provide some common connection methods for your reference: (1) connection to the ACCESS2000 Database through the JET Database Engine m_pConnection->Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C://test.mdb","","",adModeUnknown); (2) Use the DSN data source to connect to any database that supports ODBC: m_pConnection->Open("Data Source=adotest;UID=sa;PWD=;","","",adModeUnknown); (3) do not use DSN to connect to the SQL SERVER database: m_pConnection->Open("driver={SQL Server};Server=127.0.0.1;DATABASE=vckbase;UID=sa;PWD=139","","",adModeUnknown); The Server is the name of the SQL Server, and the DATABASE is the name of the DATABASE. There are many methods in addition to the Open method of the Connection object. First, we will introduce two useful attributes of the Connection object: ConnectionTimeOut and State. ConnectionTimeOut is used to set the connection timeout time, which must be called before Open, for example: M_pConnection-> ConnectionTimeout = 5; // set the timeout value to 5 seconds. m_pConnection-> Open ("Data Source = adotest;", "", "", adModeUnknown ); The State attribute specifies the status of the current Connection object. The value 0 indicates that the Connection object is closed, and the value 1 indicates that the Connection object has been opened. You can read this attribute for processing. For example: If (m_pConnection-> State) m_pConnection-> Close (); // if the connection has been enabled, Close it. [4]Execute the SQL command and obtain the result record set To obtain the result record set, we define a pointer to the Recordset object: _ RecordsetPtr m_pRecordset; And create a Recordset object for it: m_pRecordset.CreateInstance ("ADODB. Recordset "); SQL commands can be executed in multiple forms. The following describes how to execute these commands. (1) Execute SQL commands using the Execute method of the Connection object The prototype of the Execute method is as follows: _ RecordsetPtr Connection15: Execute (_ bstr_t CommandText, VARIANT * RecordsAffected, long Options) CommandText is a command string, usually an SQL command. The RecordsAffected parameter indicates the number of rows affected by the operation. The Options parameter indicates the content type in CommandText. Options can be one of the following values: Adshorttext: indicates that CommandText is a text command. AdCmdTable: indicates that CommandText is a table name. Ad1_proc: indicates that CommandText is a stored procedure. AdCmdUnknown: Unknown After Execute is executed, a pointer pointing to the record set is returned. The specific code is provided and described below. _ Variant_t RecordsAffected; // run the SQL command: CREATE TABLE to CREATE users. users contains four fields: integer ID, string username, and integer old, date type birthday m_pConnection-> Execute ("create table users (id integer, username TEXT, old INTEGER, birthday DATETIME)", & RecordsAffected, ad1_text ); /// Add the record m_pConnection-> Execute ("insert into users (ID, username, old, birthday) VALUES (1, 'Washington ', 25, '2014/1/1') ", & RecordsAffected, ad1_text ); /// Add the values of all record old fields to m_pConnection-> Execute ("UPDATE users SET old = old + 1", & RecordsAffected, ad1_text ); /// Execute the SQL statistics command to obtain the set of records containing the number of records m_pRecordset = m_pConnection-> Execute ("SELECT COUNT (*) FROM users", & RecordsAffected, ad1_text ); _ variant_t vIndex = (long) 0; _ variant_t vCount = m_pRecordset-> GetCollect (vIndex); // get the value of the first field and put it in the vCount variable m_pRecordset-> Close (); /// disable the record set CString message; message. format ("% d records in total", vCount. lVal); AfxMessageBox (message); // display the number of current records (2) Execute SQL commands using Command objects _ CommandPtr m_pCommand; m_pCommand.CreateInstance ("ADODB. command "); _ variant_t vNULL; vNULL. vt = VT_ERROR; vNULL. scode = DISP_E_PARAMNOTFOUND; // It is defined as a very critical sentence without the parameter m_pCommand-> ActiveConnection = m_pConnection, assign the established connection m_pCommand-> CommandText = "SELECT * FROM users"; // command string m_pRecordset = m_pCommand-> Execute (& vNULL, & vNULL, ad1_text ); /// execute the command to obtain the record set In this Code, we only use the Command object to execute the SELECT query statement. The Command object can truly reflect its role in calling the stored procedure. We will introduce it in detail next time. (3) directly use the Recordset object for query to obtain the record set For example m_pRecordset->Open("SELECT * FROM users",_variant_t((IDispatch *)m_pConnection,true),adOpenStatic,adLockOptimistic,adCmdText); The prototype of the Open method is as follows: HRESULT Recordset15: Open (const _ variant_t & Source, const _ variant_t & ActiveConnection, enum CursorTypeEnum CursorType, enum LockTypeEnum LockType, long Options) Where: ① Source is a data query string ② ActiveConnection is a established Connection (we need to use the Connection object pointer to construct a _ variant_t object) ③ CursorType the cursor type. It can be one of the following values. Please refer to this enumeration structure: Enum CursorTypeEnum { AdOpenUnspecified =-1, // do not specify AdOpenForwardOnly = 0, // roll the static cursor forward. The cursor can only browse the record set forward. For example, if MoveNext is used to scroll forward, this method can increase the browsing speed. However, neither BookMark, RecordCount, AbsolutePosition, or AbsolutePage can be used. AdOpenKeyset = 1, // the record set with this cursor cannot see the new or delete operations of other users, but you can see the operations to update the original records. AdOpenDynamic = 2, // dynamic cursor. All database operations are immediately reflected in each user record set. AdOpenStatic = 3 // static cursor. It generates a static backup for your record set, but the addition, deletion, and update operations of other users are invisible to your record set. }; ④ LockType: it can be one of the following values. See the following enumeration structure: Enum LockTypeEnum { AdLockUnspecified =-1, // not specified AdLockReadOnly = 1, // read-only record set AdLockPessimistic = 2, pessimistic locking mode. Lock all other actions when data is updated. This is the most secure locking mechanism. AdLockOptimistic = 3, Optimistic Locking mode. The record is locked only when you call the Update method. Before that, you can update, insert, or delete data. AdLockBatchOptimistic = 4, optimistic batch update. The record is not locked during editing. Modification, insertion, and deletion are completed in batch mode. }; ⑤ For Options, refer to the introduction to the Execute method of the Connection object in this article. [5]Record set traversal and update According to the users table created by executing the SQL command just now, it contains four fields: ID, username, old, birthday The following code enables you to open a record set, traverse all records, delete the first record, add three records, move the cursor to the second record, change its age, and save it to the database. _ Variant_t vUsername, vBirthday, vID, vOld; _ RecordsetPtr m_pRecordset; m_pRecordset.CreateInstance ("ADODB. recordset "); m_pRecordset-> Open (" SELECT * FROM users ", _ variant_t (IDispatch *) m_pConnection, true), adOpenStatic, adLockOptimistic, ad1_text); while (! M_pRecordset-> adoEOF) // Why is it adoEOF instead of EOF? Do you still remember rename ("EOF", "adoEOF? {VID = m_pRecordset-> GetCollect (_ variant_t (long) 0); // obtain the value of the 1st column and count it from 0, you can also directly give the column name, as shown in the following line: vUsername = m_pRecordset-> GetCollect ("username "); /// obtain the username field value vOld = m_pRecordset-> GetCollect ("old"); vBirthday = m_pRecordset-> GetCollect ("birthday "); /// if (vID. vt! = VT_NULL & vUsername. vt! = VT_NULL & vOld. vt! = VT_NULL & vBirthday. vt! = VT_NULL) TRACE ("id: % d, name: % s, age: % d, birthday: % s/r/n", vID. lVal, (LPCTSTR) (_ bstr_t) vUsername, vOld. lVal, (LPCTSTR) (_ bstr_t) vBirthday); m_pRecordset-> MoveNext (); // move to the next record} m_pRecordset-> MoveFirst (); /// move to the first record m_pRecordset-> Delete (adAffectCurrent); // Delete the current record // Add three new records and assign the value for (int I = 0; I <3; I ++) {m_pRecordset-> AddNew (); // Add the new record m_pRecordset-> PutCollect ("ID", _ variant_t (long) (I + 10 ))); m_pRecordset-> PutCollect ("username", _ variant_t ("Silverlight"); m_pRecordset-> PutCollect ("old", _ variant_t (long) 71 )); m_pRecordset-> PutCollect ("birthday", _ variant_t ("1930-3-15");} m_pRecordset-> Move (1, _ variant_t (long) adBookmarkFirst )); /// move one record down from the first record, that is, to the second record m_pRecordset-> PutCollect (_ variant_t ("old"), _ variant_t (long) 45 )); /// modify the age m_pRecordset-> Update (); // save it to the database. [6]Close record set and Connection You can Close Record Sets or connections using the Close method. M_pRecordset-> Close (); // Close the record set m_pConnection-> Close (); // Close the connection At this point, I think you are familiar with the general process of operating the database through ADO. Maybe you have a good idea. Maybe you still have something to worry about! I suggest you try to write a few examples to better familiarize yourself with ADO. Finally, I wrote a small example for you, in this example, all records are read and placed in the list control, and records can be added, deleted, or modified. Click here to download the sample code Note: Due to the length constraints, many content in ADO has not yet been introduced. Next time, we will detail the attributes and methods of the Recordset object and discuss several key technologies: the binding method processes record set data, call of stored procedures, transaction processing, storage and reading of images in the database, and use with table controls.