I have a search form which contains a set of search criteria and a datagridview. the datagridview is populated by entering none or some of or all of these critera and click on "search" button. the search criteria are: name, dob, gender, date_time_registered, active, etc.
I am able to build a simple query for each possibility, e.g. only search for one field, or search for all fields, or search for any 2 or these fields. This is rather inefficient.
Is there a way to build one query to accommodate all possibilities I tried to do something like this:
SELECT *
FROM patient
WHERE name = AND dob = AND date_time_registered = AND active =
assume user only specified two criteria: name and active, I did this in my code:
this.HPMTableAdapter.FillByUserInputs( HPMDataSet.patient, "adam", any, any, any, "yes");
I also tried this:
this.HPMTableAdapter.FillByUserInputs( HPMDataSet.patient, "adam", all, all, all, "yes");
and
this.HPMTableAdapter.FillByUserInputs( HPMDataSet.patient, "adam", *, *, *, "yes");
Neither of them worked out, plz help
build query based on user input criteria (search function)
SameerNSameer
thx for the quick reply. Sorry for confusing u. In my "very-long" generateQuery() method, did u notice there are something like
//patientNo + gender + sname + gname
or
//sname + gname + dob
these places are where i am going to put code to retrieve the acquary from the application settings.
for example in application settings, i created a setting called "searchQuery_surename_givenname_dob", which stores the query for "when user wants to search for surename and givenname and dob". There are 32 settings in the application settings which cover all the possibilities of the search function, that is, 32 pre-defined queries. when user decided to search based on a set of criteria, my application will figure out which query to call, and retrieve the actual query from the corresponding application setting. e.g.
theQuery = Properties. Settings. Default.searchQuery_surename_givenname_dob
(maybe i should call this method "retrieveQuery()" to make more sense)
by doing so, i can easily change the way to retrieve data and choose what data to retrieve by editing the application settings, rather than having to go through any actual code.
I am not sure if this is a smart way i should be doing this type of things. I just want to make my application more customizable and flexible. Thx in advance for and advice :)
ernisj
thx Paul, this is much more efficient than the way i did it. however, my concern is the easy of customization. I want to store the query somewhere as a global setting (e.g. application.settings). so that i only need to do something like
Properties. Settings. Default.myQueryX
to get the query string. By doing so, i will only need to modify the application settings when i want to edit the query.
Kevin Rodgers
Hi,
Well I guess if your trying to make your program flexible then I think that is a pretty thing to do. But I really suggest generating your own query. Though the strings that you will store in your app.config are just the "where" part for each field. So that we can slowly create our where statement depending on your available fields and also you can edit the config file to change any query filter to be changed.
cheers,
Paul June A. Domag
RossDempster
Tryin2Bgood
Hi,
Why not just check for the fileds individually and add them to the query Sample:
string SqlQuery = "SELECT * FROM table1 WHERE ";
if (patientNo != "" && patienNo != null) {
SqlQuery += "FieldPatientNo = " + patientNo;
}
if (gname != "" && gname != null) {
SqlQuery += " Fieldgname = " + gname;
}
The idea here is to dynamically append the field in the where statement if it has a value slowly completing the sqlquery...
cheers,
Paul June A. Domag
bobslayer
I worked out this way to do it, 32 queries in total, too many lines of code, feel difficult to handle if anything went wrong. even if i want to insert another search criteria, it will be a nightmare for me to modify the code and the queries. so is there another way of doing this more efficiently plz helpbasically i want the search button to search based on any fields that user has given values to. see my code below
//generate the actual search query based on given inputs
private string generateQuery( string patientNo , string gender , string sname , string gname , string dob )
{
string theQuery = null;
if ( patientNo == "" && patientNo == null ) //patientNo is included
{
if ( gender != "" || gender != null )
{
if ( sname != "" || sname != null )
{
if ( gname != "" || gname != null )
{
if ( dob != "" || dob != null )
{
//all included
}
else //dob excluded
{
//patientNo + gender + sname + gname
}
}
else //gname excluded
{
if ( dob == "" || dob == null )
{
//patientNo + gender + sname + dob
}
else //gname & dob excluded
{
//patientNo + gender + sname
}
}
}
else //sname excluded
{
if ( gname != "" || gname != null )
{
if ( dob != "" || dob != null )
{
//patientNo + gender + gname + dob
}
else //sname & dob excluded
{
//patientNo + gender + gname
}
}
else //sname & gname excluded
{
if ( dob == "" || dob == null )
{
//patientNo + gender + dob
}
else //sname & gname & dob excluded
{
//patientNo + gender
}
}
}
}
else //gender excluded
{
if ( sname != "" || sname != null )
{
if ( gname != "" || gname != null )
{
if ( dob != "" || dob != null )
{
//patientNo + sname + gname + dob
}
else //gender & dob excluded
{
//patientNo + sname + gname
}
}
else //gender & gname excluded
{
if ( dob == "" || dob == null )
{
//patientNo + sname + dob
}
else //gender & gname & dob excluded
{
//patientNo + sname
}
}
}
else //gender & sname excluded
{
if ( gname != "" || gname != null )
{
if ( dob != "" || dob != null )
{
//patientNo + gname + dob
}
else //gender & sname & dob excluded
{
//patientNo + gname
}
}
else //gender & sname & gname excluded
{
if ( dob == "" || dob == null )
{
//patientNo + dob
}
else //gender & sname & gname & dob excluded
{
//patientNo
}
}
}
}
}
else //patientNo excluded
{
if ( gender != "" || gender != null )
{
if ( sname != "" || sname != null )
{
if ( gname != "" || gname != null )
{
if ( dob != "" || dob != null )
{
//gender + sname + gname + dob
}
else //patientNo & dob excluded
{
//gender + sname + gname
}
}
else //patientNo & gname excluded
{
if ( dob == "" || dob == null )
{
//gender + sname + dob
}
else //patientNo & gname & dob excluded
{
//gender + sname
}
}
}
else //patientNo & sname excluded
{
if ( gname != "" || gname != null )
{
if ( dob != "" || dob != null )
{
//gender + game + dob
}
else //patientNo & sname & dob excluded
{
//gender + game
}
}
else //patientNo & sname & gname excluded
{
if ( dob == "" || dob == null )
{
//gender + dob
}
else
{
//gender
}
}
}
}
else //patientNo & gender excluded
{
if ( sname != "" || sname != null )
{
if ( gname != "" || gname != null )
{
if ( dob != "" || dob != null )
{
//sname + gname + dob
}
else
{
//sname + gname
}
}
else //patientNo & gender & gname excluded
{
if ( dob == "" || dob == null )
{
//sname + dob
}
else
{
//sname
}
}
}
else //patientNo & gender & sname excluded
{
if ( gname != "" || gname != null )
{
if ( dob != "" || dob != null )
{
//gname + dob
}
else //patient No & gender & sname &dob excluded
{
//gname
}
}
else //patientNo & gender & sname & gname excluded
{
if ( dob == "" || dob == null )
{
//dob
}
else //all excluded
{
//none
}
}
}
}
}
return theQuery;
}
HMCSharon
Hi,
In your last post I noticed that you are using a GenerateQuery() method to generate the query string and needed the function to be optimized. And now you are storing query string in the application settings Could you explain a little bit further with this
cheers,
Paul June A. Domag