Create a new Windows Forms Application named DisplayTable. Change the name of the source file to DisplayAuthorsTable.cs. The IDE updates the Form’s class name to match the source file. Set the Form’s Text property to Display Authors Table.

To interact with a database, you must create a connection to the database. This will also give you the option of copying the database file to your project.

Error-Prevention Tip 18.1

Ensure that no other program is using the database file before you attempt to add it to the project. Connecting to the database requires exclusive access.

After adding the database, you must select the database tables from which the LINQ to SQL classes will be created. LINQ to SQL uses the database’s schema to help define the classes.

When you save Books.dbml, the IDE generates the LINQ to SQL classes that you can use to interact with the database. These include a class for each table you selected from the database and a derived class of DataContext named BooksDataContext that enables you to programmatically interact with the database.

Error-Prevention Tip 18.2

Be sure to save the file in the Object Relational Designer before trying to use the LINQ to SQL classes in code. The IDE does not generate the classes until you save the file.

To use the LINQ to SQL classes for data binding, you must first add them as a data source.

The Authors table in the database is now a data source that can be used by the bindings. Open the Data Sources window (Fig. 18.16) by selecting Data > Show Data Sources—the window is displayed at the left side of the IDE. You can see the Author class that you added in the previous step. The columns of the database’s Authors table should appear below it, as well as an AuthorISBNs entry representing the relationship between the database’s Authors and AuthorISBN tables.

Figure 18.16. Data Sources window showing the Author class as a data source.

Next, you’ll use the Design view to create a GUI control that can display the Authors table’s data.

The IDE creates a DataGridView (Fig. 18.17) with the correct column names and a BindingNavigator (authorBindingNavigator) that contains Buttons for moving between entries, adding entries, deleting entries and saving changes to the database. The IDE also generates a BindingSource (authorBindingSource), which handles the transfer of data between the data source and the data-bound controls on the Form. Nonvisual components such as the BindingSource and the non-visual aspects of the BindingNavigator appear in the component tray—the gray region below the Form in Design view. We use the default names for automatically generated components throughout this chapter to show exactly what the IDE creates. To make the DataGridView occupy the entire window, select the DataGridView, then use the Properties window to set the Dock property to Fill.

Figure 18.17. Component tray holds nonvisual components in Design view.

The final step is to connect the BooksDataContext (created with the LINQ to SQL classes in Section 18.5.1) to the authorBindingSource (created earlier in this section), so that the application can interact with the database. Figure 18.18 shows the small amount of code needed to obtain data from the database and to save any changes that the user makes to the data back into the database.

Example 18.18. Displaying data from a database table in a DataGridView.

 1   // Fig. 18.18: DisplayAuthorsTable.cs
 2   // Displaying data from a database table in a DataGridView.
 3   using System;
 4   using System.Linq;
 5   using System.Windows.Forms;
 6
 7   namespace DisplayTable
 8   {
 9      public partial class DisplayAuthorsTable : Form
10      {
11         // constructor
12         public DisplayAuthorsTable()
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 DisplayAuthorsTable_Load( object sender, EventArgs e)
22         {
23            // use LINQ to order the data for display
24            authorBindingSource.DataSource =  
25               from author in database.Authors
26               orderby author.AuthorID        
27               select author;                 
28         } // end method DisplayAuthorsTable_Load
29
30         // click event handler for the Save Button in the
31         // BindingNavigator saves the changes made to the data
32         private void authorBindingNavigatorSaveItem_Click(
33            object sender, EventArgs e )
34         {
35            Validate(); // validate input fields                         
36            authorBindingSource.EndEdit(); // indicate edits are complete
37            database.SubmitChanges(); // write changes to database file  
38         } // end method authorBindingNavigatorSaveItem_Click
39      } // end class DisplayAuthorsTable
40   } // end namespace DisplayTable

As mentioned in Section 18.4, a DataContext object is used to interact with the database. The BooksDataContext class was automatically generated by the IDE when you created the LINQ to SQL classes to allow access to the Books database. Line 18 creates an object of this class named database.

