What is a good .NET library for deconstructing SQL queries that allows me to determine what table each column originates from?

Question

My company needs to allow the internal users of our application to write queries directly against the database through our UI. But when we display the results, our code needs to know which column and table each return column is for so that the results can be displayed with some color coding and other logic for each column.

So I was hoping that there was a class or some simple code out there that would do SQL parsing and somehow give me the source table and column name that each return column in a SQL query represents.

An example

select b.FirstName As Name, c.Address From BusinessOwner b left join ContactLocation c on b.ID = c.OwnerID

So I would need some code to figure out that the "Name" output column actually was from the BusinessOwner table and was the column titled FirstName and that Address came from the ContactLocation table.

Answer 1

There's no built in way to do this, you'd need some custom code to maintain the mappings you cared about.

This would be pretty hard to do in a standard way as the columns in a select query can come from one table (as in you example), multiple tables (multiple columns added, aggregated, concatenated, or otherwise computed), or no tables at all (select 'Paul' as name), and probably several other ways I haven't thought of.

Answer 2

EasyQuery from Korzh is an embeddable visual query designer that end-users can use to build queries. They have a Windows Forms and ASP.NET version available.

Answer 3

Never used these guys before... but their product looks like it could do what you want: http://www.sqlparser.com/index.php

Check out http://www.sqlparser.com/docs/How_to_identify_DB_objects_in_your_sql.html which sounds a lot like what you need to do.

Note though... what you're try to do may not be very effective. Perhaps more time on the requirements/use case side would lead to a better solution (it sounds like your users need a fast and easy way to apply standard formatting to a report/display... and I'm not sure matching by database table/field is really the answer for that).

Answer 4

This sounds like you could do this a lot simpler than parsing the sql. Get a DataReader from the supplied query and use the GetSchemaTable() method. You could always fool it by letting the user wrap columns within functions etc etc, but you'd have a good shot at it this way... And it's built-in!

Answer 5

you might try something like this:

Select b.FirstName As BusinessOwner.Name, c.Address as ContactLoaction.Address
From BusinessOwner as b, ContactLocation as c

and with some parsing of first part of the columns name that return form the query you will determine the table that columns belong to. something like this:

string tempName = tabel.Columns[0].ColumnName;
string tableName = tempName.Substring(0, tempName.IndexOf('.'));

Answer 6

But some fields don't originate from a table! For example:

select getdate()

or even

select "hello world" as ShoutItOut from sysobjects

or you can have multiple sources:

select field1 + field2 as Field1And2 from a inner join b on a.id = b.aid