Select File > New > Project… to display the New Project dialog, then select Class Library from the Visual C# templates and name the project BooksExamples. Click OK to create the project, then delete the Class1.cs file from the Solution Explorer.

To interact with the database, you’ll add an ADO.NET entity data model to the class library project. This will also configure the connection to the database.

1. Adding the ADO.NET Entity Data Model. Right click the BooksExamples project in the Solution Explorer, then select Add > New Item… to display the Add New Item dialog (Fig. 22.11). From the Data category select ADO.NET Entity Data Model and name the model BooksModel—this will be the name of a file (with the filename extension.edmx) that configures the entity data model. Click Add to add the entity data model to the class library and display the Entity Data Model Wizard dialog.

   

2. Choosing the Model Contents. The Choose Model Contents step in the Entity Data Model Wizard dialog (Fig. 22.12) enables you to specify the entity data model’s contents. The model in these examples will consist of data from the Books data-base, so select EF Designer from database and click Next > to display the Choose Your Data Connection step.

   

3. Choosing the Data Connection. In the Choose Your Data Connection step, click New Connection… to display the Connection Properties dialog (Fig. 22.13). (If

   

   the IDE displays a Choose Data Source dialog, select Microsoft SQL Server Database File and click Continue.) For the Data source field, if Microsoft SQL Server Database File (SqlClient) is not displayed, click Change…, select Microsoft SQL Server Database File (SqlClient) and click OK. Next, click Browse… to the right of the Database file name field to locate and select the Books.mdf file in the Databases directory included with this chapter’s examples. You can click Test Connection to verify that the IDE can connect to the database through SQL Server Express. Click OK to create the connection. Figure 22.14 shows the Connection string for the Books.mdf database. This contains the information that the ADO.NET Entity Framework requires to connect to the database at runtime. Click Next >. A dialog will appear asking if you’d like to add the database file to your project. Click Yes to move to the next step.

   

4. Choosing the Entity Framework Version. In the Choose Your Version step, select the Entity Framework 6.x (Fig. 22.15), then click Next >. This adds the latest version of the Entity Framework to your project.

5. Choosing the Database Objects to Include in the Model. In the Choose Your Database Objects and Settings step, you’ll specify the parts of the database that should be used in the ADO.NET Entity Data Model. Select the Tables node as shown in Fig. 22.16, then click Finish. At this point, the IDE will download the Entity Framework 6.x templates you need and add them to your project. You may see one or more Security Warning dialogs—Visual Studio displays these when you attempt to use downloaded content in your projects. Click OK to dismiss each dialog. These warnings are intended primarily for cases in which a Visual Studio template is downloaded from an untrusted website.

   

   

6. Viewing the Entity Data Model Diagram in the Model Designer. At this point, the IDE creates the entity data model and displays a diagram (Fig. 22.17) in the model designer. The diagram contains Author and Title entities—these represent authors and titles in the database and the properties of each. Notice that the IDE renamed the Title column of the Titles table as Title1 to avoid a naming conflict with the class Title that represents a row in the table. The line between the entities indicates a relationship between authors and titles—this relationship is implemented in the Books database as the AuthorISBN table. The asterisk (*) at each end of the line indicates a many-to-many relationship—each author can author many titles and each title can have many authors. The Navigation Properties section in the Author entity contains the Titles property, which connects an author to all titles written by that author. Similarly, the Navigation Properties section in the Title entity contains the Authors property, which connects a title to all of its authors.

   

7. Building the Class Library. Select Build > Build Solution to build the class library that you’ll reuse in the next several examples—this will compile the entity data model classes that were generated by the IDE.¹ When you build the class library, the IDE generates the 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 DbContext named BooksEntities that enables you to programmatically interact with the database—the IDE created the name BooksEntities (Fig. 22.14) by adding Entities to the database file’s base name (Books in Books.mdf). Building the project causes the IDE to execute a script that creates and compiles the entity data model classes.

To add a new Windows Forms project to the existing solution:

1. Right click Solution 'BooksExamples' (the solution name) in Solution Explorer and select Add > New Project… to display the Add New Project dialog.

