Auto generating SQL

Last Post 04 Jun 2004 07:18 AM by Stephen.Rice. 0 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

04 Jun 2004 07:18 AM

(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


Acceptable Use Policy