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

Viewing the contents of a binary file in Visual Studio

The toolset included in Visual Studio is just massive.  I swear that I could use Visual Studio for a lifetime and I still would not have plumbed all its depths.

With that thought in mind, I just realized that I need not look for poor third party applications to look inside a binary file.  I can use my trusty IDE Swiss Army knife, Visual Studio.

There are two ways to view and edit a binary file in Visual Studio:

1. Simply open a file with the extension of .BIN
2. After selecting the file in “Open –> FIle…” but before you click the “Open” button, instead click on the dropdown arrow on the “Open” button and select “Open With …”.  Then select “Binary Editor” from the list.

Either way you are then presented with the standard type of Binary File editor (3 columns, 1- The Offset, 2- The binary data & 3- The ASCII representation of the binary data).

In this editor, you can copy, paste, add, alter and delete bytes!

If only I had known this years ago!

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.

Calculating the average of two angles (two bearings actually)

I recently needed to find the average of two angles.  I was programmatically creating a irregular polygon. I wanted to draw a small square at the points of the polygon, and I wanted the squares to be rotated to the average of the two lines that met at that point.

The issue is that if you have two bearings, one at 20° and one at 350°, or one at 15° and one at 315°. If you just average the two numbers, you get 185°, but the more appropriate number is 5°.  This is called by some a “Wraparound issue” and if you search the web you will see lots of ways to solve this problem. Unfortunately they mostly try to solve it using a mathematical equation.  Now for people that have read this blog for awhile know that I am not allergic to math, but if we can solve this problem simply with an algorithm, we should … that’s what computers are for, right?

So, here is my algorithm and the thought behind it.

---

(Assumptions: 0 >= bearings < 360)

First if you look at the difference between the two bearings, you will see that there are two possibilities, the actual difference is greater than 180° or less than 180°.  Since we are only concerned about “fixing” the issue when the difference is greater than 180°, that is the first thing we will check.

We will call the smaller value bearing bearingA and the larger value bearing bearingB.

Since we know that bearingB has a larger value and that the difference is greater than 180, so bearingB > 180.

So, if we subtract 360  –  bearingB, then just add bearingA + bearingB and divide the total in half, we are 90% there.

One last check. If the result is less than 0, we need to add 360 back in.

So, example #1 20° & 350° 350 – 20 > 180 350 – 360 = –10 (–10 + 20) / 2 = 5 5 ≥ 0 = 5 °

So, example #2 15° & 315° 315 – 15 > 180 315 – 360 = –45 (-45 + 15) / 2 = –30 –30 < 0–30 + 360 = 354°

And here is the code:

Code Snippet

1. private static double GetAverageBearing(double bearingA, double bearingB)
2.         {
3.             if (bearingA > bearingB)
4.             {


5.                 var temp = bearingA;
6.                 bearingA = bearingB;


7.                 bearingB = temp;
8.             }
9.  
10.             if (bearingB - bearingA > 180) bearingB -= 360;
11.  
12.             var finalBearing = (bearingB + bearingA)/2;
13.  
14.             if (finalBearing < 0) finalBearing += 360;
15.             
16.             return finalBearing;
17.         }

Scripting C++/cli with IronPython (Visual Studio 2008 & IronPython 2.6.1)

Even though there has been a lot of talk about IronPython, there has been very little info about how to use it with C++/cli.  I’ve actually found posts claiming that it is not possible to use IronPython with C++/cli.

Well, it is possible and easy, once you have a working example.  If, not then … well, lets just hope your hair will grow back.

Ok, I’m never one to take the easy way out, but I started by creating a C# dll that called my IronPython script and then called the C# dll from C++/cli.  That worked but seemed overly complicated.  If you do not mind a middleman then I guess it is ok, but if you want to go straight from C++/cli to IronPython, read on.

When I first tried to write a call to an IronPython script from  C++/cli, I tried a direct conversion of C# code into C++/cli code.  That didn’t work, so I then tried using RedGate’s .Net Reflector and the C++/cli add-in for .Net Reflector.  This got me 70% there.  Combining both with some trial and error got me the rest of the way. 

Funny, but when you look at the code, it seems so simple – yet getting there was not easy.

So, the following snippet shows a simple usage of IronPython as a scripting language.  It takes the first parameter passed in prints it to the console, passes it to the IronPython script: ipyStrings.ipy, then prints to the console the value of the same parameter that was passed back.  The IronPython code takes the string, prints it, reverses it, then sends it back.

C++/cli code:

Code Snippet

1. int main(array<System::String ^> ^args)
2. {
3.     try
4.     {
5.         if(args->Length>0)
6.         {   
7.             String^ filename = "ipyStrings";
8.             String^ path = Assembly::GetExecutingAssembly()->Location;
9.  
10.             ScriptEngine^ engine = Python::CreateEngine();
11.             ScriptScope^ scope = engine->CreateScope();
12.             ScriptSource^ source = engine->CreateScriptSourceFromFile
13.             (String::Concat(Path::GetDirectoryName(path), "\\", filename, ".ipy"));
14.        
15.             Console::WriteLine(args[0]);
16.  
17.             scope->SetVariable("passedArgs", args[0]);           
18.             source->Execute(scope);
19.  
20.             Console::WriteLine(scope->GetVariable<String ^>("passedArgs"));
21.         }                                   
22.     }
23.     catch(Exception ^e)
24.     {
25.         Console::WriteLine(e->ToString());
26.     }
27.  
28.     Console::ReadLine();
29.  
30.     return 0;
31. }

Python code:

Code Snippet

1. import clr
2.  
3. print "Passed in: " + passedArgs
4. passedArgs = passedArgs[::-1]
5.  
6. print "Sending back: " + passedArgs

Random Numbers on a Bell Curve in C#

Have you ever wanted to generate some random numbers but with a distribution pattern other than the normal even pattern?

Well in a recent project I did.  I wanted a weighted distribution pattern that looked like a bell curve. 

I looked around yes there were some answers, but most got a lot deeper into math than I wanted or needed.  I simply wanted to generate a number with the probability that it was in the center of the range be greater than the probability that it was on the edges – in other words, it fit on a Bell Curve.

I cannot credit what post where gave me the simple solution, or I would post a link.  Here is a simple Extension Method to the Random class. 

It takes two parameters:

1. Steps: How many numbers in each “Chunk” do you want.  If you want a number between 0 & 600 and set the Steps equal to 50, then the midpoint value (300) will be much more likely than 0 or 300.
2. MaxValue: The possible range for the random number will be 0 to MaxValue - 1.

Now keep in mind that this is not mathematically perfect.  If you do not choose a step value that is a divisor of your MaxValue then you will not get the full range.  But, having said that, this is a great “Quick & Dirty” way to get a good approximation of a random bell curve.

Code Snippet

1. public static class RandomExtender
2. {
3.     public static int NormalNext(this Random rnd, int Steps, int MaxValue)
4.     {
5.         int count = 0;
6.         int val = 0;
7.  
8.         if (Steps < 1) return 0;
9.  
10.         while (++count * Steps <= MaxValue) val += rnd.Next(Steps);
11.  
12.         return val;
13.     }
14. }

Here is a picture of 20,000 random numbers with Steps = 50 & MaxValue = 600.