?
Solved

asp.net - writing  a generic query page

Posted on 2009-04-22
9
Medium Priority
?
611 Views
Last Modified: 2013-11-26
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,
0
Comment
Question by:Real_coffee
9 Comments
 
LVL 4

Expert Comment

by:EmrahZengin
ID: 24202505
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
 

Author Comment

by:Real_coffee
ID: 24203071
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
 
LVL 2

Expert Comment

by:neo00000110
ID: 24203158
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Real_coffee
ID: 24203317
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
 
LVL 4

Expert Comment

by:EmrahZengin
ID: 24203355
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
 

Author Comment

by:Real_coffee
ID: 24203454
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
 
LVL 2

Accepted Solution

by:
uaexpert earned 1500 total points
ID: 24212485
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
 

Author Comment

by:Real_coffee
ID: 24242501
I've purchased the Korzh product and its working great.


0
 

Author Closing Comment

by:Real_coffee
ID: 31573148
An elegant product. Im happy to recommend this if you want to provide query abilities to your web application.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses
Course of the Month16 days, 17 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question