Database Program Development Instance using ADO encapsulation class

 I. Preface

People who have used ADO know that calling ADO requires a lot of "troublesome" tasks, such as exception handling. It is a little annoying to write a lot of try-catch blocks. I simply encapsulate common functions to avoid the trouble of writing try-catch blocks. Although there is no technical content, it is also cumbersome, so only a part of it is completed, and due to limited time and personal level, there is no comprehensive test of the encapsulated things, there must be many mistakes, but it may be useful to some friends. So I donated it first. ^-^.
Before introducing these two classes, let's take a look at ADO. This article assumes that you already have certain programming capabilities:

2. Understand the structure and system of ADO

ADO (ActiveX Data Object, Active Data Object) is a language-independent Data access application programming interface provided by Microsoft. According to most of the information, it has the following main features:
I. ease of use.
2. You can access multiple data sources.
3. Fast access and high efficiency:
4. Convenient Web applications.
V. Rich technical programming interfaces.
6. low memory consumption and less disk space.

It is precisely because of the many advantages of ADO that I am interested in developing databases using ADO. It is indeed not difficult to use ADO as I mentioned earlier. In general, the ADO model includes the following objects: Connection, Command, Recordset, Field, Parameter, and Error) property, set, and event. the relationships between them are as follows:

(1) The most common objects are Connection, Recordset, and Command.
(2) for accessing a database, we generally establish an ADO connection first.
(3) The ADO connection can directly execute SQL statements to manipulate the database, but if we want to access the data between the application and the data source, we need to use the record set object. An ADO connection can have multiple ADO connections, but one ADO connection can only correspond to one and must correspond to one ADO connection.
(4) In addition, if you can perform more advanced access, you may also need to use command objects. For example, to call a stored procedure.
(5) A record set contains a field set, and a field set contains multiple field objects.
(6) A command object also contains a parameter set. A parameter set contains multiple parameter objects.
(7) The connection object also has an error set and contains multiple error objects.
This is the approximate relationship between various ADO objects.

3. Learn more about ADO connection

Before using the database, you must first establish a connection. Generally, you must use the CreateInstance method to create an ADO connection object, and then you can use the Open method to connect to the database. Its prototype is Open (BSTR ConnectionString, BSTR UserID, BSTR Password, long Options). In this example, if UserID and Password have specified the username and Password in ConnectionString, you generally do not have to worry about them. options indicates whether to connect in synchronous mode (adConnectUnspecified) or asynchronous mode (adAsyncConnect). The default value is synchronous. the key to this function is the ConnectionString parameter, which determines the method in which we will connect to the data source, for example:
For an Access database, the format is generally: "Provider = Microsoft. Jet. OLEDB.4.0; Data Source = db. mdb ";
For SQL Server: "Provider = SQLOLEDB.1; Data Source = sqlservername; Initial Catalog = master; UserID = sa; PWD = password ";
The specific content depends on your environment. To connect to other databases, please refer to the relevant information.
For example:

_ ConnectionPtr pConnection; LPCSTR strConnect = "Provider = Microsoft. jet. OLEDB.4.0; Data Source = test. mdb "; // create a Connection object --------------------------- HRESULT hr = pConnection. createInstance ("ADODB. connection "); // set the Connection time ----------------------------------- pConnection-> put_ConnectionTimeout (long (5); if (SUCCEEDED (hr )) {// connect to the database ----------------------------------------- pConnection-> Open (strConnect, "", "", adConnectUnspecified ))}

4. Understand the ADO record set

After creating an ADO connection, we can access the database through the ADO record set. similarly, you must create an object before using the record set. call the Open Method to Open the record set. it can not only execute common SQL statements, but also call stored procedures and so on:
Open (VARIANT Source, VARIANT ActiveConnection, CursorTypeEnum CursorType, LockTypeEnum LockType, LONG Options ).
The ActiveConnection parameter is a valid Connection object name, which is the ADO Connection object we mentioned above.

CursorTypeThe parameter refers to the record set cursor type, which is described in the official documents as follows:
1. adOpenForwardOnly only forwards the cursor. The default value is. Except for the forward scrolling of a record, it is the same as a static cursor. When you only need to move the record set one way, you can use it to improve performance.
2. adOpenKeyset key set cursor. Although records deleted by other users cannot be accessed from your record set, except for records added by other users, the keyset cursor is similar to the dynamic cursor. You can still see the data changed by other users.
3. adOpenDynamic dynamic cursor. You can see the ADD, change, and delete operations made by other users. All types of data can be moved in the record set, but not the bookmarked operations not supported by the provider.
4. adOpenStatic static cursor. It can be used to find static copies of data or the set of records that generate reports. Addition, modification, or deletion made to other users are not visible.
We can use the default adOpenStatic type instead of having to worry about it.

