Prashanth K

Prashanth K

  • NA
  • 1
  • 1.6k

VB.NET 2010: Datatable from multiple DBs/tables

Oct 13 2012 7:31 AM

I am trying to create a program that in VB.NET 2010 (WPF) will allow users to select tables, columns and define relationships, and finally allow them to view data in a datagrid. The tables may be in different DBs or from the same DB. I am not an old hand in programming, and went about in the following manner:

  1. Define a dataset and a containing datatable with all the columns requested for by the user (this is generated by the program at run time)
  2. Based on the relationships defined by user, dynamically generate the SQL statements with the required columns and 'where' conditions
  3. Populate dataset and bind datagrid

I am done with (1) and (2), but did not find good ways of going ahead with (3).

For e.g. consider CONTACT.NAME, CONTACT.DEPT and ADDRESS.CITY as the columns requested by user in two tables CONTACT and ADDRESS where DEPT='Sales'. The relationship b/w them is defined as CONTACT.ID=ADDRESS.CONTACT_ID. What I am trying to do is get NAME, DEPT, ID from CONTACT where DEPT='Sales', and then for each record fetch CITY from ADDRESS. In real world, I have too many records in both tables which I don't want to read unless necessary. Also, the tables may be in a single DB or in multiple DBs (e.g. Oracle, SQL Server) which are not linked to each other. (Note that the individual SQLs for each table are quite complex, the table relationship goes on the n levels. This depiction is a simplified version).

What is the best way to accomplish this? Am I approaching this the wrong way?