Transcript 21.4 SQL (Cont.)

1
21
Databases and
LINQ to SQL
 2009 Pearson Education, Inc. All rights reserved.
2
It is a capital mistake to theorize
before one has data.
– Arthur Conan Doyle
Now go, write it before them in a table,
and note it in a book, that it may be for
the time to come for ever and ever.
– Isaiah 30:8
 2009 Pearson Education, Inc. All rights reserved.
3
Get your facts first, and then you
can distort them as much as you please.
– Mark Twain
I like two kinds of men:
domestic and foreign.
– Mae West
 2009 Pearson Education, Inc. All rights reserved.
4
OBJECTIVES
In this chapter you will learn:
 The relational database model.
 To write basic database queries in SQL.
 To use LINQ to SQL to retrieve and manipulate data
from a database.
 To add data sources to projects.
 To use the Object Relational Designer to create LINQ
to SQL classes.
 To use the IDE’s drag-and-drop capabilities to display
database tables in applications.
 To use data binding to move data seamlessly between
GUI controls and databases.
 To create Master/Detail views.
 2009 Pearson Education, Inc. All rights reserved.
5
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
 2009 Pearson Education, Inc. All rights reserved.
6
21.1 Introduction
• A database is an organized collection of data.
• A database management system (DBMS) organizes
data for many users.
• Relational databases organize data as tables with
rows and columns.
• Structured Query Language (SQL) is the
international standard language used with relational
databases.
 2009 Pearson Education, Inc. All rights reserved.
7
21.2 Relational Databases
Figure 21.1 illustrates a sample Employees table.
• The ID column is the table’s primary key—a column used to
uniquely identify a row.
• A primary key composed of two or more columns is a composite key.
Fig. 21.1 | Employees table sample data.
 2009 Pearson Education, Inc. All rights reserved.
8
21.2 Relational Databases (Cont.)
• Each column represents a different data attribute.
• Most users of a database use only subsets of the rows
and columns.
• Programs use SQL to define queries that select subsets
of the data from a table (Fig. 21.2).
Fig. 21.2 | Distinct Department and Location data from
the Employees table.
 2009 Pearson Education, Inc. All rights reserved.
9
21.3 Relational Database Overview:
Books Database
• A database’s tables, their fields and the relationships between
them are collectively known as a database schema.
• The Authors table has three fields, represented by columns
(Fig. 21.3).
Column
Description
AuthorID
Author’s ID number in the database (primary key).
FirstName Author’s first name (a string).
LastName
Author’s last name (a string).
Fig. 21.3 | Authors table of the Books database.
 2009 Pearson Education, Inc. All rights reserved.
10
21.3 Relational Database Overview:
Books Database (Cont.)
• Figure 21.4 contains the data from the Authors table.
AuthorID FirstName LastName
1
Harvey
Deitel
2
Paul
Deitel
3
Greg
Ayer
4
Dan
Quirk
Fig. 21.4 | Data from the Authors table of the Books database.
 2009 Pearson Education, Inc. All rights reserved.
11
21.3 Relational Database Overview:
Books Database (Cont.)
• The Titles table (described in Fig. 21.5) consists of
four columns.
Column
Description
ISBN
ISBN of the book (a string). The table’s primary key.
BookTitle
Title of the book (a string).
EditionNumber
Edition number of the book (an integer).
Copyright
Copyright year of the book (a string).
Fig. 21.5 | Titles table of the Books database.
 2009 Pearson Education, Inc. All rights reserved.
12
21.3 Relational Database Overview:
Books Database (Cont.)
• Figure 21.6 contains the data from the Titles table.
ISBN
BookTitle
EditionNumber
Copyright
0131752421
Internet & World Wide Web How to Program
4
2008
0132222205
Java How to Program
7
2007
0132404168
C How to Program
5
2007
0136053033
Simply Visual Basic 2008
3
2009
013605305X
Visual Basic 2008 How to Program
4
2009
013605322X
Visual C# 2008 How to Program
3
2009
0136151574
Visual C++ 2008 How to Program
2
2008
0136152503
C++ How to Program
6
2008
Fig. 21.6 | Data from the Titles table of the Books database.
 2009 Pearson Education, Inc. All rights reserved.
13
21.3 Relational Database Overview:
Books Database (Cont.)
• The AuthorISBN table (described in Fig. 21.7)
matches the AuthorID and ISBN columns.
• These foreign keys form a composite primary key.
Column
Description
AuthorID
The author’s ID number, a foreign key to the Authors table.
ISBN
The ISBN for a book, a foreign key to the Titles table.
Fig. 21.7 | AuthorISBN table of the Books database.
 2009 Pearson Education, Inc. All rights reserved.
14
21.3 Relational Database Overview:
Books Database (Cont.)
• Figure 21.8 contains the data from the AuthorISBN table of the
Books database.
AuthorID
ISBN
AuthorID
ISBN
1
0131752421
2
0132222205
1
0132222205
2
0132404168
1
0132404168
2
0136053033
1
0136053033
2
013605305X
1
013605305X
2
013605322X
1
013605322X
2
0136151574
1
0136151574
2
0136152503
1
0136152503
3
0136053033
2
0131752421
4
0136151574
Fig. 21.8 | Data from the AuthorISBN table of Books.
 2009 Pearson Education, Inc. All rights reserved.
15
21.3 Relational Database Overview:
Books Database (Cont.)
• Rule of Referential Integrity: every foreign-key value
must be another table’s primary-key value.
• Foreign keys also allow related data in multiple tables
to be joined.
• A foreign key can appear many times in a table but
only once (as the primary key) in its original table.
 2009 Pearson Education, Inc. All rights reserved.
16
21.3 Relational Database Overview:
Books Database (Cont.)
Common Programming Error 21.2
Not providing a value for every column in a primary key
breaks the Rule of Entity Integrity and causes the DBMS to
report an error.
Common Programming Error 21.2
Providing the same value for the primary key in multiple rows
breaks the Rule of Entity Integrity and causes the DBMS to
report an error.
Common Programming Error 21.3
Providing a foreign-key value that does not appear as a
primary-key value in another table breaks the Rule of
Referential Integrity and causes the DBMS to report an error.
 2009 Pearson Education, Inc. All rights reserved.
