1 / 88

Chapter 11 – Introduction to Database Concepts

Chapter 11 – Introduction to Database Concepts. Introduce the concepts of a database Introduce the objects required to connect a Visual Basic .NET application to a database Explain basic operations in displaying and navigating through data stored in a database on a form via controls

vickeyj
Download Presentation

Chapter 11 – Introduction to Database Concepts

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. Chapter 11 – Introduction to Database Concepts • Introduce the concepts of a database • Introduce the objects required to connect a Visual Basic .NET application to a database • Explain basic operations in displaying and navigating through data stored in a database on a form via controls • Explain how to add, edit, and delete data stored in a database • Explain how to bind data in a database to data grid controls The Visual Basic .NET Coach

  2. Chapter 11 – Introduction to Database Concepts If data is stored on a hard drive in no apparent order, it is just that, data. Data can be thought of as the raw values obtained from a process. If data is organized into a meaningful format so that it becomes valuable in answering questions, it has become information. A database is a collection of tables organized so that the data makes sense. A table usually contains data relating to one entity. Observe the table shown in Figure 11.1, which stores the names of six basketball players and their statistics. The Visual Basic .NET Coach

  3. Chapter 11 – Introduction to Database Concepts You may notice that a table looks very much like the MS flex grid control you used earlier. While the MS flex grid control contains rows (known as records) and columns (known as fields) and is similar in appearance, tables have an important difference. Data validation occurs when values are entered into a table. Different software packages, known as database management systems (DBMSs), allow the user to specify different types of constraints on the data being entered. the table shown in Figure 11.1 was created, the following data types were associated with each field: The Visual Basic .NET Coach

  4. Chapter 11 – Introduction to Database Concepts 11.1 Displaying Data Stored in a Database One of the single most important features of Visual Basic .NET is the ease with which it allows the programmer to access data stored in a database. The Database Objects To display data in a form, you will require the use of three objects and a control to display them. The Database A database can be any one of many sources that store data. In all of the examples in this text, the database will be a Microsoft Access database stored in a file with an extension of .mdb. The Connection The first object you will use to access a database is the OleDbConnection. When you wish to access a database, you will need to communicate to Visual Basic .NET many specifics about the database that you are connecting to. The Visual Basic .NET Coach

  5. Chapter 11 – Introduction to Database Concepts The Data Adapter A data adapter provides the methods to transfer information from the database to the data set using the OleDbDataAdapter. Different mechanisms exist to specify the data that will be transferred from the database to your application. Most common is the use of an industry standard series of statements defined with a language called SQL. Information is transferred from the database to the data set using the Fill method. Information is transferred from the data set back to the database using the Update method. The Visual Basic .NET Coach

  6. Chapter 11 – Introduction to Database Concepts The Data Set A DataSet object can be thought of as a temporary representation of the data contained in the database that you are connected to. While a DataSet object has a great deal of functionality, for now, you will focus solely on its ability to cache the contents of a database that you will be accessing. Any changes you wish to make to data in a database is first made to the data set and then the table is updated through the data adapter. The Visual Basic .NET Coach

  7. Chapter 11 – Introduction to Database Concepts Example: Simple Application with Text Boxes Connected to a Database Typically, when data is displayed, your application will require a few basic navigational operations. When a form loads with a data control and associated text boxes, it will display the first record in the table it is associated with. Since Allen Iverson was the first player in the table, his data is shown. The Visual Basic .NET Coach

  8. Chapter 11 – Introduction to Database Concepts Example: Simple Application with Text Boxes Connected to a Database Application showing second record of table Application showing last record in table Application showing next-to-last record in table The Visual Basic .NET Coach

  9. Chapter 11 – Introduction to Database Concepts Creating the Basketball Stats Project Create a project called BasketballTextBoxes. Configuring the Controls The first step in creating a form to access a database is to add and configure the OleDbConnection, OleDbDataAdapter, and DataSet objects. Adding and Configuring the OleDbConnection Object The first configuration you must perform is to form a connection with the database. Step 1: Place an OleDbConnection object from the Data tab of the Toolbox. Step 2: Click on the ConnectionString property in the Properties window. A pull-down icon will appear in the Properties window. If you click on the pull-down icon, a pop-up menu will appear. Step 3: Click on the <New Connection …> menu item. The Data Link Properties window will appear. The Visual Basic .NET Coach

  10. Chapter 11 – Introduction to Database Concepts Connection Tab of the Data Link Properties window The Visual Basic .NET Coach

  11. Chapter 11 – Introduction to Database Concepts Step 4: Click on the Provider tab in the Data Link Properties window. Step 5: Select Microsoft Jet 4.0 OLE DB Provider. The Visual Basic .NET Coach

  12. Chapter 11 – Introduction to Database Concepts Step 4: Click on the Provider tab in the Data Link Properties window. Step 5: Select Microsoft Jet 4.0 OLE DB Provider. The Visual Basic .NET Coach

  13. Chapter 11 – Introduction to Database Concepts Step 6: Click on the Next button, the Connection tab will become active. The Visual Basic .NET Coach

  14. Chapter 11 – Introduction to Database Concepts Step 7: You must enter or select the physical location of the Access database file that will be connected to the application. Step 8: Once the database is specified, you have completed the specification of the connection. Click on the OK button to commit the specification. The Visual Basic .NET Coach

  15. Chapter 11 – Introduction to Database Concepts Adding and Configuring the OleDbDataAdapter Object Once a connection has been established, a data adapter must be configured. This will allow you to specify the way that you want your application to view, add, edit, and delete data through the connection. Step 1: Place an OleDbDataAdapter object from the Data tab of the Toolbox. The Data Adapter Configuration Wizard will automatically start. Step 2: An informational screen will appear, welcoming you to the wizard. Click on the Next button of the wizard. The Visual Basic .NET Coach

  16. Chapter 11 – Introduction to Database Concepts Step 3: You will be presented with a window that will allow you to select which data connection you wish the data adapter to use. There will probably be only one data connection on your form, so it will be automatically selected. Click on the Next button. The Visual Basic .NET Coach

  17. Chapter 11 – Introduction to Database Concepts Step 4: The wizard will allow you to select the type of query your data adapter will use. Since you selected a Microsoft Access database, you are limited to SQL statements. Click on the Next button to select Use SQL statements. The Visual Basic .NET Coach

  18. Chapter 11 – Introduction to Database Concepts Step 5: The next window that appears will allow you to generate a SQL statement. You have the option of writing a SQL statement from scratch or having the Query Builder create it for you. It is far simpler to have the Query Builder create the necessary statements. The Visual Basic .NET Coach

  19. Chapter 11 – Introduction to Database Concepts Step 6: Click on the Query Builder button, and the wizard will present you with a list of tables that you can base your query on. The Visual Basic .NET Coach

  20. Chapter 11 – Introduction to Database Concepts Step 7: Click on the Add button, and the PlayerStats table will be added to the Query Builder while the Add Table window is still showing. The Visual Basic .NET Coach

  21. Chapter 11 – Introduction to Database Concepts Step 8: Click on the Close button to close the Add Table window. Step 9: Click on the *(All Columns) choice in the PlayerStats window to include all the columns of the table. The Visual Basic .NET Coach

  22. Chapter 11 – Introduction to Database Concepts Step 10: Click on the OK button. You will see the SQL statement you generated in the window. The Visual Basic .NET Coach

  23. Chapter 11 – Introduction to Database Concepts Step 11: Click on the Next button, and the wizard will create all the statements required for the data adapter. Step 12: Click on the Finish button, and your data adapter will be completely configured. The Visual Basic .NET Coach

  24. Chapter 11 – Introduction to Database Concepts Adding and Configuring the DataSet Object A DataSet object must be created to temporarily hold the data from a table in the form. Its creation is quite simple. Step 1: Click on the form. If the form is not selected, the menu option for Step 2 will not appear. Step 2: Click on Generate Dataset from the Data Menu. Step 3: Select the New radio button and place type dsBasketballPlayers1 in the text box. The Visual Basic .NET Coach

  25. Chapter 11 – Introduction to Database Concepts Step 3: Click on the OK button and the data set DsBasketballPlayers1 will appear on the bottom of the form shown in Figure 11.21. The Visual Basic .NET Coach

  26. Chapter 11 – Introduction to Database Concepts Connecting Other Controls to the Database In your example, you will connect each text box to an individual field in the table. The procedure is the same for each text box. Step 1: Place a label on the form to indicate the value stored in the text box. Step 2: Change the label’s Font to be Bold. Step 3: Change the Text property of the label to reflect the value being stored in the text box. Step 4: Place a text box below the label. Step 5: Change the default Name of the text box to a more meaningful name. Step 6: Remove the default text in the Text property of the text box. The Visual Basic .NET Coach

  27. Chapter 11 – Introduction to Database Concepts Step 7: Select the field of the database to bind to the text box. Make sure the text box that you want to bind is selected. Then click on the + to the left of the DataBindings property at the top of the Properties window. The window will expand to show the Text property. Click on the Down arrow icon of the Text property. A pop-up window will appear showing the data set DsBasketballPlayers1 with a + next to it. Click on the +, and the PlayerStats table will appear. Click on the + next to the PlayerStats table, and a list of fields will appear. Click on the name of the field that you wish to associate with the text box. The Visual Basic .NET Coach

  28. Chapter 11 – Introduction to Database Concepts After all the text boxes have been added and linked, your application should appear as follows: The Visual Basic .NET Coach

  29. Chapter 11 – Introduction to Database Concepts Controlling the Database with Code Visual Basic .NET allows you to navigate the database programmatically. You will learn to code the loaded and updating of data into the data set as well as moving to the first, previous, next, and last record of the data set. Coding the Loading of a Data Set The most logical place to put the code to load the data set is in the constructor of the form. The syntax required to load data into a data set is as follows: DataAdapterName.Fill(DataSetName) The following code should be contained in the constructor: PublicSubNew() MyBase.New() 'This call is required by the Windows Form Designer InitializeComponent() 'Add any initialization after the InitializeComponent() call OleDbDataAdapter1.Fill(DsBasketballPlayers1) 'Load the Data Set EndSub The Visual Basic .NET Coach

  30. Chapter 11 – Introduction to Database Concepts Moving the Data Set to the First, Previous, Next, or Last Record In order for the user to control what record is displayed in the text boxes, the user will require a series of buttons. A button should be created to allow the user to move to the first, previous, next, or last record. Step 1: Add a button to the bottom of the form for each operation. They should be named btnFirst, btnPrevious, btnNext, and btnLast. The Visual Basic .NET Coach

  31. Chapter 11 – Introduction to Database Concepts 11.2 Binding a Data Grid While setting up access to each of the fields of a table through text boxes will allow a user access to each record in the database, its limited viewing of a single record at a time can become a hindrance to viewing and changing large amounts of data. A better way is to use the data grid control from the Windows Forms Toolbox. Your previous application could be rewritten and enhanced to exploit the benefits of a data grid. Observe the application which contains a data grid linked to the PlayerStats table. The Visual Basic .NET Coach

  32. Chapter 11 – Introduction to Database Concepts Functionality of a Data Grid The data presented in a data grid can be loaded from a table at any time. Load it from the form’s constructor. Unlike an MS flex grid, a data grid will allow the user to make changes. Users can just click on the field that they wish to change and then type the new value in the place of the old one. The ability to make changes can be shut off if you decide to give the user a read-only view of the data. If you wish to add a record to the data grid, you can type the new information in the last row. The asterisk to the left indicates this row. If you wish to delete a row, click on the gray rectangle to the left of the row and then click on the <Delete> key of your keyboard. When a change is made to the data presented in the grid, the actual data in the table is not automatically updated. The simplest way to accomplish updating the table is to place the update code in a button. The Visual Basic .NET Coach

  33. Chapter 11 – Introduction to Database Concepts Functionality of a Data Grid The data presented in a data grid can be loaded from a table at any time. Load it from the form’s constructor. Unlike an MS flex grid, a data grid will allow the user to make changes. Users can just click on the field that they wish to change and then type the new value in the place of the old one. The ability to make changes can be shut off if you decide to give the user a read-only view of the data. If you wish to add a record to the data grid, you can type the new information in the last row. The asterisk to the left indicates this row. If you wish to delete a row, click on the gray rectangle to the left of the row and then click on the <Delete> key of your keyboard. When a change is made to the data presented in the grid, the actual data in the table is not automatically updated. The simplest way to accomplish updating the table is to place the update code in a button. The Visual Basic .NET Coach

  34. Chapter 11 – Introduction to Database Concepts Steps to Create the Basketball Data Grid Application The steps to create the basketball data grid application are very similar to the steps to create the text box version of the application. Both applications require OleDbConnection, OleDbDataAdapter, and DataSet objects. The objects are configured identically to the way they were for the previous application; therefore, a series of steps with abbreviated explanations will be shown. Create a New Project Create a new project called BasketBallDataGrid. Adding and Configuring the OleDbConnection Object Step 1: Place an OleDbConnection object from the Data tab of the Toolbox. Step 2: Click on the pull-down icon of the OleDbConnection object in the ConnectionString property in the Properties window. A pop-up menu will appear. Step 3: Click on the <New Connection …> menu item. The Data Link Properties window will appear. Step 4: Click on the Provider tab in the Data Link Properties window. Step 5: Select Microsoft Jet 4.0 OLE DB Provider. The Visual Basic .NET Coach

  35. Chapter 11 – Introduction to Database Concepts Step 6: Click on the Next button. Step 7: Select the path to the Basketball.mdb file. Step 8: Once the database is specified, you have completed the specification of the connection. Click on OK to commit the specification. Adding and Configuring the OleDbDataAdapter Object Step 1: Place an OleDbDataAdapter object from the Data tab of the Toolbox. The Data Adapter Configuration Wizard will automatically start. Step 2: An informational screen will appear, welcoming you to the wizard. Click on the Next button of the wizard. Step 3: Click on the Next button of the wizard. Step 4: Click on the Next button to select Use SQL statements. Step 5: The next window that appears will allow you to generate a SQL statement. Step 6: Click on the Query Builder button, and the wizard will present you with a list of tables that you can base your query on. The Visual Basic .NET Coach

  36. Chapter 11 – Introduction to Database Concepts Step 7: Click on the Add button, and the PlayerStats table will be added to the Query Builder. Step 8: Click on the Close button to close the Add Table window. Step 9: Click on the *(All Columns) choice in the PlayerStats window to select that all the columns of the table will be included. Step 10: Click on the OK button. You will see the SQL statement you generated in the window. Step 11: Click on the Next button, and the wizard will create all the statements required for the data adapter. Step 12: Click on the Finish button, and your data adapter will be completely configured. The Visual Basic .NET Coach

  37. Chapter 11 – Introduction to Database Concepts Adding and Configuring the DataSet Object Step 1: Click on the form. Step 2: Click on Generate Data Set from the Data menu. Step 3: Click on the OK button. Adding and Configuring the Data Grid Once the three objects have been created and properly configured, the addition of a data grid and its linkage to the database is simple. Step 1: Add a data grid object to the form from the Toolbox. Step 2: Rename the data grid to grdBasketball. Step 3: Change the DataSource property of the data grid to dsBasketballPlayers1.PlayerStats. Step 4: Set the Anchor property of the grid to Top, Bottom, Left, Right. The Visual Basic .NET Coach

  38. Chapter 11 – Introduction to Database Concepts Filling the Data Grid Add the following code to the constructor of the form so that the data grid is filled when the form loads. PublicSubNew() MyBase.New() 'This call is required by the Windows Form Designer InitializeComponent() 'Add any initialization after the InitializeComponent() call OleDbDataAdapter1.Fill(dsBasketballPlayers1) 'Load the DataSet EndSub The Visual Basic .NET Coach

  39. Chapter 11 – Introduction to Database Concepts Adding an Update Button Step 1: Add a button to the form in the lower-left corner. Step 2: Rename the button to btnUpdate. Step 3: Add the following code to the button’s Click event. OleDbDataAdapter1.Update(dsBasketballPlayers1) Step 4: Change the Anchor property to Bottom, Right. The Visual Basic .NET Coach

  40. Chapter 11 – Introduction to Database Concepts Accessing Individual Values in a Data Set While not required for this application, imagine if you wanted to access individual fields within the data set. You might want to add the ability to calculate the total number of points scored by all players or even the total points scored by a single team. While these answers can be obtained by querying the database again using the SQL statements introduced in Section 11.3, they can also be calculated by looping through the data set and inspecting the individual fields. The syntax for accessing an individual field of a data set is as follows: DataSetName.Tables(TableNumber).Rows(RowNumber)(ColumnNumber) DataSetName: The name of a properly configured data set. TableNumber: While your data sets have only contained one table, a data set can be made from more than one table. To indicate the first table, you will use a 0 for the TableNumber. Subsequent tables would have a higher number. ~ RowNumber: Indicates the number of the row containing the field that you wish to access. Row numbers start with 0. ~ ColumnNumber: Indicates the number of the column containing the field that you wish to access. Column numbers start with 0. The Visual Basic .NET Coach

  41. Chapter 11 – Introduction to Database Concepts PrivateSub btnTotals_Click(… Dim intTotalPoints AsInteger'Stores the total number of points Dim intCurrentRow AsInteger 'Stores the current row of the grid Dim intMaxRow AsInteger 'Stores index of the max row of the grid 'Compute the maximum row of the grid, it’s one less than the number of rows in the grid intMaxRow = Me.BindingContext(dsBasketballPlayers1, "PlayerStats").Count - 1 For intCurrentRow = 0 To intMaxRow 'The points are located in column 4 'The team is located in column 6 If (dsBasketballPlayers1.Tables(0).Rows(intCurrentRow)(6) = txtTeam.Text) Then intTotalPoints += Val(DsBasketballPlayers1.Tables(0).Rows(intCurrentRow)(4)) EndIf Next intCurrentRow 'Output the results MsgBox("The total points for the " & txtTeam.Text & " is " & intTotalPoints.ToString()) EndSub The Visual Basic .NET Coach

  42. Chapter 11 – Introduction to Database Concepts Alternatively, you can also access values with the following syntax: DataSetName.Tables("TableName").Rows(RowNumber).Item("FieldName") DataSetName: The name of a properly configured data set. TableName: While your data sets have only contained one table, a data set can be made from more than one table. To indicate the table you wish to access, place the table name in quotes. RowNumber: Indicates the number of the row containing the field that you wish to access. Row numbers start with 0. Item: Indicates the name of the column containing the field that you wish to access. The Visual Basic .NET Coach

  43. Chapter 11 – Introduction to Database Concepts Drill 11.1 Given the data set displayed in Figure 11.27, what value would be returned given the following statements? DsBasketballPlayers1.Tables(0).Rows(4)(3) DsBasketballPlayers1.Tables(0).Rows(1)(2) DsBasketballPlayers1.Tables(0).Rows(6)(1) DsBasketball.Tables("PlayerStats").Rows(4).Item("Assists") e. DsBasketball.Tables("PlayerStats").Rows(6).Item("Assists") Camby. b. 60. c. There is no row 6 in the data set, so an error occurs. d. 100. e.There are only six rows, so an error occurs. The Visual Basic .NET Coach

  44. Chapter 11 – Introduction to Database Concepts 11.3 SQL SELECT Statement When the database concepts were introduced at the beginning of the chapter, the details of the SQL SELECT statement were skipped over. Visual Basic .NET uses a Structured Query Language for manipulating databases. The first SQL statement is SELECT. It allows the developer to retrieve data from a table or series of tables. The Visual Basic .NET Coach

  45. Chapter 11 – Introduction to Database Concepts Simple Form of the SQL SELECT Statement The syntax of the SELECT statement has many options. The simplest form of the syntax is as follows: SELECT FieldList FROM TableName SELECT: A keyword indicating this will be a SELECT SQL statement to retrieve data from a table. FieldList: If the developer wishes only some of the fields of a table to be selected, the field list should be a list of the field names desired, with each separated by a comma. If all the fields are to be selected, then an asterisk is used in place of the FieldList. FROM: A keyword that indicates the next part of the statement will be the TableName from which to retrieve the data. TableName: The name of the table from which the data will be retrieved. The table must exist in the database that you selected when building your SQL statement. The Visual Basic .NET Coach

  46. Chapter 11 – Introduction to Database Concepts Simple SELECT Statement Examples For the following examples, assume that the table shown has been created. The Visual Basic .NET Coach

  47. Chapter 11 – Introduction to Database Concepts Problem: Retrieve all the student records. SQL: SELECT * FROM StudentGrades Although the SELECT statement could have been written as SELECT FirstName, LastName, MidTerm, FinalExam, HomeworkAverage, FinalGrade FROM StudentGrades, you employ a shortcut. The asterisk in a SELECT statement acts as a wildcard that will select all the fields in the table. The Visual Basic .NET Coach

  48. Chapter 11 – Introduction to Database Concepts Problem: Retrieve only the first and last name of every student. SQL: SELECT FirstName, LastName FROM StudentGrades Since only two fields are listed in the SELECT statement, only two fields are displayed in the results shown. The Visual Basic .NET Coach

  49. Chapter 11 – Introduction to Database Concepts Drill 11.2 Write the SELECT statement that will retrieve the last name and final grade of every student. Answer: To select all the last names and the final grades of all the students, you will need to specify each field you desire in the results. The field name for a student’s last name is LastName. The field name for a student’s final grade is FinalGrade; therefore, the SQL statement required is SELECT LastName, FinalGrade FROM StudentGrades The Visual Basic .NET Coach

  50. Chapter 11 – Introduction to Database Concepts Adding Search Criteria to a SQL SELECT Statement SQL SELECT statements allow the developer to add search conditions to the statement so that only records matching the search conditions are returned when the SELECT statement is executed. The syntax to add a search condition is as follows: SELECT FieldList FROM TableName [WHERE SearchConditions] WHERE: A keyword indicating that a search condition will be specified. SearchConditions: A search condition for a SELECT statement does not vary much from the conditional expressions with which you are already familiar. Conditional expressions within a SELECT statement can be combined using the logical operators And and Or. The Visual Basic .NET Coach

More Related