3

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?

1 Answer 1

2

Instead of BackgroundWorker use either parallel task library or manually created threads. Pass connection string to the thread/task and create context in there (context is not thread safe so it should be handled in the thread directly). Execute your queries and return result to the main request processing thread where you make union of result sets.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.