17
21.3 Relational Database Overview:
Books Database (Cont.)
• Figure 21.9 is an entity-relationship (ER) diagram for
the Books database.
• Note that primary keys are italic.
• On the Authors end of the line, there is a 1, and on the
AuthorISBN end, an infinity symbol (∞), indicating a
one-to-many relationship.
Fig. 21.9 | Entity-relationship diagram for the Books database.
 2009 Pearson Education, Inc. All rights reserved.
18
21.4 SQL
• Figure 21.10 lists some common SQL keywords.
SQL keyword
Description
SELECT
Retrieves data from one or more tables.
FROM
Specifies the tables involved in a query. Required in every query.
WHERE
Specifies optional criteria for selecting rows.
ORDER BY
Specifies optional criteria for ordering rows.
INNER JOIN
Specifies optional operator for merging rows from multiple tables.
INSERT
Inserts rows in a specified table.
UPDATE
Updates rows in a specified table.
DELETE
Deletes rows from a specified table.
Fig. 21.10 | Common SQL keywords.
 2009 Pearson Education, Inc. All rights reserved.
19
21.4 SQL (Cont.)
21.4.1 Basic SELECT Query
• A SQL query “selects” rows and columns from one or
more tables in a database.
SELECT * FROM tableName
• The asterisk (*) indicates that all the columns from the
tableName table should be retrieved.
• To retrieve all the data in the Authors table:
SELECT * FROM Authors
 2009 Pearson Education, Inc. All rights reserved.
20
21.4 SQL (Cont.)
• To retrieve only specific columns, use a list of the column
names:
SELECT AuthorID, LastName FROM Authors
• This query returns the data listed in Fig. 21.11.
AuthorID
LastName
1
Deitel
2
Deitel
3
Ayer
4
Quirk
Fig. 21.11 | AuthorID and LastName data from the Authors table.
 2009 Pearson Education, Inc. All rights reserved.
21
21.4 SQL (Cont.)
21.4.2 WHERE Clause
• Users can query a database for rows that satisfy certain
selection criteria.
• The basic form of a query with selection criteria is
SELECT columnName1, columnName2,
FROM tableName WHERE criteria
 2009 Pearson Education, Inc. All rights reserved.
22
21.4 SQL (Cont.)
• To select books for which the Copyright is more recent than 2007:
SELECT BookTitle, EditionNumber, Copyright.
FROM Titles
WHERE Copyright > '2007'
• Figure 21.12 shows the result of the preceding query.
BookTitle
EditionNumber Copyright
Internet & World Wide Web How to Program
4
2008
Simply Visual Basic 2008
3
2009
Visual Basic 2008 How to Program
4
2009
Visual C# 2008 How to Program
3
2009
Visual C++ 2008 How to Program
2
2008
C++ How to Program
6
2008
Fig. 21.12 | Books with copyright dates after 2007 from table Titles.
 2009 Pearson Education, Inc. All rights reserved.
23
21.4 SQL (Cont.)
• Operator LIKE is used for pattern matching.
• A pattern with a percent character (%) searches for
zero or more characters at the percent character’s
position.
• The following query locates authors whose last
names start with the letter D:
SELECT AuthorID, FirstName, LastName
FROM Authors
WHERE LastName LIKE 'D%'
 2009 Pearson Education, Inc. All rights reserved.
24
21.4 SQL (Cont.)
• The preceding query selects the two rows shown
in Fig. 21.13.
AuthorID
FirstName
LastName
1
Harvey
Deitel
2
Paul
Deitel
Fig. 21.13 | Authors from the Authors table whose
last names start with D.
 2009 Pearson Education, Inc. All rights reserved.
25
21.4 SQL (Cont.)
• An underscore (_) indicates a single wildcard
character at that position.
• The following query locates authors whose last
names have the letter y as the second letter.
SELECT AuthorID, FirstName, LastName
FROM Authors
WHERE LastName LIKE '_y%'
 2009 Pearson Education, Inc. All rights reserved.
26
21.4 SQL (Cont.)
• The preceding query produces the row shown in
Fig. 21.14.
AuthorID FirstName
LastName
3
Ayer
Greg
Fig. 21.14 | The only author from the Authors table
whose last name contains y as the second letter.
 2009 Pearson Education, Inc. All rights reserved.
27
21.4 SQL (Cont.)
21.4.3 ORDER BY Clause
• Rows in the result can be sorted into ascending or
descending order by using the optional ORDER BY clause.
SELECT columnName1, columnName2, FROM
tableName ORDER BY column ASC
SELECT columnName1, columnName2, FROM
tableName ORDER BY column DESC
• ASC specifies ascending order, DESC specifies descending
order
• column specifies the column on which the sort is based.
 2009 Pearson Education, Inc. All rights reserved.
28
21.4 SQL (Cont.)
• To obtain the list of authors in ascending order by last
name (Fig. 21.15), use the query
SELECT AuthorID, FirstName, LastName
FROM Authors
ORDER BY LastName ASC
AuthorID
FirstName LastName
3
Greg
Ayer
1
Harvey
Deitel
2
Paul
Deitel
4
Dan
Quirk
Fig. 21.15 | Authors from table Authors in ascending
order by LastName.
 2009 Pearson Education, Inc. All rights reserved.
29
21.4 SQL (Cont.)
• To obtain the same list of authors in descending
order by last name (Fig. 21.16), use
SELECT AuthorID, FirstName, LastName
FROM Authors
ORDER BY LastName DESC
AuthorID
FirstName
LastName
4
Dan
Quirk
1
Harvey
Deitel
2
Paul
Deitel
3
Greg
Ayer
Fig. 21.16 | Authors from table Authors in descending
order by LastName.
 2009 Pearson Education, Inc. All rights reserved.
30
21.4 SQL (Cont.)
• Multiple columns can be used for sorting with an
ORDER BY clause:
ORDER BY column1 sortingOrder, column2
sortingOrder,…
• Note that the sortingOrder does not have to be
identical for each column.
 2009 Pearson Education, Inc. All rights reserved.
31
21.4 SQL (Cont.)
SELECT BookTitle, EditionNumber,Copyright
FROM Titles
ORDER BY Copyright DESC, BookTitle ASC
• This query returns books sorted first in descending
order by copyright date, then in ascending order by title (Fig. 21.17).
 2009 Pearson Education, Inc. All rights reserved.
32
21.4 SQL (Cont.)
BookTitle
EditionNumber Copyright
Simply Visual Basic 2008
3
2009
Visual Basic 2008 How to Program
4
2009
Visual C# 2008 How to Program
3
2009
C++ How to Program
6
2008
Internet & World Wide Web How to Program
4
2008
Visual C++ 2008 How to Program
2
2008
C How to Program
5
2007
Java How to Program
7
2007
Fig. 21.17 | Data from Titles in descending order by Copyright
and ascending order by BookTitle.
 2009 Pearson Education, Inc. All rights reserved.
