asp.net - writing a generic query page

Experts whats the best way to deal with this problem. Do I write a query form that builds up a select statement or is there some webbased query tool that I can just hand over to the client? Let me explain;

I have an asp.net webpage with about 30 textboxes.

The client wants another screen that allows them to run a query on any combination of these fields.

(In reality I expect that they wont ever query for more than a combination of 5 of the fields though)

So for example they want something that would allow them to choose zipcode,startdate,enddate,value and address1 and filter like this -

ZIPCODE => 94122 AND ZIPCODE < 94126
AND
STARTDATE > '01/04/2009'
AND
ENDDATE < '22/04/2009'
AND
VALUE < 10000
AND
ADDRESS1 BEGINS '27'

If you think I should build up the select statement then an example would be great if you could find one.

Im using Visual Studio 2008 (VB Code), sql server express 2008,
Real_coffeeAsked:
Who is Participating?
 
uaexpertCommented:
So what is the problem just to use this product (EasyQuery.NET) on your web-site?
We use it in one of our projects and our customers love its UI.
The guys from Korzh.com propose free version which is fully functional but has a link to their web-site. If you would like to remove that link - you will need to pay about $200 for the license.
0
 
EmrahZenginCommented:
I suugest you to use subsonic as ORM and create your sql query using subsonic query builder. Like
 

//Sorry it a C# code example...
 
Query q = YourTableOrViewName.Query();
 
if (string.IsNullOrEmpty(txtStartDate.Text))
	q.WHERE(YourTableOrViewName.Columns.Startdate,Comparison.GreaterOrEquals,txtStartDate.Text);
 
 
if (string.IsNullOrEmpty(txtZipCode.Text))
	q.WHERE(YourTableOrViewName.Columns.Zipcode,txtZipCode.Text);
 
// and so on..
 
YourGridView.DataSource = q.ExecuteDataSet();
YourGridView.DataBind();
		

Open in new window

0
 
Real_coffeeAuthor Commented:
right thanks. I get the code. Id use a big case statement and build up a sql select string i guess but whats the benefit of subsonic? I havent heard of that before - what does it do that visual studio doesnt?
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
neo00000110Commented:
hi,

i'd create a stored proc with default values in the parameters. Then test in the code whether the text boxes have been used and if so add the value. The default values should be set so they would still return all data. for example

cretae proc testproc
(
@StartDate datetime = '01/01/1900',
@EndDate datetime = '12/12/2199'
@Value int = 1000000
)
as

select * from tablename where yourdate between @startdate and @enddate and value <@Value

then in the vb.net code do the

dim objsql as new sqlcommand("testproc",connection)
objsql.commandtype = data.commandtype.storedprocedure
if startdate.text.length > 0 then objsql.parameters.add("@StartDate",sqldbtype.datetime).value = startdate.text

etc
then tie in that into your datagrid or whatever output you wish.
0
 
Real_coffeeAuthor Commented:
but with 30 textboxes and they need the ability to use AND OR. This looks a bit of a problem to me.

perhaps Im missing something. Id hoped that maybe there was something like a crytsal reports add-in that I could simply point at the table? It can stand outside the app if neccesary.
0
 
EmrahZenginCommented:
What is the use of subsonic? Subsonic generates vb or c# codes of your database schema and you will be able to build sql queries simply without using "Select * from bla bla... " ..
And you will be able to create complex queries using subsonic.. You can watch tutorials about subsonic at http://subsonicproject.com/  It s also open-source
0
 
Real_coffeeAuthor Commented:
Im thinking of something like this -

http://devtools.korzh.com/eq/dotnet/

What do you think? If you havent tried this product is there anything else like this that you have experienced?
0
 
Real_coffeeAuthor Commented:
I've purchased the Korzh product and its working great.


0
 
Real_coffeeAuthor Commented:
An elegant product. Im happy to recommend this if you want to provide query abilities to your web application.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.