20.6. Programming with ADO.NET: Extracting Information from a DatabaseIn this section, we demonstrate how to connect to a database, query the database and display the result of the query. You will notice that there is little code in this section. The IDE provides visual programming tools and wizards that simplify accessing data in your projects. These tools establish database connections and create the ADO.NET objects necessary to view and manipulate the data through GUI controls. The example in this section connects to the SQL Server Books database that we have discussed throughout this chapter. The Books.mdf file that contains the database can be found with the chapter's examples (www.deitel.com/books/vbforprogrammers2). 20.6.1. Displaying a Database Table in a DataGridViewThis example performs a simple query on the Books database that retrieves the entire Authors table and displays the data in a DataGridView (a control from namespace System.Windows.Forms that can display a data source in a GUIsee the sample outputs in Fig. 20.32 later in this section). First, we demonstrate how to connect to the Books database and include it as a data source in your project. Once the Books database is established as a data source, you can display the data from the Authors table in a DataGridView simply by dragging and dropping items in the project's Design view.
Displaying the Authors TableNow that you have added the Books database as a data source, you can display the data from the database's Authors table in your program. The IDE provides design tools that allow you to display data from a data source on a Form without writing any code. Simply drag and drop items from the Data Sources window onto a Form, and the IDE generates the GUI controls and code necessary to display the selected data source's content. To display the Authors table of the Books database, drag the Authors node from the Data Sources window to the Form. Figure 20.31 presents the Design view after we performed this action and resized the Form and controls. The IDE generates two controls that appear on FrmDisplayTablean AuthorsBindingNavigator and an AuthorsDataGridView. The IDE also generates several additional non-visual components that appear in the component traythe gray region below the Form in Design view. We use the IDE's default names for these autogenerated components (and others throughout the chapter) to show exactly what the IDE creates. We briefly discuss the AuthorsBindingNavigator and AuthorsDataGridView controls here. The next section discusses all of the autogenerated components in detail and explains how the IDE uses these components to connect the GUI controls to the Authors table of the Books database. Figure 20.31. Design view after dragging the Authors data source node to the Form.A DataGridView displays data organized in rows and columns that correspond to the rows and columns of the underlying data source. In this case, the DataGridView displays the data of the Authors table, so the control has columns named AuthorID, FirstName and LastName. In Design view, the control does not display any rows of actual data below the column headers. The data is retrieved from the database and displayed in the DataGridView only at runtime. Execute the program. When the Form loads, the DataGridView contains four rows of dataone for each row of the Authors table (Fig. 20.32). Figure 20.32. Displaying the Authors table in a DataGridView.(a)
(b)
The strip of buttons below the title bar of the window is a BindingNavigator, which enables users to browse and manipulate data displayed by another GUI control (in this case, a DataGridView) on the Form. A BindingNavigator's buttons resemble the controls on a CD or DVD player and allow you to move to the first row of data, the preceding row, the next row and the last row. The control also displays the currently selected row number in a text box. You can use this text box to enter the number of a row that you want to select. The AuthorsBindingNavigator in this example allows you to "navigate" the Authors table displayed in the AuthorsDataGridView. Clicking the buttons or entering a value in the text box causes the DataGridView to select the appropriate row. An arrow in the DataGridView's leftmost column indicates the currently selected row. A BindingNavigator also has buttons that allow you to add a new row, delete a row and save changes back to the underlying data source (that is, the Authors table of the Books database). Clicking the button with the yellow plus icon () adds a new row to the DataGridView. However, simply typing values in the FirstName and LastName columns does not insert a new row in the Authors table. To add the new row to the database on disk, click the Save button (the button with the disk icon, . Clicking the button with the red X deletes the currently selected row from the DataGridView. Again, you must click the Save button to make the change in the database. Changing the Database's Copy to Output Directory PropertyBy default, a new copy of the database file is used each time you run the application. Thus, any changes you make to the database when testing your application are lost. To allow changes to be made to the database file during testing, you must select Books.mdf in the Solution Explorer, then change its Copy to Output Directory property to Copy if newer in the Properties window. Now you can test BindingNavigator's buttons for adding and deleting records and see their results. Execute the program and add a new row, then save the changes and close the program. When you restart the program, you should see that the new row was saved to the database and appears in the DataGridView. Now delete the new row and click the Save button. Close and restart the program to see that the new row no longer exists in the database. 20.6.2. How Data Binding WorksThe technique through which GUI controls are connected to data sources is known as data binding. The IDE allows controls, such as a DataGridView, to be bound to a data source, such as a DataSet that represents a table in a database. Any changes you make through the application to the underlying data source will automatically be reflected in the way the data is presented in the data-bound control (e.g., the DataGridView ). Likewise, modifying the data in the data-bound control and saving the changes updates the underlying data source. In the current example, the DataGridView is bound to the DataTable of the BooksDataSet that represents the Authors table in the database. Dragging the Authors node from the Data Sources window to the Form caused the IDE to create this data binding for you, using several autogenerated components (i.e., objects) in the component tray. Figure 20.33 models these objects and their associations, which the following sections examine in detail to explain how data binding works. Figure 20.33. Data binding architecture used to display the Authors table of the Books database in a GUI.BooksDataSetAs discussed in Section 20.6.1, adding the Books database to the project enabled the IDE to generate the BooksDataSet. Recall that a DataSet represents a cache of data that mimics the structure of a relational database. You can explore the structure of the BooksDataSet in the Data Sources window. A DataSet's structure can be determined at execution time or at design time. An untyped DataSet's structure (i.e., the tables that comprise it and the relationships among them) is determined at execution time based on the result of a specific query. Tables and column values are accessed using indices into collections of DataTables and DataRows, respectively. The type of each piece of data in an untyped DataSet is unknown at design time. BooksDataSet, however, is created by the IDE at design time as a strongly typed DataSet. BooksDataSet (a derived class of DataSet) contains objects of classes derived from DataTable that represent the tables in the Books database. BooksDataSet provides properties corresponding to these objects. The property names match the names of the underlying tables. For example, BooksDataSet.Authors represents a cache of the data in the Authors table. Each DataTable contains a collection of DataRows. Each DataRow contains members whose names and types correspond to those of the columns of the underlying database table. Thus, BooksDataSet.Authors(0).AuthorID refers to the AuthorID of the first row of the Authors table in the Books database. Note that zero-based indices are used to access DataRows in a DataTable. The BooksDataSet object in the component tray is an object of the BooksDataSet class. When you indicate that you want to display the contents of the Authors table on the Form, the IDE generates a BooksDataSet object to store the data that Form will display. This is the data to which the DataGridView will be bound. The DataGridView does not display data from the database directly. Instead, it displays the contents of a BooksDataSet object. As we discuss shortly, the AuthorsTableAdapter fills the BooksDataSet object with data retrieved from the database by executing a SQL query. AuthorsTableAdapterThe AuthorsTableAdapter is the component that interacts with the Books database on disk (i.e., the Books.mdf file). When other components need to retrieve data from the database or write data to the database, they invoke the methods of the AuthorsTableAdapter. Class AuthorsTableAdapter is generated by the IDE when you drag a table from the Books database onto the Form. The AuthorsTableAdapter object in the component tray is an object of this class. This object is responsible for filling the BooksDataSet with the Authors data from the databasethis stores a copy of the Authors table in local memory. As you will soon see, the cached copy can be modified during program execution. The AuthorsTableAdapter is also responsible for updating the database when the data in the BooksDataSet changes. Class AuthorsTableAdapter encapsulates a SqlDataAdapter object, which contains SqlCommand objects that specify how the SqlDataAdapter selects, inserts, updates and deletes data in the database. Recall from Section 20.5 that a SqlCommand object must have a SqlConnection object through which the SqlCommand can communicate with a database. In this example, the AuthorsTableAdapter sets the Connection property of each of the SqlDataAdapter's SqlCommand objects, based on the connection string that refers to the Books database. To interact with the database, the AuthorsTableAdapter invokes the methods of its SqlDataAdapter, each of which executes the appropriate SqlCommand object. For example, to fill the BooksDataSet's Authors table, the AuthorsTableAdapter's Fill method invokes its SqlDataAdapter's Fill method, which executes a SqlCommand object representing the SELECT query SELECT AuthorID, FirstName, LastName FROM Authors This query selects all the rows and columns of the Authors table and places them in BooksDataSet.Authors. You will see an example of AuthorsTableAdapter's Fill method being invoked shortly. AuthorsBindingSource and AuthorsDataGridViewThe AuthorsBindingSource object (an object of class BindingSource) identifies a data source that a program can bind to a control and serves as an intermediary between a data-bound GUI control and its data source. In this example, the IDE uses a BindingSource object to connect the AuthorsDataGridView to BooksDataSet.Authors. To achieve this data binding, the IDE first sets AuthorsBindingSource's DataSource property to BooksDataSet. This property specifies the DataSet that contains the data to be bound. The IDE then sets the DataMember property to Authors. This property identifies a specific table within the DataSource. After configuring the AuthorsBindingSource object, the IDE assigns this object to AuthorsDataGridView's DataSource property to indicate what the DataGridView will display. A BindingSource object also manages the interaction between a data-bound GUI control and its underlying data source. If you edit the data displayed in a DataGridView and want to save changes to the data source, your code must invoke the EndEdit method of the BindingSource object. This method applies the user's changes to the data (i.e., the pending changes) to the data source bound to that control. Note that this updates only the DataSet an additional step is required to permanently update the database itself. You will see an example of this shortly, when we present the code generated by the IDE in the FrmDisplayTable.vb file. AuthorsBindingNavigatorRecall that a BindingNavigator allows you to move through (i.e., navigate) and manipulate (i.e., add or delete rows) data bound to a control on a Form. A BindingNavigator communicates with a BindingSource (specified in the BindingNavigator's BindingSource property) to carry out these actions in the underlying data source (i.e., the DataSet). The BindingNavigator does not interact with the data-bound control. Instead, it invokes BindingSource methods that cause the data-bound control to update its presentation of the data. For example, when you click the BindingNavigator's button to add a new row, the BindingNavigator invokes a method of the BindingSource. The BindingSource then adds a new row to its associated DataSet. Once this DataSet is modified, the DataGridView displays the new row, because the DataGridView and the BindingNavigator are bound to the same BindingSource object (and thus the same DataSet). Examining the Autogenerated Code for FrmDisplayTableFigure 20.34 presents the code for FrmDisplayTable. You do not need to write any of this codethe IDE generates it when you drag and drop the Authors table from the Data Sources window onto the Form. We modified the autogenerated code to add comments and split long lines for display purposes. The IDE also generates a considerable amount of additional code, such as the code that defines classes BooksDataSet and AuthorsTableAdapter, as well as the designer code that declares the autogenerated objects in the component tray. The additional IDE-generated code resides in files visible in the Solution Explorer when you select Show All Files. We present only the code in FrmDisplayTable.vb, because it is the only file you'll need to modify. Figure 20.34. Auto-generated code for displaying data from a database table in a DataGridView control.
Lines 613 contain the Click event handler for the Save button in the AuthorsBindingNavigator. Recall that you click this button to save changes made to the data in the DataGridView in the underlying data source (i.e., the Authors table of the Books database). Saving the changes is a two-step process:
Before the event handler saves any changes, line 10 invokes Me.Validate() to validate the controls on the Form. If you implement Validating or Validated events for any of Form's controls, these events enable you to validate user input and potentially indicate errors for invalid data. Line 11 invokes AuthorsBindingSource's EndEdit method to ensure that the object's associated data source (BooksDataSet.Authors) is updated with any changes made by the user to the currently selected row in the DataGridView (e.g., adding a row, changing a column value). Any changes to other rows were applied to the DataSet when you selected another row. Line 12 invokes AuthorsTableAdapter's Update method to write the modified version of the Authors table (in memory) to the SQL Server database on disk. The Update method executes the SQL statements (encapsulated in SqlCommand objects) necessary to make the data in the database's Authors table match the data in BooksDataSet.Authors. The FrmDisplayTable_Load event handler (lines 1722) executes when the program loads. This event handler fills the in-memory DataSet with data from the SQL Server database on disk. Once the DataSet is filled, the GUI control bound to it can display its data. Line 21 calls AuthorsTableAdapter's Fill method to retrieve information from the database, placing this information in the DataSet object provided as an argument. Recall that the IDE generated AuthorsTableAdapter to execute SqlCommands over the connection we created within the Data Source Configuration Wizard. Thus, the Fill method executes a SELECT statement to retrieve all the rows of the Authors table of the Books database, then places the result of this query in BooksDataSet.Authors. Recall that AuthorsDataGridView's DataSource property is set to AuthorsBindingSource (which references BooksDataSet.Authors). Thus, after this data source is loaded, the AuthorsDataGridView automatically displays the data retrieved from the database. |