Iterate Data From SQL Server Making Minimal Calls RRS feed

  • Question

  • I am sure there is a better way to go about doing this, but in my mind - this is how I have devised.  Rough pseudocode.

    This is what I am needing

    1) Query SQL Server to get a list of all employee names

    2) Use a for each loop to iterate a List<string> of those employee names to query an API and populate a DataTable with the results

    3) Once all employee names have been passed to the API - use SQL Bulk Copy to copy data to SQL Table

    4) Run A SQL Server stored procedure  to alter the table we just used Bulk Copy and pushed the data to to add a column called District

    5) Run a SQL Server stored procedure to join the original table on the new table based off employee name and update district

    6) Use C# syntax to now query the sql table and create individual csv files by region.

    I feel like I am using a whole lot of overhead and calls to achieve something that should be simple.  Can someone with more C# expertise provide an assist here on a more logical/fluent way of doing this?

    Monday, February 5, 2018 7:39 PM

All replies

  • In regards to overhead, do you mean lots of code or a performance issue? When considering overhead as "lots of code" one might consider how the code is written, is there a better way (even if it means more code e.g. using Interfaces, a pattern out of the "gang of four").

    Before writing the above did you try and write down what needs to happen from start to finish in plain English w/o mention anything to do with code?

    I really don't want to mention this but "API" is super generic as we have no clue to what "this" API is about but don't answer it now.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, February 5, 2018 9:46 PM
  • The missing link here is the API. What is it? If possible you may want to consider accessing this additional information from the server side (stored procedure) if it involves fetching data using a native SQL Server table.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, February 5, 2018 10:15 PM