Merging multiple DataTables from different databases in parallel

In a previous post (LINQ to the rescue!) I used linq to merge multiple DataTables, sort them and exclude duplicates.

But, what about filling those DataTables in the first place.  If, like I, you have multiple databases in production at work and need to show to the users a “database agnostic” view of their data, you need to query all those databases and merge the data.

Querying a large number of databases in serial is a slow process, but playing with threads seems … dangerous.

Well, with the new Parallel libraries, this becomes very simple to do, even with Oracle ( ;-) )

Code Snippet

1. var dbs = new List<string>() { "Database1", "Database2", "Database3", "Database4" };
2. var dts = new DataTable[dbs.Count];
3.             
4. Parallel.For(0, dbs.Count, i => LoadData(dbs[i], dts, i));
5.  
6. var myTable = dts[0];
7.  
8. for (int i = 1; i < dbs.Count; i++) myTable.Merge(dts[i]);
9.  
10. var dtLinqData = (from MyRow in myTable.AsEnumerable()
11.                     orderby MyRow.Field<string>("Name") ascending
12.                     select MyRow).Distinct().CopyToDataTable();
13.  
14.  
15. static void LoadData(string db, DataTable[] dts, int index)
16. {
17.     DataTable returnValue = null;
18.  
19.     using (var connection = new OracleConnection(GenerateConnectionString(db, "MyUserId", "MyPassword")))
20.     {
21.         using (var dataAdapter = new OracleDataAdapter("SELECT * FROM CUSTOMER", connection))
22.         {
23.             returnValue = new DataTable();
24.             dataAdapter.Fill(returnValue);
25.         }
26.     }
27.  
28.     dts[index] = returnValue;
29. }
30.  
31. private static string GenerateConnectionString(string instance, string userId, string password)
32. {
33.     return string.Format("data source = {0}; user id = {1}; password={2}; pooling=true; Connection Lifetime=60; Max Pool Size=50", instance, userId, password);
34. }