VC operation Database Technology

VC uses the database to store and manage the required data. This article describes ODBC, DAO, OLEDB, and ADO technologies. 1. ODBCODBC (OpenDatabaseConnection) is a database application design interface using SQL. It establishes a set of specifications and provides a set of standard APIs for database access (

VC uses the database to store and manage the required data. This article describes ODBC, DAO, OLE/DB, and ADO technologies. 1. ODBC (Open Database Connection) is a database application design interface that uses SQL. It establishes a set of specifications and provides a set of standard APIs for database access (

VC uses the database to store and manage the required data. This article describes ODBC, DAO, OLE/DB, and ADO technologies:

1. ODBC
ODBC (Open Database Connection) is a Database application design interface that uses SQL. It establishes a set of specifications and provides a set of standard APIs for database access (application programming interfaces). It provides an object-oriented method for ODBC programming, this makes it very convenient to use MFC to create ODBC applications. When using ODBC APIs, you must introduce the header

"SQL. h", "sqlext. h", "sqhypes. h ". MFC encapsulates ODBC mainly by developing the CDataBase class and the CRecordSet class. CDataBase class: This class can be used to operate data sources. Construct a CDataBase object and call its Open or OpenEx member functions to establish a data source connection. CRecordSet: the record set class provides the result record set extracted from the data source and operates on it. It is returned through an SQL statement and can be queried or stored in tables and attempts. Note that in an application, the CRecordSet class is generally used instead of its derived class, because when you use the MFC Class Wizard to generate a derived Class of the CRecordSet Class, the MFC Class Wizard automatically adds the member variables of the fields in the corresponding database table and automatically reloads the member function DoFieldExchange () of the CRecordSet Class (). The RFX function is used to exchange data between database fields and record set field data member variables. When using mfc odbc in a program, in addition to logging on to the data source, you also need to include afxdb. h In stdafx. h.

2. DAO
ODBC is easy to use. You only need to log on to the data source in the Windows Control Panel to Connect ODBC to the corresponding database. However, using ODBC to access the database is slow. My colleague ODBC del needs a lot of corresponding components to support it. DAO overcomes ODBC's shortcomings. Because it actually enables access to the database through the Microsoft Jet Database Engine, DAO method is very suitable for access to the Jet Database (. mdb ).

You do not need to log on to the data source using DAO. You only need to specify the database path. The ODBC and DAO methods of VC ++ are similar in usage form. The basic class of the ODBC access record set is CRecordSet, And the DAO is CDaoRecordSet. The difference is "DAO ", you can dynamically create Access databases and tables by defining objects of these classes and calling corresponding member functions. RFX_LongBinary is used for ODBC database access. The process of using AppWizard is basically similar to that of using ODBC. You only need to select the created DAO data source when selecting the data source, and then select the source database and table from the system, make it automatically generate this part of the code. To use mfc dao in an application, follow these steps:

First, make sure that. the h header file contains afxado. h header file: # include "afxdao. h "then create your own CDaoRecordSet class (set to MyDaoRecordSet) and connect it to the table of the database you need. At the same time, add m_pdb = new CDaoDataBase (); m_pdb-> open (); m_pset = new MyDaoRecordSet (m_pdb) in the OnOpenDocument () function of the document class ); m_pset-> open ();

Finally, create your own dialog box and select the ClassInfo page of the MFC Class Wizard. Fill in the previously created MyDaoRecordSet in the ForeignClass item, fill in m_pset in the ForeignVariable item, and map variables to each edit column.

3. OLE/DB

DAO also has a major disadvantage: It mainly refers to the version between the mfc dao and the Jet Database Engine. Because VC ++.. NET environment and Wizard no longer support DAO (although the DAO class is thrown). You must manually write the tasks that were previously executed by the Wizard. Microsoft recommends that you use the OLE/DB template or ODBC for the new project, OLE/DB accesses the database based on the COM interface, so it inherits all the features of the COM interface and has good stability, this allows the program to be applied to any data source that provides the data program. Therefore, programs developed using OLE/DB have good portability. In addition, OLE/DB improves database access efficiency by dividing database functions into customers and servers. The traditional database access technologies (ODBC and DAO) mentioned above can only access relational databases, while OLE/DB can access any form of file systems, including traditional relational databases and non-relational data sources and user-defined file formats. However, in Database Access Based on COM technology, ADO technology is usually used instead of OLE/DB.

4. ADO

The ADO object model includes seven objects: 1) the data Connection object establishes a data exchange environment, and the application accesses data through the Connection; 2) the Command) the object uses the established Connection to publish commands to complete database operations; 3) RecordSet (Object Storage) The results returned by the query command; 4) Error object (Error) an error occurs during the application of ADO. 5) the Property object represents the specific Property of the data provider. 6) The Field object represents a domain of a record set; 7) The Parameter object represents the parameters in the SQL stored procedure or Parameter query. When there are three objects, Connection, Command, and RecordSet, the subject objects can be created and released independently. The Connection object is used to establish and maintain connections to data sources. The Command object creates and executes database operation commands on data. You can use this object to execute SQL statements or call stored procedures; after the Command object is executed, A RecordSet object is returned. The RecordSet object contains a record set returned after the database operation is performed. This object allows you to browse and update the records saved in the database.

