Visual Basic Programming II Lecture 11

1. Visual Basic Programming IILecture 11 MIS233 Instructor – Larry Langellier

2. This Week – Data Access Objects (DAO) • Understand the Jet engine and working with DAO Collections • Open Databases and Recordsets • Work with the Recordset object • Reading and Writing field values • Seek and Find • Add, Edit and Update • The Benefits of Modular Design • Three-Tier Architecture • User-Interface Layer • Business-Rules Layer • Persistence Layer

3. The Jet Engine and DAO Collections • The Jet Engine features an object model based on a hierarchy of collections • An object model contains a collection of objects • Each object may in turn contain a collection of other objects • At the top of the collection hierarchy is a single object – the DBEngine object • The Engine may connect you to multiple databases, but we will focus on the portion of the hierarchy for a single Database • The DAO object model is contained in a Library • Project -> References • Access ’97: Microsoft DAO 3.51 Object Library • Access 2000: Microsoft DAO 3.6 Object Library

4. The Jet engine (DAO) Object Model • The nested hierarchy of objects in DAO is as follows: Database TableDef(s)A stored table definition Field(s) Index(es) Field(s) QueryDef(s)A stored query definition Field(s) Parameter(s) Recordset(s)An object that represents a cursored view Field(s) into a table or a query result Relation(s) Field(s)

5. Database Collections • As we’ve seen, Collections are much like arrays except: • They size themselves automatically • Different types of objects can be stored in a single collection • DAO collections work the same as the Collection object we’ve seen before, except: • they are zero (0) based instead of one (1) based • You can • navigate through the nested object hierarchy using the “dot” operator for access • Iterate through these collections with a For Each…Next loop • The “Fields” collection is the default for a Recordset • Example 24.1 – Iterating Database Collections

6. Do It Together! • Revise the solution for Example 24.1 to use the Baseball.mdb database (located in the lecture examples download for today) • Add a ListBox to display all the indexes of a Table in addition to it’s fields and write the code to populate this list when a Table is selected (clicked) • Baseball.mdb is an Access 2000 database – what do you need to do to deal with that?

7. Interface Sample

8. Sample Solution • Located in BBCollections.vbp in tonight’s lecture example download

9. Opening Databases with DAO • Syntax: Set db = OpenDatabase(dbName) • Where: • db is a variable of type Database • dbName – the name of an existing database (String) • Example Dim db AsDAO.Database Set db = OpenDatabase("MyDB.mdb")

10. Opening Recordsets from a Database Object • Syntax: Set anRS = db.OpenRecordset(source, [type]) • Where: • anRS is a variable of type Recordset • db is a Database object • source is a String specifying a table name, query name or SQL statement that returns records • type allows creation of a Table(default), Dynaset or Snapshot Recordset dbOpenTable, dbOpenDynaset or dbOpenSnapshot • Example • 24.2: Using the three OpenRecordset Types

11. Working with Recordsets • Moving • MoveFirst, MoveLast, MovePrevious, MoveNext • BOF, EOF • Example 24.4: Using Recordset Move Methods • Accessing Fields – the Fields Collection is the default rs!FieldName rs.FieldName.Value rs![FieldName] rs.[FieldName].Value rs(0) rs(0).Value rs("Field Name") rs("Field Name").Value With the Fields Collection rs.Fields("Field Name") rs.Fields("Field Name").Value • Number of Records myRS.MoveLast myRS.RecordCount

12. Using the Seek Method • Locate records based on search criteria on an index • Only available for Table-type Recordsets • Seeks based on an Index in the table, “PrimaryKey” by default aTableRS.Index = "HomeRuns" • Fastest way to find a record • Syntax: aTableRS.Seekcomparison, key1, key2… • Where: • comparison is a string: “<“, “<=“, “=“, etc. • key1, key2… are values corresponding to the selected Index of the table • Test the recordset’s NoMatch Property to see if your search was successful • Example • 24.5: Using the Seek Method

13. Using Find Methods • Find Methods can also be used to locate records • Only work on dynaset or snapshot records (not tables) • FindFirst and FindNext search toward the end • FindLast and FindPrevious search toward the top • Syntax: anRS.Find______ criteria • Where: • criteria is a string expression (like an SQL WHERE clause) used to locate the record • AddNew, Edit, Update and Delete • Example • 24.6: Editing and Updating a Database Record

