Pfthb

I have a piece of code which looks like this:

string filePath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + @"DeletedItems" + "DeletedItems" + ".xml";
 XDocument xmlDoc = XDocument.Load(filePath);
 var list = xmlDoc.Root.Elements("ItemID")
 .Select(element => element.Value)
 .ToList();
 var idsList = FormItemIdList(list);

 ctx.zsp_deleteEndedItems(idsList);
 ctx.zsp_deleteEndedItemsTransactions(idsList);

This part of the code loads up all items from the XML file:

var list = xmlDoc.Root.Elements("ItemID")
 .Select(element => element.Value)
 .ToList();

Where the XML file looks like this:

<?xml version="1.0" encoding="utf-8"?>
<ItemsToDelete>
 <ItemID>113347292264</ItemID>
 <ItemID>113334066205</ItemID>
 <ItemID>113331816848</ItemID>
 <ItemID>113191634415</ItemID>
 <ItemID>183480362055</ItemID>
 <ItemID>113303425739</ItemID>
 <ItemID>112533425202</ItemID>
 <ItemID>112007496785</ItemID>
 <ItemID>111956371906</ItemID>
 <ItemID>112016647700</ItemID>
</ItemsToDelete>

Once the items are loaded into list from C# I form a string which looks like following:

113347292264,113334066205... etc etc

The string is then passed to the stored procedure which looks like following:

create procedure [dbo].[zsp_deleteEndedItems]
(
@ItemIDList nvarchar(max)
)
as
delete from 
SearchedUserItems
WHERE EXISTS (SELECT 1 FROM dbo.SplitStringProduction(@ItemIDList,',') S1 WHERE ItemID=S1.val) 

Then the SplitStringProduction function is called to break down the passed string and delete the records in DB based on the passed item ID:

create FUNCTION [dbo].[SplitStringProduction]
(
 @string nvarchar(max),
 @delimiter nvarchar(5)
) RETURNS @t TABLE
(
 val nvarchar(500)
)
AS
BEGIN
 declare @xml xml
 set @xml = N'<root><r>' + replace(@string,@delimiter,'</r><r>') + '</r></root>'

 insert into @t(val)
 select 
 r.value('.','varchar(500)') as item
 from @xml.nodes('//root/r') as records(r)

 RETURN
END

Now, all this works fine, but there are some problems that I'm aware of and that I would like to avoid:

1. The list from the XML file can be very long, often containing 100000 + records inside

Now to avoid this I was thinkng to do the following:

• Perform deletion in batches of 5000 records let's say

So the steps would be:

• Pull 5000 items from the XML file


• Pass those 5000 items into the procedure


• Remove the 5000 ItemID's from the XML file

Can someone help me out with this, I'm not sure how to do it ?

It sounds like you just want to split the list into smaller chunks:

var list = xmlDoc.Root.Elements("ItemID")
 .Select(element => element.Value)
 .ToList();
while(list.Any())

 var subList = list.Take(5000);
 var idsList = FormItemIdList(subList);

 ctx.zsp_deleteEndedItems(idsList);
 ctx.zsp_deleteEndedItemsTransactions(idsList);

 list.RemoveRange(subList);