33
21.4 SQL (Cont.)
• The WHERE and ORDER BY clauses can be
combined.
• ORDER BY must be the last clause in the query.
SELECT ISBN, BookTitle,
EditionNumber, Copyright
FROM Titles
WHERE BookTitle LIKE '%How to Program'
ORDER BY BookTitle ASC
 2009 Pearson Education, Inc. All rights reserved.
34
21.4 SQL (Cont.)
•
ISBN
The query results are shown in Fig. 21.18.
BookTitle
EditionNumber Copyright
0132404168 C How to Program
5
2007
0136152503 C++ How to Program
6
2008
0131752421 Internet & World Wide Web How to Program 4
2008
0132222205 Java How to Program
7
2007
013605305X Visual Basic 2008 How to Program
4
2009
013605322X Visual C# 2008 How to Program
3
2009
0136151574 Visual C++ 2008 How to Program
2
2008
Fig. 21.18 | Books from table Titles whose BookTitles end with How to
Program in ascending order by BookTitle.
 2009 Pearson Education, Inc. All rights reserved.
35
21.4 SQL (Cont.)
21.4.4 Retrieving Data from Multiple Tables:
INNER JOIN
• Database designers typically normalize databases—i.e.,
split related data into separate tables to ensure that a
database does not store redundant data.
• For example, we use a table to store “links” between
authors and titles.
• If we did not separate this information into individual
tables, we would need to include author information with
each entry in the Titles table.
 2009 Pearson Education, Inc. All rights reserved.
36
21.4 SQL (Cont.)
• Often, it is desirable to merge data from multiple
tables into a single result.
• An INNER JOIN merges rows from two tables by
testing for matching values in a column that is
common to the tables:
SELECT columnName1, columnName2,…
FROM table1 INNER JOIN table2
ON table1.columnName = table2.columnName
 2009 Pearson Education, Inc. All rights reserved.
37
21.4 SQL (Cont.)
• The ON clause specifies the columns from each table that are
compared to determine which rows are merged.
• The following query produces a list of authors accompanied
by the ISBNs for books written by each author:
SELECT FirstName, LastName, ISBN
FROM Authors INNER JOIN AuthorISBN
ON Authors.AuthorID = AuthorISBN.AuthorID
ORDER BY LastName, FirstName
Common Programming Error 21.4
In a SQL query, failure to qualify names for columns
that have the same name in two or more tables is an error.
 2009 Pearson Education, Inc. All rights reserved.
38
21.4 SQL (Cont.)
• Figure 21.19 depicts the results of the preceding query,
ordered by LastName and FirstName.
FirstName LastName
ISBN
Greg
Ayer
0136053033
Harvey
Deitel
0131752421
Harvey
Deitel
0132222205
Harvey
Deitel
0132404168
Harvey
Deitel
0136053033
Harvey
Deitel
013605305X
Fig. 21.19 | Authors and ISBNs for their books in ascending
order by LastName and FirstName. (Part 1 of 2.)
 2009 Pearson Education, Inc. All rights reserved.
39
21.4 SQL (Cont.)
FirstName LastName
ISBN
Harvey
Deitel
013605322X
Harvey
Deitel
0136151574
Harvey
Deitel
0136152503
Paul
Deitel
0131752421
Paul
Deitel
0132222205
Paul
Deitel
0132404168
Paul
Deitel
0136053033
Paul
Deitel
013605305X
Paul
Deitel
013605322X
Paul
Deitel
0136151574
Paul
Deitel
0136152503
Dan
Quirk
0136151574
Fig. 21.19 | Authors and ISBNs for their books in ascending
order by LastName and FirstName. (Part 2 of 2)
 2009 Pearson Education, Inc. All rights reserved.
40
21.4 SQL (Cont.)
21.4.5 INSERT Statement
• The INSERT statement inserts a row into a table:
INSERT INTO tableName ( columnName1, columnName2,…,
columnNameN )
VALUES ( value1, value2, … , valueN )
• The SQL keyword VALUES specifies values in the new
row.
• The values must match up with the columns specified after
the table name in both order and type.
 2009 Pearson Education, Inc. All rights reserved.
41
21.4 SQL (Cont.)
• The following INSERT statement inserts a row into
the Authors table:
INSERT INTO Authors ( FirstName, LastName )
VALUES ( 'Sue', 'Smith' )
• AuthorID is an identity column, so it is assigned
the next value in an autoincremented sequence.
 2009 Pearson Education, Inc. All rights reserved.
42
21.4 SQL (Cont.)
• Figure 21.20 shows the Authors table after the
INSERT operation.
AuthorID FirstName
LastName
1
Harvey
Deitel
2
Paul
Deitel
3
Greg
Ayer
4
Dan
Quirk
5
Sue
Smith
Fig. 21.20 | Table Authors after an INSERT operation.
 2009 Pearson Education, Inc. All rights reserved.
43
21.4 SQL (Cont.)
Common Programming Error 21.5
It is an error to specify a value for an identity column
in an INSERT statement.
Common Programming Error 21.6
To specify a string containing a single quote in a SQL
statement, there must be two single quotes in the position
where the single-quote character appears in the string
(e.g., 'O''Malley').
 2009 Pearson Education, Inc. All rights reserved.
44
21.4 SQL (Cont.)
21.4.6 UPDATE Statement
An UPDATE statement modifies data in a table:
UPDATE tableName
SET columnName1 = value1, columnName2 = value2, … , columnNameN =valueN
WHERE criteria
• The following UPDATE statement updates a row in the
Authors table.
UPDATE Authors
SET LastName = 'Jones‘
WHERE LastName = 'Smith' AND FirstName = 'Sue'
 2009 Pearson Education, Inc. All rights reserved.
45
21.4 SQL (Cont.)
• Figure 21.21 shows the Authors table after the UPDATE
operation has taken place.
AuthorID
FirstName
LastName
1
Harvey
Deitel
2
Paul
Deitel
3
Greg
Ayer
4
Dan
Quirk
5
Sue
Jones
Fig. 21.21 | Table Authors after an UPDATE operation.
 2009 Pearson Education, Inc. All rights reserved.
