Connecting to Databases

1
Connecting to Databases
2
Objectives

• In this chapter you will learn
• About basic database structure
• About database management systems
• About structured query language
• How to connect to databases with MFC
• How to link dialog controls to database fields
• How to sort and filter records
• How to add and delete records

3
Understanding Databases

• A database is an ordered collection of
  information from which a computer program can
  quickly access information
• The information stored in computer databases is
  stored in tables similar to spreadsheets
• Each row in a database table is called a record
• A record in a database contains a single complete
  set of related information
• Each recipe in a recipe database, for instance,
  is a single database record

4
Understanding Databases

• Each column in a database table is called a field
• Fields are the individual pieces of information
  stored in a record
• Figure 13-2 shows an example of an employee
  directory for programmers at an application
  development company

5
Understanding Databases

• A flat-file database stores information in a
  single table
• A relational database stores information across
  multiple related tables
• Relational databases consist of one or more
  related tables
• A primary table is the main table in a
  relationship that is referenced by another table
• A related, or child, table references a primary
  table in a relational database

6
Understanding Databases

• Tables in a relationship are connected using
  primary and foreign keys
• A primary key is a field that contains a unique
  identifier for each record in a primary table
• A foreign key is a field in a related table that
  refers to the primary key in a primary table
• Primary and foreign keys are what link records
  across multiple tables in a relational database
• A one-to-one relationship exists between two
  tables when a related table contains exactly one
  record in the primary table

7
Understanding Databases
8
Understanding Databases

• You create one-to-one relationships when you want
  to break information into multiple, logical sets
• A one-to-many relationship exists in a relational
  database when one record in a primary table has
  many related records in a related table
• Breaking tables into multiple related tables in
  order to reduce redundant and duplicate
  information is called normalization
• The elimination of redundant information
  (normalization) reduces the size of a database
  and makes the data easier to work with

9
Understanding Databases

• A one-to-many relationship provides a more
  efficient and less redundant method of storing
  this information in a database
• Figure 13-6 shows the same information organized
  into a one-to-many relationship

10
Understanding Databases

• A many-to-many relationship exists in a
  relational database when many records in one
  table are related to many records in another
  table
• To create a many-to-many relationship, you must
  use a junction table because most relational
  database systems cannot work directly with
  many-to-many relationships
• A junction table creates a one-to-many
  relationship for each of the two tables in a
  many-to-many relationship

11
Understanding Databases

• Figure 13-7 contains an example of a many-to-many
  relationship between a Programmers table and a
  Programming Languages table

12
Database Management Systems

• An application or collection of applications used
  to create, access, and manage a database is
  called a database management system, or DBMS
• A database management system that stores data in
  a flat-file format is called a flat-file database
  management system
• A database management system that stores data in
  a relational format is called a relational
  database management system, or RDBMS
• There are also hierarchical and network database
  management systems

13
Database Management Systems

• Two other important aspects of database
  management systems are querying and reporting
  capabilities
• A query is a structured set of instructions and
  criteria for retrieving, adding, modifying, and
  deleting database information
• A report is the formatted, printed output of a
  database table or the results of a query
• Most database management systems use a data
  manipulation language, or DML, for creating
  queries

14
Database Management Systems

• Different database management systems support
  different data manipulation languages
• However, structured query language, or SQL, has
  become somewhat of a standard data manipulation
  language among many database management systems
• Open database connectivity, or ODBC, allows
  applications that are written to comply with the
  ODBC standard to access any data source for which
  there is an ODBC driver

15
Database Management Systems

• ODBC uses SQL commands (known as ODBC SQL) to
  allow an ODBC-compliant application to access a
  database
• Essentially, an ODBC application connects to a
  database for which there is an ODBC driver and
  then executes ODBC SQL commands
• Then, the ODBC driver translates the SQL commands
  into a format that the database can understand

16
Structured Query Language

• IBM invented SQL in the 1970s as a way of
  querying databases for specific criteria
• Since then, SQL has been adopted by numerous
  database management systems running on
  mainframes, minicomputers, and PCs
• In 1986 the American National Standard Institute
  (ANSI) approved an official standard for the SQL
  language
