Access the database directly through ADO

Xu Jingzhou, author of Access database directly through ADO, downloaded the source code. I published two articles in issue 14th and issue 15th of VC Knowledge Base online magazine-"Reading and Writing Excel directly through ODBC table Files "and" read and write Access files directly through DAO ", we have introduced the basic users of ODBC and DAO Database Access Technologies.

I directly use ADO to Access the database author/Xu Jingzhou to download the source code. I published two articles in issue 14th and issue 15th of the VC Knowledge Base online magazine-"Read data directly through ODBC, write Excel table files "and" read and write Access files directly through DAO ", we have introduced the basic users of ODBC and DAO Database Access Technologies.

Access the database directly through ADO


Author/Xu Jingzhou

Download source code

I published two articles in issue 14th and issue 15th of VC Knowledge Base online magazine-"Reading and Writing Excel file directly through ODBC" and "Reading and Writing through DAO directly ". access File ", I have introduced you to the basic methods of using ODBC and DAO database access technologies. This time I will introduce you to the usage of ADO database access technology. ADO (Active Data Object) is actually an automatic interface (IDispatch) Technology Based on the COM (Component Object Model, based on ole db (Object connection and embedded database), the database access technology carefully packaged by ole db can be used to quickly create database applications. ADO provides a simple set of objects that encapsulate general data access details. Since the ODBC data source also provides a general ole db Privider, ADO can not only apply its own ole db Privider, but also all ODBC drivers. For more information about ole db and ADO, read related books or MSDN on your own. Let's go straight to the topic: how to master the database access technology such as ADO. The ADO operation method is similar to the DAO operation mentioned above in many aspects. Here, I want to describe its usage more effectively, an example program named AdoRWAccess is developed with VC6.0. This example program can be used to operate the Access database directly through ADO. The running effect of the example program is shown in:

In the example program, we still use the original database structure, database name Demo. mdb: DemoTable, Name of the table in the database, and Name of the field in the Table: Name (Name) and Age (Age) to construct the Access database required for the example program operation, this is also compatible with the library structure in the sample source code of the previous two articles.
Next let's take a look at the basic steps and methods of using the ADO database access technology:
First, use the # import Statement to reference the component type library that supports ADO (*. tlb), where the type library can be positioned as a part of the executable program (DLL, EXE, etc.) in its own sub-Resources in the program, such: it is located in the ancillary resources of msado15.dll. You only need to reference it directly with # import. You can directly Add the following statement to the Stdafx. h file:

[Cpp]View plaincopy

 
 

  
  
// Add the ADO support library, jingzhou xu
  
  
# Import "c:/program files/common files/system/ado/msado15.dll "/
  
  
No_namespace/
  
  
Rename ("EOF", "adoEOF ")
 
 
     

The path name can be set based on the path of the ADO support file installed in the system. When the compiler encounters the # import Statement, it generates a packaging class for the interface in the reference component type library. The # import Statement is actually equivalent to executing the LoadTypeLib () API Han number (). # The import Statement will generate two files in the project executable program output directory *. tlh (Type Library header file) and *. tli (Type Library implementation file), which generates smart pointers for each interface, declares various interface methods, enumeration types, CLSID, and creates a series of packaging methods. The statement no_namespace indicates that the ADO object does not use a namespace. rename ("EOF", "adoEOF") indicates that the end sign eof ado is changed to adoEOF to avoid conflicts with the names in other libraries.
Second, components need to be initialized in the initial process of the program. Generally, you can use CoInitialize (NULL); To implement this method. At the end of this method, you must disable the initialized COM, you can use CoUnInitialize (); in the following sentence. In MFC, you can use another method to initialize COM. In this method, you only need one statement to automatically initialize COM and Disable COM at the end, the statement is as follows: AfxOleInit ();
Then, you can directly use the ADO operation. We often use the packaging class generated when # import Statement is used to reference the Type Library. the three smart pointers declared in tlh are _ ConnectionPtr, _ RecordsetPtr, and _ CommandPtr. The following describes how to use them.

