LINQ to SQL Insert Operation by Stored Procedure using C#

Updated on 09 Apr 2012,
Published on 31 Dec 2010

LINQ to SQL DataContext class generated by using O/R mapper designer tool provides the two ways to perform the insert operation for storing the new records into the database. The first way generates the Insert SQL command at runtime and pushes the fresh data into the data table whereas the second way enables to attach the stored procedure for performing the insert operation. You can drag and drop the stored procedure created from the Data Explorer of Visual Studio Web Developer 2010 onto the designer area of O/R mapper tool displaying layout diagram of data context class. In this tutorial we have used the Northwind SQL database to perform the insert operation over the categories table by stored procedure using LINQ to SQL C# code.

Stored Procedure

CREATE PROCEDURE sproc_InsertCategory
 @CategoryID   INT OUTPUT,
 @CategoryName  NVARCHAR(15),
 @Description  NTEXT,
 @Picture   IMAGE
AS
BEGIN
 INSERT INTO Categories(CategoryName, Description, Picture)
 VALUES(@CategoryName, @Description, @Picture)
 
 SET @CategoryID = SCOPE_IDENTITY()
END

The above stored procedure has been used to insert a new category into the categories table. After creating the above stored procedure you can follow the steps given below to attach and customize the way of insert operation using LINQ to SQL ORM:

1. In the Data Explorer, locate the insert stored procedure created above and drop it onto the designer area of O/R mapper as shown in the figure 1. This will add a new method on the designer surface with a name same as of the stored procedure. You can easily rename it by selecting the stored procedure on the designer area and pressing the F4 key. It will open the properties window where you can edit the name for the selected stored procedure.

 

Figure 1

2. Now select the category entity class diagram in the designer area and press F4 to open its properties window. In the properties window locate the Insert property to attach the stored procedure added in the first step to the insert operation of category class as shown in the figure 2.

Figure 2

3. Click the [...] button that appears in the Insert property locator to open the configuration behavior of insert operation for category entity class as shown in the figure 3.

Figure 3

By default the configure behavior dialog box will show the "Use runtime" behavior option selected. Change the selection to "Customize" and select the Insert method for categories from the dropdown menu just below the "Customize" option as shown in the above figure.

4. When you will select the Insert method associated to the insert operation for category entity class from the dropdown menu it will populate the method parameters and class properties below it automatically as shown in figure 4.

Figure 4

Press ok and save the changes made to data context class in the O/R designer. It will auto-generate the strongly typed method in the DataContext class that will call the stored procedure for inserting a new category into the SQL database table.

C# Code Example for LINQ to SQL Insert Operation using Stored Procedure

DataClassesDataContext dc = new DataClassesDataContext();

Category category = new Category();
category.CategoryName = "new category";
category.Description = "new category description";

dc.Categories.InsertOnSubmit(category);

dc.SubmitChanges();

The above C# code will call the stored procedure to insert the new category into the underlying categories table of SQL Northwind database.

Continue to next tutorial: LINQ to SQL Update Operation by Stored Procedure using C# to learn how to update the database record using stored procedure with LINQ to SQL.

1 Responses to "LINQ to SQL Insert Operation by Stored Procedure using C#"
Jim
Your tutorial was the only tutorial I could find that says to use an OUTPUT clause to return the new identity, THANK YOU! I've been trying to do a SELECT at the end of the proc and it wouldn't work.
Leave a Comment
* required
* required
* will not be published
* optional
* hint: http://www.example.com
  • Subscribe via Email