Create the Form’s Load handler by double clicking the Form’s title bar in Design view. We allow data to move between the DataContext and the BindingSource by creating a LINQ query that extracts data from the BooksDataContext’s Authors property (lines 25–27), which corresponds to the Authors table in the database. The authorBindingSource’s DataSource property (line 24) is set to the results of this query. The authorBinding-Source uses the DataSource to extract data from the database and to populate the DataGridView.

If the user modifies the data in the DataGridView, we’d also like to save the modifications in the database. By default, the BindingNavigator’s Save Data Button() is disabled. To enable it, right click this Button’s icon and select Enabled. Then, double click the icon to create its Click event handler.

Saving the data entered into the DataGridView back to the database is a three-step process (lines 35–37). First, all controls on the form are validated (line 35)—if any of the controls have event handlers for the Validating event, those execute. You typically handle this event to determine whether a control’s contents are valid. Second, line 36 calls EndEdit on the authorBindingSource, which forces it to save any pending changes in the BooksDataContext. Finally, line 37 calls SubmitChanges on the BooksDataContext to store the changes in the database. For efficiency, LINQ to SQL saves only data that has changed.

When you run the program in debug mode, the database file is overwritten with the original database file each time you execute the program. This allows you to test your program with the original content until it works correctly. When you run the program in release mode (Ctrl + F5), changes you make to the database persist automatically; however, if you change the code, the next time you run the program, the database will be restored to its original version. To persist changes for all executions, select the database in the Solution Explorer and set the Copy to Output Directory property in the Properties window to Copy if newer.

First, create a new Windows Forms Application named DisplayQueryResult. Rename the source file to TitleQueries.cs. Set the Form’s Text property to Display Query Results.

Follow the steps in Section 18.5.1 to add the Books database to the project and generate the LINQ to SQL classes.

Follow Steps 1 and 2 in Section 18.5.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.

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 can also 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:

Create the TitleQueries_Load event handler (lines 20–28) by double clicking the title bar in Design view. Line 23 sets the Log property of the BooksDataContext to Console.Out. This causes the program to output to the console the SQL query that is sent to the database for each LINQ query. When the Form loads, it should display the complete list of books from the Titles table, sorted by title. Rather than defining the same LINQ query as in lines 53–55, we can programmatically cause the queriesComboBox_SelectedIndexChanged event handler to execute simply by setting the queriesComboBox’s SelectedIndex to 0 (line 27).

Follow the instructions in the previous example to add a handler for the BindingNavigator’s Save Data Button (lines 32–41). Note that, except for changes to the names, the three lines are identical. The last statement (line 40) displays the results of the All titles query in the DataGridView.

Create a new Windows Forms Application called MasterDetail. Name the source file Details.cs and set the Form’s Text property to Master/Detail.

Follow the instructions in Section 18.5.1 to add the Books database and create the LINQ to SQL classes to interact with the database.

Add two Labels and two ComboBoxes to the top of the Form. Position them as shown in Fig. 18.27. The Label and ComboBox on the left should be named authorLabel and authorComboBox, respectively. The Label and ComboBox on the right should be named titleLabel and titleComboBox. Set the Text properties of the Labels to Author: and Title:, respectively. Also change the DropDownStyle properties of the ComboBoxes from DropDown to DropDownList—this prevents the user from being able to type in the control.

Figure 18.27. Finished design of Master/Detail application.

Next, create a DataGridView called booksDataGridView to hold the details that are displayed. Unlike previous examples, do not automatically create it by dragging a data source from the Data Sources window—this example sets the data source programmatically. Instead, drag the DataGridView from the Toolbox. Resize the DataGridView so that it fills the remainder of the Form. Because this control is only for viewing data, set its ReadOnly property to True using the Properties window.

Finally, we need to add two BindingSources from the Data section of the Toolbox, one for information from the Titles table and one for information from the Authors table. Name these titleBindingSource and authorBindingSource, respectively. As in the previous examples, these appear in the component tray. These BindingSources are used as data sources for the DataGridView—the data source switches between them, depending on whether we want to view a list of Titles or a list of Authors. With the GUI creation complete, we can now write the code to provide the master/detail functionality.

As you saw in Fig. 18.26, the Author: ComboBox displays each author’s full name. This example uses data binding to display the names in the ComboBox. When you bind a collection of objects to a ComboBox’s DataSource property, the ComboBox normally displays the result of calling ToString on each object in the collection. If the String representation is not appropriate, you can specify one property of each object in the collection that should be displayed. In this example, we want to display each author’s first and last name.

