Share via

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

  • Article

Applies to: yesVisual Studio noVisual Studio for Mac

Note

This article applies to Visual Studio 2017. If you're looking for the latest Visual Studio documentation, see Visual Studio documentation. We recommend upgrading to the latest version of Visual Studio. Download it here

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 run-time behavior that performs Inserts, Updates, and Deletes when changes are saved from entity classes to a database (for example, when calling the SubmitChanges method).

Note

If your stored procedure returns values that need to be sent back to the client (for example, values calculated in the stored procedure), create output parameters in your stored procedures. If you cannot use output parameters, write a partial method implementation instead of relying on overrides generated by the O/R Designer. Members mapped to database-generated values need to be set to appropriate values after successful completion of INSERT or UPDATE operations. For more information, see Responsibilities of the Developer In Overriding Default Behavior.

Note

LINQ to SQL handles database-generated values automatically for identity (auto-increment), rowguidcol (database-generated GUID), and timestamp columns. 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: AutoSync.Always, AutoSync.OnInsert, or AutoSync.OnUpdate.

Configure 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.

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in this article. You may be using a different edition of Visual Studio or different environment settings. For more information, see Personalize the IDE.

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 or 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 the 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 and 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 appears and provides you an opportunity to apply your changes.

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