LINQ - Updating Data Using Stored Procedures

Hi,

This blog post summarizes the overview of LINQ.

Below are the new features been introduced in Orcas

§ Automatic Properties

§ Object Initializers

§ Collection Initializers

§ Extesnion Methods

§ Lambda Expressions - p => expressions

§ Query Syntax

§ Anonymous Types

§ Var Keyword

These language features help in making query against relational data. This overall querying programming model is called as "LINQ" - which stands for .NET Language Integrated Query.

Insert/Update/Delete Using Stored Procedures

LINQ to SQL is a very flexible ORM. It enables you to write clean object-oriented code to retrieve, update and insert data.

· Best of all - it enables you to cleanly design Data Model Classes independent of how they are persisted and loaded from a database.

· You can use the built-in ORM engine to efficiently retrieve and update data in the database using Dynamic SQL.

· Or alternatively you can configure your data layer to use SPROCs.

· The nice thing is that your code consuming the data layer, and all of the business logic validation rules you annotate it with, can be the same regardless of which persistence approach you use.

How to Use SPROCs for Insert/Update/Delete Scenarios

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:

· 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

· By adding a NorthwindDataContext partial class in our project, and then by implementing the appropriate Insert/Update/Delete partial methods provided on it that will be called when we insert/update/delete data model objects. 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.

In general recommended is using the LINQ to SQL designer to configure the SPROCs for the 90% case - and then in more advanced scenarios go in and custom tweak the SPROC invocation code it generates if you need to.

Here is the practical explanation of when which approach (Dynamic SQL or SPROC) is used. Take an example as below.

private void InsertOrder()

{

NorthwindDataContext db = new NorthwindDataContext();

Customer customer = db.Customers.Single(c => c.CustomerID == "ALFKI");

Order order = new Order();

order.OrderDate = DateTime.Now;

order.RequiredDate = DateTime.Now.AddDays(3);

order.ShipCity = "Beverly";

order.ShipPostalCode = "33333";

customer.Orders.Add(order);

db.SubmitChanges();

}

Default - Dynamic SQL: When SubmitChanges() is fired at the end, LINQ to SQL will check that our business logic rules are valid, and if so AUTOMATICALLY GENERATE THE APPROPRIATE DYNAMIC SQL (Here Insert Statement) to update our Customer record above, and insert a new record into the Orders table.

Use of SPs: Now if we do not want to use the dynamically generated SQL, and instead wish to use the SP when the SubmitChanges() is fired, then it requires a small change in the ‘Orders’ object. The change would be as:

· Add a new SP in the DB. For e.g InsertOrder

· Drag and Drop it onto LINQ to SQL Designer (Right Panel). See Fig -1

· Tell the data access layer to use the InsertOrder SPROC when inserting new Order objects into the database. To do this select the "Order" class in the LINQ to SQL ORM designer, and then by going to the property grid and clicking the "..." button to override how Insert operations happen for it. See Fig -2

Fig - 1	Fig - 2

What Happens if the SPROC Throws an Error?

If a SPROC raises an error when doing an Insert/Update/Delete operation, LINQ to SQL will automatically cancel and Rollback The Transaction of all changes associated with the current SubmitChanges() call on the DataContext. This ensures that your data is always kept in a clean, consistent state.

Reference: http://weblogs.asp.net/scottgu/archive/2007/08/23/linq-to-sql-part-7-updating-our-database-using-stored-procedures.aspx

Hope this helps.

Thanks & Regards,

Arun Manglick || Tech Lead