Recall that the author’s name is stored as two separate fields in the database, so the auto-generated Author class does not have single property that returns the full name. For this reason, we use a class called AuthorBinding (Fig. 18.28, lines 21–25) to help display the author’s full name. Class AuthorBinding’s Name property stores an author’s full name, and the Author property stores the Author object that contains the author’s information from the database. Class AuthorBinding is intended for use only in this example, so we defined it inside class Details—it’s a so-called nested class. Class definitions may be nested inside other classes when they’re intended to be used only by their enclosing classes—that is, they’re not meant for use by other programs.

 1   // Fig. 18.28: Details.cs
 2   // Using a DataGridView to display details based on a selection.
 3   using System;
 4   using System.Linq;
 5   using System.Windows.Forms;
 6
 7   namespace MasterDetail
 8   {
 9      public partial class Details : Form
10      {
11         public Details()
12         {
13            InitializeComponent();
14         } // end constructor
15
16         // connection to database
17         private BooksDataContext database = new BooksDataContext();
18
19         // this class helps us display each author's first
20         // and last name in the authors drop-down list
21         private class AuthorBinding                                       
22         {                                                                 
23            public Author Author { get; set; } // contained Author object
24            public string Name { get; set; } // author's full name       
25         } // end class AuthorBinding                                      
26

The ComboBox’s DisplayMember property is set to the String "Name" (Fig. 18.29, line 31), which tells the ComboBox to use the Name property of the objects in its DataSource to determine what text to display for each item. The DataSource in this case is the result of the LINQ query in lines 35–38, which creates an AuthorBinding object for each author. The authorComboBox will contain the Name of each author in the query result. Recall from Section 10.13 that object initializers (like lines 37–38) can initialize an object without explicitly calling a constructor.

27         // initialize data sources when the Form is loaded
28         private void Details_Load( object sender, EventArgs e )
29         {
30            // display AuthorBinding.Name
31            authorComboBox.DisplayMember = "Name";
32
33            // set authorComboBox's DataSource to the list of authors
34            authorComboBox.DataSource =
35               from author in database.Authors
36               orderby author.LastName, author.FirstName
37               select new AuthorBinding { Author = author,          
38                  Name = author.FirstName + " " + author.LastName };
39
40            // display Title.Title1
41            titleComboBox.DisplayMember = "Title1";
42
43            // set titleComboBox's DataSource to the list of titles
44            titleComboBox.DataSource =
45               from title in database.Titles
46               orderby title.Title1
47               select title;
48
49            // initially, display no "detail" data
50            booksDataGridView.DataSource = null;
51         } // end method Details_Load
52

For the titleComboBox, we specify that each book’s title should be displayed (line 41). The LINQ query in lines 45–47 returns a sorted list of Title objects and assigns it to the titleComboBox’s DataSource.

Initially, we don’t want to display any data in the DataGridView. However, when you set a ComboBox’s DataSource, the control’s SelectedIndexChanged event handler is called. To prevent this data from being displayed when the program first loads, we explicitly set the DataGridView’s DataSource property to null (line 50).

Simple GUI elements like ComboBoxes can work directly from a data source, such as the result of a LINQ to SQL query. However, a DataGridView requires a BindingSource as its DataSource. While building the GUI, you created two BindingSource objects—one for displaying a list of Authors and one for displaying a list of Titles. You can change the columns and data displayed in the DataGridView merely by changing its DataSource between the two BindingSource objects. The DataGridView automatically determines the column names it needs to display from its BindingSource and refreshes itself when the BindingSource changes.

The authorComboBox_SelectedIndexChanged event handler (Fig. 18.30) performs three distinct operations. First, it retrieves the selected Author (lines 58–59) from the authorComboBox. The ComboBox’s SelectedItem property returns an object, so we convert the SelectedItem property’s value to the type AuthorBinding—recall that the ComboBox’s DataSource was set to a collection of AuthorBinding objects. Then, the event handler accesses the AuthorBinding’s Author property to retrieve the wrapped Author object.

53         // display titles that were co-authored by the selected author
54         private void authorComboBox_SelectedIndexChanged(
55            object sender, EventArgs e )
56         {
57            // get the selected Author object from the ComboBox
58            Author currentAuthor =                                      
59               ( ( AuthorBinding ) authorComboBox.SelectedItem ).Author;
60
61            // set titleBindingSource's DataSource to the
62            // list of titles written by the selected author
63            titleBindingSource.DataSource =          
64               from book in currentAuthor.AuthorISBNs
65               select book.Title;                    
66
67            // display the titles in the DataGridView
68            booksDataGridView.DataSource = titleBindingSource;
69         } // end method authorComboBox_SelectedIndexChanged
70

Next, the event handler uses LINQ to retrieve the Title objects representing books that the currentAuthor worked on (lines 64–65). The results of the LINQ query are assigned to the DataSource property of titleBindingSource (line 63). The event handler sets the titleBindingSource because we want to display Title objects associated with the currentAuthor. Finally, the DataGridView’s DataSource is assigned titleBindingSource to display the books this author wrote (line 68).

The titleComboBox_SelectedIndexChanged event handler (Fig. 18.31) is nearly identical to authorComboBox_SelectedIndexChanged. Line 76 gets the selected Title from the ComboBox. Lines 80–82 set the authorsBindingSource’s DataSource to the list of Authors for the current book. Finally, the DataGridView’s DataSource is assigned authorBindingSource to display the authors who wrote this book (line 85).

71         // display the authors of the selected title
72         private void titleComboBox_SelectedIndexChanged(
73            object sender, EventArgs e )
74         {
75            // get the selected Title object from the ComboBox
76            Title currentTitle = ( Title ) titleComboBox.SelectedItem;
77
78            // set authorBindingSource's DataSource to the
79            // list of authors for the selected title
80            authorBindingSource.DataSource =        
81               from book in currentTitle.AuthorISBNs
82               select book.Author;                  
83
84            // display the authors in the DataGridView
85            booksDataGridView.DataSource = authorBindingSource;
86         } // end method titleComboBox_SelectedIndexChanged
87      } // end class Details
88   } // end namespace MasterDetail

