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 |
It is a capital mistake to theorize before one has data. | ||
--Arthur Conan Doyle |
In this chapter you’ll learn:
<objective>The relational database model.
</objective> <objective>To use LINQ to retrieve and manipulate data from a database.
</objective> <objective>To add data sources to projects.
</objective> <objective>To use the Object Relational Designer to create LINQ to SQL classes.
</objective> <objective>To use the IDE’s drag-and-drop capabilities to display database tables in applications.
</objective> <objective>To use data binding to move data seamlessly between GUI controls and databases.
</objective> <objective>To create Master/Detail views that enable you to select a record and display its details.
</objective> </feature><feature> <supertitle>Outline</supertitle> </feature>A database is an organized collection of data. A database management system (DBMS) provides mechanisms for storing, organizing, retrieving and modifying data. Today’s most popular DBMSs manage relational databases, which organize data simply as tables with rows and columns.
Some popular proprietary DBMSs are Microsoft SQL Server, Oracle, Sybase and IBM DB2. PostgreSQL and MySQL are popular open-source DBMSs that can be downloaded and used freely by anyone. In this chapter, we use Microsoft’s free SQL Server Express, which is installed with Visual C# Express and Visual Studio. It can also be downloaded separately from Microsoft (www.microsoft.com/express/sql).
SQL Server Express provides many features of Microsoft’s full (fee-based) SQL Server product, but has some limitations, such as a maximum database size. A SQL Server Express database can be easily migrated to a full version of SQL Server—we did this with our deitel.com website once our database became too large for SQL Server Express. You can learn more about the SQL Server versions at bit.ly/SQLServerEditions
.
Today’s most popular database systems are relational databases. A language called Structured Query Language (SQL)—pronounced “sequel”—is an international standard used with relational databases to perform queries (that is, to request information that satisfies given criteria) and to manipulate data. For years, programs that accessed a relational database passed SQL queries as String
s to the database management system, then processed the results.
A logical extension of querying and manipulating data in databases is to perform similar operations on any sources of data, such as arrays, collections (like the Items
collection of a ListBox
) and files. Chapter 9 introduced LINQ to Objects and used it to to manipulate data stored in arrays. LINQ to SQL allows you to manipulate data stored in a SQL Server or SQL Server Express relational database. The SQL in LINQ to SQL stands for SQL Server, not Structured Query Language. As with LINQ to Objects, the IDE provides Intelli-Sense for your LINQ to SQL queries.
This chapter introduces general concepts of relational databases, then explores LINQ to SQL and the IDE’s tools for working with databases. In later chapters, you’ll see other practical database and LINQ to SQL applications, such as a web-based bookstore and a web-based airline reservation service. Databases are at the heart of almost all “industrial strength” applications.
[Note: In previous editions of this book, this chapter included an introduction to Structured Query Language (SQL). We now perform all of the database interactions in this chapter using LINQ, so we’ve moved the introduction to SQL to this book’s website at www.deitel.com/books/csharphtp4/.]
A relational database organizes data simply in tables. Figure 18.1 illustrates a sample Employees
table that might be used in a personnel system. The table stores the attributes of employees. Tables are composed of rows (also called records) and columns (also called fields) in which values are stored. This table consists of six rows (one per employee) and five columns (one per attribute). The attributes are the employee’s ID, name, department, salary and location. The ID
column of each row is the table’s primary key—a column (or group of columns) requiring a unique value that cannot be duplicated in other rows. This guarantees that each primary key value can be used to identify one row. A primary key composed of two or more columns is known as a composite key. Good examples of primary-key columns in other applications are a book’s ISBN number in a book information system or a part number in an inventory system—values in each of these columns must be unique. LINQ to SQL requires every table to have a primary key to support updating the data in tables. The rows in Fig. 18.1 are displayed in ascending order by primary key. But they could be listed in decreasing (descending) order or in no particular order at all.
Each column represents a different data attribute. Some column values may be duplicated between rows. For example, three different rows in the Employees
table’s Department
column contain the number 413, indicating that these employees work in the same department.
You can use LINQ to SQL to define queries that select subsets of the data from a table. For example, a program might select data from the Employees
table to create a query result that shows where each department is located, in increasing order by Department
number (Fig. 18.2).
We now consider a simple Books
database that stores information about some Deitel publications. First, we overview the database’s tables. A database’s tables, their fields and the relationships among them are collectively known as a database schema. LINQ to SQL uses a database’s schema to define classes that enable you to interact with the database. Next, we show how to use LINQ to SQL to retrieve information from the Books
database. The database file—Books.mdf
—is provided with this chapter’s examples. SQL Server database files have the .mdf
(“master data file”) file-name extension.
The database consists of three tables: Authors
, Titles
and AuthorISBN
. The Authors
table (described in Fig. 18.3) consists of three columns that maintain each author’s unique ID number, first name and last name, respectively. Figure 18.4 contains the data from the Authors
table.
Table 18.3. Authors
table of the Books
database.
The Titles
table (described in Fig. 18.5) consists of four columns that maintain information about each book in the database, including its ISBN, title, edition number and copyright year. Figure 18.6 contains the data from the Titles
table.
Table 18.5. Titles
table of the Books
database.
Column | Description |
---|---|
| ISBN of the book (a string). The table’s primary key. ISBN is an abbreviation for “International Standard Book Number”—a numbering scheme that publishers worldwide use to give every book a unique identification number. |
| Title of the book (a string). |
| Edition number of the book (an integer). |
| Copyright year of the book (a string). |
Table 18.6. Data from the Titles
table of the Books
database.
|
|
|
|
---|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
The AuthorISBN
table (described in Fig. 18.7) consists of two columns that maintain ISBNs for each book and their corresponding authors’ ID numbers. This table associates authors with their books. The AuthorID
column is a foreign key—a column in this table that matches the primary-key column in another table (that is, AuthorID
in the Authors
table). The ISBN
column is also a foreign key—it matches the primary-key column (that is, ISBN
) in the Titles
table. Together the AuthorID
and ISBN
columns in this table form a composite primary key. Every row in this table uniquely matches one author to one book’s ISBN. Figure 18.8 contains the data from the AuthorISBN
table of the Books
database.
A database might consist of many tables. A goal when designing a database is to minimize the amount of duplicated data among the database’s tables. Foreign keys, which are specified when a database table is created, link the data in multiple tables.
Every foreign-key value must appear as another table’s primary-key value so the DBMS can ensure that the foreign key value is valid. For example, the DBMS ensures that the AuthorID
value for a particular row of the AuthorISBN
table (Fig. 18.8) is valid by checking that there is a row in the Authors
table with that AuthorID
as the primary key.
Foreign keys also allow related data in multiple tables to be selected from those tables—this is known as joining the data. There is a one-to-many relationship between a primary key and a corresponding foreign key (for example, one author can write many books and one book can be written by many authors). This means that a foreign key can appear many times in its own table but only once (as the primary key) in another table. For example, the ISBN 0131450913
can appear in several rows of AuthorISBN
(because this book has several authors) but only once in Titles
, where ISBN
is the primary key.
Figure 18.9 is an entity-relationship (ER) diagram for the Books
database. This diagram shows the tables in the database and the relationships among them. The first compartment in each box contains the table’s name. The names in italic font are primary keys—AuthorID
in the Authors
table, AuthorID
and ISBN
in the AuthorISBN
table, and ISBN
in the Titles
table. Every row must have a value in the primary-key column (or group of columns), and the value of the key must be unique in the table; otherwise, the DBMS will report an error. The names AuthorID
and ISBN
in the AuthorISBN
table are both italic—together these form a composite primary key for the AuthorISBN
table.
The lines connecting the tables in Fig. 18.9 represent the relationships among the tables. Consider the line between the Authors
and AuthorISBN
tables. On the Authors
end of the line, there’s a 1
, and on the AuthorISBN
end, an infinity symbol (∞). This indicates a one-to-many relationship—for each author in the Authors
table, there can be an arbitrary number of ISBNs for books written by that author in the AuthorISBN
table (that is, an author can write any number of books). Note that the relationship line links the AuthorID
column in the Authors
table (where AuthorID
is the primary key) to the AuthorID
column in the AuthorISBN
table (where AuthorID
is a foreign key)—the line between the tables links the primary key to the matching foreign key.
The line between the Titles
and AuthorISBN
tables illustrates a one-to-many relationship—one book can be written by many authors. Note that the line between the tables links the primary key ISBN
in table Titles
to the corresponding foreign key in table AuthorISBN
. The relationships in Fig. 18.9 illustrate that the sole purpose of the AuthorISBN
table is to provide a many-to-many relationship between the Authors
and Titles
tables—an author can write many books, and a book can have many authors.
LINQ to SQL enables you to access data in SQL Server databases using the same LINQ syntax introduced in Chapter 9. You interact with the database via classes that are automatically generated from the database schema by the IDE’s LINQ to SQL Designer. For each table in the database, the IDE creates two classes:
A class that represents a row of the table: This class contains properties for each column in the table. LINQ to SQL creates objects of this class—called row objects—to store the data from individual rows of the table.
A class that represents the table: LINQ to SQL creates an object of this class to store a collection of row objects that correspond to all of the rows in the table.
Relationships between tables are also taken into account in the generated classes:
In a row object’s class, an additional property is created for each foreign key. This property returns the row object of the corresponding primary key in another table. For example, the class that represents the rows of the Books
database’s AuthorISBN
table also contains an Author
property and a Title
property—from any AuthorISBN
row object, you can access the full author and title information.
In the class for a row object, an additional property is created for the collection of row objects with foreign-keys that reference the row object’s primary key. For example, the LINQ to SQL class that represents the rows of the Books
database’s Authors
table contains an AuthorISBNs
property that you can use to get all of the books written by that author. The IDE automatically adds the “s
” to “AuthorISBN
” to indicate that this property represents a collection of AuthorISBN
objects. Similarly, the LINQ to SQL class that represents the rows of the Titles
table also contains an AuthorISBNs
property that you can use to get all of the co-authors of a particular title.
Once generated, the LINQ to SQL classes have full IntelliSense support in the IDE. Section 18.7 demonstrates queries that use the relationships among the Books
database’s tables to join data.
LINQ to SQL works through the IQueryable
interface, which inherits from the IEnumerable
interface introduced in Chapter 9. When a LINQ to SQL query on an IQueryable
object executes against the database, the results are loaded into objects of the corresponding LINQ to SQL classes for convenient access in your code.
All LINQ to SQL queries occur via a DataContext
class, which controls the flow of data between the program and the database. A specific DataContext
derived class, which inherits from the class System.Data.Linq.DataContext
, is created when the LINQ to SQL classes representing each row of the table are generated by the IDE. This derived class has properties for each table in the database, which can be used as data sources in LINQ queries. Any changes made to the DataContext
can be saved back to the database using the DataContext
’s SubmitChanges
method, so with LINQ to SQL you can modify the database’s contents.
In this section, we demonstrate how to connect to a database, query it and display the results of the query. There is little code in this section—the IDE provides visual programming tools and wizards that simplify accessing data in applications. These tools establish database connections and create the objects necessary to view and manipulate the data through Windows Form
s GUI controls—a technique known as data binding.
Our first example performs a simple query on the Books
database from Section 18.3. We retrieve the entire Authors
table and use data binding to display its data in a DataGridView
—a control from namespace System.Windows.Forms
that can display data from a data source in tabular format. The basic steps we’ll perform are:
Connect to the Books
database.
Create the LINQ to SQL classes required to use the database.
Add the Authors
table as a data source.
Drag the Authors
table data source onto the Design view to create a GUI for displaying the table’s data.
Add a few statements to the program to allow it to interact with the database.
The GUI for the program is shown in Fig. 18.10. All of the controls in this GUI are automatically generated when we drag a data source that represents the Authors
table onto the Form
in Design view. The BindingNavigator
at the top of the window is a collection of controls that allow you to navigate through the records in the DataGridView
that fills the rest of the window. The BindingNavigator
controls also allow you to add records, delete records and save your changes to the database. If you add a new record, note that empty values are not allowed in the Books
database, so attempting to save a new record without specifying a value for each field will cause an error.
This section presents the steps required to create LINQ to SQL classes for a database.
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.
In Visual C# 2010 Express, select View > Other Windows > Database Explorer to display the Database Explorer window. By default, it appears on the left side of the IDE. If you’re using a full version of Visual Studio, select View > Server Explorer to display the Server Explorer. From this point forward, we’ll refer to the Database Explorer. If you have a full version of Visual Studio, substitute Server Explorer for Database Explorer in the steps.
Click the Connect to Database icon() at the top of the Database Explorer. If the Choose Data Source dialog appears (Fig. 18.11), select Microsoft SQL Server Database File from the Data source: list. If you check the Always use this selection CheckBox
, the IDE will use this type of database file by default when you connect to databases in the future. Click Continue to display the Add Connection dialog.
In the Add Connection dialog (Fig. 18.12), the Data source: TextBox
reflects your selection from the Choose Data Source dialog. You can click the Change... Button
to select a different type of database. Next, click Browse... 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.
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.
Right click the project name in the Solution Explorer and select Add > New Item... to display the Add New Item dialog. Select the LINQ to SQL Classes template, name the new item Books.dbml
and click the Add button. The Object Relational Designer window will appear (Fig. 18.13). You can also double click the Books.dbml
file in the Solution Explorer to open the Object Relational Designer.
Expand the Books.mdf
database node in the Database Explorer, then expand the Tables node. Drag the Authors
, Titles
and AuthorISBN
tables onto the Object Relational Designer. The IDE prompts whether you want to copy the database to the project directory. Select Yes. The Object Relational Designer will display the tables that you dragged from the Database Explorer (Fig. 18.14). Notice that the Object Relational Designer named the class that represents items from the Authors
table as Author
, and named the class that represents the Titles
table as Title
. This is because one object of the Author
class represents one author—a single row from the Authors
table. Similarly, one object of the Title
class represents one book—a single row from the Titles
table. Because the class name Title
conflicts with one of the column names in the Titles
table, the IDE renames that column’s property in the Title
class as Title1
.
Save the Books.dbml
file.
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.
The IDE’s automatic data binding capabilities simplify creating applications that can view and modify the data in a database. You must write a small amount of code to enable the autogenerated data-binding classes to interact with the autogenerated LINQ to SQL classes. You’ll now perform the steps to display the contents of the Authors
table in a GUI.
To use the LINQ to SQL classes for data binding, you must first add them as a data source.
Select Data > Add New Data Source... to display the Data Source Configuration Wizard.
The LINQ to SQL 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. 18.15 and ensure that Author is checked. An object of this class will be used as the data source.
Click Finish.
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.
Next, you’ll use the Design view to create a GUI control that can display the Authors
table’s data.
Switch to Design view for the DisplayAuthorsTable
class.
Click the Author node in the Data Sources window—it should change to a dropdown 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.
Drag the Author node from the Data Sources window onto the Form
in Design view.
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
.
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.
Now that you’ve seen how to display an entire database table in a DataGridView
, we show how to perform several different queries and display the results in a DataGridView
. The Display Query Results application (Fig. 18.19) allows the user to select a query from the ComboBox
at the bottom of the window, then displays the results of the query.
Perform the following steps to build the Display Query Results application’s GUI.
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:
Next you must write code that executes the appropriate query each time the user chooses a different item from queriesComboBox
. Double click queriesComboBox
in Design view to generate a queriesComboBox_SelectedIndexChanged
event handler (Fig. 18.20, lines 44–78) in the TitleQueries.cs
file. In the event handler, add a switch
statement (lines 48–75) to change the titleBindingSource
’s DataSource
property to a LINQ query that returns the correct set of data. The data bindings created by the IDE automatically update the titleDataGridView
each time we change its DataSource
. The MoveFirst
method of the BindingSource
(line 77) moves to the first row of the result each time a query executes. The results of the queries in lines 53–55, 61–64 and 70–73 are shown in Fig. 18.19(a), (b) and (c), respectively. [Note: As we mentioned previously, in the generated LINQ to SQL classes, the IDE renamed the Title
column of the Titles
table as Title1
to avoid a naming conflict with the class Title.]
Example 18.20. Displaying the result of a user-selected query in a DataGridView
.
1 // Fig. 18.20: TitleQueries.cs 2 // Displaying the result of a user-selected query in a DataGridView. 3 using System; 4 using System.Linq; 5 using System.Windows.Forms; 6 7 namespace DisplayQueryResult 8 { 9 public partial class TitleQueries : Form 10 { 11 public TitleQueries() 12 { 13 InitializeComponent(); 14 } // end constructor 15 16 // LINQ to SQL data context 17 private BooksDataContext database = new BooksDataContext(); 18 19 // load data from database into DataGridView 20 private void TitleQueries_Load( object sender, EventArgs e ) 21 { 22 // write SQL to standard output stream 23 database.Log = Console.Out; 24 25 // set the ComboBox to show the default query that 26 // selects all books from the Titles table 27 queriesComboBox.SelectedIndex = 0; 28 } // end method TitleQueries_Load 29 30 // Click event handler for the Save Button in the 31 // BindingNavigator saves the changes made to the data 32 private void titleBindingNavigatorSaveItem_Click( 33 object sender, EventArgs e ) 34 { 35 Validate(); // validate input fields 36 titleBindingSource.EndEdit(); // indicate edits are complete 37 database.SubmitChanges(); // write changes to database file 38 39 // when saving, return to "all titles" query 40 queriesComboBox.SelectedIndex = 0; 41 } // end method titleBindingNavigatorSaveItem_Click 42 43 // loads data into titleBindingSource based on user-selected query 44 private void queriesComboBox_SelectedIndexChanged( 45 object sender, EventArgs e ) 46 { 47 // set the data displayed according to what is selected 48 switch ( queriesComboBox.SelectedIndex ) 49 { 50 case 0: // all titles 51 // use LINQ to order the books by title 52 titleBindingSource.DataSource = 53 from book in database.Titles 54 orderby book.Title1 55 select book; 56 break; 57 case 1: // titles with 2008 copyright 58 // use LINQ to get titles with 2008 59 // copyright and sort them by title 60 titleBindingSource.DataSource = 61 from book in database.Titles 62 where book.Copyright == "2008" 63 orderby book.Title1 64 select book; 65 break; 66 case 2: // titles ending with "How to Program" 67 // use LINQ to get titles ending with 68 // "How to Program" and sort them by title 69 titleBindingSource.DataSource = 70 from book in database.Titles 71 where book.Title1.EndsWith( "How to Program" ) 72 orderby book.Title1 73 select book; 74 break; 75 } // end switch 76 77 titleBindingSource.MoveFirst(); // move to first entry 78 } // end method queriesComboBox_SelectedIndexChanged 79 } // end class TitleQueries 80 } // end namespace DisplayQueryResult
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).
In this section, we concentrate on LINQ to SQL features that simplify querying and combining data from multiple tables. The Joining Tables with LINQ application (Fig. 18.21) uses LINQ to SQL to combine and organize data from multiple tables, and shows the results of queries that perform the following tasks:
Get a list of all the authors and the ISBNs of the books they’ve authored, sorted by last name then first name (Fig. 18.21(a)).
Get a list of all the authors and the titles of the books they’ve authored, sorted by last name then first; for each author sort the titles alphabetically (Fig. 18.21(b)).
Get a list of all the book titles grouped by author, sorted by last name then first; for a given author sort the titles alphabetically (Fig. 18.21(c)).
For this example (Fig. 18.22–Fig. 18.25), create a Windows Forms application named JoinQueries
and rename the Form.cs
file as JoiningTableData.cs
. We set the following properties for the outputTextBox
:
Font
property: Set to Lucida Console
to display the output in a fixed-width font.
Anchor
property: Set to Top
, Bottom
, Left
, Right
so that you can resize the window and the outputTextBox
will resize accordingly.
Scrollbars
property: Set to Vertical
, so that you can scroll through the output.
Example 18.22. Creating the BooksDataContext
for querying the Books
database.
1 // Fig. 18.22: JoiningTableData.cs 2 // Using LINQ to perform a join and aggregate data across tables. 3 using System; 4 using System.Linq; 5 using System.Windows.Forms; 6 7 namespace JoinQueries 8 { 9 public partial class JoiningTableData : Form 10 { 11 public JoiningTableData() 12 { 13 InitializeComponent(); 14 } // end constructor 15 16 private void JoiningTableData_Load(object sender, EventArgs e) 17 { 18 // create database connection 19 BooksDataContext database = new BooksDataContext(); 20
Follow the steps from previous sections to set up the connection to the database and the LINQ to SQL classes.
The code combines data from the three tables in the Books
database and displays the relationships between the book titles and authors in three different ways. It uses LINQ to SQL classes that have been created using the same steps as the first two examples. As in previous examples, the BooksDataContext
object (Fig. 18.22, line 19) allows the program to interact with the database.
The first query (Fig. 18.23, lines 23–26) joins data from two tables and returns a list of author names and the ISBNs representing the books they’ve written, sorted by LastName
then FirstName
. The query takes advantage of the properties that LINQ to SQL creates based on foreign-key relationships between the database’s tables. These properties enable you to easily combine data from related rows in multiple tables.
Example 18.23. Getting a list of authors and the ISBNs of the books they’ve authored.
21 // get authors and ISBNs of each book they co-authored 22 var authorsAndISBNs = 23 from author in database.Authors 24 from book in author.AuthorISBNs 25 orderby author.LastName, author.FirstName 26 select new { author.FirstName, author.LastName, book.ISBN }; 27 28 outputTextBox.AppendText( "Authors and ISBNs:" ); 29 30 // display authors and ISBNs in tabular format 31 foreach ( var element in authorsAndISBNs ) 32 { 33 outputTextBox.AppendText( 34 String.Format( " {0,-10} {1,-10} {2,-10}", 35 element.FirstName, element.LastName, element.ISBN ) ); 36 } // end foreach 37
The first from
clause (line 23) gets one author
from the Authors
table. The second from
clause (line 24) uses the generated AuthorISBNs
property of the Author
class to get only the rows in the AuthorISBN
table that link to the current author
—that is, the ones that have the same AuthorID
as the current author
. The combined result of the two from
clauses is a collection of all the authors and the ISBNs of the books they’ve authored. The two from
clauses introduce two range variables into the scope of this query—other clauses can access both range variables to combine data from multiple tables. Line 26 combines the FirstName
and LastName
of an author from the Authors
table with a corresponding ISBN
from the AuthorISBNs
table. This line creates a new anonymous type that contains these three properties.
As you know, anonymous types allow you to create simple classes used to store data without writing a class definition. An anonymous type declaration (line 26)—known formally as an anonymous object-creation expression—is similar to an object initializer (Section 10.13). The anonymous type declaration begins with the keyword new
followed by a member-initializer list in braces ({}). No class name is specified after the new
keyword. The compiler generates a class definition based on the anonymous object-creation expression. This class contains the properties specified in the member-initializer list—First-Name
, LastName
and ISBN
. All properties of an anonymous type are public
. Anonymous type properties are read-only—you cannot modify a property’s value once the object is created. Each property’s type is inferred from the values assigned to it. The class definition is generated automatically by the compiler, so you don’t know the class’s type name (hence the term anonymous type). Thus, you must use implicitly typed local variables to store references to objects of anonymous types (e.g., line 31). Though we are not using it here, the compiler defines a ToString
method when creating the anonymous type’s class definition. The method returns a string
in curly braces containing a comma-separated list of PropertyName =
value pairs. The compiler also provides an Equals
method, which compares the properties of the anonymous object that calls the method and the anonymous object that it receives as an argument.
The second query (Fig. 18.24, lines 40–45) gives similar output, but uses the foreign-key relationships to go one step further and get the title of each book that an author wrote. The first from
clause (line 40) gets one title
from the Titles
table. The second from
clause (line 41) uses the generated AuthorISBNs
property of the Title
class to get only the rows in the AuthorISBN
table that link to the current title
—that is, the ones that have the same ISBN
as the current title
. Each of those book
objects contains an Author
property that represents the foreign-key relationship between the AuthorISBNs
table and the Authors
table. This Author
property gives us access to the names of the authors for the current book.
Example 18.24. Getting a list of authors and the titles of the books they’ve authored.
38 // get authors and titles of each book they co-authored 39 var authorsAndTitles = 40 from title in database.Titles 41 from book in title.AuthorISBNs 42 let author = book.Author 43 orderby author.LastName, author.FirstName, title.Title1 44 select new { author.FirstName, author.LastName, 45 title.Title1 }; 46 47 outputTextBox.AppendText( " Authors and titles:" ); 48 49 // display authors and titles in tabular format 50 foreach ( var element in authorsAndTitles ) 51 { 52 outputTextBox.AppendText( 53 String.Format( " {0,-10} {1,-10} {2}", 54 element.FirstName, element.LastName, element.Title1 ) ); 55 } // end foreach 56
Line 42 uses the let
query operator, which allows you to declare a new variable in a LINQ query—usually to create a shorter name for an expression. The variable can be accessed in later statements just like a range variable. The author
variable created in the let
clause refers to book.Author
. The select
clause (lines 44–45) uses the author
and title
variables introduced earlier in the query to get the FirstName
and LastName
of each author from the Authors
table and the title of each book from the Titles
table.
Most queries return results with data arranged in a relational-style table of rows and columns. The last query (Fig. 18.25, lines 60–66) returns hierarchical results. Each element in the results contains the name of an Author
and a list of Title
s that the author wrote. The LINQ query does this by using a nested query in the select
clause. The outer query iterates over the authors in the database. The inner query takes a specific author and retrieves all titles that the author worked on. The select
clause (lines 62–66) creates an anonymous type with two properties:
The property Name
(line 62) combines each author’s name, separating the first and last names by a space.
The property Titles
(line 63) receives the result of the nested query, which returns the title of each book written by the current author
.
Example 18.25. Getting a list of titles grouped by authors.
57 // get authors and titles of each book 58 // they co-authored; group by author 59 var titlesByAuthor = 60 from author in database.Authors 61 orderby author.LastName, author.FirstName 62 select new { Name = author.FirstName + " " + author.LastName, 63 Titles = 64 from book in author.AuthorISBNs 65 orderby book.Title.Title1 66 select book.Title.Title1 }; 67 68 outputTextBox.AppendText( " Titles grouped by author:" ); 69 70 // display titles written by each author, grouped by author 71 foreach ( var author in titlesByAuthor ) 72 { 73 // display author's name 74 outputTextBox.AppendText( " " + author.Name + ":" ); 75 76 // display titles written by that author 77 foreach ( var title in author.Titles) 78 { 79 outputTextBox.AppendText( " " + title ); 80 } // end inner foreach 81 } // end outer foreach 82 } // end method JoiningTableData_Load 83 } // end class JoiningTableData 84 } // end namespace JoinQueries
In this case, we’re providing names for each property in the new anonymous type. When you create an anonymous type, you can specify the name for each property by using the format name = value.
The nested foreach
statements (lines 71–81) use the properties of the anonymous type created by the query to output the hierarchical results. The outer loop displays the author’s name and the inner loop displays the titles of all the books written by that author.
Notice the expression book.Title.Title1
used in the inner orderby
and select
clauses (lines 65–66). This is due to the database having a Title
column in the Titles
table, and is another example of following foreign-key relationships. (Recall that the IDE renamed the Title
column in the LINQ to SQL classes to avoid a naming conflict with the generated Title
class.) The range variable book
iterates over the rows of the AuthorISBN
for the current author’s books. Each book
’s Title
property contains the corresponding row from the Titles
table for that book
. The Title1
in the expression returns the Title
column (the title of the book) from that row of the Titles
table in the database.
Figure 18.26 demonstrates a so-called master/detail view—one part of the GUI (the master) allows you to select an entry, and another part (the details) displays detailed information about that entry. In this example, if you select an author from the Author: ComboBox
, the application displays the details of the books written by that author (Fig. 18.26(b)). If you select a book title from the Title: ComboBox
, the application displays the co-authors of that book (Fig. 18.26(c)).
You’ve seen that the IDE can automatically generate the BindingSource
, BindingNavigator
and GUI elements when you drag a data source onto the Form
. While this works for simple applications, those with more complex operations involve writing more substantial amounts of code. Before explaining the code, we list the steps required to create the GUI.
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 Label
s and two ComboBox
es 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 Label
s to Author
: and Title
:, respectively. Also change the DropDownStyle
properties of the ComboBox
es from DropDown
to DropDownList
—this prevents the user from being able to type in the control.
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 BindingSource
s 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 BindingSource
s are used as data sources for the DataGridView
—the data source switches between them, depending on whether we want to view a list of Title
s or a list of Author
s. 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.
Example 18.28. Nested class AuthorBinding
in class Details
.
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.
Example 18.29. Configuring the ComboBox
es’ and DataGridView
’s data sources.
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 ComboBox
es 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 Author
s and one for displaying a list of Title
s. 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.
Example 18.30. Displaying the books for the selected author.
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 Author
s for the current book. Finally, the DataGridView
’s DataSource
is assigned authorBindingSource
to display the authors who wrote this book (line 85).
Example 18.31. Displaying the authors of the selected book.
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
Our next example (Fig. 18.32) implements a simple AddressBook
application 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 application 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, delete a contact and save changes to a contact. When you run the application, experiment with the BindingNavigator
’s controls. The CD- or DVD-like buttons of the BindingNavigator
allow you to change the currently displayed row. Adding a row clears the TextBox
es and sets the TextBox
to the right of Address ID to zero. When you save a new entry, the Address ID field is automatically changed from zero to a unique number by the database.
Recall from Section 18.5 that to allow changes to the database to persist between executions of the application, you can run the program in release mode (Ctrl + F5).
We discuss the application’s code momentarily. First we show the steps to create this application.
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
.
Drag the Address
node from the Data Sources window to the Form
. This automatically creates a BindingNavigator
and the Labels
and TextBox
es 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.
Example 18.34. Creating the BooksDataContext
and defining method RefreshContacts
for use in other methods.
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.
Example 18.35. Calling RefreshContacts
to fill the TextBox
es when the application loads.
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.
Example 18.36. Saving changes to the database when the user clicks the Save Data Button
.
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.
Example 18.37. Finding the contacts whose last names begin with a specified String
.
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
.
Example 18.38. Allowing the user to browse all contacts.
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
Our extensive LINQ Resource Center at www.deitel.com/LINQ contains many links to additional information, including blogs by Microsoft LINQ team members, sample chapters, tutorials, videos, downloads, FAQs, forums, webcasts and other resource sites.
A useful tool for learning LINQ is LINQPad (www.linqpad.net), which allows you to execute and view the results of any C# or Visual Basic expression, including LINQ queries. It also supports connecting to a SQL Server database and querying it using SQL and LINQ to SQL.
This chapter introduced the relational database model, LINQ to SQL and the IDE’s visual programming tools for working with databases. You examined the contents of a simple Books
database and learned about the relationships among the tables in the database. You used LINQ and the LINQ to SQL classes generated by the IDE to retrieve data from, add new data to, delete data from and update data in a SQL Server Express database.
We discussed the LINQ to SQL classes automatically generated by the IDE, such as the DataContext
class that controls interactions with the database. You learned how to use the IDE’s tools to connect to databases and to generate LINQ to SQL classes based on a database’s schema. You then used the IDE’s drag-and-drop capabilities to automatically generate GUIs for displaying and manipulating database data.
In the next chapter, we demonstrate how to build web applications using Microsoft’s ASP.NET technology. We introduce the concept of a three-tier application, which is divided into three pieces that can reside on the same computer or be distributed among separate computers across a network such as the Internet. One of these tiers—the information tier—typically stores data in a database.
A database is an organized collection of data.
A database management system (DBMS) provides mechanisms for storing, organizing, retrieving and modifying data.
SQL Server Express provides most of the features of Microsoft’s full (fee-based) SQL Server product, but has some limitations, such as a maximum database size.
A SQL Server Express database can be easily migrated to a full version of SQL Server.
LINQ to SQL allows you to manipulate relational data stored in a SQL Server or SQL Server Express database.
A relational database organizes data simply in tables.
Tables are composed of rows and columns (also called fields) in which values are stored.
A column (or group of columns) of each row is the table’s primary key—a column (or group of columns) requiring a unique value that cannot be duplicated in other rows. This guarantees that a primary key value can be used to uniquely identify a row.
A primary key composed of two or more columns is known as a composite key.
Each column represents a different data attribute.
Rows are unique (by primary key) within a table, but some column values may be duplicated between rows.
A database’s tables, their fields and the relationships between them are collectively known as a database schema.
LINQ to SQL uses a database’s schema to define classes that enable you to interact with the database.
A foreign key is a column in one table that matches the primary-key column in another table.
Foreign keys, which are specified when a database table is created, link the data in multiple tables.
Every foreign-key value must appear as another table’s primary-key value so the DBMS can ensure that the foreign-key value is valid.
Foreign keys also allow related data in multiple tables to be selected from those tables—this is known as joining the data.
There’s a one-to-many relationship between a primary key and a corresponding foreign key—a foreign key can appear many times in its own table but only once (as the primary key) in another table.
An entity-relationship (ER) diagram shows the tables in a database and their relationships.
Every row must have a value in the primary-key column, and the value of the key must be unique in the table.
LINQ to SQL enables you to access data in SQL Server databases using LINQ syntax.
You interact with LINQ to SQL via classes that are automatically generated by the IDE’s LINQ to SQL Designer based on the database schema.
LINQ to SQL requires every table to have a primary key to support modifying the database data.
The IDE creates a class for each table. Objects of these classes represent the collections of rows in the corresponding tables.
The IDE also creates a class for a row of each table with a property for each column in the table. Objects of these classes (row objects) hold the data from individual rows in the database’s tables.
In the class for a row object, an additional property is created for each foreign key. This property returns the row object of the corresponding primary key in another table.
In the class for a row object, an additional property is created for the collection of row objects with foreign-keys that reference the row object’s primary key.
Once generated, the LINQ to SQL classes have full IntelliSense support in the IDE.
The IDE provides visual programming tools and wizards that simplify accessing data in your projects. These tools establish database connections and create the objects necessary to view and manipulate the data through the GUI—a technique known as data binding.
A DataGridView (namespace System.Windows.Forms
) displays data from a data source in tabular format.
A BindingNavigator
is a collection of controls that allow you to navigate through the records displayed in a GUI. The BindingNavigator
controls also allow you to add records, delete records and save your changes to the database.
To interact with a database, you must create a connection to the database.
In Visual C# 2010 Express, use the Database Explorer window to connect to the database. In full versions of Visual Studio 2010, use the Server Explorer window.
After connecting to the database, you can generate the LINQ to SQL classes by adding a new LINQ to SQL Classes item to your project, then dragging the tables you wish to use from the Database Explorer onto the Object Relational Designer. When you save the .dbml
file, the IDE generates the LINQ to SQL classes.
To use the LINQ to SQL classes for data binding, you must first add them as a data source.
Select Data > Add New Data Source... to display the Data Source Configuration Wizard. Use an Object data source. Select the LINQ to SQL object to use as a data source. Drag that data source from the Data Sources window onto the Form
to create controls that can display the table’s data.
By default, the IDE creates a DataGridView
with the correct column names and a BindingNavigator
that contains Button
s for moving between entries, adding entries, deleting entries and saving changes to the database.
The IDE also generates a BindingSource
, which handles the transfer of data between the data source and the data-bound controls on the Form
.
The result of a LINQ query on the DataContext
can be assigned to the BindingSource
’s DataSource
property. The BindingSource
uses the DataSource
to extract data from the database and to populate the DataGridView
.
To save the user’s changes to the data in the DataGridView
, enable the BindingNavigator
’s Save Data Button
(). Then, double click the icon to create its Click
event handler. In the event handler, you must validate the data, call EndEdit
on the BindingSource
to save pending changes in the DataContext
, and call SubmitChanges
on the DataContext
to store the changes in the database. For efficiency, LINQ to SQL saves only data that has changed.
The IDE displays smart tag menus for many GUI controls to provide you with quick access to common properties you might set for a control, so you can set these properties directly in Design view. 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.
The MoveFirst
method of the BindingSource
moves to the first row of the result.
To join data from multiple tables you use the properties that LINQ to SQL creates based on foreign-key relationships between the database’s tables. These properties enable you to easily access related rows in other tables.
The Let
query operator allows you to declare a new variable in a query—usually to create a shorter name for an expression. The variable can be accessed in later clauses just like a range variable.
Most queries return result with data arranged in relational-style rows and columns. With LINQ to SQL you can create queries that return hierarchical results in which each item in the result contains a collection of other items.
Use anonymous types to create simple classes used to store data without writing a class definition.
An anonymous type declaration—also called an anonymous object-creation expression—begins with the keyword new
followed by a member-initializer list.
The compiler generates a new class definition based on the anonymous object-creation expression, containing the properties specified in the member-initializer list.
All properties of an anonymous type are public
.
Properties of anonymous types are read-only.
Each property’s type is inferred from the value assigned to it.
Objects of anonymous types are stored in implicitly typed local variables.
The compiler defines the ToString
method when creating the anonymous type’s class definition. The method returns a string
of comma-separated PropertyName =
value pairs in curly braces.
The Equals
method, generated for any anonymous type, compares the properties of the anonymous object that calls the method and the anonymous object that it receives as an argument.
In a master/detail view, one part of the GUI (the master) allows you to select an entry, and another part (the details) displays detailed information about that entry.
Class definitions may be nested inside other classes.
A ComboBox’s DisplayMember
property indicates which property to display in the ComboBox
from each object in its DataSource
.
You can change the columns and data displayed in a DataGridView
by changing its DataSource
. The DataGridView
determines the column names it needs to display from the BindingSource
.
The IDE allows you to specify the type of control(s) that it creates when you drag and drop a data-source member onto a Form
. The Details option indicates that the IDE should create a set of Label
/TextBox
pairs for each column-name/column-value pair in the data source.
18.3 | (Display Authors Table Application Modification) Modify the DisplayTable application in Section 18.5 to contain a |
18.4 | (Display Query Results Application Modification) Modify the Display Query Results application in Section 18.6 to contain a |
18.5 | ( |
18.6 | ( |
18.7 | (Project: msftdbprodsamples.codeplex.com/releases/view/37109 The installer allows you to select which version of the database to install. Use the |
18.8 | (Project: |