• In 1991 the X/Open and SQL Access Group created a
  standardized version of SQL known as the Common
  Applications Environment (CAE) SQL draft
  specification

17
Structured Query Language

• Figure 13-10 lists several SQL keywords that are
  common to most versions of SQL

18
Connecting to Databases with MFC

• You connect to databases with MFC using ODBC or
  OLE DB
• You have already learned that ODBC allows
  applications that are written to comply with the
  ODBC standard to access any data source for which
  there is an ODBC driver
• OLE DB is a data source connectivity standard
  promoted by Microsoft as a successor to ODBC

19
Connecting to Databases with MFC

• One of the primary differences between OLE DB and
  ODBC is that ODBC supports access only to
  relational databases, whereas OLE DB provides
  access to both relational databases and
  non-relational data sources, such as spreadsheet
  programs
• To create an ODBC database application, perform
  the instructions on pages 697 and 698

20
Creating the Data Source Name

• To make it easier to access ODBC-compliant
  databases on 32-bit Windows operating systems,
  such as Windows NT, Windows 98, Windows 2000, and
  Windows XP, you create a Data Source Name to
  locate and identify the database
• A Data Source Name, or DSN, contains
  configuration information that Windows operating
  systems use to access a particular ODBC-compliant
  database
• The DSNs to which you can connect in a Windows
  environment are installed and managed using the
  ODBC Administrator utility

21
Creating the Data Source Name

• There are three types of DSNs system, user, or
  file
• The system DSN enables all users logged onto a
  server to access a database
• A user DSN restricts database access to
  authorized users only
• A file DSN creates a file-based data source, with
  an extension of .dsn, that can be shared among
  users

22
Creating the Data Source Name

• You will create a user DSN in this chapter
• To create a user DSN for the Library.mdb database
  file, refer to pages 698-700

23
Creating an ODBC Database Application

• The functionality that allows MFC to access
  ODBC-compliant databases is contained in the
  classes listed in Figure 13-14
• MFC Application Wizard derives classes for you
  from the ODBC database classes, and the framework
  manages almost all of the function calls and data
  members required to connect to an ODBC database
• All ODBC database applications begin with the
  CDatabase class

24
Creating an ODBC Database Application

• The CFieldExchange class manages the exchange of
  information between your application and the
  database
• The MFC framework hides all of the details of the
  CDatabase and CFieldExchange classes from you

25
Creating an ODBC Database Application

• The classes you need to explore include the
  CRecordSet and CRecordView classes
• The CRecordset class represents records returned
  from a database, and the CRecordView class
  displays those records in a dialog box-style
  window that you can edit with the Dialog Editor
• You can create two types of CRecordset objects a
  snapshot or a dynaset
• A snapshot is a static view of the records in a
  database

26
Creating an ODBC Database Application

• Any changes made to the database after you run
  your application will not be reflected in your
  applications record set
• In comparison, a dynaset is a dynamic record set
  that displays the most recent changes to a
  database each time you move from one record to
  another in a database application

27
Creating an ODBC Database Application

• Essentially, a snapshot queries the database only
  once when your application first executes
• In contrast, a dynaset queries the database when
  an application first executes and each time a
  user moves to a different record
• To use MFC Application Wizard to create the
  Library Database project, follow the instructions
  outlined on pages 702 through 704 of the textbook

28
The Database Support Tab in the MFC Application
Wizard
29
The CRecordset Class

• The CRecordset class represents the records
  returned from a dialog
• The CLibraryDatabaseSet class that the MFC
  Application Wizard built for you derives from the
  CRecordset class
• Figure 13-18 shows the CLibraryDatabaseSet class
  implementation file
• You will notice the data member declarations that
  the MFC Application Wizard created that
  correspond to each field in the database

30
The CRecordset Class

• Modify the LibraryDatabaseSet.h file so that the
  data members corresponding to the fields in the
  database are of the CString data type as shown on
  page 706

31
The CRecordset Class

• You should be aware that the statements within
  the DoFieldExchange() function are what enable
  the transfer of values between a derived
  CRecordset classs data members and the fields in
  a database
