Click here to Skip to main content
Click here to Skip to main content

Bulk INSERT / UPDATE / DELETE in LINQ to SQL

, 22 Jan 2010
Rate this:
Please Sign up or sign in to vote.
This article discusses SET based operations involving INSERT / UPDATE and DELETE using LINQ to SQL.

Introduction

LINQ to SQL is great as an ORM tool. It provides ease of access to data in our data oriented applications. It is also very easy to learn. In this article, we will be discussing about SET based operations for database operations using LINQ to SQL.

Background

Like many other ORMs, it is not very good with data insertion, manipulation, and deletion. Though it does allow all of these operations, all of these operations are not efficient in terms of the performance standpoint of applications involving large data manipulations. These operations are submitted to the database as soon as we call the submitChanges() method of our DataContext object. These are submitted as individual INSERT, UPDATE, or DELETE statements for each record involved. You might notice these statements using SQL Profiler when the submitChanges() method is called.

Since the system does not support bulk insertion, manipulation, or deletion out of the box, we need to provide this functionality ourselves.

Discussion

We know that LINQ to SQL supports Stored Procedures as a first class citizen through DataContext. Like entities, we need to add the definition of the Stored Procedures to our DBMLs. The solution proposed here would use a Stored Procedure. This is not a generic solution, but would help in doing bulk operations with your database entities. Those who have been doing bulk database operations in .NET might have used similar solutions using datasets. This involves sending XML to the Stored Procedures and using OpenXML for applying SET based operations to our data.

To present the solution, let us create a table in the database. I am using SQL Server 2005. We are creating a table named TBL_TEST_TEST with two columns (ID and Name). Here, ID is an identity column which is the primary key of the table.

CREATE TABLE [dbo].[TBL_TEST_TEST]
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    [NAME] [varchar](50) 
)

We create a C# console project named TestIQueryable.csproj.

Create_Project.JPG

Now, we add a LINQ to SQL class item to our project, named Test.dbml.

AddDbml.JPG

Add a connection to your database in Server Explorer, and drop TBL_TEST_TEST to the LINQ to SQL designer for Test.dbml.

DragEntity.JPG

Now let us discuss about each operation separately.

Bulk Insert

Let us start with the insertion of bulk data in our database. We create a Stored Procedure in the database as follows:

CREATE PROCEDURE [dbo].[spTEST_InsertXMLTEST_TEST](@UpdatedProdData nText)
AS 
 DECLARE @hDoc int   
 
 exec sp_xml_preparedocument @hDoc OUTPUT,@UpdatedProdData 

 INSERT INTO TBL_TEST_TEST(NAME)
 SELECT XMLProdTable.NAME
    FROM OPENXML(@hDoc, 'ArrayOfTBL_TEST_TEST/TBL_TEST_TEST', 2)   
       WITH (
                ID Int,                 
                NAME varchar(100)
            ) XMLProdTable

EXEC sp_xml_removedocument @hDoc

As discussed earlier, we are passing XML data to this Stored Procedure in the nText argument. We are using OpenXML to get data and insert into our table.

Now, we open the LINQ to SQL designer for adding this Stored Procedure to Test.dbml. Drag the Stored Procedure from Server Explorer to the tab of the designer set aside for Stored Procedures. Update the name of the Stored Procedure to some thing meaningful. We update the name to insertTestData.

InsertProcedureDBML.JPG

We open Program.cs and write some code to generate data. Add the following code to the Main method of the Program class:

using (TestDataContext db = new TestDataContext())
{
    TBL_TEST_TEST[] testRecords = new TBL_TEST_TEST[50];
    for (int count = 0; count < 50; count++)
    {
        TBL_TEST_TEST testRecord = new TBL_TEST_TEST();
        testRecord.NAME = "Name : " + count;
        testRecords[count] = testRecord;
    }

    StringBuilder sBuilder = new StringBuilder();
    System.IO.StringWriter sWriter = new System.IO.StringWriter(sBuilder);
    XmlSerializer serializer = new XmlSerializer(typeof(TBL_TEST_TEST[]));    
    serializer.Serialize(sWriter, testRecords);
    db.insertTestData(sBuilder.ToString());
}

You can see that we have neither used db.insertOnSubmit() nor submitChanges(). But we have generated data in an array of TBL_TEST_TEST type objects provided by LINQ to SQL. After generating data in the array, we convert it to XML using XMLSerializer. We pass this XML directly to the Stored Procedure using the DataContext object.

Now finally, we are successful in inserting 50 rows of data in one shot to the database.

Bulk Updates

You might be more interested to learn about operations involving IQueryable types. Let us create this Stored Procedure in the database:

CREATE PROCEDURE [dbo].[spTEST_UpdateXMLTEST_TEST](@UpdatedProdData nText)
AS 
 DECLARE @hDoc int   
 
 exec sp_xml_preparedocument @hDoc OUTPUT,@UpdatedProdData 

 UPDATE TBL_TEST_TEST
 SET 
   TBL_TEST_TEST.NAME = XMLProdTable.NAME
 FROM OPENXML(@hDoc, 'ArrayOfTBL_TEST_TEST/TBL_TEST_TEST', 2)   
       WITH (
                ID Int,                 
                NAME varchar(100)
            ) XMLProdTable
