Programming with ADO.NET: Extracting Information from a Database

Programming with ADO NET Extracting Information from a Database

In 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/csharpforprogrammers2).

20.6.1. Displaying a Database Table in a DataGridView

This 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 output 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.

Step 1: Creating the Project

Create a new Windows Application named DisplayTable. Change the Form name to DisplayTableForm and change the source file name to DisplayTable.cs. Then set the Form's Text property to Display Table.

Step 2: Adding a Data Source to the Project

To interact with a data source (e.g., a database), you must add it to the project using the Data Sources window, which lists the data that your project can access. Open the Data Sources window (Fig. 20.23) by selecting Data > Show Data Sources or by clicking the tab to the right of the tab for the Solution Explorer. In the Data Sources window, click Add New Data Source... to open the Data Source Configuration Wizard (Fig. 20.24). This wizard guides you through connecting to a database and choosing the parts of the database you will want to access in your project.

Figure 20.23. Adding a data source to a project.

Figure 20.24. Choosing the data source type in the Data Source Configuration Wizard.

Step 3: Choosing the Data Source Type to Add to the Project

The first screen of the Data Source Configuration Wizard (Fig. 20.24) asks you to choose the data source type you wish to include in the project. Select Database and click Next >.

Step 4: Adding a New Database Connection

You must next choose the connection that will be used to connect to the database (i.e., the actual source of the data). Click New Connection... to open the Add Connection dialog (Fig. 20.25). If the Data Source is not set to Microsoft SQL Server Database File (SqlClient), click Change..., select Microsoft SQL Server Database File and click OK. In the Add Connection dialog, click Browse..., locate the Books.mdf database file on your computer, select it and click Open. You can click Test Connection to verify that the IDE can connect to the database through SQL Server. Click OK to create the connection.

Figure 20.25. Adding a new data connection.

Step 5: Choosing the Books.mdf Data Connection

Now that you have created a connection to the Books.mdf database, you can select and use this connection to access the database. Click Next > to set the connection, then click Yes when asked whether you want to move the database file to your project (Fig. 20.26).

Figure 20.26. Choosing the Books.mdf data connection.

(This item is displayed on page 1013 in the print version)

Step 6: Saving the Connection String

The next screen (Fig. 20.27) asks you whether you want to save the connection string to the application configuration file. A connection string specifies the path to a database file on disk, as well as some additional settings that determine how to access the database. Saving the connection string in a configuration file makes it easy to change the connection settings at a later time. Leave the default selections and click Next > to proceed.

Figure 20.27. Saving the connection string to the application configuration file.

Step 7: Selecting the Database Objects to Include in Your DataSet

The IDE retrieves information about the database you selected and prompts you to select the database objects (i.e., the parts of the database) that you want your project to be able to access (Fig. 20.28). Recall that programs typically access a database's contents through a cache of the data, which is stored in a DataSet. In response to your selections in this screen, the IDE will generate a class derived from System.Data.DataSet that is designed specifically to store data from the Books database. Click the checkbox to the left of Tables to indicate that the custom DataSet should cache (i.e., locally store) the data from all the tables in the Books databaseAuthors, AuthorISBN and Titles. [Note: You can also expand the Tables node to select specific tables. The other database objects listed do not contain any data in our sample Books database and are beyond the scope of the book.] By default, the IDE names the DataSet BooksDataSet, though it is possible to specify a different name in this screen. Finally, click Finish to complete the process of adding a data source to the project.

Figure 20.28. Choosing the database objects to include in the DataSet.

Step 8: Viewing the Data Source in the Data Sources Window

Notice that a BooksDataSet node now appears in the Data Sources window (Fig. 20.29) with child nodes for each table in the Books databasethese nodes represent the DataTables of the BooksDataSet. Expand the Authors node and you will see the table's columnsthe DataSet's structure mimics that of the actual Books database.

Figure 20.29. Viewing a data source listed in the Data Sources window.

(This item is displayed on page 1015 in the print version)

Step 9: Viewing the Database in the Solution Explorer

Books.mdf is now listed as a node in the Solution Explorer (Fig. 20.30), indicating that the database is now part of this project. In addition, the Solution Explorer now lists a new node named BooksDataSet.xsd. You learned in Chapter 19 that a file with the .xsd extension is an XML Schema document, which specifies the structure of a set of XML documents. The IDE uses an XML Schema document to represent a DataSet's structure, including the tables that comprise the DataSet and the relationships among them. When you added the Books database as a data source, the IDE created the BooksDataSet.xsd file based on the structure of the Books database. The IDE then generated class BooksDataSet from the schema (i.e., structure) described by the .xsd file.

Figure 20.30. Viewing a database listed in the Solution Explorer.

Displaying the Authors Table

Now 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 controls. The IDE generates two GUI controls that appear on DisplayTableFormauthorsBindingNavigator and 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 (in this case, 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. Test these buttons. 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 Works

The 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.

BooksDataSet

As 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 the objects whose names match those 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.

AuthorsTableAdapter

The 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. The AuthorsTableAdapter 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, this cached copy can be modified during program execution. Thus, 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 authorsDataGridView

The 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 databound 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 changes made to the data through the GUI control (i.e., the pending changes) to the data source bound to that control. Note that this updates only the DataSetan 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 DisplayTable.cs file.

authorsBindingNavigator

Recall 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 DisplayTableForm

Figure 20.34 presents the code for DisplayTableForm. Note that 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, split long lines for display purposes and remove unnecessary using declarations. 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 DisplayTable.cs, 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.

(This item is displayed on page 1021 in the print version)

Lines 1723 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 21 invokes this.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 21 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 22 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 database's Authors table match booksDataSet.Authors.

The Load event handler for DisplayTableForm (lines 2733) 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 32 calls authorsTableAdapter's Fill method to retrieve information from the database, placing this information in the DataSet member provided as an argument. Recall that authorsTableAdapter was generated by the IDE to execute SqlCommands over the connection we created within the Data Source Configuration Wizard. Thus, the Fill method here 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.