• Notice the definition for the m_nDefaultType data
  member, which determines whether you want the
  CRecordset object to be a dynaset or a snapshot
• You can modify the statement manually after
  running the MFC Application Wizard if you change
  your mind about what type of CRecordset object
  you want to use

32
The CRecordset Class

• When you pass a value of NULL to the pDatabase
  pointer in the derived CRecordset class
  constructor, the Open() function constructs a new
  CDatabase object and then calls the
  GetDefaultConnect() function to connect to the
  database
• The MFC Application Wizard created an overridden
  version of the GetDefaultConnect() function for
  you that specifies the necessary information to
  connect to your database

33
The CRecordView Class

• The CRecordView class, which derives from CView,
  displays records from the CRecordset class in a
  dialog box-style window that you can edit with
  the Dialog Editor
• Figure 13-20 in the text shows the Library
  Database applications CLibraryDatabaseView class
  interface file that the MFC Application Wizard
  derived from CRecordView
• It is in the OnInitialUpdate() function that you
  assign to the m_pSet variable a pointer to the
  derived CRecordset associated with a derived
  CRecordView class

34
Linking Dialog Controls to Database Fields

• In order for the CRecordView classs dialog
  window to communicate with the CRecordset classs
  data members, you need to add controls to a
  derived CRecordView classs dialog window
• Then, you add to the CRecordView classs
  DoDataExchange() function the appropriate
  DDX_Field functions listed in Figure 13-21 on
  page 712 to handle the exchange of data between
  the dialog controls and the derived CRecordset
  classs data members

35
Linking Dialog Controls to Database Fields

• Figure 13-22 illustrates how data is exchanged
  across a database application
• You will use a new type of dialog control, Radio
  Button controls, to select a books genre of
  fiction or nonfiction

36
Linking Dialog Controls to Database Fields

• A radio button appears as a small empty circle
  when selected, it appears to be filled with a
  black dot
• A radio button is usually contained within a
  group of other radio buttons, and you can select
  only one of the grouped radio buttons at a time.
  Figure 13-23 shows an example of a group of six
  radio buttons

37
Linking Dialog Controls to Database Fields

• When used with a database, a single group of
  related radio buttons is used to represent a set
  number of choices that users can place in a
  single field
• Radio Button controls are recognized as being in
  the same group when the first control in the
  group has its Group property set to True in the
  Properties window
• All radio buttons that follow are recognized as
  part of the same group until Visual C
  encounters another Radio Button control with its
  Group check box selected, which starts a new group

38
Linking Dialog Controls to Database Fields

• Additionally, the resource ID property of the
  first Radio Button control in a group is used to
  represent all of the Radio Button controls in the
  group any resource IDs you assign to other
  controls in the group will be ignored
• Add to the CLibraryDatabaseView classs dialog
  controls that will display the fields in the
  Library Database program as shown on pages 714
  and 715 of the textbook

39
Linking Dialog Controls to Database Fields

• Map the dialog control resource IDs to their
  associated field data members in the
  CLibraryDatabaseSet class referring to pages 715
  and 716

40
Manipulating Record Sets

• Two of the most common ways of manipulating
  record sets are sorting and filtering
• Sorting presents database records in alphanumeric
  order based on a field in the record set
• Filtering uses a given criterion to narrow
  records that the user can see

41
Sorting

• One way to sort records when they are first
  returned from a database, as you saw earlier in
  the chapter, involves modifying the return
  statement in the GetDefaultSQL() function
• The GetDefaultSQL() function is useful only for
  defining initial SQL criteria for the record set
  you want to be returned from a database
• When you derive a class from CRecordset, the
  derived class inherits a data member named
  m_strSort

42
Sorting

• You dynamically sort the records in a record set
  by assigning the field name by which you want to
  sort to the m_strSort data member
• Modify the Library Database application so that
  users can sort on author name and book title as
  illustrated on pages 718 and 719

43
Filtering

• Filtering works almost the same as sorting,
  except that it extracts a subset of the main
  record set instead of sorting it
• Instead of using the m_strSort data member, you
  use the m_strFilter data member, which is also
  inherited from CRecordset
