Visual c ++ database development-ado

Using ADO in VC to perform database operations
ADO is a programming interface at the application layer. It accesses data through the COM interface provided by ole db. It is suitable for various client/server application systems and web-based applications, especially in some scripting languages, accessing database operations is the main advantage of ADO. ADO is a set of object hierarchies established with automation technology. It is more flexible than other object models such as Dao (Data Access Object) and rdo (Remote Data Object, it is more convenient to use and data access efficiency is higher. SQL is a powerful database operating system. With the combination of ADO and SQL statements, we can perform a series of operations on the database, such as creating a database, creating a table, and creating an index, implement multiple database queries, advanced queries, data aggregation, and other technologies. The following routine describes how to perform database operations through the combination of ADO and SQL statements.

Step 1: Create a database test. mdb through access.

Step 2: create a single document project testado. All options take the default value.

Step 3: Initialize the com Library

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 ();
......

Step 4: Use the # import command to introduce the ADO database

We add the following statement to stdafx. h:

# 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.

Step 5: Define a pointer to the connection object in testadoview. h: _ connectionptr _ pconnection;

Step 6: Add the following code:

Void ctestadoview: oninitialupdate () { Cview: oninitialupdate (); 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 /// In the above sentence, the provider in the connection string is for the Access2000 environment. For Access97, you need to change it to provider = Microsoft. Jet. oledb.3.51 ;} } } Catch (_ com_error e) // catch an exception { Cstring errormessage; Errormessage. Format ("failed to connect to the database! \ R \ n error message: % s ", E. errormessage ()); Afxmessagebox (errormessage); // displays the error message. } }

Step 7: Close the connection object in the Destructor and release it. The Code is as follows:

Ctestadoview ::~ Ctestadoview () { M_pconnection-> close (); M_pconnection.release (); }

Step 8: add the menu item "create database table", add the corresponding message processing function, and add the Code as follows:

Void ctestadoview: onaddtable () { _ Variant_t recordsaffected; M_pconnection-> execute ("create table new (ID integer, username text, old integer)", & recordsaffected, ad1_text ); }

RunProgramRun the "Add Table" command in the menu. We can see that a new table has been added to the database, and the fields in the new table have our defined fields.

Step 9: add the menu item "delete database table", add the corresponding message processing function, and add the Code as follows:

Void ctestadoview: ondeletetable () { _ Variant_t recordsaffected; M_pconnection-> execute ("Drop table new", & recordsaffected, ad1_text ); }

Run the program and run the "Delete table" command in the menu. We can see that the new table added in the database has been deleted.

Step 10: add the menu item "add a column", add the corresponding message processing function, and add the Code as follows:

Void ctestadoview: onaddcolumn () { _ Variant_t recordsaffected; M_pconnection-> execute ("alter table new add newcolumn1 integer", & recordsaffected, ad1_text ); }

Run the program and run the "add a column" command in the menu. We can see that a new column has been added to the new table just added to the database.

Step 2: add the menu item "delete a column", add the corresponding message processing function, and add the Code as follows:

Void ctestadoview: onaddcolumn () { _ Variant_t recordsaffected; M_pconnection-> execute ("alter table new add newcolumn1 integer", & recordsaffected, ad1_text ); }

Run the program and run the "delete a column" command in the menu. We can see that the new column in the new table just added to the database has been deleted.

Step 2: add the menu item "add record", add the corresponding message processing function, and add the Code as follows:

Void ctestadoview: onaddrecord () { _ Variant_t recordsaffected; For (INT I = 1; I <10; I ++) { Cstring strsql; Strsql. Format ("insert into new (ID, username, old) values (% d, 'Washington ', % d)", I, I * 9 ); M_pconnection-> execute (_ bstr_t) strsql, & recordsaffected, ad1_text ); }

Run the program and run the "add record" command in the menu. We can find that nine new records have been added to the new table just added to the database.

Step 2: add the menu item "Add 1 to the old field", add the corresponding message processing function, and add the Code as follows:

Void ctestadoview: onoldaddone () { _ Variant_t recordsaffected; M_pconnection-> execute ("update new set old = old + 1", & recordsaffected, ad1_text ); }}

Run the program and run the "old record Add 1" command in the menu. We can find that all the old fields of the nine new records in the new table added in the database are automatically added with 1.

Step 2: add the menu item "count records", add the corresponding message processing function, and add the Code as follows:

Void ctestadoview: ontotalrecords () { _ Recordsetptr m_precordset; _ Variant_t recordsaffected; M_precordset = m_pconnection-> execute ("select count (*) from new where ID> 0", & 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 (); // close the record set Cstring message; Message. Format ("% d records in total", vcount. lval ); Afxmessagebox (Message); // display the number of current records }

Run the program and run the "count records" command in the menu to obtain the number of records in the database.

Step 2: add the menu item "Set ID as index", add the corresponding message processing function, and add the Code as follows:

Void ctestadoview: onsetidindex () { _ Variant_t recordsaffected; M_pconnection-> execute ("create unique index ID on new (ID)", & recordsaffected, ad1_text ); }

Run the program and run the "Set ID to index" command in the menu. We can find that the ID in the database is set to index.

Step 2: Add menu items "Data Summary", "sum of old fields", "average of old fields", "minimum of old", and "maximum of old fields ", add the corresponding message processing function, and then add the Code as follows:

Void ctestadoview: onoldmax () { _ Recordsetptr m_precordset; _ Variant_t recordsaffected; M_precordset = m_pconnection-> execute ("select max (old) from new", & 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 (); // close the record set M_precordset.release (); Cstring message; Message. Format ("maximum value: % d", vcount. lval ); Afxmessagebox (Message ); } Void ctestadoview: onoldmin () { _ Recordsetptr m_precordset; _ Variant_t recordsaffected; M_precordset = m_pconnection-> execute ("select Min (old) from new", & 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 (); // close the record set M_precordset.release (); Cstring message; Message. Format ("minimum value: % d", vcount. lval ); Afxmessagebox (Message ); } Void ctestadoview: onoldtotal () { _ Recordsetptr m_precordset; _ Variant_t recordsaffected; M_precordset = m_pconnection-> execute ("select sum (old) from new", & 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 (); // close the record set M_precordset.release (); Cstring message; Message. Format ("Total % d", (long) vcount ); Afxmessagebox (Message ); } Void ctestadoview: onoldaverage () { _ Recordsetptr m_precordset; _ Variant_t recordsaffected; M_precordset = m_pconnection-> execute ("select AVG (old) from new", & 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 (); // close the record set M_precordset.release (); Cstring message; Message. Format ("average value: % d", (long) vcount ); Afxmessagebox (Message ); }}

Run the program and run the summary command in the menu to obtain the summary information.
Use crecordset separately

Generally, Appwizard automatically generates a crecordset derived class in the database application, and associates the derived class with the table in a data source with the child window in the view. But sometimes this will affect the flexibility of the program. At this time, we can use the crecordset class separately. Using the crecordset class, we can execute SQL statements and read the data in the result set.

First, we need to include the header file afxdb. H. You can add # include to the stdafx. h file. In addition, when using crecordset, there must be another cdatabase object. This object is used to manage data source connections. Then, you can generate a crecordset object and run the SQL statement using bool crecordset: open (uint nopentype = afx_db_use_default_type, lpctstr lpszsql = NULL, DWORD dwoptions = none.

After successful execution, you can call the following function to scroll the cursor to read data.

Movefirst	Move the cursor to the first record
Movenext	Move the cursor to the next record
Moveprev	Move the cursor to the previous record
Movelast	Move the cursor to the last record
Isbof	Checks whether the cursor is on the first record
Iseof	Checks whether the cursor is on the last record.
Getfieldvalue	Data in the result

The following code is used :/*

Assume that cdatabase m_dbconn is a member variable. Assume that a table has the following SQL statement: Create Table Table1 (loc_id not null) */ Void cyourclass: connecttodb () {// Connect to the database Bool Fok = m_dbconn.open ("test "); Trace ("Connect Fok = % d \ n", m_dbconn ); } Void cyourclass: Select () {// Execute the SELECT statement Crecordset Rec (& m_dbconn ); Bool Fok = Rec. Open (crecordset: forwardonly, "select loc_id from Table1 order by loc_id "); Trace ("select Fok = % d \ n", Fok ); Trace ("Returned columns: % d \ n", Rec. getrowsetsize ()); Cstring szresult; While (! Rec. iseof ()) { Rec. getfieldvalue (INT) 0, szresult ); Rec. movenext (); Trace ("Fetch: % s \ n", szresult ); } }

In addition, crecordset: getfieldvalue has many prototype types. You can obtain data by specifying the column position or field name:

Void getfieldvalue (lpctstrLpszname, Cdbvariant &Varvalue, ShortNfieldtype= Default_field_type ); Void getfieldvalue (shortNindex, Cdbvariant &Varvalue, ShortNfieldtype= Default_field_type ); Void getfieldvalue (lpctstrLpszname, Cstring &Strvalue); Void getfieldvalue (shortNindex, Cstring &Strvalue);

If you use a variable of the cdbvariant type to obtain the result, you can get any type of result. The Data Types contained in the variable are recorded in the cdbvariant: m_dwtype member variable. Based on the value of the variable, you can determine the data type and reference the corresponding member variables in the cdbvariant object.