How to run a long-running stored procedure on SQL Server, even if I get disconnected

These times where your connection is forced closed is usually an indication of throttling. I have found that the main reason for throttling is from high numbers of outstanding requests within your database. Unfortunately there are no hard limits that can be monitored to identify when this throttling occurs. The good news is that the lower limits have been documented which means you can track when you are getting close to the upper limits.

To give a plug to our Cotega monitoring service, this is one of the main things we track to allow people to be notified when they approach these upper limits. Another useful thing to do when this happens is to do a deep database diagnostics to see what expensive queries, connections, etc are occurring within your database as described on How to Perform SQL Azure Performance & Diagnostics Analysis for SQL Azure.

What I'm totally stopped on is I don't see any equivalent of a List is SQL. Is there a way to build all this up in a list?

So a list in SQL Server is basically a table. You can use table-valued parameters to pass in your complete list from C#. You basically just create a table type to define that list you are passing in, see the example in the MSDN link.

You would just need to take each requirement you list in your question or that the C# is performing, and write the query to pull the correct values from your tabled-values list and create your INSERT, DELETE, and UPDATE statements based on those values.

So for

Any domain in the passed in list that is not in CompanyDomains is added to CompanyDomains.

Something like:

INSERT INTO CompanyDomains (<column list>
SELECT <column list>
FROM @TableParameter t
WHERE NOT EXISTS ( SELECT domain FROM CompanyDomains cd WHERE t.domain = cd.domain );