- 21.1 Introduction
- 21.2 Relational Databases
- 21.3 Relational Database Overview: Books Database
- 21.4 SQL
- 21.5 LINQ to SQL
- 21.6 LINQ to SQL: Extracting Information from a Database
- 21.7 More Complex LINQ Queries and Data Binding
- 21.8 Retrieving Data from Multiple Tables with LINQ
- 21.9 Creating a Master/Detail View Application
- 21.10 Programming with LINQ to SQL: Address-Book Case Study
- 21.11 Wrap-Up
- 21.12 Tools and Web Resources
This chapter is from the book
This chapter is from the book
This chapter is from the book
21.7 More Complex LINQ Queries and Data Binding
Now that you’ve seen how to display an entire database table in a DataGridView, we demonstrate how to execute more advanced queries against the database and display the results. Perform the following steps to build the example application, which executes custom queries against the Titles table of the Books database.
Step 1: Creating the Project
Create a new Windows Forms Application named DisplayQueryResult. Rename its C# file to DisplayQueryResultForm.cs. Rename the Form class to DisplayQueryResultForm when the IDE prompts you. Set the Form’s Text property to Display Query Result.
Step 2: Creating the LINQ to SQL Classes
Follow the steps in Section 21.6.1 to add the Books database to the project and generate the LINQ to SQL classes.
Step 3: Creating a DataGridView to Display the Titles Table
Follow Steps 1 and 2 in Section 21.6.2 to create the data source and the DataGridView. In this example, select the Title class (rather than the Author class) as the data source, and drag the Title node from the Data Sources window onto the form.
Step 4: Adding a ComboBox to the Form
Leave the Form’s Design view open and add a ComboBox named queriesComboBox below the DataGridView on the Form. Set the ComboBox’s Dock property to Bottom to make it fill the bottom part of the Form, and set its DropDownStyle property to DropDownList—this prevents the user from being able to type in the control. From this control users select options representing the queries to execute. Open the String Collection Editor by selecting Edit Items from the right-click or smart-tag menu of queriesComboBox. You can open the smart-tag menu by clicking the small arrowhead that appears in the upper-right corner of the control in Design view. The Visual C# IDE displays smart-tag menus for many GUI controls to facilitate common tasks. Add the following three items to queriesComboBox—one for each of the queries we’ll create:
- All titles
- Titles with 2008 copyright
- Titles ending with "How to Program"
Step 5: Programming an Event Handler for the ComboBox
Next you must write code that executes the appropriate query when the user selects an item from queriesComboBox. Double click queriesComboBox in Design view to generate a queriesComboBox_SelectedIndexChanged event handler (lines 46–80 of Fig. 21.25). In the event handler, we use a switch statement (lines 50–77) to set titleBindingSource’s DataSource property to a LINQ query that returns the correct set of data. Line 18 declares the BooksDataContext used as the subject of the LINQ queries. The data bindings created by the IDE update titlesDataGridView each time we change the DataSource. The MoveFirst method of the BindingSource (line 79) is used to move the focus to the first element each time a query executes.
Fig. 21.25 Displaying the result of a user-selected query in a DataGridView.
1 // Fig. 21.25: DisplayQueryResultForm.cs
2 // Displaying the result of a user-selected query in a DataGridView.
3 using System;
4 using System.Linq;
5 using System.Windows.Forms;
6
7 namespace DisplayQueryResult
8 {
9 public partial class DisplayQueryResultForm : Form
10 {
11 // constructor
12 public DisplayQueryResultForm()
13 {
14 InitializeComponent();
15 } // end constructor
16
17 // LINQ to SQL data context
18 private BooksDataContext database = new BooksDataContext();
19
20 // load data from database into DataGridView
21 private void DisplayQueryResultForm_Load(
22 object sender, EventArgs e )
23 {
24 // write SQL to standard output stream
25 database.Log = Console.Out;
26
27 // set the ComboBox to show the default query that
28 // selects all books from the Titles table
29 queriesComboBox.SelectedIndex = 0;
30 } // end method DisplayQueryResultForm_Load
31
32 // Click event handler for the Save Button in the
33 // BindingNavigator saves the changes made to the data
34 private void titleBindingNavigatorSaveItem_Click(
35 object sender, EventArgs e )
36 {
37 Validate(); // validate input fields
38 titleBindingSource.EndEdit(); // indicate edits are complete
39 database.SubmitChanges(); // write changes to database file
40
41 // when saving, return to "all titles" query
42 queriesComboBox.SelectedIndex = 0;
43 } // end method titleBindingNavigatorSaveItem_Click
44
45 // loads data into TitleBindingSource based on user-selected query
46 private void queriesComboBox_SelectedIndexChanged(
47 object sender, EventArgs e )
48 {
49 // set the data displayed according to what is selected
50 switch ( queriesComboBox.SelectedIndex )
51 {
52 case 0: // all titles
53 // use LINQ to order the books by title
54 titleBindingSource.DataSource =
55 from title in database.Titles
56 orderby title.BookTitle
57 select title;
58 break;
59 case 1: // titles with 2008 copyright
60 // use LINQ to get titles with 2008
61 // copyright and sort them by title
62 titleBindingSource.DataSource =
63 from title in database.Titles
64 where title.Copyright == "2008"
65 orderby title.BookTitle
66 select title;
67 break;
68 case 2: // titles ending with "How to Program"
69 // use LINQ to get titles ending with
70 // "How to Program" and sort them by title
71 titleBindingSource.DataSource =
72 from title in database.Titles
73 where title.BookTitle.EndsWith( "How to Program" )
74 orderby title.BookTitle
75 select title;
76 break;
77 } // end switch
78
79 titleBindingSource.MoveFirst(); // move to first entry
80 } // end method queriesComboBox_SelectedIndexChanged
81 } // end class DisplayQueryResultForm
82 } // end namespace DisplayQueryResult
)
Step 6: Customizing the Form’s Load Event Handler
Create the Form’s Load event handler by double clicking the title bar in Design view. Add a line setting the SelectedIndex of the queriesComboBox to 0 (line 29). This causes the program to show all titles when it executes.
Step 7: Saving Changes
Follow the instructions in the previous example to add a handler for the BindingNavigator’s save Button (lines 34–43). Note that, except for changes to the names, the three lines are identical. The last statement (line 42) makes the DataGridView reset to the All titles query. Also, set the database’s Copy to Output Directory property to Copy if newer, as was done in the preceding example.
Testing the Application
You may now run the application. As in the previous example, you can add and remove rows to and from the table, and save your changes to the database. Additionally, selecting one of the queries from the ComboBox will filter the results so that only some of the rows are displayed.
The BooksDataContext’s Log Property
Line 25 sets the BooksDataContext database’s Log property. When you set this property, the DataContext object logs all queries it runs on the database to the specified stream—in this case, Console.Out. Recall from Chapter 19 that Console.Out is the standard output stream object. In a GUI application, “standard output” is sent to the IDE’s Output window, shown in Fig. 21.26. The Output window can be opened while the program is running by selecting View > Output in the IDE. [Note: The output log is displayed only when running in debug mode.]
)
a) SQL generated by the All titles query.
)
b) SQL generated by the Titles with 2008 copyright query.
)
c) SQL generated by the Titles ending with "How to Program" query.
Fig. 21.26 Output window of the Display Query Result application.
The SQL syntax in Fig. 21.26 is slightly different from the syntax we presented earlier in this chapter. Microsoft SQL Server uses a SQL variant known as Transact-SQL. The square brackets are used to quote table and column names, instead of the double quotes used in standard SQL. The SQL generated by LINQ to SQL quotes all table and column names, even if it is not required.
The identifier t0 used throughout the SQL in Fig. 21.26 is an alias for the table named dbo.Titles. This alias is defined in the FROM clause using SQL’s AS keyword. The alias simply provides a shorter name for the table. The identifier dbo stands for “database owner.” This represents the database user that is allowed to perform all operations on a SQL Server database. The lines starting with -- are comments in the Log output.
The @p0 seen in the third line of the second query is a placeholder for a parameter to the SQL statement. The first comment line for the second SQL statement shows the value of the parameter at the end of the line in square brackets (i.e., [2008]).