ODBC programming example in Visual C ++

ODBC programming example in Visual C ++

Microsoft developer Studio provides 32-bit ODBC drivers for most standard database formats. These standard data formats include SQL Server, access, paradox, dBase, Foxpro, Excel, Oracle, and Microsoft text. If you want to use other data formats, you need to install the corresponding ODBC driver and DBMS. After you use your DBMS database management function to generate a new database mode, you can use ODBC to log on to the data source. For your applications, you can register many different databases by installing drivers. For more information about how to log on to the database, see ODBC online help. 1. ODBC database class provided by MFC The MFC base class library of Visual C ++ defines several database classes. When using ODBC programming, cdatabase, crecordset, and crecordview are often used ). A cdatabase object can be used to connect to a data source. The crecordset Class Object provides a set of Records extracted from the data source. The crecordset object is usually used in two forms: dynamic row set (dynasets) and snapshot set (snapshots ). Dynamic row sets can be synchronized with changes made by other users, while snapshot sets are a static view of data. Each form provides a set of records when the record set is opened. The difference is that when a record is rolled to a dynamic row set, changes made to this record by other users or other record sets in the application are displayed accordingly. The crecordview class object can display database records in the form of controls. This view is directly connected to the table view of a crecordset object. II. Application ODBC programming Appwizard with Visual C ++ can automatically generate an ODBC application framework. Step: Open the new option in the File menu, select projects, enter the project name, and select MFC Appwizard (exe ), then follow the prompts of Appwizard to perform the operation. If you want to read and write data to the database when Appwizard asks whether the database is supported, select database view with file support. If you want to access the database information and do not want to write back the changes, select the database view without file support. After the database support is selected, the database Source button is activated. Select it to call the data Options dialog box. The database Options dialog box displays the database resources that have been registered with ODBC. Select the database to be operated, for example, super_es. Click OK and the select database tables dialog box appears, lists All tables contained in the selected database. Select the table to be operated and click OK. After the database and data table are selected, you can continue the Appwizard operation as usual. In particular, the view class of the generated application framework (such as csuper_esview) contains a pointer m_pset pointing to the csuper_esset object, which is created by Appwizard, the purpose is to establish a connection between the view form and the record set so that the query results in the record set can be easily displayed on the view form. To establish a connection between the program and the data source, you must use the cdatebase: openex () or cdatabase: open () function for initialization. The database object must be initialized before it is used to construct the record set object. Iii. Instances 1. query records The query records use the crecordset: open () and crecordset: requery () member functions. Before using the crecordset class object, you must use the crecordset: open () function to obtain a valid record set. Once you have used the crecordset: open () function, you can apply the crecordset: requery () function when querying again. When calling the crecordset: open () function, if you pass an opened cdatabase object pointer to the m_pdatabase member variable of the crecordset Class Object, use the database object to establish an ODBC connection; otherwise, if m_pdatabase is a null pointer, create a cdatabase Class Object, connect it to the default data source, and initialize the crecordset class object. The default data source is obtained by the getdefaconnect connect () function. You can also provide the required SQL statement and use it to call the crecordset: open () function, for example, super_esset.open (afx_database_use_default, strsql ); If no parameter is specified, the program uses the default SQL statement to operate the SQL statement specified in the getdefasql SQL () function: Cstring csuper_esset: getdefasql SQL () {Return _ T ("[bsicdata], [minsize]");} For the table name returned by the getdefasql SQL () function, the default operation is the SELECT statement, that is: Select * From basicdata, mainsize In the Query Process, you can also use the member variables m_strfilter and m_strsort of the crecordset to execute the conditional query and result sorting. M_strfilter is a filter string that stores the where condition strings in SQL statements. m_strsort is a Sort string that stores the order by character strings in SQL statements. For example: Super_esset.m_strfilter = "type = 'motor '″; Super_esset.m_strsort = "voltage ″; Super_esset.requery (); The corresponding SQL statement is: Select * From basicdata, mainsize Where type = 'motor' Order by voltage In addition to directly assigning values to m_strfilter, you can also use parameterization. Parameterization allows you to perform conditional query tasks more intuitively and conveniently. To use parameterization, follow these steps: S declares the parameters: Cstring P1; Float P2; S initializes the parameters in the constructor: P1 = _ T (″″); P2 = 0.0f; M_nparams = 2; S: bind the variable with the corresponding column: Pfx-> setfieldtype (cfieldexchange: Param) Rfx_text (pfx, _ T ("p1"), P1 ); Rfx_single (pfx, _ T ("p2"), P2 ); After completing the preceding steps, you can use the parameters for conditional query: M_pset-> m_strfilter = "type =? And voltage =? "; M_pset-> P1 =" Motor ″; M_pset-> P2 = 60.0; M_pset-> requery (); The value of the Parameter Variable replaces "?" In the query string in the order of binding. Wildcard. If the query result is multiple records, you can use the crecordset functions to move (), movenext (), moveprev (), movefirst (), and movelast () to move the cursor. 2. Add records The addnew () function is used to add records, which requires that the database be opened in the allowed way: M_pset-> addnew (); // Add a new record to the end of the table M_pset-> setfieldnull (& (m_pset-> m_type), false ); M_pset-> m_type = "Motor ″; ...... // Enter a new field value M_pset-> Update (); // Save the new record to the database M_pset-> requery (); // Record set reconstruction 3. delete records You can directly use the delete () function to delete records. You do not need to call the update () function after calling the delete () function: M_pset-> Delete (); If (! M_pset-> iseof ()) M_pset-> movenext (); Else M_pset-> movelast (); 4. Modify records Use the Edit () function to modify records: M_pset-> edit (); // Modify the current record M_pset-> m_type = "generator ″; // Modify the field value of the current record ...... M_pset-> Update (); // Save the Modification result to the database M_pset-> requery (); 5. Undo If you want to discard the current operation after adding or modifying a record, you can call the update () function before: Crecordset: Move (afx_move_refresh) to undo the Add or modify mode and restore the current record before the Add or modify mode. The value of afx_move_refresh is zero. 6. Reuse of database connections A member variable m_pdatabase is defined in the crecordset class: Cdatabase * m_pdatabase; It is a pointer to the object database class. If you pass an opened cdatabase Class Object Pointer to m_pdatabase before the crecordset Class Object calls the open () function, you can share the same cdatabase class object. For example: Cdatabase m_db; Crecordset m_set1, m_set2; M_db.open (_ T ("super_es"); // establish an ODBC connection M_set1.m_pdatabase = & m_db; // M_set1 reuse m_db object M_set2.m_pdatabse = & m_db; // M_set2 reuse m_db object 7. Direct Execution of SQL statements Although we can perform most query operations through the crecordset class, and also provide SQL statements in the crecordset: open () function, sometimes we still want to perform some other operations, for example, to create a new table, delete a table, and create a new field, you need to use the cdatabase class to directly execute SQL statements. You can call the cdatabase: executesql () function to directly execute SQL statements: Bool CDB: executesqlandreportfailure (const cstring & strsql) {Try {M_pdb-> executesql (strsql ); // Execute the SQL statement directly} Catch (cdbexception, E) {Cstring strmsg; Strmsg. loadstring (ids_execute_ SQL _failed ); Strmsg + = strsql; Return false ;} End_catch Return true ;} It should be noted that, because the data operation statements provided by different DBMS are different, direct execution of SQL statements may damage the DBMS independence of the software. Therefore, such operations should be used with caution in applications. 8. Dynamic connection table The dynamic join of a table can be achieved by specifying an SQL statement when calling the crecordset: open () function. The same record set object can only access tables with the same structure. Otherwise, the query results cannot correspond to variables. Void CDB: changetable () { If (m_pset-> isopen () m_pset-> close (); Switch (m_id) { Case 0: M_pset-> open (afx_db_use_default_type, "Select * From slot0 ″); // Connection table slot0 M_id = 1; Break; Case 1: M_pset-> open (afx_db_use_default_type, "Select * From slot1"); // connect table slot1 M_id = 0; Break ;}} 9. dynamically connect to the database You can connect to the cdatabase Object Pointer of different databases by assigning the object parameter m_pdatabase of the crecordset class to achieve dynamic connection to the database. Void CDB: changeconnect () {Cdatabase * PDB = m_pset-> m_pdatabase; PDB-> close (); Switch (m_id) { Case 0: If (! PDB-> open (_ T ("super_es ″))) // Connect to the data source super_es { Afxmessagebox ("Data Source super_es failed to open", "check the corresponding ODBC connection", mb_ OK | mb_iconwarning ); Exit (0 ); } M_id = 1; Break; Case 1: If (! PDB-> open (_ T ("Motor ″))) // Connect the data source Motor { Afxmessagebox ("Data Source motor failed to open", "check the corresponding ODBC connection", mb_ OK | mb_iconwarning ); Exit (0 ); } M_id = 0; Break ;}} Summary: The ODBC class library in Visual C ++ can help programmers complete the vast majority of database operations. Using ODBC technology, programmers can be freed from a specific DBMS, which can reduce the workload of software development, shorten the development cycle, and improve efficiency and software reliability.