How to: Assign stored procedures to perform updates, inserts, and deletes (O/R Designer)

Stored procedures can be added to the O/R Designer and executed as typical DataContext methods. They can also be used to override the default LINQ to SQL runtime behavior that performs Inserts, Updates, and Deletes when changes are saved from entity classes to a database (for example, when calling the SubmitChanges method).

Configuring the Update Behavior of an Entity Class

By default, the logic to update a database (Inserts, Updates, and Deletes) with changes that were made to the data in LINQ to SQL entity classes is provided by the LINQ to SQL runtime. The runtime creates default Insert, Update, and Delete commands that are based on the schema of the table (the column and primary key information). When the default behavior is not desired, you can configure the update behavior by assigning specific stored procedures for performing the necessary Inserts, Updates, and Deletes required to manipulate the data in your table. You can also do this when the default behavior is not generated, for example, when your entity classes map to views. Finally, you can override the default update behavior when the database requires table access through stored procedures.

To assign stored procedures to override the default behavior of an entity class

1. Open the LINQ to SQL file in the designer. (Double-click the .dbml file in Solution Explorer.)

2. In Server Explorer/Database Explorer, expand Stored Procedures and locate the stored procedures that you want to use for the Insert, Update, and/or Delete commands of the entity class.

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

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

4. Select the entity class for which you want to use the stored procedure for performing updates.

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

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

7. Select Customize.

8. Select the desired stored procedure in the Customize list.

9. Inspect the list of Method Arguments and Class Properties to verify that the Method Arguments map to the appropriate Class Properties. Map the original method arguments (Original_ArgumentName) to the original properties (PropertyName (Original)) for Update and Delete commands.

   Note

   By default, method arguments map to class properties when the names match. If changed property names 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.

10. Click OK or Apply.

    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.

    To revert to using the default runtime logic for updates, click the ellipsis next to the Insert, Update, or Delete command in the Properties window and then select Use runtime in the Configure Behavior dialog box.

See Also

LINQ to SQL Tools in Visual Studio DataContext Methods (O/R Designer) Walkthrough: Creating LINQ to SQL Classes (O-R Designer) LINQ to SQL Insert, Update, and Delete Operations