Walkthrough: Customizing the Insert, Update, and Delete Behavior of Entity Classes

Article
08/02/2012

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 use LINQ technology to access SQL databases. For more information, see Language-Integrated Query (LINQ).

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 schema of the table (the column definitions and primary key information). 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 work with the data in the database. You can also do this when the default behavior is not generated, for example, when your entity classes map to views. Additionally, you can override the default update behavior when the database requires table access through stored procedures. For more information, see How to: Override By Using Stored Procedures (LINQ to SQL).

Note

This walkthrough requires the availability of the 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.

This walkthrough provides the steps that you must follow to override the default LINQ to SQL runtime behavior for saving data back to a database by using stored procedures.

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

Create a new Windows Forms application and add a LINQ to SQL file to it.
Create an entity class that is mapped to the Northwind Customers table.
Create an object data source that references the LINQ to SQL Customer class.
Create a Windows Form that contains a DataGridView that is bound to the Customer class.
Implement save functionality for the form.
Create DataContext methods by adding stored procedures to the O/R Designer.
Configure the Customer class to use stored procedures to perform Inserts, Updates, and Deletes.

Prerequisites

To complete this walkthrough, you need the following:

Access to the SQL Server version of the Northwind sample database. For more information, see How to: Install Sample Databases.
The InsertCustomer, UpdateCustomer, and DeleteCustomer stored procedures for the Northwind database. For more information, see Walkthrough: Creating Update Stored Procedures for the Northwind Customers Table.

Creating an Application and Adding LINQ to SQL Classes

Because you will be working with LINQ to SQL classes and displaying the data on a Windows Form, create a new Windows Forms application and add a LINQ to SQL Classes file.

To create a new Windows Application project that contains LINQ to SQL classes

From the File menu, create a new project.
Name the project UpdatingwithSProcsWalkthrough.

Note

The O/R Designer is supported in Visual Basic and C# projects. Therefore, create the new project in one of these languages.
Click the Windows Forms Application template and click OK. For more information, see Creating Windows-based Applications.

The UpdatingwithSProcsWalkthrough project is created and added to Solution Explorer.
On the Project menu, click Add New Item.
Click the LINQ to SQL Classes template and type Northwind.dbml in the Name box.
Click Add.

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

Creating the Customer Entity Class and Object Data Source

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 LINQ to SQL entity classes that map to the tables in the database. After you create entity classes, they can be used as object data sources just like other classes that have public properties.

To create a Customer entity class and configure a data source with it

In Server Explorer/Database Explorer, locate the Customer table in the SQL Server version of the Northwind sample database. For more information, see How to: Create a Data Connection to the Northwind Database.
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.

Note

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).
On the Build menu, click Build UpdatingwithSProcsWalkthrough to build the project.
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 UpdatingwithSProcsWalkthrough node and locate and select the Customer class.

Note

If the Customer class is not available, cancel out of the wizard, 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.

Creating a DataGridView to Display the Customer 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 that are bound to the entity classes

Open Form1 in Design view.
From the Data Sources window, drag the Customer node onto Form1.

Note

To display the Data Sources window, click Show Data Sources on the Data menu.
Open Form1 in the Code Editor.

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

Private NorthwindDataContext1 As New NorthwindDataContext

private NorthwindDataContext northwindDataContext1
    = new NorthwindDataContext();

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

CustomerBindingSource.DataSource = NorthwindDataContext1.Customers

customerBindingSource.DataSource
    = northwindDataContext1.Customers;

Implementing Save Functionality

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 database 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

Open Form1 in Design view.
Select the save button on the CustomerBindingNavigator (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:

NorthwindDataContext1.SubmitChanges()

northwindDataContext1.SubmitChanges();

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

To override the default update behavior

Open the LINQ to SQL file in the O/R Designer. (Double-click the Northwind.dbml file in Solution Explorer.)
In Server Explorer/Database Explorer, expand the Northwind databases Stored Procedures node and locate the InsertCustomers, UpdateCustomers, and DeleteCustomers stored procedures.
Drag all three stored procedures onto the O/R Designer.

The stored procedures are added to the methods pane as DataContext methods. For more information, see DataContext Methods (O/R Designer).
Select the Customer entity class in the O/R Designer.
In the Properties window, select the Insert property.
Click the ellipsis (...) next to Use Runtime to open the Configure Behavior dialog box.
Select Customize.
Select the InsertCustomers method in the Customize list.
Click Apply to save the configuration for the selected Class and Behavior.

Note

You can continue to configure the behavior for each class/behavior combination as long as you click Apply after you make each change. If you change the class or behavior before you click Apply, a warning dialog box providing an opportunity to apply any changes will appear.
Select Update in the Behavior list.
Select Customize.
Select the UpdateCustomers method in the Customize list.

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 makes it easier to track changes and create statements that check for concurrency violations.
Map the Original_CustomerID method argument to the CustomerID (Original) class property.

Note

By default, method arguments will map to class properties when the names match. If property names are 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 O/R 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).
Click Apply to save the configuration for the selected Class and Behavior.
Select Delete in the Behavior list.
Select Customize.
Select the DeleteCustomers method in the Customize list.
Map the Original_CustomerID method argument to the CustomerID (Original) class property.
Click OK.

Note

Although it is not an issue for this particular walkthrough, it is worth noting that LINQ to SQL handles database-generated values automatically for identity (auto-increment), rowguidcol (database-generated GUID), and timestamp columns during Inserts and Updates. Database-generated values in other column types will unexpectedly result in a null value. To return the database-generated values, you should manually set IsDbGenerated to true and AutoSync to one of the following: Always, OnInsert, or OnUpdate.

Testing the Application

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

To test the application

Press F5.
Modify a record in the grid to test the Update behavior.
Add a new record to test the Insert behavior.
Click the save button to save changes back to the database.
Close the form.
Press F5 and verify that the updated record and the newly inserted record persisted.
Delete the new record you created in step 3 to test the Delete behavior.
Click the save button to submit the changes and remove the deleted record from the database
Close the form.
Press F5 and verify that the deleted record was removed from the database.

Note

If your application uses SQL Server Express Edition, depending on the value of the Copy to Output Directory property of the database file, the changes may not appear when you press F5 in step 10. For more information, see How to: Manage Local Data Files in Your Project.

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:

Implement concurrency checking during updates. For information, see Optimistic Concurrency Overview (LINQ to SQL).
Add LINQ queries to filter data. For information, see Introduction to LINQ Queries.