LockTypeParameter to indicate when to lock the record:
AdLockReadOnly (default) read-only-data cannot be changed.
AdLockPessimistic locking (one by one)-The provider completes the work required to ensure successful record editing, usually by locking the records of the data source at the time of editing.
AdLockOptimistic open locks (one by one)-The provider uses open locks to lock records only when the Update method is called.
AdLockBatchOptimistic open batch Update-used in batch update mode (relative to the immediate update mode ).

OptionsThe parameter indicates the operation type:
Adshorttext indicates that strSQL is a command text, that is, a common SQL statement.
AdCmdTable indicates that ADO generates an SQL query to return all rows from the table named in strSQL.
AdCmdTableDirect indicates that all rows are returned in the table named in strSQL.
AdCmdStoredProc indicates that strSQL is a stored procedure.
AdCmdUnknown indicates that the command type in the strSQL parameter is unknown.
Ad1_file indicates that Recordset should be restored (saved) from the file named in strSQL.
AdAsyncExecute indicates that strSQL should be executed asynchronously.
AdAsyncFetch indicates that all the remaining rows should be asynchronously extracted after the Initial quantity specified in the Initial Fetch Size attribute is extracted. if the required row has not been extracted, the main thread will be blocked until the row is available again.
AdAsyncFetchNonBlocking indicates that the main thread has never been blocked during extraction. If the requested row has not been extracted, the current row is automatically moved to the end of the file.
Alas, it's another big string. If you just want to execute an SQL statement, set it to adshorttext. In this way, Source is the SQL statement you want to execute.
For example:

LPCSTR strSQL = "select * from vckbasetable";_RecordsetPtr pRecordset;pRecordset.CreateInstance("ADODB.Recordset");pRecordset->Open(_bstr_t(strSQL), _variant_t((IDispatch*)pConnection, true), adOpenStatic, AdLockOptimistic , adCmdText);

There are two methods to read the value of a specified field in the record set in ADO:
First:

FieldsPtr pFields; pRecordset-> get_Fields (& pFields); pFields-> Item [L "COLUMN_NAME"]-> Value; // or pFields-> Item [long (index)] -> Value; // where index is an integer or a long integer. the GetFields () function returns a pointer to the field set object of the record set object.

Second:

PRecordset-> get_Collect ("COLUMN_NAME"); // or pRecordset-> get_Collect (long (index ));

All of them will return a value of the _ variant_t type. The latter method is recommended.
For example:

int ncol = rset.GetFieldsCount();while (!rset.IsEOF()) {for (int i = 0; i < ncol; i++){rset.GetValueString(value, (long)(i));}rset.MoveNext();}

4. Understand ADO fields.

A record set usually contains multiple fields. Through access records, we can obtain a lot of useful information, such as the field name, field data type, defined width, and actually occupied width:
Generally, the get_Fields method of the record set is used to obtain the field set object:

FieldsPtr pFields;Recordset->get_Fields(&pFields);

Then you can obtain the corresponding field object:

