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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: