Hi,

(Note to begin: Sorry if this is the wrong forum to put this in.)

I have just finished writing a VB.Net component for mining data from a SQL db (doesn't strictly have to be T-SQL, just needs to support joins using the LEFT/RIGHT etc syntax rather than ANSI *=)

I wondered if I could get any feedback from people as to whether it is the sort of thing which might be genuinly useful for people.

The idea is that you can, free form, pick any fields you would like from the db and the component will work out the join strategy which allows these fields to be selected (a little like FireFox).

It will allow for there to be one or more PK-FK relationship between any pair of tables (these do not have to be SQL enforced PK-FK relations just relationships from an underlying data perspective)

It will even cope with situations where there are multiple paths of tables which link a pair of tables together (Something which I think no other system can deal with)

It cannot (Because I believe it is impossible?) deal with self referencing joins (but these can almost always be collapsed to the underlying data with a suitable view)

At the moment their are three steps needed to configure the component

1.Create a list of tables you would like to be able to use in any queries.

2.Create a list of fields you would like to make available (there is an option to have all the fields in the table populated automatically)

3.Define the 'real' PK-FK relations in the data (This is not necessarily a mapping of the SQL constraints but will very broadly be the same) (again there is an option to gather the info from the SQL db structure)

Thats it

To use it

Create an instance of the component

.Add the fields you want (say if you want 'all rows' which will use an appropriate OUTER JOIN)

get the SQL with a from the .SQL property

Execute it!

Absolutely any comments very welcome. Particularly if they are constructive.

The reason I built this was as part of a reporting engine which allows completely freeform selection of data by the users to mine add hoc data.

Thanks in advance

Stephen

Hi

I am trying to do something very similar to what you are doing (in VB6). I have done allot of investigation and it seems very difficult to make this component completely "dynamic", but not impossible. I have found a component on http://www.componentone.com (query8.0) but you will need to buy this but it would get the job done :(. At the moment I am working on the logic to build query strings from user selection, they choose the fields, then the tables where they specify the join(s) and a where/group by clause but will be limited.

There are stored procedures in master that contains information usable in such a app but have not been able to figure out how to return this data back to VB6 (sp_HelpDB, sp_tables and sp_columns).

Let me know if you are able to find an esier way to do this..

Regards
Marnus