Before introduction, we first write a function. In this example, we add a static function as a class variable.

[Cpp]View plaincopy

1. // Print the detailed error message generated when calling the ADO control
2. Void CAdoRWAccessDlg: dump_com_error (_ com_error & e)
3. {
4. CString ErrorStr;
5. _ Bstr_t bstrSource (e. Source ());
6. _ Bstr_t bstrDescription (e. Description ());
7. ErrorStr. format ("/n/tADO Error/n/tCode = % 08lx/n/tCode meaning = % s/n/tSource = % s/n/tDescription = % s/n/ n ",
8. E. Error (), e. ErrorMessage (), (LPCTSTR) bstrSource, (LPCTSTR) bstrDescription );
9. // Print the error message in the debugging window. You can use DBGView to view the error message in Release.
10. : OutputDebugString (LPCTSTR) ErrorStr );
11. # Ifdef _ DEBUG
12. AfxMessageBox (ErrorStr, MB_ OK | MB_ICONERROR );
13. # Endif
14. }

1. _ ConnectionPtr smart pointer, it is usually used to open or close a database connection or use its Execute method to Execute a command statement that does not return results (the usage is similar to the Execute method in _ CommandPtr ).
-- Open a database connection. First, create an instance pointer, and then Open a database connection. It returns an IUnknown automatic interface pointer. The Code is as follows:

Add a public member variable to the CAdoRWAccessApp.

[Cpp]View plaincopy

1. _ ConnectionPtr m_pConnection;

In the BOOL CAdoRWAccessApp: InitInstance () function

[Cpp]View plaincopy

