|
For the table name returned by the getdefasql SQL () function, the default operation is the SELECT statement, that is: Select * From basicdata, mainsize You can also use the member variables m_strfilter and m_strsort of the crecordset during the query to execute conditional query and result sorting. M_strfilter is a filter string that stores the where condition strings in the SQL statement; m_strsort is a Sort string that stores the orderby character strings in the SQL statement. For example: M_set.m_strfilter = "type = 'motor '"; M_set.m_strsort = "voltage "; M_set.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 makes conditional query tasks more intuitive and convenient. To use parameterization, follow these steps: (1). Declare the parameters: Cstring P1; Float P2; (2) initialize the parameters in the constructor. P1 = _ T (""); P2 = 0.0f; M_nparams = 2; (3) bind the variable to 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 =? Andvoltage =? "; 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. Adapter. If the query results contain multiple records, you can use the crecordset functions to move (), movenext (), moveprev (), movefirst (), and movelast () to move the cursor. 3.23 add record 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 (); // refresh the record set 3.24 delete records Directly use the delete () function, and 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 (); 3.25 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 (); 3.26 statistical records Statistical records are used to count the total number of record sets. You can declare a crecordset object m_pset first. Bind a variable m_lcount to count the total number of records. Execute the following statement: M_pset-> open ("select count (*) from table name where condition "); Recordcount = m_pset-> m_lcount; M_pset-> close (); Recordcount is the number of records to be counted. Or: Crecordset m_set (& dB); // dB is a cdatabase object Cstring strvalue; M_set.open (select count (*) from table name where condition "); M_pset.getfieldvalue (INT) 0, strvalue ); Long Count = atol (strvalue ); M_set.close (); Count indicates the total number of records. 3.27 Execute SQL statements Although the crecordset class allows us to perform most query operations and also provides SQL statements in the crecordset: open () function, we also want to perform other operations, for example, to create a new table, delete a table, and create new fields, you must use the cdatabase class to directly execute SQL statements. You can directly execute an SQL statement by calling the cdatabase: executesql () function: The following code is used: Bool CDB: executesqlandreportfailure (const cstring & strsql) { Try { M_pdb-> executesql (strsql); // directly execute the SQL statement } 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. 3.28 note If the class exported from crecordset contains data of the datatime type, the ctime type is used in VC. In this case, the constructor is not assigned the default value. In this case, we should assign values manually. As follows: Ctime m_time; M_time = NULL; 3.3 conclusion The ODBC class library in Visual C ++ can help programmers complete the vast majority of database operations. ODBC technology is used to free programmers from specific DBMS, which greatly reduces the workload of software development, shortens the development cycle, and improves the efficiency and software reliability. 4. Use Dao 4.1 Overview Visual c ++ encapsulates Dao. The mfc dao class encapsulates most of Dao (Database Access Object) functions, from the Visual C ++ program, you can use the mfc dao class provided by Visual C ++ to conveniently access the Microsoft Jet Database, and compile a simple and visaul c ++ database application. Database Access Object (DAO) provides a mechanism for creating and manipulating databases through program code. Multiple Dao objects constitute an architecture in which each DAO object works collaboratively. Dao supports the following four Database options: Open the ACCESS database (MDB File)-The MDB file is a self-contained database, which includes query definitions, security information, indexes, relationships, and actual data tables. You only need to specify the path name of the MDB file.
Open the ODBC data source directly-there is an important restriction here. You can only use a data source with your own ODBC driver DLL.
Use the jet engine to find the isam data source (index sequential access method) (including dBase, Foxpro, paradox, BTRIEVE, Excel, or text files)-even if the ODBC data source has been set, to use the jet engine to access one of these file types, you must also open the file in the form of an isam data source, rather than the ODBC data source.
Attach an External table to the ACCESS database-this is actually the preferred method to access the ODBC data source using Dao. First, use access to add the ODBC table to an MDB file, and then use DAO to open the MDB file according to the method described in the first option. You can also use access to attach an iasm file to an MDB file.
4.2 Application Dao Programming 4.21 open a database The cdaoworkspace object represents a DaO workspace object, which is at the highest level in the mfc dao architecture. It defines a user's session to the same database and contains the opened database, completes database transactions. We can use an implicit workspace object. The cdaodatabase object represents a connection to the database. In MFC, It is encapsulated by cdaodatabase. When constructing a cdaodatabase object, there are two methods: Create a cdaodatabase object and pass a pointer to a cdaoworkspace object that has been opened.
Create a cdaodatabase object without specifying the workspace to use. In this case, MFC creates a temporary cdaoworkspace object.
The following code is used: Cdaodatabase dB; DB. Open ("test. mdb", false, false, _ T (""); Parameter 1 includes the full path name of the file to be opened. 4.22 query records A Dao recordset object represents a set of data records. This set is a database table or all records in the running results of a query. There are three types of cdaorecorset objects: tables, dynamic sets, and snapshots. In general, we can use the export class of cdaorecordset in the application, which is generally generated through classwizard or Appwizard. However, we can also directly use the object generated by the cdaorecordset class. In this case, we can dynamically bind the data member of the recordset object. The following code is used: Colevariant var; Long ID; Cstring STR; Cdaorecordset m_set (& dB ); M_set.open ("SQL statement queried "); While (! M_set.iseof ()) { /* Processing M_set.getfieldvalue ("ID", VAR ); Id = v_i4 (VAR ); M_set.getfieldvalue ("name", VAR ); STR = var. pbval; */ M_set.movenext (); } M_set.close (); 4.23 add record The addnew function is used to add records. setfieldvalue is used to assign values. The following code is used: M_pdaorecordset-> addnew (); Sprintf (strvalue, "% s",> m_username ); M_pdaorecordset-> setfieldvalue ("username", strvalue ); Sprintf (strvalue, "% d", m_pointid ); M_pdaorecordset-> setfieldvalue ("pointid", strvalue ); Datasrc. setdatetime (m_updatetime. getyear), m_updatetime. getmonth), m_updatetime. getday (), M_updatetime. gethour (), m_updatetime. getminute (), m_updatetime. getsecond ()); Valvalue = datasrc; M_pdaorecordset-> setfieldvalue ("updatetime", valvalue ); Sprintf (strvalue, "% F", m_precordset-> m_oldvalue ); M_pdaorecordset-> setfieldvalue ("oldvalue", strvalue ); Sprintf (strvalue, "% F", m_precordset-> m_newvalue ); M_pdaorecordset-> setfieldvalue ("newvalue", strvalue ); M_pdaorecordset-> Update (); In this case, you must note that the date and time data must be assigned values using the setdatatime function. colevariant data is used here. For specific usage instructions, refer to the help documentation. 4.24 modify records Use the Edit () function to locate the record to be modified, call the Edit function, and call the UPDATE function after modification. The following code is used: M_set.edit (); M_set.setfieldvalue ("column name", "string "); M_set.update (); 4.25 delete records The Delete () function is used to delete records. You do not need to call the update () function after use. 4.26 statistical records You can use the following code to count the number of records: Colevariant varvalue; Cdaorecordset m_set (& dB ); M_set.open (dbopendynaset, "SQL statement "); Varvalue = m_set.getfieldvalue (0 ); M_lmaxcount = v_i4 (& varvalue ); M_set.close (); To count the total records in a table, you can use the cdaotabledef object, as shown in the following code: Cdaotabledef m_set (& gusedb ); Count = m_set.getrecordcount (); M_set.close (); You cannot use getrecordcount () of the cdaorecordset object to retrieve the number of records. 4.3 conclusion With DAO technology, we can easily access the Microsoft Jet Engine database. Because Microsoft Jet does not support multithreading, We must restrict all DaO calls to the main thread of the application. 5. Use OLE DB 5.1 Overview The existence of ole db provides users with a unified method to access all different types of data sources. Ole db can be converted in different data sources. With ole db, client developers only need to focus on a few details when accessing data, rather than understanding a large number of access protocols for different databases. Ole db is a set of ActiveX interfaces that access data through COM interfaces. This ole db interface is quite common enough to provide a unified means of accessing data, regardless of the method used to store data. At the same time, ole db also allows developers to continue to take advantage of the advantages of the basic database technology, without the need to use these advantages to move data out. 5.2 use the ole db data user program with ATL A large amount of code is required to design database applications directly using ole db objects and interfaces. To simplify programming, Visual C ++ provides an ATL template for designing ole db data applications and data providers. Using the ATL template, you can easily combine ole db with MFC to simplify complex programming such as database parameter query. The database classes provided by MFC make ole db programming more object-oriented. The ATL templates provided by viual C ++ for ole db can be divided into templates for data providers and templates for data usage programs. To create a data application using an ATL template, follow these steps: Create an application framework
Add the template class generated by ATL
Use the generated data access object in the Application
Use of ole db data without ATL
Using the ATL template to generate data is relatively simple to use, but it is not widely applied and cannot dynamically adapt to database changes. The following describes how to directly use the mfc ole db class to generate a data application. Template usage Ole db data user templates are composed of some templates, including the following templates. Some common classes are described below. Session class
Cdatasource class The cdatasource class corresponds to the data source object of ole db. This class represents the connection between the ole db data provider and the data source. A session object can be generated only after a connection to the data source is established. You can call open to open a connection to the data source. Csession The csession object represents a separate database access session. A data source object generated using the cdatasource class can create one or more sessions. To generate a session object on the data source object, you must call the open () function to open it. At the same time, session objects can also be used to create transaction operations. Cenumeratoraccessor class The cenumeratoraccessor class is used to access information of available data providers in the rows generated by the enumerator query. It can provide currently available data providers and visible accessors. Accessors
Cacessor class The caccessor class represents the type of the accessor. This class can be used when you know the database type and structure. It supports multiple accessors for a row set, and the buffer for storing data is allocated by the user. Cdynamicaccessor class The cdynamicaccessor class is used to dynamically create accessors when the program is running. When the system is running, the column information can be dynamically obtained from the row set, and the accessors can be dynamically created based on this information. Cmanualaccessor class In the cmanualaccessor class, you can bind columns to variables or bind parameters to variables when the program is running. Row set class
Crowset class The crowset class encapsulates the row set object and corresponding interfaces, and provides methods for querying and setting data. You can use functions such as move () to record data movement, getdata () to read data, and insert (), delete (), and setdata () to update data. Cbulkrowset class The cbulkrowset class is used to retrieve multiple row handles or operate on multiple rows in a single call. Carrayrowset class The carrayrowset class provides data access using array subscript. Command class
Ctable class The ctable class is used for simple access to the database, and the row set is obtained using the data source name to obtain data. Ccommand class The ccommand class is used for data sources that support commands. You can use the open () function to execute SQL commands, or the prepare () function to prepare commands first. For data sources that support commands, you can improve the flexibility and robustness of the program. Add the following code to the stdafx. h header file. # Include <atlbase. h> Extern ccommodule _ module; # Include <atlcom. h> # Include <atldbcli. h> # Include <atldbsch. h> // if you are using schema templates Add the following code to the stdafx. cpp file. # Include <atlimpl. cpp> Ccommodule _ module; Determines the type of access program and row set. Get Data After opening the data source, session, and row set object, you can get the data. The data type obtained depends on the access program used, and columns may need to be bound. Follow these steps.
Use the correct command to open the row set object. If you use cmanualaccessor, bind it to the corresponding column before use. To bind a column, use the getcolumninfo function as follows:
// Get the column information Ulong ulcolumns = 0; |