46
21.4 SQL (Cont.)
• Keyword AND is a logical operator that returns true if
and only if both of its operands are true.
• SQL also provides other logical operators, such as
OR and NOT.
 2009 Pearson Education, Inc. All rights reserved.
47
21.4 SQL (Cont.)
21.4.7 DELETE Statement
• A DELETE statement removes rows from a table:
DELETE FROM tableName WHERE criteria
• The following DELETE statement deletes the row
for Sue Jones.
DELETE FROM Authors
WHERE LastName = 'Jones' AND FirstName = 'Sue'
 2009 Pearson Education, Inc. All rights reserved.
48
21.4 SQL (Cont.)
• Figure 21.22 shows the Authors table after the DELETE
operation has taken place.
AuthorID
FirstName
LastName
1
Harvey
Deitel
2
Paul
Deitel
3
Greg
Ayer
4
Dan
Quirk
Fig. 21.22 | Table Authors after a DELETE operation.
 2009 Pearson Education, Inc. All rights reserved.
49
21.5 LINQ to SQL
• LINQ to SQL uses LINQ syntax to query databases.
• LINQ to SQL classes are automatically generated by
the IDE’s LINQ to SQL Designer.
• The IDE creates a class for each table, with a
property for each column in the table.
 2009 Pearson Education, Inc. All rights reserved.
50
21.5 LINQ to SQL (Cont.)
• A cache is a temporary store created for fast access
to data.
• LINQ to SQL caches all row objects that it creates,
making interacting with the database more efficient.
• This can reduce round trips to the database.
 2009 Pearson Education, Inc. All rights reserved.
51
21.5 LINQ to SQL (Cont.)
• LINQ queries on an IQueryable object are
processed together as a single SQL statement.
• If each query operator were handled separately,
multiple round trips to the database would be needed.
• A DataContext class controls the flow of data
between the program and the database.
• When cached objects have been changed, these changes
are saved using the DataContext’s
SubmitChanges method.
 2009 Pearson Education, Inc. All rights reserved.
21.6 LINQ to SQL: Extracting Information
from a Database
52
21.6.1 Creating LINQ to SQL Classes
• Create a new Windows Forms Application named
DisplayTable.
• Change the name of the source file to
DisplayTableForm.cs.
 2009 Pearson Education, Inc. All rights reserved.
21.6 LINQ to SQL: Extracting Information
from a Database (Cont.)
53
• Select Tools > Connect to Database….
• If the Choose Data Source dialog appears, select
Microsoft SQL Server Database File from the
Data source: ListBox.
• Click Continue to open the Add Connection dialog.
• Click Browse… and choose Books.mdf.
Error-Prevention Tip 21.1
SQL Server Express allows only one application at a time to
access a database file. Ensure that no other program is using
the database file before you attempt to add it to the project.
 2009 Pearson Education, Inc. All rights reserved.
21.6 LINQ to SQL: Extracting Information
from a Database (Cont.)
54
• Right click the project in the Solution Explorer and select
Add > New Item…
• Select LINQ to SQL classes, name the new item
Books.dbml and click the Add button.
• The Database Explorer window allows you navigate the
structure of databases.
• Drag the Authors, Titles and AuthorISBN tables onto the
Object Relational Designer and select Yes.
Error-Prevention Tip 21.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.
 2009 Pearson Education, Inc. All rights reserved.
21.6 LINQ to SQL: Extracting Information
from a Database (Cont.)
55
21.6.2 Creating Data Bindings
• Select Data > Add New Data Source…
to display the Data Source Configuration
Wizard.
• In the dialog, select Object and click Next >.
• Expand the tree view and select
DisplayTable > DisplayTable > Author.
• Click Next > then Finish. The Authors table in
the database is now a data source that can be used by
the bindings.
 2009 Pearson Education, Inc. All rights reserved.
21.6 LINQ to SQL: Extracting Information
from a Database (Cont.)
56
• Open the Data Sources window by selecting
Data > Show Data Sources.
• Open the DisplayTableForm in Design view.
• Click the Author node in the Data Sources
window—it should change to a drop-down list.
Ensure that the DataGridView option is selected.
 2009 Pearson Education, Inc. All rights reserved.
21.6 LINQ to SQL: Extracting Information
from a Database (Cont.)
57
• Drag the Author node from the Data Sources
window to the DisplayTableForm.
• The IDE creates a DataGridView with the correct
column names and a BindingNavigator.
 2009 Pearson Education, Inc. All rights reserved.
21.6 LINQ to SQL: Extracting Information
from a Database (Cont.)
58
• The BindingNavigator contains Buttons for
moving between entries, adding entries, deleting
entries and saving changes to the database.
• A BindingSource transfers data between the
data source and the data-bound controls on the
Form. (Fig. 21.23).
 2009 Pearson Education, Inc. All rights reserved.
21.6 LINQ to SQL: Extracting Information
from a Database (Cont.)
59
Component tray
Fig. 21.23 | Component tray holds nonvisual components in Design view.
 2009 Pearson Education, Inc. All rights reserved.
60
• Figure 21.24 shows the code needed to move data back
and forth between the database and GUI.
Outline
DisplayTable
Form.cs
( 1 of 3 )
1
// Fig. 21.24: DisplayTableForm.cs
2
// Displaying data from a database table in a DataGridView.
3
4
using System;
using System.Linq;
5
6
using System.Windows.Forms;
7
8
9
namespace DisplayTable
{
public partial class DisplayTableForm : Form
10
{
11
// constructor
12
13
14
public DisplayTableForm()
{
InitializeComponent();
15
16
} // end constructor
Fig. 21.24 | Component tray holds nonvisual components in
Design view. (Part 1 of 3. )
 2009 Pearson Education,
Inc. All rights reserved.
61
Outline
DisplayTable
Form.cs
17
18
// LINQ to SQL data context
private BooksDataContext database = new BooksDataContext();
19
20
// load data from database into DataGridView
21
22
private void DisplayTableForm_Load( object sender, EventArgs e )
{
23
24
25
26
27
// use LINQ to order the data for display
authorBindingSource.DataSource =
from author in database.Authors
orderby author.AuthorID
select author;
28
} // end method DisplayTableForm_Load
29
30
// click event handler for the Save Button in the
31
32
// BindingNavigator saves the changes made to the data
private void authorBindingNavigatorSaveItem_Click(
( 2 of 3 )
A DataContext object
allows the application to
interact with the database.
The BindingSource’s
DataSource property is set
to the results of a LINQ
query.
LINQ is used to extract data
from the Authors table in
the database.
Fig. 21.24 | Component tray holds nonvisual components in
Design view. (Part 2 of 3. )
 2009 Pearson Education,
Inc. All rights reserved.
62
Outline
object sender, EventArgs e )
33
34
35
36
37
38
{
Validate(); // validate input fields
authorBindingSource.EndEdit(); // indicate edits are complete
database.SubmitChanges(); // write changes to database file
} // end method authorBindingNavigatorSaveItem_Click
} // end class DisplayTableForm
39
40 } // end namespace DisplayTable
DisplayTable
Form.cs
( 3 of 3 )
First, all controls on the
form are validated.
EndEdit forces any
pending changes to be
saved.
SubmitChanges stores
any changes to the database.
Fig. 21.24 | Component tray holds nonvisual components in
Design view. (Part 3 of 3. )
 2009 Pearson Education,