1. // Initialize COM and create an ADO connection
2. AfxOleInit ();
3. M_pConnection.CreateInstance (_ uuidof (Connection ));
4. // In the ADO operation, we recommend that you use try... catch () to capture error information,
5. // Because it often produces unexpected errors. Jingzhou xu
6. Try
7. {
8. // Open the local Access Library Demo. mdb
9. M_pConnection-> Open ("Provider = Microsoft. Jet. OLEDB.4.0; Data Source = Demo. mdb", ", adModeUnknown );
11. }
12. Catch (_ com_error & e)
13. {
14. // Call the static function that prints the error message in CAdoRWAccessDlg.
15. CAdoRWAccessDlg: dump_com_error (e );
16. Return FALSE;
17. }

-- Close a database connection (in this example, CAdoRWAccessApp: ExitInstance ()). If the connection status is valid, Close it with the Close method and assign it a null value. The Code is as follows:

[Cpp]View plaincopy

1. // Closes the ADO connection status
2. If (m_pConnection-> State)
3. M_pConnection-> Close ();
4. M_pConnection = NULL;

2. The _ RecordsetPtr smart pointer can be used to open data tables in the database and perform various operations on the records and fields in the table.
-- Open the data table. Open the data table named DemoTable in the database. The Code is as follows:

Add class member variables

[Cpp]View plaincopy

1. _ RecordsetPtr m_pRecordset;

In CAdoRWAccessDlg: OnInitDialog ()

[Cpp]View plaincopy

1. // Use ADO to create a database record set
2. M_pRecordset.CreateInstance (_ uuidof (Recordset ));
3. // In the ADO operation, we recommend that you use try... catch () to capture error information,
4. // Because it often produces unexpected errors. Jingzhou xu
5. Try
6. {
7. M_pRecordset-> Open ("SELECT * FROM DemoTable", // query all fields in the DemoTable table
8. TheApp. m_pConnection.GetInterfacePtr (), // obtain the IDispatch pointer of the database connected to the database
9. AdOpenDynamic,
10. AdLockOptimistic,
11. AdCmdText );
12. }
13. Catch (_ com_error & e)
14. {
15. Dump_com_error (e );
16. }

-- Read table data. Read all the data in the table and display it in the list box. m_AccessList is the member variable name of the list box. If the table end mark adoEOF is not met, use the GetCollect (field name) or m_pRecordset-> Fields-> GetItem (field name)-> Value method to obtain the field Value specified by the pointer of the current record, then use the MoveNext () method to move to the next record location. The Code is as follows:

[Cpp]View plaincopy

1. _ Variant_t var;
2. CString strName, strAge;
3. // Clear the list box
4. M_AccessList.ResetContent ();
5. StrName = strAge = "";
6. // In the ADO operation, we recommend that you use try... catch () to capture error information,
7. // Because it often produces unexpected errors. Jingzhou xu
8. Try
9. {
10. If (! M_pRecordset-> BOF)
11. M_pRecordset-> MoveFirst ();
12. Else
13. {
14. AfxMessageBox ("table data is empty ");
15. Return;
16. }
17. // Read the fields in the database and add them to the list box.
18. While (! M_pRecordset-> adoEOF)
19. {
20. Var = m_pRecordset-> GetCollect ("Name ");
21. If (var. vt! = VT_NULL)
22. StrName = (LPCSTR) _ bstr_t (var );
23. Var = m_pRecordset-> GetCollect ("Age ");
24. If (var. vt! = VT_NULL)
25. StrAge = (LPCSTR) _ bstr_t (var );
26. M_AccessList.AddString (strName + "-->" + strAge );
27. M_pRecordset-> MoveNext ();
28. }
29. // The default list points to the first item, and the record pointer is moved and displayed.
30. M_AccessList.SetCurSel (0 );
31. OnSelchangeListaccess ();
32. }
33. Catch (_ com_error & e)
34. {
35. Dump_com_error (e );
36. }

-- Insert a record. You can use AddNew () to add an empty record, use PutCollect (field name, value) to enter the value of each field, and then Update () to Update the data in the database. The m_Name and m_Age variables are the member variable names in the name and age edit boxes respectively. The Code is as follows:

[Cpp]View plaincopy

1. // In the ADO operation, we recommend that you use try... catch () to capture error information,
2. // Because it often produces unexpected errors. Jingzhou xu
3. Try
4. {
5. // Write the value of each field
6. M_pRecordset-> AddNew ();
7. M_pRecordset-> PutCollect ("Name", _ variant_t (m_Name ));
8. M_pRecordset-> PutCollect ("Age", atol (m_Age ));
9. M_pRecordset-> Update ();
10. AfxMessageBox ("inserted successfully! ");
11. // Update and display the Library Content
12. Int nCurSel = m_AccessList.GetCurSel ();
13. OnReadAccess ();
14. M_AccessList.SetCurSel (nCurSel );
15. // Move the record pointer to a new position
16. OnSelchangeListaccess ();
17. }
18. Catch (_ com_error & e)
19. {
20. Dump_com_error (e );
21. }

-- Move the record pointer. The moving record pointer can be moved to the first record through the MoveFirst () method, the MoveLast () method to the last record, the MovePrevious () method to the previous record of the current record, MoveNext () method to move to the next record of the current record. However, you can use the Move (Record Number) method to Move a record to any position. Note: Move () the method moves the pointer position relative to the current record. The positive value moves backward, and the negative value moves forward, for example, Move (3). When the current record is 3, it moves three record locations from record 3. The Code is as follows:

[Cpp]View plaincopy

1. Try
2. {
3. Int curSel = m_AccessList.GetCurSel ();
4. // First move the pointer to the first record, and then move the record pointer to the first record.
5. M_pRecordset-> MoveFirst ();
6. M_pRecordset-> Move (long (curSel ));
8. }
9. Catch (_ com_error & e)
10. {
11. Dump_com_error (e );
12. }

-- Modify the field value in the record. You can move the record pointer to the location where you want to modify the record, and use PutCollect (field name, value) to write the new value into and Update () to Update the database. You can use the above method to move the record pointer. The code for modifying the field value is as follows:

[Cpp]View plaincopy

1. // Modify the field value of the current record
2. Try
3. {
4. M_pRecordset-> PutCollect ("Name", _ variant_t (m_Name ));
5. M_pRecordset-> PutCollect ("Age", atol (m_Age ));
6. M_pRecordset-> Update ();
7. // Re-read the warehouse receiving record update display
8. Int nCurSel = m_AccessList.GetCurSel ();
9. OnReadAccess ();
10. M_AccessList.SetCurSel (nCurSel );
11. // Move the record pointer to a new position
12. OnSelchangeListaccess ();
13. }
14. Catch (_ com_error & e)
15. {
16. Dump_com_error (e );
17. }

-- Delete a record. The operation for deleting a record is similar to that for modifying the record. First, move the record pointer to the location where the record is to be modified, Delete it using the Delete () method, and Update () method () to update the data base. The Code is as follows:

[Cpp]View plaincopy

1. Try
2. {
3. // Delete the current row record
4. M_pRecordset-> Delete (adAffectCurrent );
5. M_pRecordset-> Update ();
6. // Delete the current value from the list
7. Int nCurSel = m_AccessList.GetCurSel ();
8. M_AccessList.DeleteString (nCurSel );
9. If (nCurSel = 0 & (m_AccessList.GetCount ()! = 0 ))
10. M_AccessList.SetCurSel (nCurSel );
11. Else if (m_AccessList.GetCount ()! = 0)
12. M_AccessList.SetCurSel (nCurSel-1 );
14. // Move the record pointer to a new position
15. OnSelchangeListaccess ();
16. }
17. Catch (_ com_error & e)
18. {
19. Dump_com_error (e );
20. }

-- Disable record set. Close the record set directly and assign it a null value. The Code is as follows:

[Cpp]View plaincopy

1. // Close the record set
2. If (m_pRecordset! = NULL)
3. {
4. M_pRecordset-> Close ();
5. M_pRecordset.Release ();
6. M_pRecordset = NULL;
7. }

3. CommandPtr smart pointer. You can use _ ConnectionPtr or _ RecordsetPtr to execute tasks, define output parameters, and execute stored procedures or SQL statements.
-- Execute SQL statements. First create a _ CommandPtr instance pointer, and then use the database connection and SQL statement as the parameter to Execute the Execute () method. The Code is as follows:

[Cpp]View plaincopy

1. _ CommandPtr m_pCommand;
2. M_pCommand.CreateInstance (_ uuidof (Command ));
3. M_pCommand-> ActiveConnection = theApp. m_pConnection; // assign the database connection to it
4. M_pCommand-> CommandText = _ bstr_t (LPCTSTR) m_strCommand); // SQL statement
5. Try
6. {
7. // Execute an SQL statement and return the record set. This record cannot be inserted.
8. // In order not to conflict with m_pRecordset, put the newly defined m_pRecordset1
9. M_pRecordset1 = m_pCommand-> Execute (NULL, NULL, ad1_text );
10. }
11. Catch (_ com_error & e)
12. {
13. Dump_com_error (e );
14. }

