1 / 55

Connecting to Databases

Connecting to Databases. 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

qamar
Download Presentation

Connecting to Databases

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Connecting to Databases Microsoft Visual C++ .NET Chapter 13

  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 Microsoft Visual C++ .NET Chapter 13

  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 Microsoft Visual C++ .NET Chapter 13

  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 Microsoft Visual C++ .NET Chapter 13

  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 Microsoft Visual C++ .NET Chapter 13

  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 Microsoft Visual C++ .NET Chapter 13

  7. Understanding Databases Microsoft Visual C++ .NET Chapter 13

  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 Microsoft Visual C++ .NET Chapter 13

  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 Microsoft Visual C++ .NET Chapter 13

  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 Microsoft Visual C++ .NET Chapter 13

  11. Understanding Databases • Figure 13-7 contains an example of a many-to-many relationship between a Programmers table and a Programming Languages table Microsoft Visual C++ .NET Chapter 13

  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 Microsoft Visual C++ .NET Chapter 13

  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 Microsoft Visual C++ .NET Chapter 13

  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 Microsoft Visual C++ .NET Chapter 13

  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 Microsoft Visual C++ .NET Chapter 13

  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 Microsoft Visual C++ .NET Chapter 13

  17. Structured Query Language • Figure 13-10 lists several SQL keywords that are common to most versions of SQL Microsoft Visual C++ .NET Chapter 13

  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 Microsoft Visual C++ .NET Chapter 13

  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 Microsoft Visual C++ .NET Chapter 13

  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 Microsoft Visual C++ .NET Chapter 13

  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 Microsoft Visual C++ .NET Chapter 13

  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 Microsoft Visual C++ .NET Chapter 13

  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 Microsoft Visual C++ .NET Chapter 13

  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 Microsoft Visual C++ .NET Chapter 13

  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 Microsoft Visual C++ .NET Chapter 13

  26. Creating an ODBC Database Application • Any changes made to the database after you run your application will not be reflected in your application’s 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 Microsoft Visual C++ .NET Chapter 13

  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 Microsoft Visual C++ .NET Chapter 13

  28. The Database Support Tab in the MFC Application Wizard Microsoft Visual C++ .NET Chapter 13

  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 Microsoft Visual C++ .NET Chapter 13

  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 Microsoft Visual C++ .NET Chapter 13

  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 class’s 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 Microsoft Visual C++ .NET Chapter 13

  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 Microsoft Visual C++ .NET Chapter 13

  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 application’s 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 Microsoft Visual C++ .NET Chapter 13

  34. Linking Dialog Controls to Database Fields • In order for the CRecordView class’s dialog window to communicate with the CRecordset class’s data members, you need to add controls to a derived CRecordView class’s dialog window • Then, you add to the CRecordView class’s 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 class’s data members Microsoft Visual C++ .NET Chapter 13

  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 book’s genre of fiction or nonfiction Microsoft Visual C++ .NET Chapter 13

  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 Microsoft Visual C++ .NET Chapter 13

  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 Microsoft Visual C++ .NET Chapter 13

  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 class’s dialog controls that will display the fields in the Library Database program as shown on pages 714 and 715 of the textbook Microsoft Visual C++ .NET Chapter 13

  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 Microsoft Visual C++ .NET Chapter 13

  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 Microsoft Visual C++ .NET Chapter 13

  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 Microsoft Visual C++ .NET Chapter 13

  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 Microsoft Visual C++ .NET Chapter 13

  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 Microsoft Visual C++ .NET Chapter 13

  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 Microsoft Visual C++ .NET Chapter 13

  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 Microsoft Visual C++ .NET Chapter 13

  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 record’s 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 Microsoft Visual C++ .NET Chapter 13

  47. Adding Records • The process of updating database records is managed by the CRecordView class’s OnMove() function • However, in order to save a new record, you must override the CRecordView class’s 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 Microsoft Visual C++ .NET Chapter 13

  48. Adding Records • The Requery() function updates a database application’s record set • To modify the Library Database application so that it creates new records see pages 724 through 726 Microsoft Visual C++ .NET Chapter 13

  49. Deleting Records • The CRecordset class’s 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 Microsoft Visual C++ .NET Chapter 13

  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 Microsoft Visual C++ .NET Chapter 13

More Related