asked on

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,

EmrahZengin

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

Real_coffee

ASKER

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?

neo00000110

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.

Real_coffee

ASKER

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.

EmrahZengin

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

Real_coffee

ASKER

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?

ASKER CERTIFIED SOLUTION

uaexpert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Real_coffee

ASKER

I've purchased the Korzh product and its working great.

Real_coffee

ASKER

An elegant product. Im happy to recommend this if you want to provide query abilities to your web application.