Hi all

I am creating a web services application in Visual Studio Dot net
which links to a MS SQL database.

I need to create a form which lets the user pick his field of
choice(doesnt matter what it is..) using drop down boxes,radio buttons
etc and query the database according to what he has picked and
retrieve only those columns and rows.

The thing is, I do not want to hardcode the queries using
OleDbDataAdapter or SqlDataAdapter, but want the query to be generated
on the fly, once the user picks his fields from the form.

I'd greatly appreciate any help.

Thanks,
m

Seems you are looking to provide ad-hoc reporting capabilities. To avoid
hard-coding queries you would have to access the SQL Server meta-data to
retrieve information about tables and to provide options for users to pick
tables and columns. You can start by looking at the information schema views
in the SQL Server documentation. Here is one example of a query that returns
tables and columns:

SELECT c.table_catalog AS 'Database Name',
c.table_schema AS 'Owner',
c.table_name AS 'Table Name',
c.column_name AS 'Column Name',
c.data_type AS 'Column Data Type'
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES t
ON t.table_catalog = c.table_catalog
AND t.table_schema = c.table_schema
AND t.table_name = c.table_name
WHERE t.table_type = 'BASE TABLE'

Since the table and column names may not mean much to users, you may want to
build a mapping table with descriptive names. That involves some hard-coding
and maintenance (best stored in a table that you can join easily) to update
when a new table/column is added or changed, but greatly improves the user
experience.

Next you would allow selection of columns and placing filters (the WHERE
clause of the query). A good idea is to enforce a requirement to have at
least one condition. Users tend to forget about adding filters and could
possibly dump the whole table out. At this point it is very important to
check the column type and perform verification on any parameters entered.

Last is to build a dynamic query and return the results to the user. A good
reading for dynamic queries is Erland Sommarskog's article here:
http://www.sommarskog.se/dynamic_sql.html. Pay attention to the SQL
injection section as this is very common for ad-hoc query systems.

This is a very simplified approach to what you are looking for. It can get a
lot more complex if you need to provide special operations like summary
queries, or if you decide to provide capabilities to join multiple tables.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

On 27 Feb 2007 14:40:23 -0800, madhumita.iyengar (AT) gmail (DOT) com wrote:

In addition to Plamen's suggestions, you might look into the report
builder part of Sql Server Reporting Services. The programmer sets up
the appropriate data model, and the user configures the report. There
is something of a learning curve for the user but designing a report
can be fairly trivial.

On 27 Feb 2007 14:40:23 -0800, madhumita.iyengar (AT) gmail (DOT) com wrote:

And you'll likely get more replies if you're specific in your post,
rather than just Help Required!.