enter code hereIn my Webapp, I have 5 instances of a database with exact same table schemas on different SQL servers.
I want to execute a SQL query(or queries) using ExecuteStoreQuery() method available in the Objectcontext against all these 5 databases/servers in parallel and return the merged results. I don't care about duplicates so UNION of all the results from all DB's will work for me.
My initial thought was to store the connection strings to all these 5 DB's in my web.config file and in my code , go through each connection string and instantiate an Enitites object with the connections string as param and then use BackGroundWorker to spin off one thread per connection string and execute the same query on all DB's in parallel , one execution in each thread and then combine the result sets from all threads at the end.
This should work, but I'm wondering if there is a better way to solve this and if EF has any inbuilt methods that allows me to do this w/o me mucking around with parallel execution via threads.
Any idea?
UPDATE
/// <summary>
/// Executes a store query in parallel on all the DB servers
/// </summary>
/// <typeparam name="TElement"></typeparam>
/// <param name="commandText"></param>
/// <returns></returns>
public List<TElement> ExecuteStoreQuery<TElement>(string commandText)
{
List<TElement> result = new List<TElement>();
// Create a blocking collection to store the execution results from each task
// A blocking collection is a thread safe collection that can be updated by multiple tasks
BlockingCollection<ObjectResult<TElement>> bc = new BlockingCollection<ObjectResult<TElement>>();
// Create a Task to run in paralled for each connection string
List<Task> tasks = new List<Task>();
foreach (string connString in this.connectionStrings)
{
Task task = Task.Factory.StartNew(() =>
{
// Create a new Connection
MyEntities entity = new MyEntities(connString);
this.contexts.Add(entity);
// Execute the query
var results = entity.ExecuteStoreQuery<TElement>(commandText);
// Add the results to the collection
if (results != null)
{
bc.Add(results);
}
});
tasks.Add(task);
}
// Wait till all the tasks are done
try
{
Task.WaitAll(tasks.ToArray());
}
catch (AggregateException ae)
{
ae.Handle((x) =>
{
if (x is UnauthorizedAccessException) // This exception is known and we can handle it
{
return true;
}
else
{
return false;
}
});
}
// Add each item in blocking list to our result set
foreach (var item in bc)
{
result.AddRange(item);
}
return result;
}
I have another Question though. I want my method to return results even if some of the connections to the DB's fail. So out of 5 server, if i'm able to connect to atleast 1 server , then I want to return the results, and fail/throw an exception only if i failed to connect to all 5 servers.
How do i do this?