Inc. All rights reserved.
21.6 LINQ to SQL: Extracting Information
from a Database (Cont.)
63
• Use the Properties window to set the save
button’s Enabled property to True.
• Saving the data back to the database is a three-step
process:
– First, all controls on the form are validated.
– EndEdit forces any pending changes to be saved.
– SubmitChanges stores any changes to the database.
 2009 Pearson Education, Inc. All rights reserved.
21.6 LINQ to SQL: Extracting Information
from a Database (Cont.)
64
• To persist changes between program executions,
select the database in the Solution Explorer
and set the Copy to Output Directory
property to Copy if newer.
• Run the application to verify that it works.
 2009 Pearson Education, Inc. All rights reserved.
21.7 More Complex LINQ Queries and
Data Binding
65
• Create a new Windows Forms Application named
DisplayQueryResult.
• Rename its C# file to
DisplayQueryResultForm.cs.
• Add the Books database to the project and generate
the LINQ to SQL classes.
 2009 Pearson Education, Inc. All rights reserved.
21.7 More Complex LINQ Queries and
Data Binding (Cont.)
66
• Create the data source and the DataGridView.
• Select the Title class as the data source, and drag the
Title node from the Data Sources window onto the
form.
• Leave the Form’s Design view open and add a
ComboBox named queriesComboBox below the
DataGridView on the Form.
• Open the String Collection Editor by clicking
the small arrowhead that appears in the upper-right
corner of the control and selecting Edit Items.
 2009 Pearson Education, Inc. All rights reserved.
21.7 More Complex LINQ Queries and
Data Binding (Cont.)
67
• Add the following three items to
queriesComboBox:
– All titles
– Titles with 2008 copyright
– Titles ending with "How to Program"
 2009 Pearson Education, Inc. All rights reserved.
68
Outline
• The application executes the appropriate query
when the user selects an item (Fig. 21.25).
1
// Fig. 21.25: DisplayQueryResultForm.cs
DisplayQuery
ResultForm.cs
2
3
// Displaying the result of a user-selected query in a DataGridView.
using System;
( 1 of 5)
4
using System.Linq;
5 using System.Windows.Forms;
6
7 namespace DisplayQueryResult
8 {
9
public partial class DisplayQueryResultForm : Form
10
{
11
12
// constructor
public DisplayQueryResultForm()
13
14
15
{
InitializeComponent();
} // end constructor
16
17
// LINQ to SQL data context
18
private BooksDataContext database = new BooksDataContext();
Declaring the
BooksDataContext.
19
Fig. 21.25 | Displaying the result of a user-selected query in a
DataGridView. (Part 1 of 5. )
 2009 Pearson Education,
Inc. All rights reserved.
69
Outline
20
21
22
23
24
25
// load data from database into DataGridView
private void DisplayQueryResultForm_Load(
object sender, EventArgs e )
{
( 2 of 5)
// write SQL to standard output stream
database.Log = Console.Out;
26
27
28
29
// set the ComboBox to show the default query that
// selects all books from the Titles table
queriesComboBox.SelectedIndex = 0;
30
31
} // end method DisplayQueryResultForm_Load
32
33
// Click event handler for the Save Button in the
// BindingNavigator saves the changes made to the data
34
35
private void titleBindingNavigatorSaveItem_Click(
object sender, EventArgs e )
36
37
{
38
39
40
DisplayQuery
ResultForm.cs
Setting the
BooksDataContext’s
Log property, where all
commands will be recorded.
Enable the save Button so
this event handler will
execute.
Validate(); // validate input fields
titleBindingSource.EndEdit(); // indicate edits are complete
database.SubmitChanges(); // write changes to database file
Fig. 21.25 | Displaying the result of a user-selected query in a
DataGridView. (Part 2 of 5. )
 2009 Pearson Education,
Inc. All rights reserved.
70
Outline
41
42
// when saving, return to "all titles" query
queriesComboBox.SelectedIndex = 0;
43
} // end method titleBindingNavigatorSaveItem_Click
44
45
// loads data into TitleBindingSource based on user-selected query
46
47
48
49
private void queriesComboBox_SelectedIndexChanged(
object sender, EventArgs e )
{
// set the data displayed according to what is selected
50
51
52
53
switch ( queriesComboBox.SelectedIndex )
{
case 0: // all titles
// use LINQ to order the books by title
54
55
56
57
titleBindingSource.DataSource =
from title in database.Titles
orderby title.BookTitle
select title;
58
59
60
break;
case 1: // titles with 2008 copyright
// use LINQ to get titles with 2008
DisplayQuery
ResultForm.cs
( 3 of 5)
Enable the save Button so
this event handler will
execute.
Fig. 21.25 | Displaying the result of a user-selected query in a
DataGridView. (Part 3 of 5. )
 2009 Pearson Education,
Inc. All rights reserved.
71
Outline
61
// copyright and sort them by title
62
63
64
65
titleBindingSource.DataSource =
from title in database.Titles
where title.Copyright == "2008"
orderby title.BookTitle
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
select title;
break;
case 2: // titles ending with "How to Program"
// use LINQ to get titles ending with
// "How to Program" and sort them by title
titleBindingSource.DataSource =
DisplayQuery
ResultForm.cs
( 4 of 5)
from title in database.Titles
where title.BookTitle.EndsWith( "How to Program" )
orderby title.BookTitle
select title;
break;
} // end switch
titleBindingSource.MoveFirst(); // move to first entry
} // end method queriesComboBox_SelectedIndexChanged
} // end class DisplayQueryResultForm
The MoveFirst method focuses
the first element each time a query
executes.
82 } // end namespace DisplayQueryResult
Fig. 21.25 | Displaying the result of a user-selected query in a
DataGridView. (Part 4 of 5. )
 2009 Pearson Education,