-- Execute the stored procedure. The operation for executing a stored procedure is similar to that for executing the preceding SQL statement. The difference is that the CommandText parameter is no longer an SQL statement, but the name of the stored procedure, such as Demo. Another difference is that in Execute (), the parameter is changed from adshorttext (Execute SQL statement) to adshortstoredproc to Execute the stored procedure. If input and output parameters exist in the stored procedure, you need to use another smart pointer _ ParameterPtr to successively set the parameter information for input and output, and assign it to the Parameters parameter in _ CommandPtr to transmit information. Interested readers can find related books or MSDN on their own. The code for executing the stored procedure is as follows:

[Cpp]View plaincopy

1. _ CommandPtr m_pCommand;
2. M_pCommand.CreateInstance (_ uuidof (Command ));
3. M_pCommand-> ActiveConnection = m_pConnection; // assign the database connection to it
4. M_pCommand-> CommandText = "Demo ";
5. M_pCommand-> Execute (NULL, NULL, ad1_storedproc );

Finally, if you want to know the detailed implementation details, you can download the sample source code and carefully check the source code (including detailed comments ).

Contact information: Address: Unit 6, No. 2 Labor Road, Xi'an City, Shaanxi Province
Zip code: 710082
Author EMAIL: jingzhou_xu@163.net
Future Studio)