We are developing an application where we need a Query tool which allows customer to do Ad-Hoc or random query.. something similar to lets sayhttp://salebyowner.com/advancedSearch.php
We have few more options than this on which customer can do search.
I don't think dynamic query in C# code or something like that is going to help me. Am I right? Do we have to use any type of query tool or something for doing this?Hi love_luv,
Your example link shows a reasonably sophisticated query form written in PHP. But that doesn't mean PHP is a better database query tool than C# (or any other language for that matter).
PHP is just an application layer over top of some database. That database requires a completely different query language known as SQL (that has no direct link to PHP, C# or any other language).
The programmer in your example is simply using PHP to dyanmically costruct a SQL query statement (in text form) and passing it to their database to retrieve a result. C# (i.e., ASP.NET) can do that too.
In fact, with C# you can utilizeparameterized SQL queries for cleaner and safer SQL construction and execution.
The real trick behind sophisticated queries, of the type you're hoping to achieve, is a good familiarity with the SQL language (of the type native to your database brand,e.g., MS SQL Server uses a form of SQL known as Transact-SQL (a.k.a. TSql), and the knowledge to transform the user input from a webpage into said SQL.
Hope that helps.|||know, I understand that, its sophisticated query. I thought do we need to get some tool like Cognos etc etc for doing such query? More than parameterized query, I think its Dynamic or Adhoc query.|||You don't "need" a query tool at all, if you are familiar with SQL - regardless of how complex a query is. Visual Studio also query tools built in.
A special query tool or not, sophisticated or simple requirements - it all still comes down to a SQL statement to get your database results.
I found query tools useful once-upon-a-time for learning SQL, but I generally don't use them in my day-to-day programming now. You might find a tool useful to get an example of what your SQL text should look like, but once you have that then you use a language like C# to dynamically build the SQL text.
As an aside, "nearly" all database queries are dynamic (or adhoc) in nature. And using a parameterized query is just an optional format to make your dynamic (i.e., adhoc) queries easier to build (and safer to execute).
Hope that helps.|||So mean to say such a complex query is Dynamic Query written in C# code?
We really don't need any Commercial Query tool for Client end? We can use Web Forms to generate such query?
In couple of projects we implemented complex dynamic query, and that made us think if we need any tool or what? In our dynamic query, most of the time (99%) Select clause returns fix number of columns however, From Clause has base table and based on Where Conditions it may or may not join with other tables in data base.
In our version of dynamic query we builded WHERE clause in C#.
But how can we implement Join and WHERE using some proper structure.
Thanks|||
How one can handle dynamic SQL which has to perform JOIN with other tables.
Example I showed in my first post say has 2 - 3 tables:
PropertyListings and PropertyFeatures, PropertyLotFeatures
PropertyListings table gives me basic information I need to display.
PropertyFeatures and PropertyLotFeatures has information I need for advance search. I don't know which table to use and Join till user selects some features. based on Features selected for advance search I may have to Join:
1) PropertyListings with PropertyFeatures
2) PropertyListings with PropertyLotFeatures
3) PropertyListings with propertyFeatures and PropertyLotFeatures
I know once can right such query in C#, but is that how they do it or there is a better way? Am I missing something?
Let me give an Example :
How about when there is Join Present. How whould you build Dynamic SQL? Do you need Dynamic SQL or there is a Better Way?
For Eg.
Table1 = Customers
Table2 = Interests
Table3 = Customers_Interests
Table4 = Activities
Table5 = Customers_Activities
Table3 holds Customers Interests.
Table5 holds Customers Activities
Lets say Customers Table has Data: C1, C2, C3, C4
Interests Table has Data: I1, I2, I3, I4
Cusomters_Interests has Data:
C1, I1
C1, I3
C1, I4
C2, I2
C2, I4
C3, I3
Activities Table has Data: A1, A2, A3, A4
Customers_Activities has Data:
C1, A1
C1, A4
C2, A3
C4, A1
Now lets say on my webpage I represent Interests and Activities as CheckBoxes.
Case 1:
I select Interests I1, I3, I4 but No Activities Selected. In this case my query has to join Customers, Interests and Customers_Interests Table
So query will be: Give me list of Customers with Interests I1, I3, I4
Case 2:
If I select Activities A1, A3 and No Interests, query whas to join Customers, Activities and Customers_Activities Table
Case 2:
If I select Interests I1 and Activities A1, in this case Query has to join Customers, Interests, Customers_Interests, Activities and Customers_Activities tables(all tables)
How would you write Query?
Looking for help!!!!!!!!!!!!!!!!!!!!!!!!!!!
