540 likes | 769 Views
Chapter 14: Advanced Topics: DBMS, SQL, and ASP.NET. Visual Basic .NET Programming: From Problem Analysis to Program Design. Objectives. Explore the relational database model Construct database queries using SQL Write VB .NET programs that interact with a relational database
E N D
Chapter 14: Advanced Topics: DBMS, SQL, and ASP.NET Visual Basic .NET Programming: From Problem Analysis to Program Design
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
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
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
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
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
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
Visual Basic .NET Programming: From Problem Analysis to Program Design
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
Visual Basic .NET Programming: From Problem Analysis to Program Design
Visual Basic .NET Programming: From Problem Analysis to Program Design
Visual Basic .NET Programming: From Problem Analysis to Program Design
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
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
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
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
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
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
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
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
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
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
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
Visual Basic .NET Programming: From Problem Analysis to Program Design
Visual Basic .NET Programming: From Problem Analysis to Program Design
Visual Basic .NET Programming: From Problem Analysis to Program Design
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
Visual Basic .NET Programming: From Problem Analysis to Program Design
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
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
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
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
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
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
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
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
Making the StudentDA Class Work with an Access Database (continued) • Exception classes: • NotFoundException • DuplicateException Visual Basic .NET Programming: From Problem Analysis to Program Design
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
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
Visual Basic .NET Programming: From Problem Analysis to Program Design
Introducing ASP.NET • ASP.NET • Used to write interactive web pages Visual Basic .NET Programming: From Problem Analysis to Program Design
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
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
Visual Basic .NET Programming: From Problem Analysis to Program Design
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
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
Visual Basic .NET Programming: From Problem Analysis to Program Design
Visual Basic .NET Programming: From Problem Analysis to Program Design
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
Visual Basic .NET Programming: From Problem Analysis to Program Design