|
|
IntroductionLINQ 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.
BackgroundLike 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.
DiscussionWe 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.
 Collapse
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 InsertLet us start with the insertion of bulk data in our database. We create a Stored Procedure in the database as follows:
Collapse
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) ) XMLProdTableEXEC sp_xml_removedocument @hDocAs 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:
Collapse
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 UpdatesYou might be more interested to learn about operations involving IQueryable types. Let us create this Stored Procedure in the database:
Collapse
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) ) XMLProdTableWHERE TBL_TEST_TEST.ID = XMLProdTable.ID EXEC sp_xml_removedocument @hDocWe 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:
Collapse
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 DeletionNow 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.
Collapse
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 @hDocAdd 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:
Collapse
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()); 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)
Author
|
本帖子中包含更多资源
您需要 登录 才可以下载或查看，没有账号？注册
×
|
窥视卡
雷达卡
发表于 2011-5-24 14:06:07
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
抢沙发
千斤顶
显身卡
发表于 2011-6-1 22:58:21
