10.1. An Introduction to DatabasesThe management of databases is the number one use of computers today. Airlines use databases to handle nearly 1.5 billion passenger reservations per year. The 6,500 hospitals in the United States utilize databases to document the care of over 30 million patients per year. Banks in the United States utilize databases to monitor the use of 350 million credit cards. Although databases vary considerably in size and complexity, most of them adhere to the fundamental principles of design discussed in this chapter. That is, they are composed of a collection of interrelated tables. A table is a rectangular array of data. Table 10.1 provides information about large cities. Each column of the table is called a field. (The third column gives the 2005 population in millions and the fourth column gives the projected 2015 population in millions.) The names of the fields are city, country, pop1995, and pop2015. Each row, called a record, contains the same type of information as every other row. Also, the pieces of information in each row are related; they all apply to a specific city. Table 10.2, Countries, has three fields and nine records.
Source: United Nations, Dept. for Economic and Social Information and Policy Analysis. A database (or relational database) is a collection of one or more (usually related) tables that has been created with database-management software. Microsoft Access is one of the best known database-management products. Some other prominent ones are Oracle, SQL Server, and DB2. VB.NET can manage, revise, and analyze a database that has been created with any of these products. VB.NET also has a powerful data viewer that can be used to browse through all portions of a database. The database used in each example is found in the Debug subfolder for the program. The databases needed for the exercises are contained in the folder MajorDatabases located in the folder Programs\Ch10. The database files were created with Microsoft Access and have the extension .MDB. For instance, the file MEGACITIES.MDB is a database file containing the two tables presented on the preceding page. MDB files should be copied onto a hard drive and accessed from the hard drive. If the files originally resided on a CD, they might have the attribute "Read-only" set. If so, you should change this attribute for each file. To change the attribute setting, first use Windows Explorer to locate the file on the hard drive. Then right-click on the file, click Properties, and uncheck the "Read-only" box under Attributes. Database ExplorerVisual Basic Express has a tool called Database Explorer that allows a developer to browse through the contents of a database. The other editions of Visual Basic have a tool called the Server Explorer that can not only browse a database, but also view information on another computer. The following steps refer to Database Explorer, but are applicable to Server Explorer with slight modifications.
Figure 10.2. The Cities table of MEGACITIES.MDB.
The remainder of this section is devoted to creating programs that display information obtained from a database. A DataTable object is the representation of a table (with rows and columns) in ADO.NET. The ActiveX Data Objects .NET (ADO.NET) technology allows programs to access data seamlessly from multiple, varied, and distant servers. Each time we begin a new program, there are two steps you must take in order to gain access to the Data Table object.
Imports System.Data Important Note: We will assume these two steps have been carried out for every program in this chapter. Accessing a Database with a Data TableA DataTable object holds the contents of a table as a rectangular array. (A data table is similar to a two-dimensional array; it has rows and columns.) The following six lines of code create a DataTable variable named "dt" and fill it with the contents of the Cities table from the database MEGACITIES.MDB: Dim dt As New DataTable() Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=MEGACITIES.MDB" Dim sqlStr As String = "SELECT * FROM Cities" Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr) dataAdapter.Fill(dt) dataAdapter.Dispose() Treat these six lines of code as a boiler plate to be inserted into a program. Note: You can save yourself time by storing this code fragment in the Toolbox and dragging it into the Code window when needed. A database resides on a disk, and a data table resides in memory. A data adapter is an intermediary object that serves as a conduit to allow bidirectional data transfers between the two. The first statement in the boiler plate, which declares dt to be a variable of type DataTable, is often placed in the Declaration section of the Code window. The fifth statement of the boiler plate uses the data adapter to fill the data table with data from the database. The sixth statement of the boiler plate releases all resources of the data adapter. The variable connString is called a connection string. A connection string has two parameters. The Provider parameter specifies the driver that is used to communicate with the database. The Data Source parameter gives the filespec of the database. When the database is in the bin/Debug folder of the current program, the filename can be used instead of the filespec. The variable sqlStr is called an SQL string. SQL strings tell the data adapter which table to retrieve. SQL strings are discussed extensively in Section 10.2. The second and third statements of the boiler plate have the following general form: Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & DataBaseName Dim sqlStr As String = "SELECT * FROM " & TableName where the database is assumed to be in the Debug subfolder of the bin subfolder of the program folder. After the six lines of code are executed, the value of dt.Rows.Count is the number of records in the table, and the value of dt.Columns.Count is the number of fields in the table. The records are numbered 0 through dt.Rows.Count - 1, and the fields are numbered 0 through dt.Columns.Count - 1. The value of dt.Columns(j) is the name of the jth field. The value of dt.Rows(i)(j) is the entry in the jth field of the ith record. The value of dt.Rows(i)(fieldName) is the entry in the specified field of the ith record. In Table 10.3, dt holds the Cities table of MEGACITIES.MDB.
Note: The values of dt.Columns(j), dt.Row(i)(j), and dt.Row(i)(fieldName) must be converted to strings with CStr before being placed in a text box or assigned to a String variable. However, they need not be converted when placed in a list box. Example 1. |
The following program displays one record at a time from the Table 10.1 of the MEGACITIES.MDB database. The user can move forward or backward through the records, or specify the city to be displayed.
Dim dt As New DataTable() Dim rowIndex As Integer = 0 Private Sub frmCities_Load(...) Handles MyBase.Load 'Get data from the database, put it into the DataTable object dt, 'and display the initial record's data in text boxes. Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=MEGACITIES.MDB" Dim sqlStr As String = "SELECT * FROM Cities" Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr) dataAdapter.Fill(dt) dataAdapter.Dispose() UpdateTextBoxes() End Sub Private Sub btnFind_Click(...) Handles btnFind.Click 'Search through each row looking for the requested city. 'Update the fields if that city is found. 'Otherwise display a message box. Dim cityName As String Dim cityFound As Boolean = False cityName = InputBox("Enter the name of the city to search for.") For i As Integer = 0 To (dt.Rows.Count - 1) If CStr(dt.Rows(i)("city")) = cityName Then cityFound = True rowIndex = i UpdateTextBoxes() End If [Run, and then click the Next button seven times.]
|
The following program displays the contents of the Table 10.1 of MEGACITIES.MDB in a list box, along with the percentage increases for the populations:
Private Sub btnShow_Click(...) Handles btnShow.Click Dim fmtStr As String = "{0,-15}{1,-10}{2,7:N1}{3,7:N1}{4,7:P0}" Dim percentIncrease As Double 'Place contents of Cities table into a DataTable. Dim dt As New DataTable() Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=MEGACITIES.MDB" Dim sqlStr As String = "SELECT * FROM Cities" Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr) dataAdapter.Fill(dt) dataAdapter.Dispose() 'Fill the list box. lstDisplay.Items.Add(String.Format(fmtStr, "CITY", "COUNTRY", _ "2005", "2015", "INCR.")) For i As Integer = 0 To dt.Rows.Count - 1 percentIncrease = (CDbl(dt.Rows(i)("pop2015")) - _ CDbl(dt.Rows(i)("pop2005"))) / CDbl(dt.Rows(i)("pop2005")) lstDisplay.Items.Add(String.Format(fmtStr, dt.Rows(i)(0), _ dt.Rows(i)(1), dt.Rows(i)(2), dt.Rows(i)(3), percentIncrease)) Next End Sub [Run, and click the button.]
|
In Example 2, we placed information from a data table into a list box one line at a time. However, a data table can be connected directly to a list box and have information transferred automatically into the list box. In such a situation, the list box is said to be bound to the data table. The statement
lstBox.DataSource = dt
binds the list box to the data table, and the statement
lstBox.DisplayMember = "country"
displays the contents of the specified field in the list box.
The following program uses a list box to display a column of data named "country" from a data table. When a particular country is selected, the event procedure retrieves the country's monetary unit from the corresponding record and displays it in a text box.
Dim dt As New DataTable() Private Sub frmCountries_Load(...) Handles MyBase.Load Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=MEGACITIES.MDB" Dim sqlStr As String = "SELECT * FROM Countries" [Run, and click on one of the countries.]
|
Determine the output displayed when the button is clicked.
Q1: |
Private Sub btnDisplay_Click(...) Handles btnDisplay.Click Dim dt As New DataTable() Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=MEGACITIES.MDB" Dim sqlStr As String = "SELECT * FROM Countries" Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr) dataAdapter.Fill(dt) dataAdapter.Dispose() lstOutput.Items.Add(dt.Columns(2)) lstOutput.Items.Add(dt.Rows(5)(0)) lstOutput.Items.Add(dt.Rows(8)(dt.Columns.Count - 2)) lstOutput.Items.Add(dt.Rows(0)("monetaryUnit")) lstOutput.Items.Add(dt.Rows(dt.Rows.Count - 1)(2)) End Sub |
Exercises 1 through 16 refer to the database MEGACITIES.MDB. In Exercises 1 through 4, determine the output displayed when the button is clicked.
1. | Private Sub btnDisplay_Click(...) Handles btnDisplay.Click Dim dt As New DataTable() Dim sum As Double Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=MEGACITIES.MDB" Dim sqlStr As String = "SELECT * FROM Cities" Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr) dataAdapter.Fill(dt) dataAdapter.Dispose() sum = 0 For i As Integer = 0 To dt.Rows.Count - 1 sum += CDbl(dt.Rows(i)("pop2015")) Next txtOutput.Text = sum & " million" End Sub |
2. | Private Sub btnDisplay_Click(...) Handles btnDisplay.Click Dim dt As New DataTable() Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=MEGACITIES.MDB" Dim sqlStr As String = "SELECT * FROM Cities" Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr) dataAdapter.Fill(dt) dataAdapter.Dispose() For i As Integer = 0 To dt.Rows.Count - 1 If CDbl(dt.Rows(i)("pop2015")) > 22 Then lstOutput.Items.Add(dt.Rows(i)(0)) End If Next End Sub |
3. | Private Sub btnDisplay_Click(...) Handles btnDisplay.Click Dim dt As New DataTable() Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=MEGACITIES.MDB" Dim sqlStr As String = "SELECT * FROM Countries" Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr) dataAdapter.Fill(dt) dataAdapter.Dispose() For i As Integer = 0 To dt.Rows.Count - 1 If CStr(dt.Rows(i)("country")).Length = 5 Then lstOutput.Items.Add(dt.Rows(i)(0)) End If Next End Sub |
4. | Private Sub btnDisplay_Click(...) Handles btnDisplay.Click Dim dt As New DataTable() Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=MEGACITIES.MDB" Dim sqlStr As String = "SELECT * FROM Countries" Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr) dataAdapter.Fill(dt) dataAdapter.Dispose() For j As Integer = 0 To dt.Columns.Count - 1 lstOutput.Items.Add(dt.Columns(j)) Next End Sub |
5. | Write a program to place in a list box the names of the countries in the Countries table in the order that they appear in the table. |
6. | Write a program to place in a list box the names of the countries in the Countries table in the reverse order that they appear in the table. |
7. | Write a program to place in a list box the names of the cities in the Cities table whose populations are projected to exceed 20 million in the year 2015. |
8. | Write a program to place in a list box the names of the cities in the Cities table whose 2005 populations are between 12 and 16 million. |
9. | Write a program to place in a list box the names of the countries in the Countries table, where each name is followed by a hyphen and the name of its monetary unit. |
10. | Write a program to find and display the city in the Cities table that will experience the greatest percentage growth from 2005 to 2015. Note: The percentage growth is (pop2015 pop2005) / pop2005. |
11. | Write a program to place the countries from the Countries table in a list box in descending order of their 2005 populations. Hint: Place the countries and their 2005 populations in an array of structures, and sort the array in descending order based on the populations. |
12. | Write a program to place the cities from the Cities table in a list box in descending order of their percentage population growth from 2005 to 2015. |
13. | Write a program to display the name and currency of each city in the table Cities. |
14. | Write a program to display in a list box the names of each country in Table 10.1 followed by the cities in that country that are listed in the Cities table. |
15. | Write a program to back up the contents of the Cities table in one sequential file and the Countries table in another sequential file. Run the program, and compare the sizes of these two sequential files with the size of the file MEGACITIES.MDB. |
16. | Write a program that allows the user to specify a city and then displays the percentage of its country's 2005 population that lives in that city. Hint: Use two DataTables. |
Exercises 17 through 20 refer to the BIBLIO.MDB database from the folder Programs\Ch10\MajorDatabases.
17. | How many tables are in the database? What are their names? |
18. | Give the names of the fields in the table Publishers. |
19. | How many records are in the table Publishers? |
20. | Write a program that requests the name of a publisher (such as Prentice Hall or Microsoft Press) and gives the publisher's address. |
21. | The folder Programs\Ch10\MajorDatabases contains the database STATE_ABBR .MDB consisting of one table, States, having two fields, abbreviation and state. Each record consists of a two-letter abbreviation and the name of a state. Some records are (AZ, Arizona) and (MD, Maryland). Write a program that allows the user to enter a two-letter abbreviation and obtain the name of the state. Of course, if the two-letter abbreviation does not correspond to any state, the user should be so informed. |
The folder Programs\Ch10\MajorDatabases contains the database BASEBALL.MDB that has the two tables Players and Teams. The fields for the Players table are player, team, atBats, and hits. The fields for the Teams table are team, location, league, stadium, atBats, and hits. The database has been filled with information from the 2004 baseball season for the major leagues. Three sample records from each table are as follows.
Players |
---|
Barry Bonds, Giants, 373, 135 |
Ichiro Suzuki, Mariners, 704, 262 |
Manny Ramirez, Red Sox, 568, 175 |
Teams |
---|
Giants, San Francisco, National, SBC Park, 5546, 1500 |
Diamondbacks, Arizona, National, Bank One Ballpark, 5544, 1401 |
Red Sox, Boston, American, Fenway Park, 5720, 1613 |
The database BASEBALL.MDB should be used in Exercises 22 through 26.
22. | Write a program to determine the player in the Players table with the most hits. In the case of a tie, the program should list all players having the most hits. |
23. | Write a program to determine the player (or players) in the Players table with the highest batting average. |
24. | Write a program that displays all the teams in the Teams table in a list box. When the user clicks on one of the teams, the program should display the team's home stadium in a text box. |
25. | Write a program that displays all the teams in the Teams table in a list box. When the user clicks on one of the teams, the program should display the names of all the players in the Players table from that team. Hint: Use two DataTables. |
26. | Write a program to count the number of players in the Players table who play for a National League team. Hint: Use two DataTables. |
A1: |
monetaryUnit Japan 143.2 taka dollar |