1 / 59

Tutorial 25 - ATM Application Introducing Database Programming

Tutorial 25 - ATM Application Introducing Database Programming. Outline 25.1 Test-Driving the ATM Application 25.2 Planning the ATM Application 25.3 Creating Database Connections 25.4 Programming the ATM Application 25.5 Wrap-Up. Objectives. In this tutorial, you will learn to:

melyssa-simpson
Download Presentation

Tutorial 25 - ATM Application Introducing Database Programming

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. Tutorial 25 - ATMApplicationIntroducing Database Programming Outline25.1 Test-Driving the ATM Application25.2 Planning the ATM Application25.3 Creating Database Connections25.4 Programming the ATM Application25.5 Wrap-Up

  2. Objectives • In this tutorial, you will learn to: • Connect to databases. • View the contents of an Access database. • Add database controls to Windows Forms. • Use the Server Explorer window. • Use the Query Builder dialog. • Read information from and update information in databases.

  3. 25.1 Test-Driving the ATM Application

  4. 25.1 Test-Driving the ATM Application • The ATM Application • Open ATM application and select Debug > Start to run • OK,Balance,Withdraw, and DoneButtons are disabled • After account number is entered, user is prompted for a pin number • Characters in PIN number display as asterisks and enable OKButton • Clicking the OKButton displays a welcome message and enables the Balance and WithdrawButtons • Clicking the BalanceButton displays the current account balance • Clicking Withdraw prompts the user to enter a monetary amount and displays the new balance after the withdrawal

  5. Displays instructions and messages to the user Disabled Buttons Keypad for entering PIN and withdrawal amount ComboBox that displays account numbers 25.1 Test-Driving the ATM Application Figure 25.1　ATM application Form.

  6. Asterisk is displayed here for each keypad Button pressed for the PIN numbers Account number selected 25.1 Test-Driving the ATM Application Figure 25.2　Providing PIN number for the selected account.

  7. Welcome message displays in Label when user enters correct PIN number 25.1 Test-Driving the ATM Application Figure 25.3　ATM displaying welcome message.

  8. New balance displays in Label after user performs a withdrawal 25.1 Test-Driving the ATM Application Figure 25.4　ATM application displaying balance after withdrawal.

  9. 25.2 Planning the ATM Application

  10. 25.2 Planning the ATM Application

  11. 25.2 Planning the ATM Application

  12. 25.3 Creating Database Connections • Adding a database • View > Server Explorer to display Server Explorer window • Create a connection object to maintain a connection to a database

  13. Click the Connect to Database Button 25.3 Creating Database Connections Figure 25.6　ServerExplorer window.

  14. Provider tab Select this provider Click to continue 25.3 Creating Database Connections Figure 25.7　Provider tab in the DataLinkProperties dialog.

  15. Connection tab Click to select a database 25.3 Creating Database Connections Figure 25.8　Connection tab of the DataLinkProperties dialog.

  16. Select the db_ATM.mdb file 25.3 Creating Database Connections Figure 25.9　Select AccessDatabase dialog. • db_ATM.mdb contains data you will query

  17. Database path displayed User name should be Admin This should be checked Click to test the connection 25.3 Creating Database Connections Figure 25.10　Connection tab containing selected database path.

  18. Click to expand 25.3 Creating Database Connections Figure 25.11　Database connection shown in the ServerExplorer window.　

  19. OleDbConnection object added to the Form 25.3 Creating Database Connections Figure 25.13　OleDbConnection object added to the Form.　

  20. 25.3 Creating Database Connections • Understanding the db_ATM.mdb database structure • AccountInformation is a table in the db_ATM.mdb database • Tables store information in rows and columns in a database • In AccountInformation, the ten records are in table rows and the four fields are table columns • Primary keys are fields that distinguish records from one another • This database’s primary key is the AccountNumber field • The value of the primary key in each record of the table must be unique

  21. Click to display the Tables node Right click theAccountInformationnode Select to view the table’s contents 25.3 Creating Database Connections Figure 25.14　Viewing the AccountInformation table.

  22. Fields (columns) Collectively, fields and records form a table Records (rows) 25.3 Creating Database Connections Figure 25.15　AccountInformation table’s content.

  23. 25.3 Creating Database Connections • Data Command Objects • Data command objects retrieve and update data in the database • To create a data command object, drag-and-drop an ADO .NET OleDbCommand control from the Data tab in the Toolboxto the Form. • Query Builderallows you to specify the commands that retrieve information from and modify information in databases

  24. Click to specify connection object Connection property Select the existing connection (objOleDbConnection) 25.3 Creating Database Connections Figure 25.16　Properties of objSelectAccount.

  25. AddTabledialog Select this table Click to add selected table CloseButton 25.3 Creating Database Connections Figure 25.17　QueryBuilder and AddTable dialogs.

  26. AccountInformation window appears once AccountInformation is added in the AddTable dialog Primary-key-field name displayed in bold Partial SQL statement 25.3 Creating Database Connections Figure 25.18　AccountInformation table added to the QueryBuilder dialog.

  27. 25.3 Creating Database Connections • Data Command Objects(continued) • Words SELECTand FROM represent SQL statements • A Structured Query Language (SQL)often performs database queries and manipulates data. • Criteria column specifies a specific record to be retrieved or manipulated • In the Criteria column, type =? • Adds WHERE to end of SQL statement • Specifies criteria that determine rows to retrieve • Funnel icon denotes a value in the Criteria column

  28. Check this CheckBox Names selected in the AccountInformation window appear here SQL SELECT statement 25.3 Creating Database Connections Figure 25.19　Selecting fields in the QueryBuilder dialog.

  29. Funnel icon Check this CheckBox Criteria column Uncheck this box This value will be removed when AccountNumber’s OutputCheckBox is unchecked Type this value 25.3 Creating Database Connections Figure 25.20　WHERE SQL keyword added to the SQL statement.

  30. AccountNumberCheckBox is now unchecked Complete SELECT statement 25.3 Creating Database Connections Figure 25.21　Complete SQL SELECT statement.

  31. 25.3 Creating Database Connections Figure 25.22　MicrosoftDevelopmentEnvironment dialog.

  32. Click to view collection Parameters property 25.3 Creating Database Connections Figure 25.23　Parameters property of a data command object.

  33. AccountNumberparameter Parameter name which you will use in Visual Basic .NET code to complete the SQL statement 25.3 Creating Database Connections Figure 25.24　OleDbParameterCollectionEditor dialog.

  34. OleDbCommand object 25.3 Creating Database Connections Figure 25.25　 OleDbCommands displayed in the component tray.

  35. Right click the Column Select this to create an UPDATE statement 25.3 Creating Database Connections Figure 25.26　Specifying an UPDATE command.

  36. Check this CheckBox New Value column Type in this value This expression appears 25.3 Creating Database Connections Figure 25.27　Specifying the BalanceAmount field in the UPDATE command.

  37. Complete UPDATE command with parameters 25.3 Creating Database Connections Figure 25.28　UPDATE command displayed in the QueryBuilder dialog.

  38. Importing namespace System.Data.OleDb 25.4 Programming the ATM Application Figure 25.29　System.Data.OleDb imported to ATM.vb.

  39. 25.4 Programming the ATM Application • Displaying existing account numbers in the ComboBox • OleDbDataReader is a data reader object, or an object that reads data from a database • Data readers cannot modify data

  40. Opening the database connection Creating a data reader 25.4 Programming the ATM Application Figure 25.30　Opening the connection to the database and creating a data reader.

  41. Closing the database connection Filling theComboBox with account numbers 25.4 Programming the ATM Application Figure 25.31　Filling the ComboBox with account numbers. • Read method begins to read information from the database and returns False if there are no more records to be read

  42. Setting theAccountNumberparameter value of the command object Opening the database connection 25.4 Programming the ATM Application Figure 25.32　Specifying the AccountNumber parameter value of the data command object and connecting to the database.

  43. Creating the data reader Use data reader to read from database Setting instance variable values to values retrieved from the databse Closing the reader Closing the database connection 25.4 Programming the ATM Application Figure 25.33　OleDbDataReader for reading a record’s data.

  44. Specifying objUpdateBalance‘s BalanceAmount parameter value Specifying objUpdateBalance’s ORIGINAL_AccountNumber parameter value 25.4 Programming the ATM Application Figure 25.34　Connecting to the database to update a field.

  45. Opening the database connection Executing the UPDATE command Closing the database connection 25.4 Programming the ATM Application Figure 25.35　Executing an UPDATE command before closing the database connection. • Update command executed by calling ExecuteNonQuery from the objUpdateBalance object.

  46. ATM.vbPart (1 of 14)

  47. ATM.vbPart (2 of 14)

  48. ATM.vbPart (3 of 14)

  49. ATM.vbPart (4 of 14)

  50. ATM.vbPart (5 of 14)

More Related