2. Select Windows Forms Application from the Visual C# > Windows > Classic Desktop category, name the project DisplayTable and click OK.

3. Change the name of the Form1.cs 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.

4. Right click the DisplayTable project’s name in the Solution Explorer, then select Set as Startup Project to configure the solution so that project DisplayTable will execute when you select Debug > Start Debugging (or press F5).

To use the entity data model classes for data binding, you must first add a reference to the class library you created in Section 22.5.1—this allows the new project to use that class library. Each project you create typically contains references to several .NET class libraries (called assemblies) by default—for example, a Windows Forms project contains a reference to the System.Windows.Forms library. When you compile a class library, the IDE creates a .dll file containing the library’s components. To add a reference to the class library containing the entity data model’s classes:

1. Right click the DisplayTable project’s References node in the Solution Explorer and select Add Reference….

2. In the left column of the Reference Manager dialog that appears, select Projects to display the other projects in this solution, then in center of the dialog ensure that the checkbox next to BooksExamples is checked and click OK. BooksExamples should now appear in the projects References node.

You’ll also need a reference to the EntityFramework library to use the ADO.NET Entity Framework. This library was added by the IDE to the BooksExamples class library project when we created the entity data model, but you also must add the EntityFramework library to each app that will use the entity data model. To add an EntityFramework library reference to a project:

1. Right click the project’s name in the Solution Explorer and select Manage NuGet Packages… to display the NuGet tab in Visual Studio’s editors area. NuGet is a tool (known as a package manager) that that helps you download and manage libraries (known as packages) used by your projects.

2. In the dialog that appears, click Browse, then select the EntityFramework by Microsoft and click Install (Fig. 22.18).

3. The IDE will ask you to review the changes. Click OK.

4. The IDE will ask you to accept the EntityFramework license. Click I Accept to complete the installation.

EntityFramework should now appear in the projects References node. You can now close the NuGet tab.

Each app that will use the entity data model also requires the connection string that tells the Entity Framework how to connect to the database. The connection string is stored in the BooksExamples class library’s App.Config file. In the Solution Explorer, open the Books-Examples class library’s App.Config file, then copy the connectionStrings element (lines 7–9 in our file), which has the format:

<connectionStrings>
    Connection string information appears here
</connectionStrings>

Next, open the App.Config file in the DisplayTable project and paste the connection string information after the line containing </entityFramework> and before the line containing </configuration>. Save, then close the App.Config file.

To use the entity data model classes for data binding, you must first add them as a data source. To do so:

1. Select View > Other Windows > Data Sources to display the Data Sources window at the left side of the IDE, then in that window click the Add New Data Source… link to display the Data Source Configuration Wizard.

2. The Entity Data Model classes are used to create objects representing the tables in the database, so we’ll use an Object data source. In the dialog, select Object and click Next >. Expand the tree view as shown in Fig. 22.19 and ensure that Author is checked. An object of this class will be this app’s data source.

3. Click Finish.

The Authors table in the database is now a data source that can be bound to a GUI control that obtains author data automatically. In the Data Sources window (Fig. 22.20), you can see the Author class that you added in the previous step. We expanded the node to show Author’s properties, representing columns of the database’s Authors table, as well as the Titles navigation property, representing the relationship between the database’s Authors and Titles tables.

Next, you’ll use the Design view to create a DataGridView control that can display the Authors table’s data. To do so:

1. Switch to Design view for the DisplayAuthorsTable class.

2. Click the Author node in the Data Sources window—it should change to a drop-down list. Open the drop-down by clicking the down arrow and ensure that the DataGridView option is selected—this is the GUI control that will be used to display and interact with the data.

3. Drag the Author node from the Data Sources window onto the Form in Design view. You’ll need to resize the Form to fit the DataGridView.

The IDE creates a DataGridView (Fig. 22.21) with column names representing all the properties for an Author, including the Titles navigation property.

The IDE also creates a BindingNavigator that contains Buttons for

• moving between entries,

• adding entries,

• deleting entries and

• saving changes to the database.