• As with the m_strSort data member, you assign
  values to the m_strFilter data member from a
  CRecordView class using the m_pSet pointer to the
  associated CRecordset class

44
Filtering

• Instead of simply assigning a field name to the
  m_strFilter data member, you must also add an
  assignment statement that tells the MFC framework
  how you want to filter the record set
• The MFC framework uses the assignment statement
  in the m_strFilter data member to construct a
  WHERE SQL statement to execute against the
  database
• Be sure not to include the WHERE clause in the
  text string you assign to the m_strFilter data
  member

45
Adding and Deleting Records

• For some types of databases, you may not want to
  allow users to add or delete records
• Because there are many different methods of
  designing an interface for a database application
  (using menu commands, toolbars, controls, and so
  on), the MFC framework does not automatically
  create an option for adding records to a database
• Additionally, the MFC framework does not create
  an option for deleting records because of the
  complexity of table relationships across
  relational databases
• Therefore, it is up to you to write code for
  adding records to and deleting records from you
  database

46
Adding Records

• The starting point for adding a new record is the
  AddNew() function that is derived from CRecordset
• The AddNew() function prepares a new database
  record by setting the new records field values
  to NULL
• You then call the UpdateData() function with a
  value of FALSE to clear the dialog controls by
  assigning them values of NULL

47
Adding Records

• The process of updating database records is
  managed by the CRecordView classs OnMove()
  function
• However, in order to save a new record, you must
  override the CRecordView classs OnMove()
  function in your derived class
• The Update() function saves new records to the
  database and is required in order to complete a
  new record operation that is started with the
  AddNew() function

48
Adding Records

• The Requery() function updates a database
  applications record set
• To modify the Library Database application so
  that it creates new records see pages 724 through
  726

49
Deleting Records

• The CRecordset classs Delete() function deletes
  the currently displayed record
• Once you delete the current record, you use the
  MoveNext() function to navigate to the next
  record in the record set
• You delete records using a message handler
  function named OnRecordDelete(), as shown on page
  726
• If the record you delete is the last record in
  the record set, then calling the MoveNext()
  function after deleting the record will move you
  past the end of the database, so you will not
  have a valid record selected

50
Deleting Records

• In order to prevent this type of problem from
  occurring, you call the IsEOF() and MoveLast()
  functions after you call the MoveNext() function
• The IsEOF() function, inherited from CRecordset,
  returns a value of true if your position in the
  record set is at the end of the file
• If the IsEOF() function does return a value of
  true, then you should call the MoveLast()
  function, inherited from CRecordset, to navigate
  back to the last record in the record set, using
  code similar to the code illustrated on page 727

51
Deleting Records

• The IsBOF() function, inherited from CRecordset,
  returns a value of true if your position in the
  record set is at the beginning of the file
• The SetFieldNull() function receives a single
  parameter of NULL, which it uses to set all field
  data members in a derived CRecordset class to
  NULL
• After executing the SetFieldNull() function, you
  should call the UpdateData() function with a
  value of FALSE to clear the values displayed in
  the dialog controls

52
Deleting Records

• The code shown on page 727 shows how to write the
  IsBOF() and SetFieldNull() functions
• To modify the Library Database application so
  that it can delete existing records use the steps
  on pages 728

53
Summary

• A database is an ordered collection of
  information from which a computer program can
  quickly access information
• A flat-file database stores information in a
  single table
• A relational database stores information across
  multiple related tables
• Structured query language (SQL) has become a
  standard data manipulation language among many
  database management systems

54
Summary

• A Data Source Name, or DSN, contains
  configuration information that Windows operating
  systems use to access a particular ODBC-compliant
  database
• A snapshot is a static view of the records in a
  database
• MFC handles the exchange of values between
  CRecordset data members and their corresponding
  fields in a database using a mechanism called
  record field exchange, or RFX

55
Summary

• You sort a record set by assigning the field name
  you want to sort by to the m_strSort data member
• The AddNew() function prepares a new database
  record by setting the new records field values
  to NULL
• The Requery() function updates a database
  applications record set
• The Delete() function deletes the currently
  displayed record