Microsoft Visual C++ .NET Professional Projects - Premier Press
.pdfDatabase Communication Mechanisms
Most businesses today work with data. Everything from a personal organizer to
|
large corporate personnel systems use databases to store and maintain records that |
|||||
|
applications can use and maintain. Visual C++ provides you with a number of data |
|||||
|
access technologies that you can use to access databases in your applications: Open |
|||||
|
Database Connectivity (ODBC), Data AccessYObjects (DAO), and ActiveX Data |
|||||
|
Objects (ADO). In this chapter, I will discuss how ODBC can be used as a con- |
|||||
|
|
|
|
|
|
L |
|
sistent interface to access data from multiple relational data sources. I will also dis- |
|||||
|
cuss how Visual C++ uses the |
|
|
F |
||
|
CDatabase, CRecordset, and CRecordView classes to |
|||||
|
provide access to ODBC data sources. |
|||||
|
|
|
M |
|||
|
|
A |
Before getting into the mechanics of work- |
|||
|
|
|||||
|
|
|
|
ing ODBC, I will discuss the basics of |
||
|
RDBMS |
E |
|
|||
|
|
|
ODBC and DAO in the following sections. |
|||
|
A relational database managementTsys- |
|
|
|
||
|
|
|
|
ADO is discussed in detail in the next |
||
|
tem (RDBMS) manages the data in a rela- |
|
|
|
||
|
|
|
|
chapter. |
||
|
tional database. It stores data in the form |
|
|
|
||
|
|
|
|
|
||
|
of tables. Each table stores a group of |
|
|
|
|
|
|
related data, which is organized in rows |
|
|
|
ODBC |
|
|
and columns. The column of a table is |
|
|
|
||
|
called a field and each row of data in a |
|
|
|
ODBC originated in the early 1990s, when |
|
|
table is called a record. |
|
|
|
|
there was a boom in the database provider |
|
|
|
|
|
|
market and each database provider came up |
|
|
|
|
|
|
|
|
|
|
|
|
|
with his or her own version of an interface |
to access data from the database. This led to an incompatibility between the data interfaces, which required a different application to be created for each kind of database that had to be accessed.
This problem led Microsoft and other vendors to create a standard interface, Open Database Connectivity (ODBC), that could be used to access data from and send data to different data sources. Using ODBC, developers could write a single program that could access data from multiple data sources, without having to worry about the internal mechanism of storage of data in all of these databases.
Team-Fly®
DATABASE MANAGEMENT USING ODBC – AN OVERVIEW |
Chapter 7 |
161 |
|
|
|
DAO
Although ODBC went on to be the industry standard for accessing data, Microsoft developed the Data Access Objects (DAO) technology to provide a framework to create and manipulate Jet and ISAM databases.
Choosing a Data Access Technology
Selecting a data access technology that meets your requirements is based on a number of factors, so you must do a cost/benefit analysis before making a choice. The following are the factors that you need to consider:
Programming ease. Almost all database providers have ODBC interfaces that are easily programmable.
Performance. The number of concurrent users should not affect the speed of execution in the system.
You should use Active Template Library (ATL) if you need to access data regardless of the form in which it is stored. For example, if you want to access data from disparate data sources, such as spreadsheets, e-mails, sequential files, and databases, you should use ATL.
You should use ODBC libraries when you want to access data from a number of relational data sources, because it provides a common interface to access data from multiple data sources. You should use the MFC DAO classes when you want to work with Jet (.mdb) databases, such as Microsoft Access, dBase, FoxPro, and Paradox.
Having looked at the basics of ODBC, I will now discuss the MFC ODBC classes. The next section will help you understand the code that is automatically generated by the wizard.
MFC ODBC Classes
MFC provides a number of classes that are required to complete the process of connecting to the database, accessing data from it, and displaying the data to the user. The following are some of the key MFC ODBC classes:
CDatabase
CRecordset
CRecordView
162 Project 1 DATABASE PROGRAMMING USING VC++.NET
Each of these classes is a key component in the working of the database-related applications. I will now discuss in detail the role of each of these classes and the functions they contain.
The CDatabase Class
The CDatabase class provides the interface for an ODBC data connection to the data source. You can have more than one CDatabase object active at a time and connected to multiple databases.
NOTE
A data source contains the data that a user wants to access, the associated DBMS, the platform on which the DBMS resides, and the network that is used to access the platform.
After a CDatabase object is connected to a data source, you can do the following:
Construct recordsets and extract data from tables and queries.
Manage multiple transactions at the same time so that these transactions can be committed or rolled back at one time.
Execute SQL statements.
You don’t need to create or set an instance of the CDatabase class; the first instance of the CRecordset class does that for you. When you create an application based on the MFC Application Template and then choose to include ODBC database support, the wizard includes the database connection information in the first CRecordset derived class that is created.
The CRecordset Class
The CRecordset class provides the interface to extract a set of records from a data source. This class allows you to specify an SQL query that can be executed. It runs this query and maintains the set of records, or recordset, that is returned by the database. Any modifications that are made to the records in the recordset are updated in the database and, similarly, any records that are added and deleted from the recordset are reflected in the database.
DATABASE MANAGEMENT USING ODBC – AN OVERVIEW |
Chapter 7 |
163 |
|
|
|
To summarize, you can use the CRecordset class to do the following:
Display data from the data source
Filter and sort records from a recordset
Add, modify, and delete records from a database
The CRecordset class has two types of recordsets that can be created: dynaset and snapshot. A dynaset provides a dynamic view of a set of records. Any changes to the records are reflected in the recordset and can be viewed by other users in the system. A snapshot, on the other hand, provides a static view of the recordset and displays the records of the database at the time the recordset was created. This data could be different from the actual data that is present in the database because any updates to the data are not reflected in the data set.
In the subsequent sections, I will discuss some important functions of the CRecordset class. To explain these functions, I have used a database named Batch, which has a table called Student.
Table 7-1 defines the structure of the Student table.
Table 7-1 Structure of the Student Table
Field Name |
Data Type |
StudentID |
Number |
Name |
Text |
BatchID |
Number |
|
|
The following is the CRecordset derived class, CDatabaseAppSet, which is generated by the wizard:
class CDatabaseAppSet : public CRecordset
{
public:
CDatabaseAppSet(CDatabase* pDatabase = NULL);
DECLARE_DYNAMIC(CDatabaseAppSet)
long m_StudentID; CStringW m_Name; long m_BatchID;
164 Project 1 DATABASE PROGRAMMING USING VC++.NET
public:
virtual CString GetDefaultConnect(); // Default connection string
virtual CString GetDefaultSQL(); // Default SQL for Recordset
virtual void DoFieldExchange(CFieldExchange* pFX);
// RFX support };
In the subsequent sections, I will discuss the member functions of this class, including the constructor, the functions for navigation, and the functions for manipulating the recordset, in detail.
The CDatabaseAppSet Constructor
The data members of the recordset can be explicitly initialized using the constructor of the CRecordset derived class. The elements of the constructor are shown here:
CDatabaseAppSet::CDatabaseAppSet(CDatabase* pdb): CRecordset(pdb)
{
m_StudentID = 0;
m_Name = L””;
m_BatchID = 0;
m_nFields = 3;
m_nDefaultType = dynaset;
}
The constructor of the CDatabaseAppSet class takes one parameter, which is a pointer of the CDatabase class that was used to establish a connection with the database. If you want to connect to a specific database, you can pass the pointer of the database to this function. In case no parameters are passed to this function, a default database object is created with the parameters specified in the faultSQL and GetDefaultConnect functions (discussed in the next two sections). Following is a description of two important data members of the CRecordSet class that are initialized within the constructor of the CRecordSet derived class:
m_nFields. This data member contains the number of columns that have been extracted from the data source.
DATABASE MANAGEMENT USING ODBC – AN OVERVIEW |
Chapter 7 |
165 |
|
|
|
m_nDefaultType. This data member contains the type of recordset. This data member can take one of two values: snapshot or dynaset.
Some additional data members of the CRecordset class that can be set are the following:
m_strFilter. This data member can be used to specify the criteria that can be used to filter the records from a data set. This is equivalent to the
WHERE clause of an SQL statement. This data member must be set after the recordset is created but before the Open function is called. The following example illustrates the use of this data member:
m_strFilter = “StudentID=7”;
Based on the preceding statement, the recordset will contain only those records where the StudentID has a value 7.
m_strSort. This data member can be used to sort the data that has been extracted from a data source. It is equivalent to the ORDER BY clause of the SQL statement and can be used to organize data on one or more fields in the ascending or descending order. The following example illustrates the use of this data member:
m_strSort = “StudentID”;
The GetDefaultConnect() Function
The GetDefaultConnect() function is used to return the connection string that is used to locate a database and open it:
CString CDatabaseAppSet::GetDefaultConnect()
{
return _T(“DBQ=C:\\ Batch.mdb; DefaultDir=C:\\;Driver={Driver do Microsoft Access
(*.mdb)};DriverId=25;FIL=MS Access;FILEDSN=C:\\Batch.mdb.dsn;MaxBufferSize=2048;
MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=
Yes;”);
}
In the preceding code snippet, I have opened a database named Batch, which is an Access database. I specified this database at the time of creating the application using the MFC Application Template.
166 Project 1 DATABASE PROGRAMMING USING VC++.NET
NOTE
The _T macro is required to design code that is compatible with all operating systems, because different operating systems may use distinct character sets. Based on the system on which the application is deployed, the code is compiled to the appropriate character set before execution.
The GetDefaultSQL() Function
The GetDefaultSQL() function is used to return a string that contains the name of the table or query with which the recordset is associated. This function is used to populate the recordset with records from the database:
CString CDatabaseAppSet::GetDefaultSQL()
{
return _T(“[Student]”);
}
In the preceding code snippet, I have used a table named Student, which is part of the Student database. I specified this table at the time of creating the application.
The DoFieldExchange() Function
The DoFieldExchange() function is used to associate a column in a table with a corresponding data member in the class so that the data can be transferred both to and from the database. Sample of the DoFieldExchange function is as follows:
void CDatabaseAppSet::DoFieldExchange(CFieldExchange* pFX)
{
pFX->SetFieldType(CFieldExchange::outputColumn); RFX_Long(pFX, _T(“[StudentID]”), m_StudentID); RFX_Text(pFX, _T(“[Name]”), m_Name); RFX_Long(pFX, _T(“[BatchID]”), m_BatchID);
}
Let me explain the mechanics behind this function.
The DoFieldExchange() function implements the Record Field eXchange(RFX)
mechanism. This mechanism enables you to exchange data between the field
DATABASE MANAGEMENT USING ODBC – AN OVERVIEW |
Chapter 7 |
167 |
|
|
|
members of your recordset object and the corresponding columns in the record in the data source. The framework calls this function implicitly. This exchange of field data works in both directions[md]from the fields in the recordset to the fields in the data source and from the record in the data source to the fields in the recordset.
The RFX class library supplies functions for transferring data types between the data source and recordsets. As you can see from the preceding code, the RFX statements perform the data exchange between the current record and the corresponding variables in the CDatabaseAppSet class.
Table 7-2 lists the various RFX functions that are available, and the data types they correspond to.
Table 7-2 RFX Functions
Function Name |
Data type |
RFX Function |
Data type |
RFX_Int |
Int |
RFX_Long |
Long |
RFX_Text |
CString |
RFX_Single |
Float |
RFX_Byte |
BYTE |
|
|
I will now dissect the components of an RFX statement:
RFX_Long(pFX, _T(“[StudentID]”), m_StudentID);
The first parameter, pFX, is a CFieldExchange object. The CFieldExchange class is responsible for the flow of data between the CDatabase class and the CRecordset class.
The second parameter, _T(“[StudentID]”), is the field name in the data source.
The third parameter, m_StudentID, is the field name in the recordset.
I have discussed the basic functions of the CRecordset derived class. But the important question is, after you have retrieved the records from the database, how
168 Project 1 DATABASE PROGRAMMING USING VC++.NET
do you navigate the recordset? The following section discusses the functions that can be used to navigate a recordset.
Navigating a Recordset
The CRecordset class provides a number of functions that can be used to navigate a recordset. Table 7-3 lists the functions that can be used to navigate a recordset.
Table 7-3 Functions to Navigate a Recordset
Function |
Used to |
MoveFirst |
Move to the first record in the recordset |
MoveNext |
Move to the next record in the recordset |
MoveLast |
Move to the last record in the recordset |
MovePrev |
Move to the previous record in the recordset |
IsBOF |
Check whether the recordset pointer is pointing to the first record |
|
in the recordset. (Returns TRUE if the current record is the first |
|
record in the recordset.) |
IsEOF |
Check whether the recordset pointer is pointing to the last record |
|
in the recordset. (Returns TRUE if the current record is the last |
|
record in the recordset.) |
Move |
Move a specific number of records from the current record |
RecordCount() |
Find the number of records in the recordset |
|
|
In case you create your application using the MFC Application Template, the framework automatically provides this functionality without you having to even write a single line of code for it!
Adding, Modifying, and Deleting Records
Navigating through a recordset enables you to display existing data from a recordset. However, in addition, there could be situations in which you would need to add, modify, and delete records from a recordset. Table 7-4 lists the functions that can be used to edit recordsets.
DATABASE MANAGEMENT USING ODBC – AN OVERVIEW |
Chapter 7 |
169 |
|
|
|
Table 7-4 |
Data Manipulation Functions |
Function |
Used to |
|
|
AddNew |
Add a new record to the recordset |
Delete |
Delete the current record from the recordset |
Update |
Save the current changes to the database |
Edit |
Make changes to the current record |
Requery |
Re-execute the current SQL query to refresh the recordset |
|
|
Having looked at the functions that form the basis let me now discuss the order in which these functions should be used to edit the records.
Adding Records
The following are the steps to add a record:
1.Invoke the AddNew function.
2.Assign default values to the fields in the recordset.
3.Invoke the Update function to add the record to the database. If you don’t invoke the Update function before you navigate to the next record, the new record will not be added to the table.
4.Invoke the Requery function to refresh the recordset.
The following code snippet displays the code to add a record to a data source:
if (m_pSet->GetRecordCount() > 0) m_pSet->MoveLast();
m_pSet->AddNew();
if (!m_pSet->Update()) MessageBox(“Cannot add the new record”); if (m_pSet->Requery()==0)
return;
Deleting Records
The following are the steps to delete a record:
1.Navigate to the record that needs to be deleted.
2.Invoke the Delete function. This function deletes the current record.