Databases with LINQ
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. 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.
DisplayTableBooks example
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. 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. 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.
DisplayTableBooks example
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. 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.
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.
DisplayQueryResult example
More Complex LINQ Queries and Data Binding
JoiningWithLINQ example
LINQ to SQL to combine and organize data from multiple
tables.
MasterDetailexample
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.
AddressBook example
The AddressBook application provides a GUI for querying
the database with LINQ.
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. 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.