How to keep adding up a DataTable?

[Povilas_Jonikas]

Hello guys. I’ve ran into a problem, that dont really know how to solve. I am doing a Ebay API based code, that Loops through an API, deserializes given data and at the end, bulk inserts it to SQL table. But the thing is, that the API im using is filtered by ‘EntriesPerPage’ and PageNumber. That means, that im doing over a hundred API calls for one specific account to get all of the data i need. The code that Im using to get the data i need looks like this:

string xmlResponse = @in_XMLResponse;
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml(xmlResponse);

XmlNamespaceManager nsManager = new XmlNamespaceManager(xmlDoc.NameTable);
nsManager.AddNamespace("ebay", "urn:ebay:apis:eBLBaseComponents");

XmlNode totalPagesNode = xmlDoc.SelectSingleNode("//ebay:PaginationResult/ebay:TotalNumberOfPages", nsManager);
int totalNumberOfPages = 0;
if (totalPagesNode != null && int.TryParse(totalPagesNode.InnerText, out totalNumberOfPages))
{
    // Assign the parsed total number of pages to an output variable
    Out_TotalNumberOfPages = totalNumberOfPages;
}
else
{
    // Handle the error or assign a default value if needed
    Out_TotalNumberOfPages = -1; // example error value
}

dataTable = new DataTable();
dataTable.Columns.Add("ItemID", typeof(string));
dataTable.Columns.Add("Title", typeof(string));
dataTable.Columns.Add("SKU", typeof(string));
dataTable.Columns.Add("CurrentPrice", typeof(string));
dataTable.Columns.Add("BuyItNowPrice", typeof(string));
dataTable.Columns.Add("StartTime", typeof(string));
dataTable.Columns.Add("ViewItemURL", typeof(string));
dataTable.Columns.Add("ListingDuration", typeof(string));
dataTable.Columns.Add("Quantity", typeof(string));
dataTable.Columns.Add("ShippingServiceCost", typeof(string));
dataTable.Columns.Add("QuantityAvailable", typeof(string));
dataTable.Columns.Add("ShippingProfileName", typeof(string));
dataTable.Columns.Add("ReturnProfileName", typeof(string));
dataTable.Columns.Add("PaymentProfileName", typeof(string));
// Summary details
dataTable.Columns.Add("ActiveAuctionCount", typeof(int));
dataTable.Columns.Add("AuctionSellingCount", typeof(int));
dataTable.Columns.Add("TotalAuctionSellingValue", typeof(decimal));
dataTable.Columns.Add("TotalSoldCount", typeof(int));
dataTable.Columns.Add("TotalSoldValue", typeof(decimal));
dataTable.Columns.Add("SoldDurationInDays", typeof(int));

XmlNodeList itemNodes = xmlDoc.SelectNodes("//ebay:ActiveList/ebay:ItemArray/ebay:Item", nsManager);