Inc. All rights reserved.
72
Outline
DisplayQuery
ResultForm.cs
( 5 of 5)
Fig. 21.25 | Displaying the result of a user-selected query in a
DataGridView. (Part 5 of 5. )
 2009 Pearson Education,
Inc. All rights reserved.
21.7 More Complex LINQ Queries and
Data Binding (Cont.)
73
• The DataContext object is set to log all queries to
Console.Out.
• The Output window can be opened by selecting
View > Output in the IDE (Fig. 21.26).
a) SQL generated by the All titles query.
Fig. 21.26 | Output window of the Display Query Result
application. (Part 1 of 2. )
 2009 Pearson Education, Inc. All rights reserved.
21.7 More Complex LINQ Queries and
Data Binding (Cont.)
74
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. (Part 2 of 2. )
 2009 Pearson Education, Inc. All rights reserved.
75
Outline
• Figure 21.27 uses LINQ to SQL to combine and
organize data from multiple tables.
JoiningTest.cs
( 1 of 7 )
1
// Fig. 21.27: JoiningTest.cs
2
3
4
// Using LINQ to perform a join and aggregate data across tables.
using System;
using System.Linq;
5
6
7
8
9
10
11
12
13
14
namespace JoiningWithLINQ
{
public class JoiningTest
{
public static void Main( string[] args )
{
// create database connection
BooksDataContext database = new BooksDataContext();
Fig. 21.27 | Using LINQ to perform a join and aggregate
data across tables. (Part 1 of 7. )
 2009 Pearson Education,
Inc. All rights reserved.
76
Outline
15
16
// get authors and ISBNs of each book they co-authored
var authorsAndISBNs =
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
JoiningTest.cs
from author in database.Authors
join book in database.AuthorISBNs
on author.AuthorID equals book.AuthorID
orderby author.LastName, author.FirstName
17
18
19
( 2 of 7 )
select new { author.FirstName, author.LastName, book.ISBN };
Console.WriteLine( "Authors and ISBNs:" ); // display header
// display authors and ISBNs in tabular format
foreach ( var element in authorsAndISBNs )
Using LINQ’s join clause to
combine data from multiple
tables.
{
Console.WriteLine( "\t{0,-10} {1,-10} {2,-10}",
element.FirstName, element.LastName, element.ISBN );
} // end foreach
// get authors and titles of each book they co-authored
var authorsAndTitles =
from title in database.Titles
from book in title.AuthorISBNs
let author = book.Author
orderby author.LastName, author.FirstName, title.BookTitle
Fig. 21.27 | Using LINQ to perform a join and aggregate
data across tables. (Part 2 of 7. )
Using LINQ to
SQL properties
to access related
rows in other
tables.
 2009 Pearson Education,
Inc. All rights reserved.
77
Outline
38
39
40
41
42
43
select new { author.FirstName, author.LastName,
title.BookTitle };
JoiningTest.cs
Console.WriteLine( "\nAuthors and titles:" ); // header
( 3 of 7 )
// display authors and titles in tabular format
44
45
46
47
foreach ( var element in authorsAndTitles )
{
Console.WriteLine( "\t{0,-10} {1,-10} {2}",
element.FirstName, element.LastName, element.BookTitle );
48
} // end foreach
49
50
// get authors and titles of each book
51
52
53
54
// they co-authored; group by author
var titlesByAuthor =
from author in database.Authors
orderby author.LastName, author.FirstName
55
56
57
58
let name = author.FirstName + " " + author.LastName
let titles =
from book in author.AuthorISBNs
orderby book.Title.BookTitle
59
60
select book.Title.BookTitle
select new { Name = name, Titles = titles };
Fig. 21.27 | Using LINQ to perform a join and aggregate
data across tables. (Part 3 of 7. )
Using LINQ to
SQL properties
to access related
rows in other
tables.
Using LINQ to return
hierarchical results
through a nested
query in the second
let clause.
 2009 Pearson Education,
Inc. All rights reserved.
78
Outline
JoiningTest.cs
61
62
63
Console.WriteLine( "\nTitles grouped by author:" ); // header
64
65
// display titles written by each author, grouped by author
foreach ( var author in titlesByAuthor )
66
67
68
{
69
70
71
72
73
74
( 4 of 7 )
// display author's name
Console.WriteLine( "\t" + author.Name + ":" );
// display titles written by that author
foreach ( var title in author.Titles )
{
Console.WriteLine( "\t\t" + title );
} // end inner foreach
75
} // end outer foreach
76
} // end Main
77
} // end class JoiningTest
78 } // end namespace JoiningWithLINQ
Fig. 21.27 | Using LINQ to perform a join and aggregate
data across tables. (Part 4 of 7. )
 2009 Pearson Education,
Inc. All rights reserved.
79
Outline
JoiningTest.cs
Authors and ISBNs:
Greg
Ayer
0136053033
Harvey
Deitel
0131752421
Harvey
Deitel
0132222205
Harvey
Deitel
0132404168
Harvey
Deitel
0136053033
Harvey
Deitel
013605305X
Harvey
Deitel
013605322X
Harvey
Deitel
0136151574
Harvey
Deitel
0136152503
Paul
Deitel
0131752421
Paul
Deitel
0132222205
Paul
Deitel
0132404168
Paul
Paul
Paul
Paul
Paul
Dan
Deitel
Deitel
Deitel
Deitel
Deitel
Quirk
0136053033
013605305X
013605322X
0136151574
0136152503
0136151574
( 5 of 7 )
(continued on next page...)
Fig. 21.27 | Using LINQ to perform a join and aggregate
data across tables. (Part 5 of 7. )
 2009 Pearson Education,
