471,122 Members | 1,015 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,122 software developers and data experts.

Help required!

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

Feb 27 '07 #1
3 1541
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
Feb 28 '07 #2
dgk
On 27 Feb 2007 14:40:23 -0800, ma***************@gmail.com wrote:
>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
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.
Feb 28 '07 #3
dgk
On 27 Feb 2007 14:40:23 -0800, ma***************@gmail.com wrote:
>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
And you'll likely get more replies if you're specific in your post,
rather than just Help Required!.
Feb 28 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by mike | last post: by
reply views Thread by Vish | last post: by
3 posts views Thread by Tim::.. | last post: by
11 posts views Thread by Naeem | last post: by
36 posts views Thread by aljamala | last post: by
5 posts views Thread by forest demon | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.