In addition, the IDE 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 nonvisual aspects of the Binding-Navigator appear in the component tray—the gray region below the Form in Design view. The IDE names the BindingNavigator and BindingSource (authorBindingNavigator and authorBindingSource, respectively) based on the data source’s name (Author). 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 below the BindingNavigator, select the DataGridView, then use the Properties window to set the Dock property to Fill. You can stretch the window horizontally to see all the DataGridView columns. We do not use the Titles column in this example, so right click the DataGridView and select Edit Columns… to display the Edit Columns dialog. Select Titles in the Selected Columns list, click Remove to remove that column, then click OK.

The final step is to connect the data source to the authorBindingSource, so that the app can interact with the database. Figure 22.22 shows the code needed to obtain data from the database and to save any changes that the user makes to the data back into the database.

As mentioned in Section 22.4, a DbContext object interacts with the database on the app’s behalf. The BooksEntities class (a derived class of DbContext) was automatically generated by the IDE when you created the entity data model classes to access the Books data-base (Section 22.5.1). Lines 20–21 create an object of this class named dbcontext.

You can create the Form’s Load event handler (lines 24–34) by double clicking the Form’s title bar in Design view. In this app, we allow data to move between the DbContext and the database by using LINQ to Entities extension methods to extract data from the Books-Entities’s Authors property (lines 27–30), which corresponds to the Authors table in the database. The expression

dbcontext.Authors

indicates that we wish to get data from the Authors table.

The OrderBy extension method call

.OrderBy(author => author.LastName)

indicates that the rows of the table should be retrieved in ascending order by the authors’ last names. Extension method OrderBy receives as its argument a Func delegate (namespace System) representing a method that receives one parameter and returns a value that’s used to order the results. In this case, we pass a lambda expression that defines an anonymous method in which

• the parameter author (an object of the Author entity data model class) is passed to the method, and

• the expression to the right of the lambda operator (=>)—the author’s LastName in this case—is implicitly returned by the method.

The lambda expression infers author’s type from dbcontext.Authors—which contains Author objects—and infers the lambda’s return type (string) from author.LastName.

When there are multiple authors with the same last name, we’d like them to be listed in ascending order by first name as well. The ThenBy extension method call

.ThenBy(author => author.FirstName)

enables you to order results by an additional column. This is applied to the Author objects that have already been ordered by last name. Like OrderBy, ThenBy also receives a Func delegate that’s used to order the results.

Finally, line 30 calls the Load extension method (defined in class DbExtensions from the namespace System.Data.Entity). This method executes the LINQ to Entities query and loads the results into memory. This data is tracked by the BookEntities DbContext in local memory so that any changes made to the data can eventually be saved into the database. Lines 27–30 are equivalent to using the following statement:

(from author in dbcontext.Authors
 orderby author.LastName, author.FirstName
 select author).Load();

Line 33 sets the authorBindingSource’s DataSource property to the Local property of the dbcontext.Authors object. In this case, the Local property is an ObservableCollection<Author> that represents the query results that were loaded into memory by lines 27–30. When a BindingSource’s DataSource property is assigned an ObservableCollection<T> (namespace System.Collections.ObjectModel), the GUI that’s bound to the BindingSource is notified of any changes to the data so the GUI can be updated accordingly. In addition, changes made by the user to the data in the GUI will be tracked so the DbContext can eventually save those changes to the database.

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 in the BindingNavigator and select Enabled. Then, double click the icon to create its Click event handler and add the code in the method’s body (lines 41–53).

Saving the data entered in the DataGridView back to the database is a three-step process. First, the Form’s controls are validated (line 41) by calling the inherited Validate method—if any control has an event handler for the Validating event, it executes. You typically handle this event to determine whether a control’s contents are valid. Next, line 42 calls EndEdit on the authorBindingSource, which forces it to save any pending changes into the BooksEntities model in memory. Finally, line 47 calls SaveChanges on the BooksEntities object (dbcontext) to store any changes into the database. We placed this call in a try statement, because the Authors table does not allow empty values for the first name and last name—these rules were configured when we originally created the database. When SaveChanges is called, any changes stored into the Authors table must satisfy the table’s rules. If any do not, a DBEntityValidationException occurs.