Walkthrough: Creating LINQ to SQL Classes (O/R ...
Data Access in Client and Middle-Tier Programming
Walkthrough: Creating LINQ to SQL Classes (O/R Designer)

The Object Relational Designer (O/R Designer) provides a visual design surface for creating and editing LINQ to SQL classes (entity classes) that are based on objects in a database. By using LINQ to SQL, you can access SQL databases with LINQ technology. For more information, see Language-Integrated Query (LINQ).

This walkthrough provides the steps that you must follow to create LINQ to SQL entity classes mapped to the Customers and Orders tables in the Northwind database and display the data on a Windows Form. In addition to the steps for displaying the data from the table, the steps for binding data to a LINQ query are also provided. Finally, the steps for using stored procedures to replace the default LINQ to SQL logic for sending updates from the entity classes to the database are provided.

During this walkthrough, you will learn how to perform the following tasks:

  • Add a LINQ to SQL file to a project.

  • Create new entity classes that are mapped to related tables in the database.

  • Create an object data source that references the entity classes.

  • Create a Windows Form containing controls that are bound to entity classes.

  • Add code to load and save the data between the entity classes and the database.

  • Construct a simple LINQ query and display the results on the form.

  • Add stored procedures to the O/R Designer.

  • Configure an entity class to use stored procedures to perform Inserts, Updates, and Deletes.

 Prerequisites

To complete this walkthrough, you need the following:

 Creating the Windows-Based Application

Because you will be working with LINQ to SQL classes and displaying the data on a Windows Form, the first step in this walkthrough is to create a new Windows Forms application.

Bb384428.alert_note(en-us,VS.90).gifNote:

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Visual Studio Settings.

To create the new Windows Application project

  1. From the File menu, create a new project.

  2. Name the project ORDesignerWalkthrough.

    Bb384428.alert_note(en-us,VS.90).gifNote:

    The O/R Designer is supported in Visual Basic and C# projects, so create the new project in one of these languages.

  3. Click the Windows Forms Application template and click OK. For more information, see Creating Windows-Based Applications.

    The ORDesignerWalkthrough project is created and added to Solution Explorer.

 Adding a LINQ to SQL Classes File to the Project (Opening the O/R Designer)

Entity classes are created and stored in LINQ to SQL Classes files (.dbml files). The O/R Designer opens when you open a .dbml file. Add .dbml files to projects by selecting the LINQ to SQL Classes template in the Add New Item dialog box.

To add a .dbml file to a project

  1. On the Project menu, click Add New Item.

  2. Click the LINQ to SQL Classes template and type Northwind.dbml in the Name box.

  3. Click Add.

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

After you add the new LINQ to SQL file to the project, the empty design surface opens, displaying two separate panes. The pane on the left is the entities pane, where entity classes are displayed and configured. The pane on the right is the methods pane that displays the DataContext methods added to the designer. If the methods pane is not visible right click an empty area in the entities pane and click Show Methods Pane. The entire empty surface represents a DataContext ready to be configured. The DataContext name corresponds to the name that you provided for the .dbml file. For this walkthrough, because you named the LINQ to SQL file Northwind.dbml, the DataContext is named NorthwindDataContext. You can verify this by clicking any empty area on the designer and inspecting the Properties window.

Bb384428.alert_note(en-us,VS.90).gifNote:

The DataContext class contains methods and properties for connecting to a database and manipulating the data in the database (for example, performing Inserts, Updates, and Deletes). For more information, see DataContext Methods (O/R Designer).

 Creating Customer and Order Entity Classes

Create LINQ to SQL classes that are mapped to database tables by dragging tables from Server Explorer/Database Explorer onto the O/R Designer. The result is a LINQ to SQL entity class that maps to the table in the database.

To add a Customer entity class to the O/R Designer

  1. In Server Explorer/Database Explorer, locate the tables in the SQL Server version of the Northwind sample database. For more information, see How to: Create a Data Connection to the Northwind Database.

  2. Drag the Customers node from Server Explorer/Database Explorer onto the O/R Designer surface.

    An entity class named Customer is created. It has properties that correspond to the columns in the Customers table. The entity class is named Customer (not Customers) because it represents a single customer from the Customers table.

    Bb384428.alert_note(en-us,VS.90).gifNote:

    This renaming behavior is called pluralization. It can be turned on or off in the Options Dialog Box (Visual Studio). For more information, see How to: Turn Pluralization On and Off (O/R Designer).

  3. Drag the Orders node from Server Explorer/Database Explorer onto the O/R Designer surface.

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

    Bb384428.alert_note(en-us,VS.90).gifNote:

    The entity class is named Order because it represents a single order. The parent class (Customer) has an Orders property that represents the collection of orders for that specific customer. For more information about LINQ to SQL associations, see How to: Create an Association (Relationship) Between LINQ to SQL Classes (O/R Designer).

 Creating an Object Data Source with the Customer Entity Class

Entity classes, just like other classes that have public properties, can be used as object data sources. They can be added to the Data Sources window and dragged onto forms to create data-bound controls (controls that are bound to the values in the public properties of the object). Add entity classes to the Data Sources window by running the Data Source Configuration Wizard and clicking Object for the data source in the wizard.

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

  1. On the Build menu, click Build ORDesignerWalkthrough to build the project.

  2. On the Data menu, click Show Data Sources.

  3. In the Data Sources window, click Add New Data Source.

  4. Click Object on the Choose a Data Source Type page and then click Next.

  5. Expand the ORDesignerWalkthrough node (the node with the name of your project) and locate and select the Customer class.

    Bb384428.alert_note(en-us,VS.90).gifNote:

    If the Customer class is not available, cancel out of the wizard, build the project, and run the wizard again.

  6. Click Finish to create the data source and add the Customer entity class to the Data Sources window.

 Creating Data-Bound Controls to Display the Data on a Windows Form

Create controls that are bound to entity classes by dragging LINQ to SQL data source items from the Data Sources window onto a Windows Form.

To add controls bound to the entity classes

  1. Open Form1 in Design view.

  2. From the Data Sources window, drag the Customer node onto Form1.

    Bb384428.alert_note(en-us,VS.90).gifNote:

    To display the Data Sources window, click Show Data Sources on the Data menu.

  3. Drag the Orders node from the Data Sources window onto Form1. Place it under CustomerDataGridView.

  4. Open Form1 in code view.

  5. Add the following code to the form, global to the form, outside any specific method, but inside the Form1 class:

    Visual Basic 
    Private NorthwindDataContext1 As New NorthwindDataContext

    C# 
    private NorthwindDataContext northwindDataContext1
    = new NorthwindDataContext();

  6. Create an event handler for the Form_Load event and add the following code to the handler:

    Visual Basic 
    CustomerBindingSource.DataSource = NorthwindDataContext1.Customers

    C# 
    customerBindingSource.DataSource
    = northwindDataContext1.Customers;
 Testing the Application

Run the application. At this point the form contains one DataGridView displaying the data from the Customers table and a second DataGridView displaying the data from the selected customer's orders.

Bb384428.alert_note(en-us,VS.90).gifNote:

Notice that the save button is disabled. (You will implement save functionality in the next section.)

To test the application

  1. Press F5.

  2. Verify that data appears in the grids.

  3. Select a customer.

  4. Verify that the orders displayed are for the selected customer.

  5. Close the form. (On the Debug menu, click Stop Debugging.)

 Implementing Save Functionality

As noted earlier, by default the save button is not enabled, and save functionality is not implemented. Also, code is not automatically added to save changed data to the form when data-bound controls are created for object data sources. This section explains how to enable the save button and implement save functionality for LINQ to SQL objects.

To implement save functionality

  1. Open Form1 in Design view.

  2. Select the save button on the CustomerBindingNavigator. (The button labeled with a floppy disk icon.)

  3. In the Properties window, set the Enabled property to True.

  4. Double-click the save button to create an event handler and switch to the Code Editor.

  5. Add the following code into the save button event handler:

    Visual Basic 
    Try
    NorthwindDataContext1.SubmitChanges()
Catch ex As Exception
    MessageBox.Show(ex.Message)
End Try

    C# 
    try
{
    northwindDataContext1.SubmitChanges();
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}
 Testing the Application

Run the application. The save button should be enabled, and the ability to save data is available.

To test the application

  1. Press F5.

  2. Modify some data in either grid. (Navigate off the edited row in the grid to commit in-process changes.)

  3. Click the save button to save changes back to the database.

  4. Close the form.

  5. Press F5 and verify that the changes were persisted (or locate the table in the database to verify that the changes were saved).

 Binding to LINQ Queries

In addition to binding the CustomerBindingSource to the DataContext, you can also bind directly to LINQ queries. For more information about how to create LINQ queries, see Introduction to LINQ Queries.

Adding a Button and TextBox to the Form

To learn how to bind controls to LINQ queries, add controls to the form that enable you to enter a query parameter and then run the query.

To add controls to the form

  1. Open Form1 in Design view.

  2. Add a TextBox to the form and set its Name property to CityTextBox.

  3. Add a Button to the form and set the following properties:

    • Name = RunQueryButton

    • Text = Run Query

Data Binding to the LINQ Query

Add code to run a LINQ query. The query uses the value typed into CityTextBox as a query parameter.

To bind to a LINQ query

  • Double-click the RunQueryButton and add the following code to the RunQueryButton_click event handler:

    Visual Basic 
    Dim CustomersQuery = From customers in NorthwindDataContext1.Customers _
    Where customers.City = CityTextBox.Text _
    Select customers

CustomerBindingSource.DataSource = CustomersQuery

    C# 
    var CustomersQuery = from customers in northwindDataContext1.Customers
                      where customers.City == CityTextBox.Text
                      select customers;
customerBindingSource.DataSource = CustomersQuery;
 Testing the Application

Run the application. You can now query for customers in a specific city.

To test the application

  1. Press F5.

  2. Type London in the text box.

  3. Click the Run Query button.

  4. Verify that only customers who have a value of London in their City property are displayed.

 Overriding the Default Behavior for Performing Updates (Inserts, Updates, and Deletes)

By default, the logic to perform updates is provided by the LINQ to SQL runtime. The runtime creates default Insert, Update, and Delete statements based on the Select statement that is used to populate your entity class with data. When you do not want to use the default behavior, you can configure the update behavior and designate specific stored procedures for performing the necessary Inserts, Updates, and Deletes required to manipulate the data in your database. You can also do this when the default behavior is not generated, for example, when your entity classes map to joined tables. Additionally, you can override the default update behavior when the database requires table access through stored procedures.

Bb384428.alert_note(en-us,VS.90).gifNote:

This section requires the availability of the additional InsertCustomer, UpdateCustomer, and DeleteCustomer stored procedures for the Northwind database. For details about how to create these stored procedures, see Walkthrough: Creating Update Stored Procedures for the Northwind Customers Table.

To override the default update behavior

  1. Open the LINQ to SQL file in the O/R Designer. (Double-click the Northwind.dbml file in Solution Explorer.)

  2. In Server Explorer/Database Explorer, expand the Northwind databases Stored Procedures node and locate the UpdateCustomers stored procedure.

  3. Drag the UpdateCustomers stored procedure onto the O/R Designer.

    The UpdateCustomers stored procedure is added to the methods pane as a DataContext method. For more information, see DataContext Methods (O/R Designer).

  4. Select the Customer entity class in the O/R Designer.

  5. In the Properties window, select the command to override. (Insert, Update, or Delete). For this example, select the Update property.

  6. Click the ellipsis next to Use Runtime to open the Configure Behavior dialog box.

  7. Select Customize.

  8. Select the UpdateCustomers method in the Customize list.

  9. Inspect the list of Method Arguments and Class Properties and notice that there are two Method Arguments and two Class Properties for some columns in the table. This facilitates tracking changes and creating statements that check for concurrency violations.

  10. Map the original method arguments (Original_ArgumentName) to the original properties (PropertyName (Original)). For this walkthrough, you have to map the Original_CustomerID argument to the CustomerID (Original) property.

    Bb384428.alert_note(en-us,VS.90).gifNote:

    By default, method arguments will map to class properties when the names match. If property names were changed and no longer match between the table and the entity class, you might have to select the equivalent class property to map to if the designer cannot determine the correct mapping. Additionally, if method arguments do not have valid class properties to map to, you can set the Class Properties value to (None).

  11. Click OK.

 Testing the Application

Run the application again to verify that the UpdateCustomers stored procedure correctly updates the customer record on the database.

To test the application

  1. Press F5.

  2. Locate the ContactName column in the grid for ALFKI.

  3. Change the name from Maria Anders to Anders.

  4. Navigate off the row to commit the change.

  5. Click the save button.

  6. Close the form.

  7. Press F5 to run the application again and verify that only Anders appears in the ContactName column for ALFKI.

 Next Steps

Depending on your application requirements, there are several steps that you may want to perform after you create LINQ to SQL entity classes. Some enhancements you could make to this application include the following:

 See Also

Concepts

O/R Designer Overview
What's New in Data

Reference

LINQ General Programming Guide

Other Resources

Object Relational Designer (O/R Designer)
LINQ to SQL
LINQ to ADO.NET
LINQ Documentation Roadmap
Accessing Data (Visual Studio)
Tags What's this?: Add a tag
Community Content   What is Community Content?
Add new content RSS  Annotations
Acessando dados no cliente e programação N-Camadas      Juliana Prado   |   Edit   |   Show History
O Objeto Relacional Designer (O / R Designer) prevê um design visual superfície para criação e edição
de LINQ SQL classes (entidade classes) que são baseadas em objetos em um banco de dados.
Usando LINQ para SQL, você pode acessar
bancos de dados com o SQL tecnologia LINQ . P
ara mais informações, consulte-Linguagem estruturada de Pesquisa. (LINQ).
Esta passagem fornece os passos
que devemos seguir para criar a entidade LINQ to SQL e com isso as classes são
mapeadas para as tabelas Clientes e Encomendas na base de dados Adamastor
e exibir os dados em um Windows Form.
Para além destes passos para exibir os dados da tabela,conseguirmos vinculos para uma consulta LINQ.
Finalmente, estes passos mostram como utilizar procedimentos
armazenados para a substituição do padrão SQL para a Lógica LINQ
e com isso enviamos atualizações para a entidade de classes no banco de dados com os procedimentos descritos acima.

Durante esta etapas, você vai aprender como realizar as seguintes tarefas:
-Adicionar em um projeto o LINQ TO SQL
-Criar nova classe entidade que é mapeada para relacionamento de tabelas no banco de dados.
-Criar um objeto fonte de dados que faz referência a entidade classes.
-Criar um formulário que contém comandos do Windows que estão vinculados à entidade classes.
-Adicionar código para carregar e salvar os dados entre a entidade e as classes banco de dados.
-Construir uma simples consulta LINQ e exibir os resultados no formulário.
-Adicionar procedimentos armazenados para o O / R Designer.
-Configurar uma classe entidade para usar procedimentos armazenados para executar inserções,atualizações e remoções.
Pré-Requisitos
Para concluir essa etapa, é necessário o seguinte:
O acesso a versão amostra do banco de dado SQL Server.
Para mais informações, consulte Como: Instalar Bancos de dados de amostra.
O UpdateCustomer
procedimento armazenado para o banco de dados Northwind.
Para mais informações, consulte Etapa: Criando Stored Procedures para
Atualização da Tabela Clientes contida no banco de dados padrão Northwind.
Criando uma aplicação windows
Porque você vai trabalhar
com classes para LINQ to SQL e exibir os dados em um formulário do Windows,
o primeiro passo nesta etapa é criar um novo aplicativo do tipo Windows Forms.

O seu computador poderá exibir de maneira a localização de alguns componentes de elementos da interface da IDE
do Visual Studio nas seguintes instruções. A edição do Visual Studio que você tem
e as configurações que você usa determinar esses elementos.
Para mais informações, consulte Configurações do Visual Studio.
Para criar uma nova aplicação windows
-No menu Arquivo, crie um novo projeto.
-Nome do projeto ORDesignerWalkthrough.
Observação
O O / R Designer é suportado em projetos em Visual Basic e C # de forma que você pode criar um novo projeto em qualquer uma dessas linguagens.
-Clique na aplicação do tipo do Windows Forms e clique em OK.
Para mais informações, consulte Criando Aplicações Windows.
-ORDesignerWalkthrough é criado e adicionado ao Solution Explorer.

Adicionando um arquivo de classe LINQ TO SQL no projeto (Abertura do O / R Designer)
Classes entidades são criadas e armazenadas em uma classe LINQ To SQL (arquivos. Dbml).
O O / R abriu o designer quando você abre um arquivo Dbml.
Adicionar arquivos Dbml em projetos, Basta selecionar o modelo LINQ TO SQL Classes que está na caixa de diálogo na opção
Adicionar novo item.
Para adicionar um arquivo Dbml em um projeto basta clicar no menu Projeto, clique em Adicionar novo Item.
Clique no modelo de classe LINQ TO SQL e o nomeie na caixa de texto para Northwind.dbml.
Clique em Adicionar.
Um novo arquivo LINQ TO SQL Classes chamado (Northwind.dbml) é adicionado ao projeto,
e do O / R abriu o modo design.

Após adicionar o novo arquivo do tipo LINQ TO SQL no projeto,
Mostra um desenho vazio e uma superfície aberta que exibem dois painéis distintos.
O painel à esquerda é o painel entidades, entidade onde são exibidas as classes e configurações.
O painel à direita é o painel que exibe os métodos DataContext métodos adicionados ao designer.
Se o painel não está visível então exiba-os utilizando o botão direito em uma área vazia do painel
e clique em Mostrar entidades.
A totalidade da superfície vazia representa DataContext um pronto para ser configurado.
O nome DataContext corresponde ao nome que você forneceu para o arquivo Dbml.
Para esta etapa, pois chamamos o arquivo Northwind.dbml,que foi feito em LINQ TO SQL,
o DataContext é chamado NorthwindDataContext.
Você pode verificar isso clicando em qualquer área vazia na área de designer e inspecionar a janela Propriedades.
Observação:
A classe DataContext contém métodos e propriedades para ligação a um banco de dados
e manipulação dos dados no banco de dados (
por exemplo, realizando Inserções, atualizações, e remoções).
Para mais informações, consulte Métodos DataContext (O / R Designer).

Tags What's this?: Add a tag
Flag as ContentBug
Page view tracker