Creating LINQ to SQL Classes: Using the O/R Designer

Article
07/25/2011

The Object Relational Designer (O/R Designer) enables mapping LINQ to SQL classes to tables in a database. These mapped classes are also known as entity classes. The properties of an entity class map to the columns of the table, and you can bind the data to controls on a Windows Form. To learn more about LINQ to SQL, see Using LINQ in Visual Basic Express.

In this lesson, you will learn how to use the O/R Designer to create entity classes that are mapped to tables in the Northwind database. You will then display data on a Windows Forms application.

Prerequisites

If you did not install SQL Server Express Edition when you installed Visual Basic Express, you must do this before continuing. SQL Server Compact 3.5 databases are not supported in LINQ to SQL. For information about how to obtain SQL Server Express Edition, see the Obtaining SQL Server Express Edition section in How to: Install Sample Databases.
To complete this lesson, you must have access to the SQL Server version of the Northwind sample database. For more information, see How to: Install Sample Databases.

Try it!

To open the O/R Designer

On the File menu, click New Project.
In the New Project dialog box, in the Templates pane, click Windows Forms Application, name the project LINQToSQLSample, and then click OK.

A new Windows Forms project opens.
On the Project menu, click Add New Item.
Click the LINQ to SQL Classes template and type NorthwindSample.dbml in the Name box.
Click Add.

An empty LINQ to SQL file (NorthwindSample.dbml) is added to the project, and the O/R Designer opens.

The O/R Designer is made up of two design surfaces: the Entities pane on the left, and the Methods pane area on the right. The Entities pane is the main design surface that displays the entity classes that are defined in the file. The Methods pane is the design surface that displays the DataContext methods that are mapped to stored procedures and functions. You can hide the Methods pane by right-clicking the O/R Designer and then clicking Hide Methods Pane. You will not use the Methods pane in this lesson, but you can learn more about it in DataContext Methods (O/R Designer).

Click the main design surface and verify that its name is NorthwindSampleDataContext by looking at the Name property in the Properties window. You can create entity classes by dragging database tables from the Database Explorer onto the O/R Designer surface. But first you must create a connection to the database. This process is like the process described in Getting the Information You Need: Connecting to an Existing Database, except that in this lesson you will use a SQL Server Express database.

To connect to the Northwind database

On the View menu, click Database Explorer.

The Database Explorer opens.
Click the Connect to Database button in the Database Explorer toolbar.
Click Microsoft SQL Server Database File in the Choose Data Sources window and then click Continue.

The Add Connection dialog box opens.
In the Add Connection dialog box, make sure that the data source is Microsoft SQL Server Database File (SqlClient).
Click Browse and then locate and select the Northwind.mdf database. Click Open.
Click Test Connection to verify that the connection succeeded.
Click OK to close the Add Connection dialog box.

Creating Entity Classes

The tables in the Northwind database are now visible in the Database Explorer. You can drag tables from the Database Explorer onto the O/R Designer. This automatically creates entity classes that have properties that map to the columns in the tables that you dragged from the Database Explorer.

To create entity classes

In the Database Explorer, expand Northwind.mdf, and then expand Tables.
Drag Customers from Database Explorer onto the O/R Designer surface. If you are prompted to copy the data file to your project, click Yes.

An entity class named Customer is created. It has properties that correspond to the columns in the Customers table.
Drag Orders from Database Explorer onto the O/R Designer surface.

An entity class named Order is created that has an association (relationship) to the Customer entity class. It has properties that correspond to the columns in the Orders table.

Now that you have created the entity classes, you can add them to the Data Sources window by running the Data Source Configuration Wizard.

To add an entity class as an object data source in the Data Sources window

On the Build menu, click Build LINQToSQLSample.
On the Data menu, click Show Data Sources.
In the Data Sources window, click Add New Data Source.
Click Object on the Choose a Data Source Type page and then click Next.
Expand the LINQToSQLSample node and locate the Customer class.

Note

If the Customer class is not available, click Cancel, build the project, and run the wizard again.
Click Finish to create the data source and add the Customer entity class to the Data Sources window.

Binding Controls on a Windows Form to Entity Classes

You can add controls that are data bound to entity classes to a Windows Form by dragging tables or fields from the Data Sources window to the form.

To add controls bound to the entity classes

Right-click Form1 in Solution Explorer and then click View Designer.
In the Data Sources window, expand Customer.
Click the drop-down arrow next to CompanyName and then click Label.
Drag CompanyName to the form.

A label and a navigation toolbar are added to the form.
Drag Orders from the Data Sources window to Form1. Place it under CompanyName.
Right-click Form1 in Solution Explorer and then click View Code.
Add the following code to the form, at the top of the Form class, outside any specific method.
```
Private NorthwindSampleDataContext1 As New _
    NorthwindSampleDataContext
```
Create an event handler for the Form_Load event by double-clicking the form. Add the following code to the event handler:
```
CustomerBindingSource.DataSource = _
    NorthwindSampleDataContext1.Customers()
```
Press F5 to run the code.
When the form opens, click the Move next button on the toolbar to scroll through the companies and verify that the orders data is updated to display the selected customer's orders.

Saving Changes

When you add the controls to the O/R Designer, the Save Data button on the navigation toolbar is not enabled. If you want to enable users to save changes, you must first enable the save button and then add code to save changed data.

To enable the save button

In Design view, select the save button on the navigation toolbar. (The button with the floppy disk icon.)
In the Properties window, set the Enabled property to True.
Double-click the save button to create an event handler and switch to the Code Editor.
Add the following code into the save button event handler:
```
NorthwindSampleDataContext1.SubmitChanges()
```
Press F5 to run the code.
Change the value in one of the Shipped Date fields and press the TAB key to commit the change.
Click the Save Data button.
Close the form.
Press F5, and verify that the change you made is saved.

Next Steps

In this lesson you learned how to create LINQ to SQL classes by using the O/R Designer. In the next lesson, you will learn how to bind data by using a LINQ query. To learn more about how to use LINQ in an application, see Using LINQ in Visual Basic Express.

Next Lesson: Using LINQ to Bind Data to Controls