foreach (XmlNode itemNode in itemNodes)
{
    string itemID = itemNode.SelectSingleNode("ebay:ItemID", nsManager)?.InnerText;
    string title = itemNode.SelectSingleNode("ebay:Title", nsManager)?.InnerText;
    string sku = itemNode.SelectSingleNode("ebay:SKU", nsManager)?.InnerText;
    string currentPrice = itemNode.SelectSingleNode("ebay:SellingStatus/ebay:CurrentPrice", nsManager)?.InnerText;
    string buyItNowPrice = itemNode.SelectSingleNode("ebay:BuyItNowPrice", nsManager)?.InnerText;
    string startTime = itemNode.SelectSingleNode("ebay:ListingDetails/ebay:StartTime", nsManager)?.InnerText;
    string viewItemURL = itemNode.SelectSingleNode("ebay:ListingDetails/ebay:ViewItemURL", nsManager)?.InnerText;
    string listingDuration = itemNode.SelectSingleNode("ebay:ListingDuration", nsManager)?.InnerText;
    string quantity = itemNode.SelectSingleNode("ebay:Quantity", nsManager)?.InnerText;
    string shippingServiceCost = itemNode.SelectSingleNode("ebay:ShippingDetails/ebay:ShippingServiceOptions/ebay:ShippingServiceCost", nsManager)?.InnerText;
    string quantityAvailable = itemNode.SelectSingleNode("ebay:QuantityAvailable", nsManager)?.InnerText;
    string shippingProfileName = itemNode.SelectSingleNode("ebay:SellerProfiles/ebay:SellerShippingProfile/ebay:ShippingProfileName", nsManager)?.InnerText;
    string returnProfileName = itemNode.SelectSingleNode("ebay:SellerProfiles/ebay:SellerReturnProfile/ebay:ReturnProfileName", nsManager)?.InnerText;
    string paymentProfileName = itemNode.SelectSingleNode("ebay:SellerProfiles/ebay:SellerPaymentProfile/ebay:PaymentProfileName", nsManager)?.InnerText;

    // Extracting summary details
    int activeAuctionCount = int.Parse(xmlDoc.SelectSingleNode("//ebay:Summary/ebay:ActiveAuctionCount", nsManager)?.InnerText ?? "0");
    int auctionSellingCount = int.Parse(xmlDoc.SelectSingleNode("//ebay:Summary/ebay:AuctionSellingCount", nsManager)?.InnerText ?? "0");
    decimal totalAuctionSellingValue = decimal.Parse(xmlDoc.SelectSingleNode("//ebay:Summary/ebay:TotalAuctionSellingValue", nsManager)?.InnerText ?? "0");
    int totalSoldCount = int.Parse(xmlDoc.SelectSingleNode("//ebay:Summary/ebay:TotalSoldCount", nsManager)?.InnerText ?? "0");
    decimal totalSoldValue = decimal.Parse(xmlDoc.SelectSingleNode("//ebay:Summary/ebay:TotalSoldValue", nsManager)?.InnerText ?? "0");
    int soldDurationInDays = int.Parse(xmlDoc.SelectSingleNode("//ebay:Summary/ebay:SoldDurationInDays", nsManager)?.InnerText ?? "0");

    dataTable.Rows.Add(itemID, title, sku, currentPrice, buyItNowPrice, startTime, viewItemURL, listingDuration, quantity, shippingServiceCost, quantityAvailable, shippingProfileName, returnProfileName, paymentProfileName, activeAuctionCount, auctionSellingCount, totalAuctionSellingValue, totalSoldCount, totalSoldValue, soldDurationInDays);
}

My main concern is, is it possible, to keep adding up the datatable, till the loop for PageNumber ends, and then insert the whole datatable into SQL, or not? Because i can bulk insert 200 rows after each API call, but i dont think its practical. Thanks a lot!

[avejr748 (Ave Salvante Jr)]

To append data to a DataTable, you can use the “Merge Data Table” activity. This activity allows you to combine two DataTables by adding the rows from one DataTable to another. Here’s how you can do it:

1. Ensure you have two DataTables: The one you want to append to (let’s call it DestinationDataTable) and the one with the new rows you want to add (let’s call it SourceDataTable).
2. Drag the “Merge Data Table” activity into your workflow.
3. In the “Merge Data Table” properties panel, set the following:

• Destination: The DataTable variable for DestinationDataTable.
• Source: The DataTable variable for SourceDataTable.

4. Configure additional properties if needed, such as:

• MissingSchemaAction: Choose what action to take when the source and destination DataTable schemas do not match.
• PreserveChanges: Specify whether to preserve changes in the destination DataTable.

5. Run the workflow. The DestinationDataTable will now have the rows from SourceDataTable appended to it.

If you need to create a new DataTable to append to your existing one, you can use the “Build Data Table” activity to define the schema and then use the “Add Data Row” or “Add Data Column” activities to populate it before merging. Remember to handle any potential issues with schema mismatches or duplicate rows according to your specific requirements.

[Povilas_Jonikas]

Works perfectly, thanks