ADO is based on the COM technology. Therefore, you must initialize the COM environment before using the ADO object. Add the following code to the member functions of the VC project application class Initinstance to initialize the COM environment:

::Coinitialize(NULL);

1) there is no class in MFC that can be used with ADO. Therefore, to use ADO objects in VC ++ programs, you must first import the ADO dynamic link library, in the stadafx. add the following code to the h header file:

#import "C:\Program Files\Common Files\System\ado\msado15.dll\no_namespace" Rename("EOF","EndOfFile");

The no_namespace attribute indicates that the editor does not inherit its own namespace, and rename ("EOF", "EndOfFile") indicates replacing all "EOF" with "EndOfFile ". To facilitate the use of various ADO classes, the # import command should be called at the beginning of the program. The ADO class library contains three smart pointers: ConnectionPtr, CommandPtr, and RecordSetPtr, the ConnectionPtr pointer is used to manage the information required to connect to a data source provider. It provides many methods, such as Open and Close, to Open and Close a Live Connection to the database provider ), you can also execute commands on the data source through the Excute method, while RecordSetPtr encapsulates the records returned by the data source provider. It can be used to browse the returned results, or to insert, delete, or modify existing records. To ensure code security, you must add the "try... catch..." block to the function call:

try{m_pRs = m_pConn->Excute(...)return m_pRs;}cath(_corn_error& a_pComError){TraceCOMExceptions(a_pComError));return NULL;}

2) use the Connection object to establish a Connection with the database
3) use the established Connection object to execute SQL commands through the Command object. If data is read from the database, the extracted database is put into the RecordSet object.
4) disconnect from the database and release the object after use. You need to call the following code to release the COM resources occupied by the program. Add the following code to the ExitInstance member function of VC:

::Couninitialize(NULL);

Focus on the smart pointer of ADO:

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.

_ ConnectionPtr m_pConnection; // initialize COM, create ADO Connection, and other operations AfxOleInit (); m_pConnection.CreateInstance (_ uuidof (Connection )); // try... catch () to capture the error message, // because it sometimes produces unexpected errors. Jingzhou xutry {// open the local Access library Demo. mdbm_pConnection-> Open ("Provider = Microsoft. jet. OLEDB.4.0; Data Source = Demo. mdb "," "," ", adModeUnknown);} catch (_ com_error e) {AfxMessageBox (" database connection failed, confirm database Demo. whether mdb is in the current path! "); Return FALSE ;}

Close a database connection. If the connection status is valid, Close it with the Close method and assign it a null value. The Code is as follows:

if(m_pConnection->State)m_pConnection->Close();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:

