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

PLINQO adds 'wow' to LINQ 'cool'

Ok, I am the first to admit that I am firmly entrenched in the "Not Built Here" camp. Not only for controls but tools as well. The only tool I've let into Visual Studio for the longest time is Resharper.

I love the power of LINQ but the LINQ to SQL ORM tools have never really thrilled me.

Well, I have changed that rule and I've added PLINQO. I've looked at other tools for ORM, even LINQ to SQL and EF. Thus far I've never liked how the code that these tools generated, but the code that PLINQO generates is the BOMB! PLINQO is like LINQ on steroids, without the roid rage!

Individual files for each table, Generated code does not overwrite custom changes, custom names .... the list just goes on and on.

I could go on and on, but there is a fantastic 70 minute video tutorial here and I guaranty that within the first 10 minutes you will want to pause the video and download PLINQO!

LINQ to the rescue!

If you have ever had multiple data sources that you pull into DataTables, you know that you can use the .Merge function to combine the data. But, what if there are dupicates? What about sorting the merged data?

Well, using LINQ to data, you can use the power of LINQ without needing SQL Server or a database at all, for that matter. Here is a simple example using LINQ to data, to sort and pull only distinct records in the merged DataTable.

static void Main()
{
    var dtFirst = new DataTable();
    var dtSecond = new DataTable();

    dtFirst.Columns.Add("Value", Type.GetType("System.Int32"));
    dtSecond.Columns.Add("Value", Type.GetType("System.Int32"));

    var row = new object[1];

    for (var i = 25; i >= 0; i--)
    {
        row[0] = i;
        dtFirst.Rows.Add(row);
        row[0] = i+5;
        dtSecond.Rows.Add(row);
    }

    dtFirst.Merge(dtSecond);

    IEnumerable MyTable = dtFirst.AsEnumerable();

    var dtLinqData = (from
            MyRow
        in
            MyTable
        orderby
            MyRow.Field("Value")
        ascending
        select
            MyRow.Field("Value")).Distinct().ToArray();

    Console.WriteLine("Original Data");

    foreach (DataRow dr in dtFirst.Rows)
        Console.Write(dr["Value"] + " ");

    Console.WriteLine();
    Console.WriteLine("Linq Data");

    foreach (var dr in dtLinqData)
        Console.Write(dr + " ");

    Console.Read();
}