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.
Now, we add a LINQ to SQL class item to our project, named Test.dbml.
Add a connection to your database in Server Explorer, and drop TBL_TEST_TEST to the LINQ to SQL designer for Test.dbml.
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.
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.