Create a new Windows Forms Application named AddressBook, set the Form’s filename to Contacts.cs, then set the Form’s Text property to Address Book.

Follow the instructions in Section 18.5.1 to add a database to the project and generate the LINQ to SQL classes. For this example, add the AddressBook database and name the file AddressBook.dbml. You must also add the Address table as a data source, as we did with the Authors table in Step 1 of Section 18.5.2.

In the earlier sections, you dragged an object from the Data Sources window to the Form to create a DataGridView that was bound to the data in that object. The IDE allows you to specify the type of control(s) that it will create when you drag and drop an object from the Data Sources window onto a Form. In Design view, click the Address node in the Data Sources window. Note that this becomes a drop-down list when you select it. Click the down arrow to view the items in the list. The item to the left of DataGridView is initially highlighted in blue, because the default control that’s bound to a table is a DataGridView. Select the Details option (Fig. 18.33) in the drop-down list to indicate that the IDE should create a set of Label/TextBox pairs for each column-name/column-value pair when you drag and drop Address onto the Form.

Figure 18.33. Specifying that an Address should be displayed as a set of Labels and TextBoxes.

Drag the Address node from the Data Sources window to the Form. This automatically creates a BindingNavigator and the Labels and TextBoxes corresponding to the columns of the database table. The fields may be placed out of order, with the Email at the top. Reorder the components, using Design view, so they’re in the proper order shown in Fig. 18.32.

The AddressID column of the Addresses table is an autoincremented identity column, so users should not be allowed to edit the values in this column. Select the TextBox for the AddressID and set its ReadOnly property to True using the Properties window.

While the BindingNavigator allows you to browse the address book, it would be more convenient to be able to find a specific entry by last name. To add this functionality to the application, we must create controls to allow the user to enter a last name and provide event handlers to perform the search.

Add a Label named findLabel, a TextBox named findTextBox, and a Button named findButton. Place these controls in a GroupBox named findGroupBox, then set its Text property to Find an entry by last name. Set the Text property of the Label to Last Name: and set the Text property of the Button to Find.

To allow users to return to browsing all the contacts after searching for contacts with a specific last name, add a Button named browseAllButton below the findGroupBox. Set the Text property of browseAllButton to Browse All Entries.

As we showed in previous examples, we must connect the addressBindingSource that controls the GUI with the AddressBookDataContext that interacts with the database. In this example, we do this in the RefreshContacts method (Fig. 18.34, lines 21–31), which is called from several other methods in the application. Method RefreshContacts sets the addressBindingSource’s DataSource property to the result of a LINQ query on the Addresses table. We created a private method in this example, because there are three locations in the program where we need to update the addressBindingSource’s DataSource property.

 1   // Fig. 18.34: Contact.cs
 2   // Manipulating an address book.
 3   using System;
 4   using System.Linq;
 5   using System.Windows.Forms;
 6
 7   namespace AddressBook
 8   {
 9      public partial class Contacts : Form
10      {
11         public Contacts()
12         {
13            InitializeComponent();
14         } // end constructor
15
16         // LINQ to SQL data context
17         private AddressBookDataContext database =
18            new AddressBookDataContext();
19
20         // fill our addressBindingSource with all rows, ordered by name
21         private void RefreshContacts()
22         {
23            // use LINQ to create a data source from the database
24            addressBindingSource.DataSource =             
25               from address in database.Addresses         
26               orderby address.LastName, address.FirstName
27               select address;                            
28
29            addressBindingSource.MoveFirst(); // go to the first result
30            findTextBox.Clear(); // clear the Find TextBox
31         } // end method RefreshContacts
32

Method Contacts_Load (Fig. 18.35) calls RefreshContacts (line 36) so that the first record is displayed when the application starts. As before, you create the Load event handler by double clicking the Form’s title bar.

33         // when the form loads, fill it with data from the database
34         private void Contacts_Load( object sender, EventArgs e )
35         {
36            RefreshContacts(); // fill binding with data from database
37         } // end method Contacts_Load
38

Method addressBindingNavigatorSaveItem_Click (Fig. 18.36) saves the changes to the database when the BindingNavigator’s save Button is clicked. (Remember to enable this button in the BindingNavigator.) We call RefreshContacts after saving to re-sort the data and move back to the first element.

39         // Click event handler for the Save Button in the
40         // BindingNavigator saves the changes made to the data
41         private void addressBindingNavigatorSaveItem_Click(
42            object sender, EventArgs e )
43         {
44            Validate(); // validate input fields
45            addressBindingSource.EndEdit(); // indicate edits are complete
46            database.SubmitChanges(); // write changes to database file
47
48            RefreshContacts(); // change back to initial unfiltered data
49         } // end method addressBindingNavigatorSaveItem_Click
50

The AddressBook database requires values for the first name, last name, phone number and e-mail. We did not check for errors to simplify the code—if a field is empty when you attempt to save, a SqlException exception (namespace System.Data.SqlClient) occurs.

Method findButton_Click (Fig. 18.37) uses LINQ (lines 57–60) to select only people whose last names start with the characters entered in the findTextBox. The query sorts the results by last name then first name. When you enter a last name and click Find, the BindingNavigator allows the user to browse only the rows containing the matching last names. This is because the data source bound to the Form’s controls (the result of the LINQ query) has changed and now contains only a limited number of rows.

51         // use LINQ to create a data source that contains only people
52         // with last names that start with the specified text
53         private void findButton_Click( object sender, EventArgs e )
54         {
55            // use LINQ to create a data source from the database
56            addressBindingSource.DataSource =                       
57               from address in database.Addresses                   
58               where address.LastName.StartsWith( findTextBox.Text )
59               orderby address.LastName, address.FirstName          
60               select address;                                      
61
62            addressBindingSource.MoveFirst(); // go to first result
63         } // end method findButton_Click
64

Method browseAllButton_Click (Fig. 18.38) allows users to return to browsing all the rows after searching for specific rows. Double click browseAllButton to create a Click event handler. Have the event handler call RefreshContacts (line 68) to restore the data source to the full list of people and clear the findTextBox.

65         // reload addressBindingSource with all rows
66         private void browseButton_Click( object sender, EventArgs e )
67         {
68            RefreshContacts(); // change back to initial unfiltered data
69         } // end method browseButton_Click
70      } // end class Contacts
71   } // end namespace AddressBook