Our final example implements a simple AddressBook
app (with sample outputs in (Figs. 22.38–22.40) that enables users to perform the following tasks on the database AddressBook.mdf
(which is included in the directory with this chapter’s examples):
Insert new contacts.
Find contacts whose last names begin with the specified letters.
Update existing contacts.
Delete contacts.
We populated the database with six fictional contacts.
Rather than displaying a database table in a DataGridView
, this app presents the details of one contact at a time in several TextBox
es. The BindingNavigator
at the top of the window allows you to control which row of the table is displayed at any given time. The BindingNavigator
also allows you to add a contact and delete a contact—but only when browsing the complete contact list. When you filter the contacts by last name, the app disables the Add new
and Delete
buttons (we’ll explain why shortly). Clicking Browse All Entries enables these buttons again. Adding a row clears the TextBox
es and sets the TextBox
to the right of Address ID to zero to indicate that the TextBox
es now represent a new record. When you save a new entry, the Address ID field is automatically changed from zero to a unique ID number by the database. No changes are made to the underlying database unless you click the Save Data button:
We discuss the app’s code momentarily. First you’ll set up a new solution containing the entity data model and a Windows Form
s app. Close the BooksExamples
solution you used in this chapter’s previous examples.
Perform the steps in Section 22.5.1 to create a Class Library project named AddressExample
that contains an entity data model for the AddressBook.mdf
database, which contains only an Addresses
table with AddressID
, FirstName
, LastName
, Email
and PhoneNumber
columns. Name the entity data model AddressModel
. The AddressBook.mdf
database is located in the Databases
folder with this chapter’s examples.
AddressBook
AppPerform the steps in Section 22.5.2 to create a new Windows Forms Application project named AddressBook
in the AddressExample
solution. Set the Form
’s filename to Contacts.cs
, then set the Form
’s Text property to Address
Book
. Set the AddressBook
project as the solution’s startup project.
Address
Object as a Data SourceAdd the entity data model’s Address
object as a data source, as you did with the Author
object in Step 1 of Section 22.5.3.
In Design view, select the Address node in the Data Sources window. Click the Address node’s down arrow and select the Details option to indicate that the IDE should create a set of Label
–TextBox
pairs to show the details of a single record at a time.
Address
Data-Source Node to the Form
Drag the Address
node from the Data Sources window to the Form
. This automatically creates a BindingNavigator
and the Label
s and TextBox
es corresponding to the columns of the database table. The fields are placed in alphabetical order. Reorder the components, using Design view, so they’re in the order shown in Fig. 22.38. You’ll also want to change the tab order of the controls. To do so, select View > Tab Order, then click the TextBox
es from top to bottom in the order they appear in Fig. 22.38.
AddressID
TextBox
ReadOnly
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 app, 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.
The Contacts.cs
code-behind file is split into several figures (Figs. 22.41–22.45) for presentation purposes.
RefreshContacts
As we showed in previous examples, we must connect the addressBindingSource
that controls the GUI with the DbContext
that interacts with the database. In this example, we declare the AddressEntities
DbContext
object at line 20 of Fig. 22.41, but create it and initiate the data binding in the RefreshContacts
method (lines 23–45), which is called from several other methods in the app. When this method is called, if dbcontext
is not null
, we call its Dispose
method, then create a new
AddressEntities
DbContext
at line 32. We do this so we can re-sort the data in the entity data model. If we maintained one dbcontext.Addresses
object in memory for the duration of the program and the user changed a person’s last name or first name, the records would still remain in their original order in the dbcontext.Addresses
object, even if that order is incorrect. Lines 36–39 order the Address
objects by LastName
, then FirstName
and load the objects into memory. Then line 42 sets the addressBindingSource
’s DataSource
property to dbcontext.Addresses.Local
to bind the data in memory to the GUI.
Contacts_Load
Method Contacts_Load
(Fig. 22.42) calls RefreshContacts
(line 50) so that the first record is displayed when the app starts. As before, you create the Load
event handler by double clicking the Form
’s title bar.
addressBindingNavigatorSaveItem_Click
Method addressBindingNavigatorSaveItem_Click
(Fig. 22.43) saves the changes to the database when the BindingNavigator
’s Save Data Button
is clicked. (Remember to enablethis button.) The AddressBook
database requires values for the first name, last name, phone number and e-mail. If a field is empty when you attempt to save, a DbEntityValidationException
exception occurs. We call RefreshContacts
(line 72) after saving to re-sort the data and move back to the first element.
findButton_Click
Method findButton_Click
(Fig. 22.44) uses LINQ query syntax (lines 81–85) to select only people whose last names start with the characters in the findTextBox
. The query sorts the results by last name, then first name. In LINQ to Entities, you cannot bind a LINQ query’s results directly to a BindingSource
’s DataSource
. So, line 88 calls the query object’s ToList
method to get a List
representation of the filtered data and assigns the List
to the BindingSource
’s DataSource
. When you convert the query result to a List
, only changes to existing records in the DbContext
are tracked by the DbContext
—any records that you add or remove while viewing the filtered data would be lost. For this reason we disabled the Add new and Delete buttons when the data is filtered. 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.
browseAllButton_Click
Method browseAllButton_Click
(Fig. 22.45) allows users to return to browsing all the rows after searching for specific rows. Double click browseAllButton
to create a Click
event handler. The event handler enables the Add new and Delete buttons, then calls RefreshContacts
to restore the data source to the full list of people (in sorted order) and clear the findTextBox
.