This programming model symmetry is powerful both in that you don't have to learn two ways of doing things, and also because it means that you don't have to decide up front at the beginning of your project whether you are going to use SPROCs or not.
You can start off using the dynamic SQL support provided by the LINQ to SQL ORM for all queries, inserts, updates and deletes.
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.
or: 2) By adding a Northwind Data Context partial class in our project, and then by implementing the appropriate Insert/Update/Delete partial methods provided on it (for example: Insert Order, Update Order, Delete Order) that will be called when we insert/update/delete data model objects.
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.
In today's blog post I'm going to discuss how you can also optionally use SPROCs to update/insert/delete data from the database.
To help illustrate this - let's start from scratch and build-up a data access layer for the Northwind sample database: In my Part 2: Defining our Data Model Classes tutorial I discussed how to use the LINQ to SQL ORM designer that is built-in to VS 2008 to create a LINQ to SQL class model like below: After defining our data model classes and relationships we'll want to add some business logic validation to our data model.
These partial methods will be passed the data model instances we want to update, and we can then execute whatever SPROC or SQL code we want to save it within the database.
When we use approach #1 (the LINQ to SQL designer) to graphically configure SPROCs to call, it is under the covers generating the same code (in a partial class it creates) that you'd write when using approach #2.
Over the last few weeks I've been writing a series of blog posts that cover LINQ to SQL.
LINQ to SQL is a built-in O/RM (object relational mapper) that ships in the .
Like I discussed in my last blog post in this series (Part 6: Retrieving Data Using Stored Procedures), we can drag/drop SPROCs from the server-explorer onto the method pane of our Data Context designer.