How to dynamically JOIN T-SQL tables in C#, COLUMNS from ListView [closed]

Question

Is there a way to join between SQL tables dynamically? For example, a function that looks at the tables provided and generate join statements between primary and foreign keys

I have items in winform listview as

 1. TABLE1.NAME
 1. TABLE2.AGE
 1. TABLE3.ADDRESS

In the database, I have 3 tables:

TABLE1
------
 - TBL1_ID 
 - NAME

TABLE2
------
 - TBL_ID2
 - TBL1_ID_FK
 - AGE

TABLE3
------
 - TBL_ID3
 - TBL2_ID_FK
 - ADDRESS

The output that I am trying to achieve should look like this:

SELECT TABLE1.NAME, TABLE2.AGE, TABLE3.ADDRESS FROM TABLE1 JOIN TABLE2 ON TBL1_ID_FK = TBL1_ID JOIN TABLE3 ON TBL2_ID_FK = TBL_ID2

Answer 1

you probably don't want to do this... if you REALLY think you need to do this see this as an idea of an approach:

read and understand the TSQL SELECT syntax: https://msdn.microsoft.com/en-US/en-en/library/ms189499.aspx

break down your problem into the fragments of that syntax

you need to create a select_list, a table_source and most likely a WHERE clause

create a datastructure that represents your schema (how your tables are to be joined, and which columns are in which table)

for a column selection, itterate through all selected columns, and find the tables they reside in. store the table selection in a temporary list (keep in mind that a column selection also needs to hold information about the association to the other selected columns ... for example, a Person can be someone who is a buyer of some goods, or the seller, while in both cases, the records are stored in the same table. the columns alone dont hold enough information about which way to join the Person table ... on the buyer or the seller side?)

for the table selection, select one of the tables as the starting point and start with this table as the first table of your FROM clause, itterate through all selected tables, and find ALL paths along your schema definition on how to connect/join them and keep those that are valid for the associations of your columnselection. walk along the paths and add all tables with their associated join condition to the from clause, while giving each a distinct name. optionally now reduce the joins based on the tables and associations (you need only one pair for each)

update the columselection to reference the names given to the joined tables, use the association information of the table to match the right columns

in the end, actually generate text for all fragments, and put them together in the right order...

as you can see a GENERAL solution is hard work ... if you can water down your expectations, especially on the association side, you can greatly reduce the complexity, but it will not work for every situation

or use an ORM which will save you litterally thousands of hours...

wit EF for example, you can use a user defined expression tree to project the result you want to have, and EF takes care of the statement for you