Updating a Single Column in Linq to SQL using a Stored Procedure

One way to efficiently update specific columns of a database record in Linq to SQL is to write a Stored Procedure which is called with the primary key and the updated column values:

CREATE PROCEDURE [dbo].[UpdateFooMaxID] (
  @OID INT,
  @ID  NVARCHAR(50)
) AS
BEGIN
  SET NOCOUNT ON;
  UPDATE FooMax SET ID = @ID WHERE OID = @OID
END

The stored procedure is then mapped in the .dbml file, and executed from the DataContext:

using (var database = new DataContext())
{
  database.UpdateFooMaxID(OID, newID);
  database.SubmitChanges();
}

The SQL statement generated by this piece of code is simply:

EXEC @RETURN_VALUE = [dbo].[UpdateFooMaxID] @OID = @p0, @ID = @p1

The advantages of this solution are

• it is easy to implement.

• if your organization requires every database access is performed via stored procedures, it is also the only way to go.

I find, though, that there are many disadvantages in this solution:

• a basic operation is coded in a different language (C#/VB.net vs. TSQL)

• each combination of updated columns require a separate stored procedure

• TSQL stored procedures are not compiled or syntax-checked

• later changes in the data model may break the stored procedure without notice

Advertisements