Inc. All rights reserved.
80
Outline
(continued from previous page…)
Authors and titles:
Greg
Ayer
Harvey
Deitel
Harvey
Deitel
Harvey
Deitel
Harvey
Deitel
Harvey
Deitel
Harvey
Deitel
Harvey
Deitel
Harvey
Deitel
Paul
Deitel
Paul
Deitel
Paul
Deitel
Paul
Deitel
Paul
Deitel
Paul
Deitel
Paul
Deitel
Paul
Deitel
Dan
Quirk
Simply Visual Basic 2008
C How to Program
C++ How to Program
Internet & World Wide Web How to Program
Java How to Program
Simply Visual Basic 2008
Visual Basic 2008 How to Program
Visual C# 2008 How to Program
Visual C++ 2008 How to Program
C How to Program
C++ How to Program
Internet & World Wide Web How to Program
Java How to Program
Simply Visual Basic 2008
Visual Basic 2008 How to Program
Visual C# 2008 How to Program
Visual C++ 2008 How to Program
Visual C++ 2008 How to Program
(continued on next page...)
JoiningTest.cs
( 6 of 7 )
Fig. 21.27 | Using LINQ to perform a join and aggregate
data across tables. (Part 6 of 7. )
 2009 Pearson Education,
Inc. All rights reserved.
81
Outline
(continued from previous page…)
Titles grouped by author:
Greg Ayer:
Simply Visual Basic 2008
Harvey Deitel:
C How to Program
C++ How to Program
Internet & World Wide Web How to Program
Java How to Program
Simply Visual Basic 2008
Visual Basic 2008 How to Program
Visual C# 2008 How to Program
Visual C++ 2008 How to Program
Paul Deitel:
C How to Program
C++ How to Program
Internet & World Wide Web How to Program
Java How to Program
Simply Visual Basic 2008
Visual Basic 2008 How to Program
Visual C# 2008 How to Program
Visual C++ 2008 How to Program
Dan Quirk:
Visual C++ 2008 How to Program
JoiningTest.cs
( 7 of 7 )
Fig. 21.27 | Using LINQ to perform a join and aggregate
data across tables. (Part 7 of 7. )
 2009 Pearson Education,
Inc. All rights reserved.
82
• Figure 21.28 demonstrates a master/detail view—one
part of the interface allows you to select an entry, and
another part displays detailed information about that
entry.
1
// Fig. 21.28: MasterDetailForm.cs
2
// Using a DataGridView to display details based on a selection.
3
4
using System;
using System.Linq;
5
6
using System.Windows.Forms;
Outline
MasterDetail
Form.cs
( 1 of 6 )
7 namespace MasterDetail
8 {
9
public partial class MasterDetailForm : Form
10
{
11
12
13
public MasterDetailForm()
{
InitializeComponent();
14
15
} // end constructor
16
// connection to database
17
18
private BooksDataContext database = new BooksDataContext();
19
20
// this class helps us display each author's first
// and last name in the authors drop-down list
Fig. 21.28 | Using a DataGridView to display details based on a
selection. (Part 1 of 6.)
 2009 Pearson Education,
Inc. All rights reserved.
83
Outline
21
22
private class AuthorBinding
{
23
24
25
26
public Author Author { get; set; } // contained Author object
public string Name { get; set; } // author's full name
} // end class AuthorBinding
27
28
29
30
// initialize data sources when the Form is loaded
private void MasterDetailForm_Load( object sender, EventArgs e )
31
32
33
34
{
( 2 of 6 )
The ComboBox’s
DisplayMember
property is set to
"Name".
// display AuthorBinding.Name
authorComboBox.DisplayMember = "Name";
// set authorComboBox's DataSource to the list of authors
authorComboBox.DataSource =
35
36
from author in database.Authors
orderby author.LastName, author.FirstName
37
38
let name = author.FirstName + " " + author.LastName
select new AuthorBinding { Author = author, Name = name };
39
40
41
MasterDetail
Form.cs
// display Title.BookTitle
titleComboBox.DisplayMember = "BookTitle";
Creating an
AuthorBinding
object for each
author as the
ComboBox’s
DataSource.
The text in the ComboBox is
retrieved from the BookTitle
property.
Fig. 21.28 | Using a DataGridView to display details based on a
selection. (Part 2 of 6.)
 2009 Pearson Education,
Inc. All rights reserved.
84
Outline
42
43
// set titleComboBox's DataSource to the list of titles
44
45
titleComboBox.DataSource =
from title in database.Titles
46
orderby title.BookTitle
47
48
49
50
select title;
// initially, display no "detail" data
booksBindingSource.DataSource = null;
51
52
53
// set the DataSource of the DataGridView to the BindingSource
booksDataGridView.DataSource = booksBindingSource;
54
55
56
57
62
63
( 3 of 6 )
Creating the
DataSource for
titleComboBox.
} // end method MasterDetailForm_Load
// display titles that were co-authored by the selected author
private void authorComboBox_SelectedIndexChanged(
object sender, EventArgs e )
58
59
60
61
MasterDetail
Form.cs
{
// get the selected Author object from the ComboBox
Author currentAuthor =
( ( AuthorBinding ) authorComboBox.SelectedItem ).Author;
Retrieving the
selected Author
and using LINQ to
retrieve related
Titles.
Fig. 21.28 | Using a DataGridView to display details based on a
selection. (Part 3 of 6.)
 2009 Pearson Education,
Inc. All rights reserved.
85
Outline
64
// set booksBindingSource's DataSource to the
65
// list of titles written by the selected author
66
67
booksBindingSource.DataSource =
from book in currentAuthor.AuthorISBNs
select book.Title;
68
69
70
71
} // end method authorComboBox_SelectedIndexChanged
72
private void titleComboBox_SelectedIndexChanged(
object sender, EventArgs e )
{
// get the selected Title object from the ComboBox
76
Title currentTitle = ( Title ) titleComboBox.SelectedItem;
77
78
79
// set booksBindingSource's DataSource to the
// list of authors for the selected title
80
booksBindingSource.DataSource =
81
82
83
( 4 of 6 )
// display the authors of the selected title
73
74
75
MasterDetail
Form.cs
Retrieving the
selected Author
and using LINQ to
retrieve related
Titles.
from book in currentTitle.AuthorISBNs
select book.Author;
} // end method titleComboBox_SelectedIndexChanged
84
} // end class MasterDetailForm
85 } // end namespace MasterDetail
Fig. 21.28 | Using a DataGridView to display details based on a
selection. (Part 4 of 6.)
 2009 Pearson Education,
Inc. All rights reserved.
86
Outline
a) Master/Detail application when it begins execution
MasterDetail
Form.cs
( 5 of 6 )
Fig. 21.28 | Using a DataGridView to display details based on a
selection. (Part 5 of 6.)
 2009 Pearson Education,
