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. }

Oracle Greater Than/Less Than calculations in the select list

At work I recently had a simple need.  I had a table with data.  One of the columns is a date-time stamp – lets call it “Last_Login_Date”.  I wanted a “Boolean” column that was a false if the value was less than 60 days old and true if it was older than 60 days. (Yes, I know that Oracle does not have a Boolean data type so how about Zero (0) for false and One (1) for true, OK?)

Ok, you would think that this would be simple: Just add the calculation in the select list, right? SYSDATE - Last_Login_Date > 60 AS Is_Old_Account.

But no, nothing can be easy can it.

With a fairly cryptic combination of Oracle commands I replicated what I wanted.  Here it is:

DECODE(GREATEST(FLOOR(SYSDATE – NVL(LAST_LOGIN_DATE, TO_DATE('2000/01/01', 'yyyy/mm/dd')), 60), 60, 0, 1) AS IS_OLD_ACCOUNT

And, here is the breakdown:

First NVL incase the field had never been populated.

Next, FLOOR to turn the difference into an integer (probably not needed, but during debugging, having an integer was easier to deal with.

Then, GREATEST takes two values, the date difference and my max value.  So, if the date difference is less than 60, the  GREATEST function will return 60.

Lastly DECODE will look at the value and say – If it is 60 return 0, else return 1. 

So, the end result is if the value is 60 or less, the value is 0 else it is 1.

Note:  I could have ran the SQL twice with a JOIN command and add to the where clauses the filters of  SYSDATE - Last_Login_Date > 60 and SYSDATE - Last_Login_Date <= 60 and a dummy column in the select list added in out “Boolean” column IS_OLD ACCOUNT, but this did not seem very efficient.