Updating database stored procedure
You can then add your business and validation rules to your model.
And then later you can optionally update your data mapping model to use SPROCs - or not if you decide you don't want to.
We'll first go to the Visual Studio "Server Explorer" window, expand into the "Stored Procedures" node of our database, and then right-click and choose "Add New Stored Procedure": We'll then create a new SPROC that we'll call "Insert Order" that inserts a new order record into our Orders table: Notice above how the SPROC defines the "Order ID" parameter as an output param.
This is because the Order ID column in the database is an identity column that is set to auto-increment each time a new record is added.
We can modify the data access layer we've been building to use SPROCs to handle updates, instead of dynamic SQL, in one of two ways: 1) By using the LINQ to SQL designer to graphically configure SPROCs to execute in response to Insert/Update/Delete operations on our data model classes.
Let's look at a simple scenario using it where we retrieve an existing customer object, update the customer's Contact Name and Phone Number, and then create a new Order object and associate it with them.
Once defined in partial classes like below, these validation methods will automatically be enforced anytime we write code to update our data model objects in an application.
Now that we have our data model classes created, and have applied validation rules to them, we can query and interact with the data.
The caller of the SPROC will pass in NULL as a value when calling it - and the SPROC then passes back the newly created Order ID value as the output value (by calling the SCOPE_IDENTITY() function at the end of the SPROC).
After creating the SPROC we'll then open up the LINQ to SQL ORM designer for our data access layer.