Inc. All rights reserved.
87
Outline
b) Select Paul Deitel
from the Author:
drop-down list to
view books he has
co-authored
MasterDetail
Form.cs
( 6 of 6 )
c) Select Simply
Visual Basic 2008
from the Title: drop-
down to view the
authors who wrote
that book
Fig. 21.28 | Using a DataGridView to display details based on a
selection. (Part 6 of 6.)
 2009 Pearson Education,
Inc. All rights reserved.
21.9 Creating a Master/Detail View
Application
88
• Create a new Windows Forms Application called
MasterDetail.
• Add the Books database and create the LINQ to
SQL classes.
 2009 Pearson Education, Inc. All rights reserved.
21.9 Creating a Master/Detail View
Application (Cont.)
89
• Add two Labels and two ComboBoxes, positioned as shown in
Fig. 21.29.
• Create a DataGridView called booksDataGridView and set
its ReadOnly property to True using the Properties window.
Fig. 21.29 | Finished design of MasterDetail application.
 2009 Pearson Education, Inc. All rights reserved.
90
Outline
• The AddressBook application (Fig. 21.30)
provides a GUI for querying the database with
LINQ.
AddressBookForm
.cs
( 1 of 5 )
1
// Fig. 21.30: AddressBookForm.cs
2
// Manipulating an address book.
3
4
using System;
using System.Linq;
5
using System.Windows.Forms;
6
7 namespace AddressBook
8 {
9
public partial class AddressBookForm : Form
10
{
11
12
13
public AddressBookForm()
{
InitializeComponent();
14
15
} // end constructor
Fig. 21.30 | Manipulating an address book. (Part 1 of 5.)
 2009 Pearson Education,
Inc. All rights reserved.
91
Outline
16
17
// LINQ to SQL data context
private AddressBookDataContext database =
new AddressBookDataContext();
18
19
20
// fill our addressBindingSource with all rows, ordered by name
21
private void BindDefault()
22
23
{
24
25
26
27
// use LINQ to create a data source from the database
addressBindingSource.DataSource =
from address in database.Addresses
orderby address.LastName, address.FirstName
select address;
28
29
addressBindingSource.MoveFirst(); // go to the first result
30
findTextBox.Clear(); // clear the Find TextBox
31
32
33
34
AddressBookForm
.cs
( 2 of 5 )
The BindDefault
method sets the
AddressBinding
Source’s
DataSource
property to the result
of a LINQ query.
} // end method BindDefault
private void AddressBookForm_Load( object sender, EventArgs e )
{
Fig. 21.30 | Manipulating an address book. (Part 2 of 5.)
 2009 Pearson Education,
Inc. All rights reserved.
92
Outline
35
BindDefault(); // fill binding with data from database
36
37
} // end method AddressBookForm_Load
38
39
40
41
42
43
44
// Click event handler for the Save Button in the
// BindingNavigator saves the changes made to the data
private void addressBindingNavigatorSaveItem_Click(
object sender, EventArgs e )
{
Validate(); // validate input fields
addressBindingSource.EndEdit(); // indicate edits are complete
45
46
database.SubmitChanges(); // write changes to database file
47
BindDefault(); // change back to initial unfiltered data on save
48
49
} // end method addressBindingNavigatorSaveItem_Click
50
51
// load LINQ to create a data source that contains
// only people with the specified last name
52
private void findButton_Click( object sender, EventArgs e )
AddressBookForm
.cs
( 3 of 5 )
Data is set to be
displayed when the
application starts.
Event handler for the
BindingNavigator’s
save Button.
Fig. 21.30 | Manipulating an address book. (Part 3 of 5.)
 2009 Pearson Education,
Inc. All rights reserved.
93
Outline
53
54
55
// use LINQ to create a data source that contains
// only people with the specified last name
56
57
addressBindingSource.DataSource =
from address in database.Addresses
58
59
60
61
62
63
AddressBookForm
.cs
{
where address.LastName == findTextBox.Text
orderby address.LastName, address.FirstName
select address;
( 4 of 5 )
Doing a search changes the
DataSource to a set of
matches.
addressBindingSource.MoveFirst(); // go to first result
} // end method findButton_Click
64
65
66
67
68
69
private void browseButton_Click( object sender, EventArgs e )
{
BindDefault(); // change back to initial unfiltered data
} // end method browseButton_Click
} // end class AddressBookForm
70 } // end namespace AddressBook
Fig. 21.30 | Manipulating an address book. (Part 4 of 5.)
 2009 Pearson Education,
Inc. All rights reserved.
94
Outline
a) AddressBook application after adding four entries.
b) Searching for a specific last name.
AddressBookForm
.cs
( 5 of 5 )
c) Use the Browse All
Entries Button to
view all people in the
address book.
Fig. 21.30 | Manipulating an address book. (Part 5 of 5.)
 2009 Pearson Education,
Inc. All rights reserved.
21.10 Programming with LINQ to SQL:
Address-Book Case Study
95
• Create a new Windows Forms Application named
AddressBook.
• Add the AddressBook.mdf database and name
the file AddressBook.dbml.
• You must also add the Addresses table as a data
source.
 2009 Pearson Education, Inc. All rights reserved.
21.10 Programming with LINQ to SQL:
Address-Book Case Study (Cont.)
96
• Click the Address node in the Data Sources
window. Click the down arrow to view the items in
the list.
– Select the Details option to indicate that the IDE should create
a set of Label/TextBox pairs.
• Drag the Address node from the Data Sources
window to the Form.
 2009 Pearson Education, Inc. All rights reserved.
21.10 Programming with LINQ to SQL:
Address-Book Case Study (Cont.)
97
• The AddressID is an autoincremented identity
column, so set its ReadOnly property to True.
• The database in this example is initially empty, so
you’ll need to add several records before testing the
find capability.
• To add search functionality, we create controls to
allow the user to enter a last name.
• When you enter a last name and click Find, the
BindingNavigator is restricted to matches
because the data source is changed.
 2009 Pearson Education, Inc. All rights reserved.
21.10 Programming with LINQ to SQL:
Address-Book Case Study (Cont.)
98
• The IDE sets the TextBox’s Text with the
DataBindings.Text property.
• Click the plus sign next to (DataBindings) in
the Properties window.
• Clicking the drop-down list (as in Fig. 21.31) allows
you to choose a BindingSource object and a
property to bind.
 2009 Pearson Education, Inc. All rights reserved.
21.10 Programming with LINQ to SQL:
Address-Book Case Study (Cont.)
99
Fig. 21.31 | Data bindings for firstNameTextBox in the AddressBook application .
 2009 Pearson Education, Inc. All rights reserved.