22.6 Dynamically Binding Query Results
Next we show how to perform several different queries and display the results in a DataGridView. This app only reads data from the entity data model, so we disabled the buttons in the BindingNavigator that enable the user to add and delete records—simply select each button and set its Enabled property to False in the Properties window. You also could delete these buttons from the BindingNavigator. Later, we’ll explain why we do not support modifying the database in this example.
The Display Query Results app (Figs. 22.23–22.25) allows the user to select a query from the ComboBox at the bottom of the window, then displays the results of the query.
Fig. 22.23 Results of the Display Query Results app’s All titles query, which shows the contents of the Titles table ordered by the book titles.
Fig. 22.24 Results of the Display Query Results app’s Titles with 2016 copyright query.
Fig. 22.25 Results of the Display Query Results app’s Titles ending with "How to Program" query.
22.6.1 Creating the Display Query Results GUI
Perform the following steps to build the Display Query Results app’s GUI.
Step 1: Creating the Project
Perform the steps in Section 22.5.2 to create a new Windows Forms Application project named DisplayQueryResult in the same solution as the DisplayTable app. Rename the Form1.cs source file to TitleQueries.cs. Set the Form’s Text property to Display Query Results. Be sure to add references to the BooksExamples and EntityFramework libraries, add the connection string to the project’s App.Config file and set the DisplayQuery-Result project as the startup project.
Step 2: Creating a DataGridView to Display the Titles Table
Follow Steps 1 and 2 in Section 22.5.3 to create the data source and the DataGridView. For this example, select the Title class (rather than Author) as the data source, and drag the Title node from the Data Sources window onto the form. Remove the Authors column from the DataGridView, as it will not be used in this example.
Step 3: Adding a ComboBox to the Form
In Design view, add a ComboBox named queriesComboBox below the DataGridView on the Form. Users will select which query to execute from this control. Set the ComboBox’s Dock property to Bottom and the DataGridView’s Dock property to Fill.
Next, you’ll add the names of the queries to the ComboBox. Open the ComboBox’s String Collection Editor by right clicking the ComboBox and selecting Edit Items…. You also can access the String Collection Editor from the ComboBox’s smart tag menu. A smart tag menu provides you with quick access to common properties you might set for a control (such as the Multiline property of a TextBox), so you can set these properties directly in Design view, rather than in the Properties window. You can open a control’s smart tag menu by clicking the small arrowhead
that appears in the control’s upper-right corner in Design view when the control is selected. In the String Collection Editor, add the following three items to queriesComboBox— one for each of the queries we’ll create:
All titlesTitles with 2016 copyrightTitles ending with “How to Program”
22.6.2 Coding the Display Query Results App
Next you’ll create the code for this app (Fig. 22.26).
Fig. 22.26 Displaying the result of a user-selected query in a DataGridView.
Alternate View
1 // Fig. 22.26: TitleQueries.cs
2 // Displaying the result of a user-selected query in a DataGridView.
3 using System;
4 using System.Data.Entity;
5 using System.Linq;
6 using System.Windows.Forms;
7
8 namespace DisplayQueryResult
9 {
10 public partial class TitleQueries : Form
11 {
12 public TitleQueries()
13 {
14 InitializeComponent();
15 }
16
17 // Entity Framework DbContext
18 private BooksExamples.BooksEntities dbcontext =
19 new BooksExamples.BooksEntities();
20
21 // load data from database into DataGridView
22 private void TitleQueries_Load(object sender, EventArgs e)
23 {
24 dbcontext.Titles.Load(); // load Titles table into memory
25
26 // set the ComboBox to show the default query that
27 // selects all books from the Titles table
28 queriesComboBox.SelectedIndex = 0;
29 }
30
31 // loads data into titleBindingSource based on user-selected query
32 private void queriesComboBox_SelectedIndexChanged(
33 object sender, EventArgs e)
34 {
35 // set the data displayed according to what is selected
36 switch (queriesComboBox.SelectedIndex)
37 {
38 case 0: // all titles
39 // use LINQ to order the books by title
40 titleBindingSource.DataSource =
41 dbcontext.Titles.Local.OrderBy(book => book.Title1);
42 break;
43 case 1: // titles with 2016 copyright
44 // use LINQ to get titles with 2016
45 // copyright and sort them by title
46 titleBindingSource.DataSource =
47 dbcontext.Titles.Local
48 .Where(book => book.Copyright == "2016")
49 .OrderBy(book => book.Title1);
50 break;
51 case 2: // titles ending with "How to Program"
52 // use LINQ to get titles ending with
53 // "How to Program" and sort them by title
54 titleBindingSource.DataSource =
55 dbcontext.Titles.Local
56 .Where(
57 book => book.Title1.EndsWith("How to Program"))
58 .OrderBy(book => book.Title1);
59 break;
60 }
61
62 titleBindingSource.MoveFirst(); // move to first entry
63 }
64 }
65 }
Customizing the Form’s Load Event Handler
Create the TitleQueries_Load event handler (lines 22–29) by double clicking the title bar in Design view. When the Form loads, it should display the complete list of books from the Titles table, sorted by title. Line 24 calls the Load extension method on the BookEntities DbContext’s Titles property to load the Titles table’s contents into memory. Rather than defining the same LINQ query as in lines 40–41, we can programmatically cause the queriesComboBox_SelectedIndexChanged event handler to execute simply by setting the queriesComboBox’s SelectedIndex to 0 (line 28).
queriesComboBox_SelectedIndexChanged Event Handler
Next you must write code that executes the appropriate query each time the user chooses a different item from queriesComboBox. Double click queriesComboBox in Design view to generate a queriesComboBox_SelectedIndexChanged event handler (lines 32–63) in the TitleQueries.cs file. In the event handler, add a switch statement (lines 36–60). Each case in the switch will change the titleBindingSource’s DataSource property to the results of a query that returns the correct set of data. The data bindings created by the IDE automatically update the titleDataGridView each time we change its DataSource. The BindingSource maintains a Position property that represents the current item in the data source. The MoveFirst method of the BindingSource (line 62) sets the Position property to 0 to move to the first row of the result each time a query executes. The results of the queries in lines 40–41, 46–49 and 54–58 are shown in Figs. 22.23–22.25, respectively. Because we do not modify the data in this app, each of the queries is performed on the in-memory representation of the Titles table, which is accessible through dbcontext.Titles.Local.
Ordering the Books By Title
Lines 40–41 invoke the OrderBy extension method on dbcontext.Titles.Local to order the Title objects by their Title1 property values. As we mentioned previously, the IDE renamed the Title column of the database’s Titles table as Title1 in the generated Title entity data model class to avoid a naming conflict with the class’s name. Recall that Local returns an ObservableCollection<T> containing the row objects of the specified table—in this case, Local returns an ObservableCollection<Title>. When you invoke OrderBy on an ObservableCollection<T>, the method returns an IOrderedEnumerable<T>. We assign that object to the titleBindingSource’s DataSource property. When the DataSource property changes, the DataGridView iterates through the contents of the IEnumerable<T> and displays the data.
Selecting Books with 2016 Copyright
Lines 46–49 filter the titles displayed by using the Where extension method with the lambda expression
book => book.Copyright == “2016”
as an argument. The Where extension method expects as its parameter a Func delegate representing a method that receives one parameter and returns a bool indicating whether the method’s argument matches the specified criteria. The lambda expression used here takes one Title object (named book) as its parameter and uses it to check whether the given Title’s Copyright property (a string in the database) is equal to 2014. A lambda expression that’s used with the Where extension method must return a bool value. Only Title objects for which this lambda expression returns true will be selected. We use OrderBy to order the results by the Title1 property so the books are displayed in ascending order by title. The type of the lambda’s book parameter is inferred from dbcontext.Titles.Local, which contains Title objects. As soon as the titleBindingSource’s DataSource property changes, the DataGridView is updated with the query results.
Selecting Books with Titles That End in “How to Program”
Lines 54–58 filter the titles displayed by using the Where extension method with the lambda expression
book => book.Title1.EndsWith(“How to Program”)
as an argument. This lambda expression takes one Title object (named book) as its parameter and uses it to check whether the given Title’s Title1 property value ends with “How to Program”. The expression books.Title1 returns the string stored in that property, then we use the string class’s EndsWith method to perform the test. We order the results by the Title1 property so the books are displayed in ascending order by title.