Long lIndex = 0; FieldPtr pf = pFields-> GetItem (_ variant_t (lIndex); // or: FieldPtr pf = pFields-> GetItem ("COLUMN_NAME ");

Field objects have many useful attributes. For more information, see my source code or other related materials. For example:

Get_ActualSize (long * pl) // actual width get_Attributes (long * pl) // attribute get_DefinedSize (long * pl) // defines the width (in bytes, for example, integer 4, long integer is 8 ...) get_Name (BSTR * pbstr) // field name get_Type (DataTypeEnum * pDataType) // value of the Data Type get_Value (VARIANT * pvar) //

With the above basic understanding of ADO, we will officially start to write the application, please refer to the following.

5. Write the ADO application.

Before using ADO, we also need to add the following statement to introduce the ADO Library to the project.

#import "c:/program files/common files/system/ado/msado15.dll"         no_namespace rename("EOF","adoEOF") 

The specific paths may vary depending on the machine installation settings.
In addition, the following warning message is displayed during compilation:
Msado15.tlh (405): warning C4146: unary minus operator applied to unsigned type, result still unsigned
We recommend that you ignore this issue on MSDN. If you really don't want to see it, you can add the following code in stdafx. h:
# Pragma warning (disable: 4146)
In this way, the warning information will not appear again.

 

ADO uses COM, so you must initialize COM before using ADO. Otherwise, it cannot be used. you can use AfxOleInit () for initialization, but it can only be initialized once. You cannot call this function multiple times. We recommend that you initialize it in the InitInstance method of the APP class of the application.
The general information above is described in detail, so I will not elaborate on it. Let's take a look at how to encapsulate the two classes.

Vi. ADO encapsulation class: CAdoConnection and CAdoRecordSet

First of all, it is necessary to Connect to the data source. The function to Connect to the data source is the Connect Method of _ CAdoConnection. It encapsulates the CreateInstance and Open methods of the ADO connection object:
Let's take a look at how I encapsulate it:

BOOL CAdoConnection: Connect (LPCTSTR strConnect, long lOptions) {m_strConnect = strConnect; try {// create a Connection object ------------------------- HRESULThr = m_pConnection.CreateInstance ("ADODB. connection "); if (SUCCEEDED (hr) {// connect to the database if (SUCCEEDED (m_pConnection-> Open (strConnect," "," ", lOptions ))) {return TRUE ;}} catch (_ com_error e) {TRACE (_ T (":( error occurred when connecting to the database: % s/n"), e. errorMessage (); return FALSE;} catch (...) {TRACE (_ T (":( unknown error occurred when connecting to the database:") ;}return FALSE ;}

Before use, define a CAdoConnection class object such as m_adoConnection, for example:

CString strSrcName = "E: // Access // datebase. mdb "; // assume that the database file CString strConnect =" Provider = Microsoft. jet. OLEDB.4.0; Data Source = "+ strSrcName; m_adoConnection.Connect (LPCSTR (strConnect ));

In this way, the data source is connected.

The next step is to open the record set. Below I encapsulate its open method: You can ignore the three parameters and directly pass an SQL statement to it.

HRESULT CAdoRecordSet: Open (LPCTSTR strSQL, long lOption, CursorTypeEnum CursorType, LockTypeEnum LockType) {try {if (m_pConnection = NULL) {return-1 ;} else if (m_pRecordset = NULL) {m_pRecordset.CreateInstance ("ADODB. recordset ");} m_pRecordset-> Open (_ bstr_t (strSQL), _ variant_t (IDispatch *) m_pConnection-> GetConnection (), true), CursorType, LockType, lOption ); if (m_pRecordset = NULL) {return-1;} return (M_pRecordset-> adoEOF )? 0: 1;} catch (_ com_error e) {TRACE (_ T (":( error occurred when opening record set: % s/n"), e. errorMessage (); return-1 ;}}

For example, we can use it like this:

CAdoRecordSet rset; rset. setAdoConnection (& (GetDocument ()-> m_adoConnection); // remember to specify the corresponding connection object first; otherwise, an error will occur. m_strSQL = "select * from city"; // SQL statement to be executed. rset. open (m_strSQL );

7. Write a Data Query Tool(I only briefly introduced the implementation process. For details, refer to the source code ):

7.1 connect to the database

First, write the code to connect to the database. Therefore, I wrote a dialog box class (CLogoDig) to select different data sources,
Then, add the CAdoConnection class member variable in the View class (which is derived from the CFromView class) and add the following connection functions.

Void CAccessView: OnFileConnect () {CLogoDig dlg; if (dlg. doModal () = IDOK) {if (dlg. m_nsctype = 0) {CString strConnect = "Provider = Microsoft. jet. OLEDB.4.0; Data Source = "+ dlg. m_strSrcName; GetDocument ()-> m_adoConnection.Disconnect (); if (! GetDocument ()-> m_adoConnection.Connect (LPCSTR (strConnect) {AfxMessageBox ("failed to connect to the database! "); Return ;}} else if (dlg. m_nsctype = 1) {CString strConnect = "Provider = SQLOLEDB.1; Data Source =" + dlg. m_strSrcName + "; Initial Catalog =" + dlg. m_strDbName + "; User ID =" + dlg. m_strUserName + "; PWD =" + dlg. m_strPassWord; GetDocument ()-> m_adoConnection.Disconnect (); if (! GetDocument ()-> m_adoConnection.Connect (LPCSTR (strConnect) {AfxMessageBox ("failed to connect to the database! "); Return ;}( (CMainFrame *) GetParentFrame ()-> m_wndLeftBar.InitTree ();}}

7.2 CCoolControlBar and CMSFlexGrid

In this data query tool, we use two other classes: CCoolControlBar and CMSFlexGrid .. The former is a control bar class that I wrote that can dynamically change the size, and the latter is a grid control that has fewer functions, however, it is sufficient to display only the query results.
In the edit CFromView dialog box resource view, right-click the resource in the edit dialog box, select insert ActiveX control, and then select the Microsoft FlexGrid control. In the Class Wizard, add a variable for it on the "Member Variables" page. The Class Wizard will prompt you to introduce the variable to the head file and confirm that several classes will be added to your class view.
First, we will introduce several functions to be used:
SetCols sets the total number of Columns
SetFixedCols sets a fixed number of columns, that is, the type with the background gray.
SetRows sets the total number of rows
SetCol: set the current column
SetRow
SetText sets the text of the current grid. The position is determined by the above two functions.
SetColWidth: Set the column width.

7.3 data display and Processing

In addition, we also need two editing controls to enter SQL statements and Display error messages, and dynamically set their positions in the OnSize message processing function:

Void CAccessView: OnSize (UINT nType, int cx, int cy) {CFormView: OnSize (nType, cx, cy); if (m_wndGrid.GetSafeHwnd ()! = NULL) // whether the control has been created {m_editError.MoveWindow (0, 10, cx, cy-50); m_wndGrid.MoveWindow (0, 0, cx, cy-40); m_editSQL.MoveWindow (0, cy-40, cx, 40 );}}

Add a menu item and add the corresponding response function to start executing the entered SQL statement:

void CAccessView::OnRun() {UpdateData();UpdateGrid();}

The query result is displayed as follows:

Void CAccessView: UpdateGrid () {// whether the connection object is opened ------------------------------------------ if (! GetDocument ()-> m_adoConnection.IsOpen () {AfxMessageBox ("the database is not opened or closed! "); Return;} // hide the two controls first; m_wndGrid.ShowWindow (SW_HIDE); m_editError.ShowWindow (SW_HIDE); CAdoRecordSet rset; rset. setAdoConnection (& (GetDocument ()-> m_adoConnection); if (rset. open (m_strSQL, ad1_text )! = 1) {// query error. The error information is obtained and displayed in the edit control ---------------- m_strError = GetDocument ()-> m_adoConnection.GetLastError (); UpdateData (FALSE); m_editError.ShowWindow (SW_SHOW ); return;} // some of the functions I use below may not be encapsulated in the class, so try blocks are used, just in case, :( try {// obtain the number of fields and number of rows in the record set -------------------------------- int nrow = rset. getRecordCount (); int ncol = rset. getFields ()-> Count; // set the number of columns and the number of rows of the Grid Control ------------------------------ m_wndGrid.SetCols (ncol); m_wnd Grid. setRows (nrow + 1); // leave one more row to show the field name m_wndGrid.SetFixedCols (0); CString value; // fill in the field name m_wndGrid.SetRow (0); for (int I = 0; I <ncol; I ++) {m_wndGrid.SetCol (I); m_wndGrid.SetText (LPCSTR (rset. getFieldName (I); // set the approximate width of the current column ------------------------------- int nwidth = rset. getFieldDefineSize (I) * 200; nwidth = nwidth> 2000? 2000: nwidth; m_wndGrid.SetColWidth (I, nwidth);} // read the record set ----------------------------------------------- int n = 1; while (! Rset. isEOF () {m_wndGrid.SetRow (n); n ++; for (int I = 0; I <ncol; I ++) {m_wndGrid.SetCol (I); rset. getValueString (value, (long) (I); m_wndGrid.SetText (LPCTSTR (value);} rset. moveNext ();} m_wndGrid.ShowWindow (SW_SHOW);} catch (_ com_error) {return ;}}

After the record set is used up, you can Close it with Close () in time to release the corresponding resource. CAdoRecordSet class constructor, and the record is automatically closed.

7.4 other functions (obtain database information)

We can also add another feature: In the tree control on the right, display the forms owned by the database and all their fields. this section is mainly used to demonstrate how to obtain some information about the database.
The main implementation functions are as follows:

Void CLeftBar: InitTree () {CAdoRecordSet rset; _ bstr_t Value; CString strTablename = ""; HTREEITEM item = TVI_ROOT; try {if (GetDocument ()-> m_adoConnection.GetConnection () -> State! = AdStateOpen) return; m_ctrlTree.DeleteAllItems (); // obtain the database field information. Export rset = GetDocument ()-> m_adoConnection.OpenSchema (adSchemaColumns); while (! Rset. IsEOF () {CString strValue; // obtain the table name ----------------------------------------------- rset. GetValueString (strValue, "TABLE_NAME"); if (strValue! = StrTablename) {strTablename = strValue; item = m_ctrlTree.InsertItem (LPCTSTR) strTablename, 1, 1);} // obtain the field name struct Value = rset. getFields ()-> Item [L "COLUMN_NAME"]-> Value; m_ctrlTree.InsertItem (LPCTSTR) Value, 2, 2, item); rset. moveNext () ;}} catch (_ com_error e ){}}

Of course, you can also query the views and indexes of the database. the CCoolControlBar class is also used here, but we will not elaborate on how to use it here. After all, we mainly talk about ADO. If you are interested in it, please contact me. :)

A small queryer is almost complete.

8. Other reference information(There are a lot of detailed comments in the source code that I will not repeat one by one)
_ Connection, _ Recordset, and _ Field references