Chapter 14: Advanced Topics: DBMS, SQL, and ASP.NET

1. Chapter 14: Advanced Topics: DBMS, SQL, and ASP.NET Visual Basic .NET Programming: From Problem Analysis to Program Design

2. Objectives • Explore the relational database model • Construct database queries using SQL • Write VB .NET programs that interact with a relational database • Explore ASP.NET Visual Basic .NET Programming: From Problem Analysis to Program Design

3. Exploring the Relational Database Model • Real-world applications require data persistence • VB .NET provides several mechanisms • Sequential files • Relational database Visual Basic .NET Programming: From Problem Analysis to Program Design

4. Understanding Tables, Rows, Columns, and Primary and Foreign Keys • Tables • Organize data • Relational database consists of many different tables • Related to each other in some way • Consist of • Rows • Columns Visual Basic .NET Programming: From Problem Analysis to Program Design

5. Understanding Tables, Rows, Columns, and Primary and Foreign Keys (continued) • Rows • Records • Instances • Columns • Attributes • Fields Visual Basic .NET Programming: From Problem Analysis to Program Design

6. Understanding Tables, Rows, Columns, and Primary and Foreign Keys (continued) • Primary key • Field that uniquely identifies each record • Good database design calls for primary key whose • Value is not likely to change over time • Value is not null for any instance • Can add unique identifying attribute to table Visual Basic .NET Programming: From Problem Analysis to Program Design

7. Understanding Tables, Rows, Columns, and Primary and Foreign Keys (continued) • Foreign key • Links information in one table to information in another table • Must be primary key in other table Visual Basic .NET Programming: From Problem Analysis to Program Design

8. Visual Basic .NET Programming: From Problem Analysis to Program Design

9. Mapping PD Attributes to Database Tables • Many different choices for organizing attributes into relational tables • Attributes can be mapped to more than one table • Can add fields that were not originally contained in PD class • Seldom single choice • Weigh benefits and drawbacks of several possible designs • Choose design that best fits needs of application Visual Basic .NET Programming: From Problem Analysis to Program Design

10. Visual Basic .NET Programming: From Problem Analysis to Program Design

11. Visual Basic .NET Programming: From Problem Analysis to Program Design

12. Visual Basic .NET Programming: From Problem Analysis to Program Design

13. Constructing Queries Using SQL • Query • Request for information. • Usually specifies one or more conditions that extracted information must meet • Can be written in Structured Query Language • SQL Visual Basic .NET Programming: From Problem Analysis to Program Design

14. Constructing Queries Using SQL (continued) • SQL • Enables you to perform other common database operation • Insert • Update • Delete Visual Basic .NET Programming: From Problem Analysis to Program Design

15. Extracting Records from a Database • SELECT statement. • Format: SELECT attribute1, attribute2, ... , attributeN FROM table WHERE condition • Each attribute specifies name of column • Table specifies name of table • Condition specifies criteria for selecting records from table Visual Basic .NET Programming: From Problem Analysis to Program Design

16. Extracting Records from a Database (continued) • SELECT statement • Example: SELECT FirstName, LastName FROM Customer WHERE State = ‘GA’ • Can use keywords AND and OR • To specify compound conditions • Extract attributes from multiple tables Visual Basic .NET Programming: From Problem Analysis to Program Design

17. Inserting Records into a Database • INSERT statement • Formats: INSERT INTO table VALUES (value1, value2, ... , valueN) INSERT INTO table (attribute1, attribute2, ... , attributeN) VALUES (value1, value2, ... , valueN) • First format • Must know order of fields in underlying table Visual Basic .NET Programming: From Problem Analysis to Program Design

18. Inserting Records into a Database (continued) • INSERT statement • Example: INSERT INTO Customer VALUES ('51954', 'John', 'Doe', 'Miami', 'FL', '345-6789') Visual Basic .NET Programming: From Problem Analysis to Program Design

19. Updating Records in a Database • UPDATE statement • Format: UPDATE table SET attribute1=value1, attribute2=value2, ..., attributeN=valueN WHERE condition • SET clause • Assign values to each attribute to be modified Visual Basic .NET Programming: From Problem Analysis to Program Design

20. Updating Records in a Database (continued) • UPDATE statement (continued) • WHERE clause • Specifies to which record UPDATE command applies • Example: UPDATE Customer SET State = 'Georgia' WHERE State = 'GA' Visual Basic .NET Programming: From Problem Analysis to Program Design

21. Deleting Records from a Database • DELETE statement • Format: DELETE FROM table WHERE condition • WHERE clause • Specifies conditions that determine record to delete • Example: DELETE FROM Customer WHERE State = 'Georgia' Visual Basic .NET Programming: From Problem Analysis to Program Design

22. Writing VB .NET Programs that Interact with a RelationalDatabase • VB .NET Framework provides support for several database management systems including: • Oracle • Microsoft Access • Microsoft SQLServer Visual Basic .NET Programming: From Problem Analysis to Program Design

23. Writing VB .NET Programs that Interact with a RelationalDatabase (continued) • Data providers • Used to • Connect to database • Execute commands, • Retrieve results • Namespaces: • System.Data • System.Data.OleDb Visual Basic .NET Programming: From Problem Analysis to Program Design

24. Visual Basic .NET Programming: From Problem Analysis to Program Design

