I have a C# Web App. I have multiple databases where data is the same, so I can use a Round Robin method to distribute the Database calls.
I plan to read in each connection string, and iterate through each DB and return the data for the first call that passes.
I would like to record the last database that was used, so I can try the next database in the list for the next call that comes in.
A database seems overkill for this, so could I use a static List to track this and lock the read and update of the list?
In terms of doing a round robin approach, you can definitely use a List with a Lock, but I make some general comments below which might be helpful.
You are trying to implement a Network Load Balancer here. You will face a couple of problems. IIS will happily spin up multiple threads of calls to your website if it receives multiple requests before the first one has completed. Secondly if your website is in a WebGarden (multiple instances of IIS on same computer) or runnning as a WebFarm (multiple instances of the OS) or is on Azure or some other cloud platform, then those multiple isntances of your web call might not even be on the same machine (or VM), so you need to be clear that it will be almost impossible to generate a true round robin series of database hits on a properly scalable website.
I'm not sure creating a new synchronisation point between all your web threads is a good idea for scalability. Round Robin is also not the best use of resources - if you want your website to run as fast as possible using as few resources as possible (generally why a NLB system is put in place) then use a Pool based approach to leasing an open database connection rather than iterating around a set of open database connections. The calling code gets handed the next connection which has not been released back into the pool.