14. Do It Together! • In Form_Load • Connect to the Baseball.mdb database again. • Open the Teams and Players tables • Iterate through each record of the Teams table and populate a ListBox with the name of each team • When you click on a Team • Find every player in the Players table whose TeamId matches that of the selected team and add the player’s name to the other ListBox • In Form_Unload • Close all the tables and the database • Release the memory associated with those objects

15. Interface Sample

16. Sample Solution • Contained in TeamList.vbp in the lecture example download

17. Modular Design • Modular Design is breaking down a computer solution into small, functional components, rather than a single long application • The Benefits • It is easier to: • Define the solution to the design problem • Communicate the internal organization of the design • Specify the functionality by designing each procedure to accomplish a specific task • Assign programming tasks to a group of people • Develop rapid solutions • Isolate problems better during program design and testing • Maintain computer programs when errors occur • One example of Modular Design is known as Three-Tier Architecture

18. The Layers of an Application –3 Tier Architecture • User-Interface Layer • The Visual Components the user utilizes to input information, navigate through the program, execute functionality, and display results • Business-Rule Layer • This layer contains the implementation of a company’s policies, rules, and procedures • Also serves as a middle man – stores and retrieves to/from the Persistence Layer while also used to update GUI • Data (Persistence) Layer • Consists of the application’s stored data • Typically implemented as a relational database

19. The Layers of an Application (cont.) • Examples of Various 3 Tier Solutions • Client-Server • VB GUI • Business Logic as Visual Basic or stored procedures • Oracle database on a server • Internet Application • Thin client browser interface (HTML) • Dynamic content via Active Server Pages • SQL database • This layered approach is not limited to client/server – applications like multimedia, games, and even standalone applications can benefit from this design methodology

20. GUI – The Presentation Layer • A Login Window and a Main Window • Example 21.1 • Prototyping a User-Interface • A way to improve communication with the end user • Clarifies requirements • A “living” document of what has been agreed to • A starting point for multi-person team efforts • Example 21.2 – a Prototyping example • Should never • Communicate directly with the persistent layer • Contain business logic – calculations, etc.

21. Business Rules • The Business Rule layer of an application typically: • Retrieves information entered by the user • Makes calculations • Reads and writes persistent data to and from a database or file • Returns the results of processing to the user-interface layer • Maintains the “current state” of the “world” • The Business Rule Layer is typically implemented as a hierarchy of objects • “When in doubt – it goes here!”

22. The Business Object Model – “Group” Classes • Why • Model the Group • Properties • The Collection of “Objects” • The associated Recordset • Possibly an index for the member “currently being operated on” • Methods • Add a Member • Remove a Member • Load all members/records from the database/table • Support for the User Interface • Events • Initialize – Populate the minimum (or more…, or all…) • Terminate – cleanup (close recordsets, database, release memory)

23. The Business Object Model – “Object” Classes • Why • Model the Individual • Properties • The Adjectives – the normal properties we’ve been declaring • The unique key/id of this object in the database • A clean/dirty attribute if database “syncing” is deferred • Methods • The Verbs – the normal methods we’ve been declaring • Load self from the database (called by the collection’s Load All) • If any property is a collection/group class, then pass-throughs for: • Add a Member • Remove a Member • Load all members/records from the database/table • Support for the User Interface • Events • Initialize and Terminate

24. Persistence Layer • Stores information so the next time the program is run it can be loaded again without having to retype everything • Typically, a Database with Tables, Records, Fields, etc. • Typically you will have a single table for each “Group” Class in your Object Model (Business Layer) • Each Table will have Fields corresponding to the Properties of the associated “Object” Class

25. Issues • How do the Forms get the info to display? • When should the database/tables be opened? • When is the Object Model created? • When does the info get loaded from the database into the objects? • When should the database be updated with changes? • Where is the code located for • Updating the display • Creating the Object Model • Updating the Object Model • Loading info from the database • Writing info to the database

26. Semester Project Design • This Design should be comprised of three elements: • A Prototype of your User-Interface – similar in spirit to example 21.2 (page 499) • An Object Model definition • What Classes are you going to have? • What Properties will each class have? • What Behaviors (Methods) will each class have? • A Database schema • What Tables will your database contain? • What Fields will each table have? • What will the Primary Keys be for each table?

27. Just Try It! • Use VisData to create a database for your Semester Project • I’ll be happy to come around and discuss this with each of you individually

28. Details… • Today • Final Project Handout • Next Class • Read Chapter 25: ActiveX Data Objects • Homework #12 will be due at the beginning of class