text: - .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]).