Implementing Optimistic Concurrency with Stored Procedures
Visual Studio can implement optimistic concurrency with stored procedures. Ultimately this approach builds an SQL command with a WHERE clause that contains all of the original data store values. Parameters that include the WHERE clause values are passed to a stored procedure within the data store. The WHERE clause will not return any records if changes have occurred to the data store. These changes can be handled according to business needs.
To implement optimistic concurrency with stored procedures
- If you do not already have a connection to a database, add one. For more information, see Adding New Data Connections in Server Explorer.
- Drag a table from Server Explorer onto the designer surface.
You will get an adapter object and a connection object.Note If you are working with a project that has an interface (such as a Windows application or a Web application), a component tray will appear at the bottom of the designer. This tray allows visual development of components that have no run-time user interface. If you are working with other project types (such as an XML Web service), the objects will appear on the Component Designer — that is, the entire main window will provide a component design surface.
- Right-click the adapter object, and then select Configure Data Adapter from the shortcut menu to display the Data Adapter Configuration Wizard.
- Click Next and then click Next again for the Choose a Query Type page.
- Make sure that Create new stored procedures is selected, and then click Next.
- In the Generate the stored procedures page, click the Query Builder button.
- In the Query Builder, check the individual fields you want.
Note In the Generate the stored procedures page, click the Advanced Options button, and notice the Use optimistic concurrency option is selected by default.
- Click OK, and then click Next to bring up the Create the Stored Procedures page.
- In the Create the Stored Procedures page, assign names to each of the four stored procedures that will be generated, and you can have the wizard automatically create them in the database for you or choose to manually create them later. Click Next then Finish to complete the wizard.
You can inspect the results of the configuration by examining the three update-related data adapter command properties: the DeleteCommand, InsertCommand, and the UpdateCommand.