1. Use the MFC to access the ODBC data source
Emio: zzh1415@21cn.com
3.1 Overview
The Visualc MFC class library defines several database classes. When programming with ODBC, you are often used to use CDATABASE, CRecordSet (Record Class), and CRecordView. among them:
The CDATABASE class object provides a connection to the data source, and you can operate the data source by it.
The CRecordView class object can display database records in the form of control. This view is a table view that is directly connected to a CRecordset object.
The CRecordset class object provides a recordset that extracted from the data source. CRecordset objects are usually used in two forms: dynamic rows (Dynastes) and snapshots. Dynamic rows can keep synchronization with changes made with other users. The snapshot set is a static view of the data. Each form provides a set of records when the recording set is opened, and the difference is that when you scroll to a record in a dynamic row, other users or other records in your application are The changes made by the record will be displayed accordingly.
Visual C provides several records that can be used to customize the application's way. The fastest way to see these different options should take care of speed and feature. You will find that in many cases, if you want to add a feature, you must pay the price of the program to perform speed reduction. Let's tell you some of the records that can be dominated. More importantly, to tell you that you can get faster speed or more features from this option.
1, Snapshot (Snapshot) This option To download the entire query in a snapshot of Visual C . In other words, the content of the database is quickly taken in time, and it is the foundation of future work. This method has three shortcomings. First, you can't see the updates of others on the network, which may mean that your decision is based on old information. Second, download all these records once, which means adding a heavy burden to the network during download. Third, the user will end when the download is downloaded, which means that the call performance of the network becomes lower. However, this approach has two advantages. First, once the record is downloaded, the network activity required by the station is almost no date, which releases the bandwidth for other requests. In short, you will see that the throughput of the network is increased. Second, because all the records of the application are in the user's machine, the user actually obtains better overall performance. You may want to limit the method of snapshots on a smaller database because snapshots apply to user request information and do not apply to data editing sessions.
2, DYNASET (Dynamic Set) Use this option, Visual C creates the actual pointer to each record of the requested. In addition, only records actually needed when filling the screen is downloaded from the server. The advantage of this method is obvious. Almost immediately can see the record on the screen. And you will also see other users made changes to the database. Finally, other users will also see what you do, because the dynamic set is uploaded to the server when you change your record. Obviously, this method requires real-time access to the server, which reduces the total network throughput and reduces the performance of the application. This option is suitable for creating a user to take a lot of time to edit the data. At the same time, it is also the best choice for large databases because only information actually needs.
3.2 Applying ODBC Programming
AppWizard can be applied to create an ODBC application framework, or you can use ODBC to program programming directly. At this time, the header file AFXDB.H should be included.
Applying ODBC programming two most important classes are CDatabase and CRecordset, but in the application, you should not use the CRecordset class directly, and you must generate an export class from the CRecordset class and add a member variable corresponding to the fields in the database table. Subsequently, the member function of the CRecordset class DOFIELDEXCHANGE, which completes the database field with the RFX function and the data exchange of the recorded domain data member variable, the RFX function is similar to the dialog box data exchange (DDX) mechanism, responsible for completing the database and members Data exchange between variables. The following example shows the programming skills of ODBC in the Visualc environment:
3.21 Database connection
Define a member variable m_pdatabase in the CRecordset class:
CDATABASE * m_PDATABASE;
It is a pointer to the object database class. If you pass a open CDatabase class object norm to M_PDatabase, you can share the same CDatabase class object before the CRecordset class object calls the Open () function. Such as:
CDATABASE M_DB;
CRecordset m_set1, m_set2;
m_db.open (_t ("super_es")); // Building ODBC connection
m_set1.m_pdatabase = & m_db; // m_set1 multiplex m_db object
m_set2.m_pdatabase = & m_db; // m_set2 multiplex m_db object
Or as follows:
CDATABASE DB;
DB.Open ("Database"); // Establish an ODBC connection
CRecordset M_Set (& DB); // Constructs records to make the database points to DB
3.22 query record
The query record uses CRecordset :: Open () and CRecordset :: Requery () member functions. Before using the CRecordset class object, you must use the CRecordset :: Open () function to get a valid recordset. Once you have used the CRecordset :: Open () function, you can apply the CRecordset :: Requery () function when you query. When calling the CRecordset :: Open () function, if you have passed a M_PDatabase member variable that has been passed to the CRecordset class object, use the database object to create an ODBC connection; otherwise, if m_pdatabase is an empty pointer, you will create a new one. The CDATABASE class object is connected to the default data source and then performs the initialization of CRecordset class objects. The default data source is obtained by a getDefaultconnect () function. You can also provide the SQL statement you need, and call CRecordset :: open () functions, for example:
m_set.open (AFX_DATABASE_USE_DEFAULT, STRSQL);
If you do not specify a parameter, the program uses the default SQL statement to operate the SQL statement specified in the getDefaultsql () function:
CString CTESTRECORDSET :: getDefaultsql ()
{RETURN _T ("[BasicData], [MAINSIZE]");
For the table name returned for the getDefaultsql () function, the corresponding default operation is a SELECT statement, namely:
Select * from BasicData, MAINSIZE
The CRECORDSET's member variable M_Strfilter and M_STRSORT can also be used in the query process. m_strfilter is a filter string, stores the condition string after WHERE in the SQL statement; m_strsort is a sort string, stores the string after ORDERBY in the SQL statement. Such as: m_set.m_strfilter = "Type = 'electric machine";
m_set.m_strsort = "Voltage";
m_set.Requery ();
The corresponding SQL statement is:
Select * from BasicData, MAINSIZE
Where type = 'electric machine
ORDER by Voltage
In addition to the direct assignment to m_strfilter, parameterization can be used. Using parameterization can be more intuitive and more convenient to complete the conditional query task. The steps to use parameterization are as follows:
(1). Disclaimer the parameters:
CSTRING P1;
Float p2;
(2). Initialize the amount of parameters in the constructor
P1 = _t ("");
P2 = 0.0F;
m_nparams = 2;
(3). Bind the parameters with the corresponding column
PFX-> setfieldType (cfieldexchange :: param)
RFX_Text (PFX, _T ("P1"), P1);
RFX_SINGLE (PFX, _T ("p2"), p2);
After completing the above steps, you can use the parameters to check:
m_pset-> m_strfilter = "type =? Andvoltage =?"
m_pset-> p1 = "electric motor";
m_pset-> p2 = 60.0;
m_pset-> Requory ();
The value of the parameter is replaced in the "?" Adaptifier in the query string according to the order of the bound.
If the result of the query is a plurality of records, you can use the CRecordset class to move the cursor using the CRecordset class. Move (), MoveFirst (), MoveLast ().
3.23 Add record
Increase records use the addNew () function, requiring the database to be opened in a way that is allowed:
m_pset-> addnew (); // Add a new record at the end of the table
m_pset-> setfieldnull (& (M_Pset-> M_Type), False);
m_pset-> m_type = "electric motor";
... // Enter a new field value
m_pset-> Update (); // Deposit a new record into the database
m_pset-> Requory (); // Reconstruction record set
3.24 Delete Record
Use the delete () function directly, and do not call the Update () function after calling the delete () function:
m_pset-> delete ();
IF (! m_pset-> ISEOF ())
m_pset-> moext ();
Else
m_pset-> movelast ();
3.25 Modify Record
Modify the record using the edit () function:
m_pset-> edit (); // Modify the current record
m_pset-> m_type = "Generator"; / / Modify the current record field value
...
m_pset-> update (); // Deposit the modification into the database m_pset-> request ();
3.26 statistical record
The statistical record used to count the total number of records. You can declare a CRecordset object m_pset first. Bind a variable m_lcount to count the total number of records. Perform the following statement:
m_pset-> open ("SELECT Count (*) from Name WHERE Limit Conditions");
Recordcount = m_pset-> m_lcount;
m_pset-> close ();
Recordcount is the number of records to be statistical.
Or as follows:
CRecordset M_Set (& DB); // DB is CDATABASE object
CString Strvalue;
m_set.open (Select Count (*) from FROM Name WHERE Limited Conditions ");
m_pset.getfieldValue ((int) 0, strvalue);
Long count = atol (strValue);
m_set.close ();
COUNT is the total number of records.
3.27 Execute SQL statement
Although we can do most query operations through the CRecordset class, and in the CRecordset :: Open () function, we can also provide SQL statements, but sometimes we want to make some other operations, such as establishing a new table, deleting a table, Establish a new field, etc., then you need to use the mechanism of direct SQL statements to the CDATABASE class. Direct execution of the SQL statement is completed by the calling cDatabase :: executesql () function:
As shown below
Bool CDB :: EXECUTESQLANDREPORTFAILURURE (Const Cstring & Strsql)
{
Try
{
m_pdb-> executesql (strsql); // Execute SQL statement directly
}
Catch (CDBEXCEPTION, E)
{
CString strmsg;
STRMSG.LOADSTRING (IDS_EXECUTE_SQL_FAILD);
STRMSG = strsql;
Return False;
}
END_CATCH
Return True;
}
It should be noted that since the data operation statement provided by different DBMS is not the same, the SQL statement directly executes the SQL statement may destroy the software's DBMS independence, so this type of operation should be used with caution.
3.28 Note
If the data exported from the CRecordset, if the data contains the DataTime type, in the VC is replaced with a CTIME type, at this time, the constructor does not mean the default. At this time, we should assign a value. As follows:
CTIME M_TIME;
M_TIME = NULL;
3.3 Summary
The ODBC class library in Visualc can help programmers complete the vast majority of database operations. Using ODBC technology, the programmer explained from the specific DBMS, thereby greatly reduced the workload of software development, shortening the development cycle, and improving efficiency and software reliability.