25. Visual Basic .NET Programming: From Problem Analysis to Program Design

26. Visual Basic .NET Programming: From Problem Analysis to Program Design

27. Understanding the Examples in this Section • Build on Programming Example presented at end of Chapter 13 • Integrated system • Adding students and professors to database • Producing lists of students and professors. • Extends functionality to allow updating/deleting Visual Basic .NET Programming: From Problem Analysis to Program Design

28. Visual Basic .NET Programming: From Problem Analysis to Program Design

29. Understanding the Examples in this Section (continued) • Three-tier design • PD and GUI classes are unaware of how DA class implements data persistence • DA class handles all interaction with database and carries out tasks of: • Inserting • Updating • Deleting • Extracting information Visual Basic .NET Programming: From Problem Analysis to Program Design

30. Understanding the Examples in this Section (continued) • Three-tier design • GUI classes that capture and display information simply invoke DA methods Visual Basic .NET Programming: From Problem Analysis to Program Design

31. Making the StudentDA Class Work with an Access Database • Import namespaces that provide functionality to interact with Microsoft Access database • Initialize method • Creates and opens connection to database • Attempt to open connection is enclosed within Try-Catch block Visual Basic .NET Programming: From Problem Analysis to Program Design

32. Making the StudentDA Class Work with an Access Database (continued) • GetAll method • Issues SELECT command to retrieve all records from database • General approach for executing SELECT command: • Create data set instance • Create data adapter instance Visual Basic .NET Programming: From Problem Analysis to Program Design

33. Making the StudentDA Class Work with an Access Database (continued) • General approach for executing SELECT command (continued): • Define Stringvariable containing SELECT statement • Use SelectCommand property of data adapter to create command instance and execute command against database • Invoke Fill method of data adapter class to populate data set Visual Basic .NET Programming: From Problem Analysis to Program Design

34. Making the StudentDA Class Work with an Access Database (continued) • Find method • Nearly identical to GetAll method • SELECT statement issued within Find method returns at most one record • AddNew method • Insert new record into database Visual Basic .NET Programming: From Problem Analysis to Program Design

35. Making the StudentDA Class Work with an Access Database (continued) • To create and execute INSERT command in VB.NET: • Create data adapter instance • Create instance of OleDbCommand class • Assign instance to data adapter’s InsertCommand • Invoke ExecuteNonQuery method of OleDbCommand class Visual Basic .NET Programming: From Problem Analysis to Program Design

36. Making the StudentDA Class Work with an Access Database (continued) • Update method • Similar to AddNew method. • Defines SQL statement to update database • Delete method • Defines SQL DELETE statement • Deletes record from database • Terminate method • Closes database connection Visual Basic .NET Programming: From Problem Analysis to Program Design

37. Making the StudentDA Class Work with an Access Database (continued) • Exception classes: • NotFoundException • DuplicateException Visual Basic .NET Programming: From Problem Analysis to Program Design

38. Recognizing the Benefits of the Three-Tier Design • Very little change required to PD and GUI classes • To make them work with new StudentDA class Visual Basic .NET Programming: From Problem Analysis to Program Design

39. Programming Example: Updating and Deleting Records in a Relational Database • Demonstrates how to update and delete records from relational database in three-tier design • Using methods of StudentDA class • Benefit of three-tier design • Add new GUIs without having to make changes in PD and DA tiers Visual Basic .NET Programming: From Problem Analysis to Program Design

40. Visual Basic .NET Programming: From Problem Analysis to Program Design

41. Introducing ASP.NET • ASP.NET • Used to write interactive web pages Visual Basic .NET Programming: From Problem Analysis to Program Design

42. Reviewing Web Basics • Hypertext Markup Language (HTML) • Employed by web pages • Enables browsers to display Web pages on variety of computing platforms • Descriptive language • Uniform Resource Locator (URL) • ASP.NET • Can contain HTML and related files containing code to provide interactivity Visual Basic .NET Programming: From Problem Analysis to Program Design

43. Reviewing Web Basics (continued) • Web pages • Actual files containing HTML code • Have extension of .htm or .html • Contains tags that define format of Web page • Can be created using any text editor • Such as Notepad Visual Basic .NET Programming: From Problem Analysis to Program Design

44. Visual Basic .NET Programming: From Problem Analysis to Program Design

45. Creating ASP.NET Projects • NET Framework provides tools to develop both • Windows applications • Web applications • ASP.NET separates procedural code from Web form (HTML) code: • Web form file has suffix of .aspx, • Code file has suffix .vb • Called code-behind file Visual Basic .NET Programming: From Problem Analysis to Program Design

46. Creating ASP.NET Projects (continued) • In new projects dialog box in Visual Studio .NET • Choose ASP.NET Web Application template • Use Toolbox to add form components • Display Web Forms or HTML sections Visual Basic .NET Programming: From Problem Analysis to Program Design

47. Visual Basic .NET Programming: From Problem Analysis to Program Design

48. Visual Basic .NET Programming: From Problem Analysis to Program Design

49. Creating ASP.NET Projects (continued) • Validation controls • Facilitate adding data validation • Appear in Toolbox • Can be placed on Web page just as any other controls • Properties can be accessed in Properties window Visual Basic .NET Programming: From Problem Analysis to Program Design

50. Visual Basic .NET Programming: From Problem Analysis to Program Design