Vfrdtyky

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);

}