_ RecordsetPtrm_pRecordset; m_pRecordset.CreateInstance (_ uuidof (Recordset); // try is recommended for statements in ADO operations... catch () to capture the error message, // because it sometimes produces unexpected errors. Jingzhou xutry {m_pRecordset-> Open ("SELECT * FROM DemoTable", // query all fields in the DemoTable table theApp. m_pConnection.GetInterfacePtr (), // obtain the IDispatch pointer adOpenDynamic, adLockOptimistic, adshorttext);} catch (_ com_error * e) {AfxMessageBox (e-> ErrorMessage ());}

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:

_ Variant_t var; CString strName, strAge; try {if (! M_pRecordset-> BOF) m_pRecordset-> MoveFirst (); else {AfxMessageBox ("table data is empty"); return ;} // read the fields in the database and add them to the list box while (! M_pRecordset-> adoEOF) {var = m_pRecordset-> GetCollect ("Name"); if (var. vt! = VT_NULL) strName = (LPCSTR) _ bstr_t (var); var = m_pRecordset-> GetCollect ("Age"); if (var. vt! = VT_NULL) strAge = (LPCSTR) _ bstr_t (var); m_AccessList.AddString (strName + "-->" + strAge); m_pRecordset-> MoveNext ();} // The default list points to the first item, and moves the record pointer and displays m_AccessList.SetCurSel (0);} catch (_ com_error * e) {AfxMessageBox (e-> ErrorMessage ());}

Insert records. 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:

Try {// write the values of fields m_pRecordset-> AddNew (); m_pRecordset-> PutCollect ("Name", _ variant_t (m_Name); m_pRecordset-> PutCollect ("Age ", atol (m_Age); m_pRecordset-> Update (); AfxMessageBox ("inserted successfully! ");} Catch (_ com_error * e) {AfxMessageBox (e-> ErrorMessage ());}

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, when we often need to Move the record pointer to any record location at will, we can use the Move (Record Number) method. 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:

Try {int curSel = m_AccessList.GetCurSel (); // first move the pointer to the first record, and then move the record pointer m_pRecordset-> MoveFirst () relative to the first record (); m_pRecordset-> Move (long (curSel);} catch (_ com_error * e) {AfxMessageBox (e-> ErrorMessage ());}

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:

Try {// assume that m_pRecordset-> MoveFirst (); m_pRecordset-> Move (1); // m_pRecordset-> PutCollect ("Name ", _ variant_t (m_Name); m_pRecordset-> PutCollect ("Age", atol (m_Age); m_pRecordset-> Update ();} catch (_ com_error * e) {AfxMessageBox (e-> ErrorMessage ());}

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, and Delete it using the Delete () method and Update () method to Update the database. The Code is as follows:

Try {// assume that the second record m_pRecordset-> MoveFirst (); m_pRecordset-> Move (1); // m_pRecordset-> Delete (adAffectCurrent) is deleted from 0 ); // The adAffectCurrent parameter is used to delete the current record m_pRecordset-> Update ();} catch (_ com_error * e) {AfxMessageBox (e-> ErrorMessage ());}

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

m_pRecordset->Close();m_pRecordset = NULL;      

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:

_ CommandPtrm_pCommand; m_pCommand.CreateInstance (_ uuidof (Command); m_pCommand-> ActiveConnection = m_pConnection; // assign the database connection to m_pCommand-> CommandText = "SELECT * FROM DemoTable "; // SQL statement m_pRecordset = m_pCommand-> Execute (NULL, NULL, ad1_text); // Execute the SQL statement and return the record set

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:

_ CommandPtrm_pCommand; m_pCommand.CreateInstance (_ uuidof (Command); m_pCommand-> ActiveConnection = m_pConnection; // assign the database connection to m_pCommand-> CommandText = "Demo "; m_pCommand-> Execute (NULL, NULL, ad1_storedproc );

How to connect to and open a database:

1) connection to the Access database through the JET Database Engine:

For 2003: m_pConnection-> Open ("Provider = Microsoft. jet. OLEDB.4.0; Data Source = Demo. mdb "," "," ", adModeUnknown); for 2007: m_pConnection-> Open (" Provider = Microsoft. jet. OLEDB.12.0; Data Source = Demo. 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.

----- The database connection string template ---------- for connecting to SQL Server from ADO: "SQL server and windows" Provider = SQLOLEDB.1; Persist Security Info = True; User ID = User name; password = Password; Initial Catalog = database name; Data Source = SQL Server Name authentication mode: "windows only" Provider = SQLOLEDB.1; Integrated Security = SSPI; Persist Security Info = False; initial Catalog = database name; Data Source = SQL Server Name

Note: The effects of the two methods are the same. Security issues are different. I suggest using the first method (Authentication Mode: "SQL server and windows ")

The following describes two authentication modes on the Internet:

The focus is on trusted connections and untrusted connections.

Windows authentication is more secure than Hybrid Authentication. When using this connection mode, SQL does not judge the sa password, but performs authentication only based on the user's windows permissions. This is called "trusted connection ", however, during remote connection, you cannot log on because of NTML verification.

Hybrid Authentication means that when a local user accesses SQL, windows authentication is used to establish a trusted connection. When a remote user accesses SQL, windows Authentication fails, for SQL server Authentication (sa users can also log on to SQL), and establish a "untrusted connection", so that remote users can log on.

More directly, it is windows authentication. If the sa password is not verified and the windows logon password is incorrect, you cannot access SQL. in hybrid mode, you can use windows authentication to log on, you can log on remotely using the sa password.

To be accurate, the Hybrid Authentication mode is based on Windows identity authentication and SQL Server identity Hybrid Authentication. In this mode, the system determines whether the account is trusted in the Windows operating system. For trusted connections, the system uses Windows Authentication instead of trusted connections, this connection includes not only remote users but also local users. SQL Server automatically verifies the connection by the existence of the account and password matching. For example, when an SQL Server instance runs on Windows 98, hybrid mode is required because Windows 98 does not support Windows Authentication mode.