Custom Insert logic with Linq to SQL

On the forums, it was asked how to have your own custom logic for the insert process, for example using a stored procedure to insert the object.

One approach is to redefine the insert behaviour within the designer inside Visual Studio 2008.  For these examples, I’m going to be the Category table in Northwind.

Firstly, create your stored procedure which accepts all the properties on the object as arguments.  The stored procedure needs to have a special OUTPUT parameter.   This output parameter should return the ID of the row being inserted so Linq can re-populate the ID for the object and track any future changes.

CREATE PROCEDURE [dbo].[InsertCategory]
@categoryID INT OUTPUT,
@categoryName nvarchar(15),
@description ntext,
@picture image
AS

INSERT INTO [Northwind].[dbo].[Categories]
           ([CategoryName]
           ,[Description]
           ,[Picture])
     VALUES
           (@categoryName
           ,@description
           ,@picture)

SET @CategoryID = CAST (SCOPE_IDENTITY() as Int)

Information on why to use Scope Identity vs Identity can be found here.

Then, drag it onto the designer so it is a method on the data context.

Then, select the category table on your designer surface (add it if required). In the properties window, you can set the action to be taken for Delete, Insert and Update.  Select insert and click the button on the property.

The Configure Behaviour form will then be displayed. Here, you can select to Customize the action, and then select the stored procedure to use on the data context.  If should then auto configure the parameters for the sproc, but you manually do this stage if required.

At this point, whenever you insert a Category and call SubmitChanges, the stored procedure will be used instead of Linq’s own code. You can do configure update and delete in a similar fashion. However, with update sprocs you can access in the original data values as well as the updated data.

The other approach is to use the partial methods feature of C# 3.0.  In a partial class for your DataContext, you add the partial method for InsertCategory which calls your stored procedure on the data context.

partial void InsertCategory(Category instance)
{
    System.Nullable nullableID = instance.CategoryID;
    this.InsertCategory(ref nullableID, instance.CategoryName, instance.Description, instance.Picture);
    instance.CategoryID = nullableID.GetValueOrDefault();
}

Another approach is just to have ADO.net code but I think the above method is neater.

partial void InsertCategory(Category instance)
{
    SqlConnection sqlConn = new SqlConnection(global::LinqPOC.Properties.Settings.Default.NorthwindConnectionString);
    SqlCommand sqlCmd = new SqlCommand(“InsertCategory”, sqlConn);
    //Setup Parameters for sproc
    try
    {
        sqlConn.Open();
        int insertedID = (int)sqlCmd.ExecuteScalar();
        instance.CategoryID = insertedID;
    }
    finally
    {
        sqlConn.Close();
    }
}

Which approach should you use? Well, under the covers they are very similar. Instead of having a partial method within the designer, as below.

partial void InsertCategory(Category instance);

There is a private InsertCategory implementation. This takes the category object being inserted, calls the stored procedure as a method on the datacontext and then repopulates the CategoryID

private void InsertCategory(Category obj)
{
    System.Nullable p1 = obj.CategoryID;
    this.InsertCategory(ref p1, obj.CategoryName, obj.Description, obj.Picture);
    obj.CategoryID = p1.GetValueOrDefault();
}

As such, it just depends on if you want to implement the method yourself or have the designer do it for you.

Forum post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2592415&SiteID=1