WHERE    TBL_TEST_TEST.ID = XMLProdTable.ID        

EXEC sp_xml_removedocument @hDoc

We add the definition of this Stored Procedure to Test.dbml:

You can remove the code written in Program.cs (Main method). Add the following code:

using (TestDataContext db = new TestDataContext())
{
    var myPackages = from tbl in db.TBL_TEST_TESTs select tbl;

    foreach (TBL_TEST_TEST t in myPackages)
    {
        t.NAME = t.NAME + " _Updated";
    }

    XmlSerializer serializer = new XmlSerializer(typeof(TBL_TEST_TEST[]));
    StringBuilder sBuilder = new StringBuilder();
    System.IO.StringWriter sWriter = new System.IO.StringWriter(sBuilder);
    serializer.Serialize(sWriter, myPackages.ToArray<tbl_test_test>());

    db.updateTestData(sBuilder.ToString());
}

In the above code, we have queried the database using the IQueryable interface. After fetching data, we have updated it using a foreach loop. We have serialized it to XML data, and have written it to StringBuilderObject sBuilder. We have passed this data to the database using the Stored Procedure already added. This way, we achieve sending bulk data to our database for update.

Bulk Deletion

Now finally, we discuss about bulk deletion. This would be nearly the same solution. The difference is just that instead of updating, we will be deleting data. Let's attempt to delete all records from the database with the values of ID greater than 25.

CREATE PROCEDURE [dbo].[spTEST_deleteTEST_TEST](@UpdatedProdData nText)
AS 
 DECLARE @hDoc int   
 
 exec sp_xml_preparedocument @hDoc OUTPUT, @UpdatedProdData 

 DELETE FROM TBL_TEST_TEST
 WHERE ID IN
 (
     SELECT XMLProdTable.ID
        FROM OPENXML(@hDoc, 'ArrayOfTBL_TEST_TEST/TBL_TEST_TEST', 2)   
           WITH (
                    ID Int,                 
                    NAME varchar(100)
                ) XMLProdTable
 )

EXEC sp_xml_removedocument @hDoc

Add the definition of this Stored Procedure to Test.dbml and rename it to deleteTestData.

Like in the Update operation, update the code of the Main method of Program.cs as follows:

using (TestDataContext db = new TestDataContext())
{
    var myPackages = from tbl in db.TBL_TEST_TESTs 
                        where tbl.ID > 25
                        select tbl;

    XmlSerializer serializer = new XmlSerializer(typeof(TBL_TEST_TEST[]));
    StringBuilder sBuilder = new StringBuilder();
    System.IO.StringWriter sWriter = new System.IO.StringWriter(sBuilder);
    serializer.Serialize(sWriter, myPackages.ToArray<tbl_test_test />());

    db.deleteTestData(sBuilder.ToString());
}

History

  • Article posted: 01/20/2010.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Muhammad Shujaat Siddiqi
Software Developer (Senior)
United States United States
Muhammad Shujaat Siddiqi
New Jersey, USA

Comments and Discussions

 
QuestionBulk INSERT on multiple table with foreign key relation PinmemberMember 79569153-Jul-13 5:07 
GeneralMy vote of 2 PinmemberMember 81732605-Apr-13 5:16 
GeneralRe: My vote of 2 Pinmembersobo12318-Mar-14 16:49 
Questionsp_xml_preparedocument is deprecated in 2008r2 and possibly 2005 too Pinmembermonk777713-Feb-12 7:17 
QuestionBulk Inserts for multiple columns PinmemberBikrum00312-Dec-11 6:56 
QuestionUsing SqlBulkCopy with your existing linq-to-sql context. PinmemberMåns Tånneryd28-Nov-11 11:46 
GeneralThere is a more efficient method that uses SqlBulkCopy and a wrapper class - see Microsoft example source. Pinmembersmt5218-Feb-11 4:00 
GeneralMy vote of 1 Pinmemberavinash7778-Feb-11 0:27 
GeneralMy vote of 4 PinmemberStefanHM25-Jan-11 23:03 
QuestionRe: My vote of 4 PinmemberStefanHM28-Jan-11 1:00 
GeneralMy vote of 5 PinmemberMember 455191118-Nov-10 1:13 
GeneralTry User Defined Table Types Pinmemberbeast10026-Jan-10 8:27 
General[My vote of 2] Missing the point PinmvpJosh Fischer21-Jan-10 18:14 
GeneralRe: [My vote of 2] Missing the point PinmemberSergioAmorim10-Jan-14 6:11 
GeneralThanks! PinmemberChuckaChuck21-Jan-10 13:48 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140709.1 | Last Updated 22 Jan 2010
Article Copyright 2010 by Muhammad Shujaat Siddiqi
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid