Senz79
asked on
Generalized Search Function
Hello All
I am looking for some approach to buid a Generalised search function which can cater to multiple search parameter combinations.
For example
string Account,string Server,string UserID, string UserName, string ActionRequired, string SupportGroup
The above are the search parameters with which i need to create more than 12 combination and for each combination i have to write one IF loop.
Thus kindly help me with some way/approach thru which i can generalize the approach
thanks
Senz
I am looking for some approach to buid a Generalised search function which can cater to multiple search parameter combinations.
For example
string Account,string Server,string UserID, string UserName, string ActionRequired, string SupportGroup
The above are the search parameters with which i need to create more than 12 combination and for each combination i have to write one IF loop.
Thus kindly help me with some way/approach thru which i can generalize the approach
thanks
Senz
can you pls put an example of what you are doing? i really dont understand your question
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Indeed it's not very clear what you want but if I'm not wrong, you just need to construct a search string based on some selections.
Have a look at my code. It's not tested at run time (I didn't bother to setup database) but idea should be clear.
Have a look at my code. It's not tested at run time (I didn't bother to setup database) but idea should be clear.
private void button3_Click(object sender, EventArgs e)
{
List<KeyValuePair<string, string>> searchParams = new List<KeyValuePair<string,string>>();
if (serachByComapany)
{
KeyValuePair<string, string> pair = new KeyValuePair<string, string>();
pair.Key = "Company";
pair.Value = txtCompanyName.Text;
}
//....
SqlDataReader reader = getDataReader(searchParams);
if (reader != null)
{
while (reader.Read())
{
//...
}
}
}
private SqlDataReader getDataReader(List<KeyValuePair<string, string>> searchParams)
{
SqlCommand cmd = new SqlCommand();
string searchStr = "Select * FROM MyTable WHERE ";
for (int i = 0; i < searchParams.Count; i++)
{
searchStr = searchStr + searchParams[i].Key + " = @" + searchParams[i].Key;
if (i < searchParams.Count - 1)
{
searchStr = searchStr + " AND ";
}
// 2. define parameters used in command object
SqlParameter sqlParam = new SqlParameter();
sqlParam.ParameterName = "@" + searchParams[i].Key;
sqlParam.Value = searchParams[i].Key;
cmd.Parameters.Add(sqlParam);
}
SqlConnection conn = null;
SqlDataReader reader = null;
cmd.CommandText = searchStr;
cmd.Connection = conn;
try
{
conn = new SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI");
conn.Open();
reader = cmd.ExecuteReader();
}
catch (Exception ex)
{
}
finally
{
if (conn != null)
{
conn.Close();
}
return reader;
}
}
you can use a searchCriteria class and set the values to the class and then pass the class to the called function(readability). here you can see for the member variable and its value.
secondly you can use a hashtable to store the name value pair and in called function iterate through the collection and create the dynamic string for search criteria using name and value.
secondly you can use a hashtable to store the name value pair and in called function iterate through the collection and create the dynamic string for search criteria using name and value.
ASKER
Hey Cyberkiwi
Can you elaborate your solution with the below parameters
string Account,string Server,string UserID, string UserName, string ActionRequired, string SupportGroup
Can you elaborate your solution with the below parameters
string Account,string Server,string UserID, string UserName, string ActionRequired, string SupportGroup
ASKER
I urge the administrator to block these kind of message which gives us a negetive impact on our discussion.
Answering to your problem, the thing anarki was saying is on sql you set the default value of the variable to null, so if you dont send it, the "join" statment doesnt get inside your query.
lines 2-4: sets up @Account, @Server, @UserID with the correct types
lines 11-16: adds the criteria to the SQL
Give it a try with the remaining 3 - string UserName, string ActionRequired, string SupportGroup
And show the SQL if you cannot get it right.
lines 11-16: adds the criteria to the SQL
Give it a try with the remaining 3 - string UserName, string ActionRequired, string SupportGroup
And show the SQL if you cannot get it right.
ASKER
Hi CyberWiki
can you help me manipulate "and"
SELECT Account,Server,Userid,User Name,LastL ogin,Actio nRequired, AccountSta tus,Owner, Classifica tion,Modul eID,CheckI D,Descript ion,Harden Status,Sup port_group _name,supp ort_group_ location,U pload_Date from ALLUsers Where
and Account = @Account -- error
Please review the SP
can you help me manipulate "and"
SELECT Account,Server,Userid,User
and Account = @Account -- error
Please review the SP
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[searchanyfield]
@Account varchar(100),
@Server varchar(100),
@UserID varchar(100),
@UserName varchar(100),
@ActionRequired varchar(100),
@SupportGroup varchar(100)
AS
declare @sql nvarchar(max)
set @sql = 'SELECT Account,Server,Userid,UserName,LastLogin,ActionRequired,AccountStatus,Owner,Classification,ModuleID,CheckID,Description,HardenStatus,Support_group_name,support_group_location,Upload_Date from ALLUsers Where'
if @Account is not null
set @sql = @sql + ' and Account = @Account'
if @Server is not null
set @sql = @sql + ' and Server= @Server'
if @UserID is not null
set @sql = @sql + ' and UserID = @UserID'
if @UserName is not null
set @sql = @sql + ' and UserName = @UserName'
if @ActionRequired is not null
set @sql = @sql + ' and ActionRequired = @ActionRequired'
if @SupportGroup is not null
set @sql = @sql + ' and Support_group_name = @SupportGroup'
print @sql
exec sp_executesql @sql,N'@Account varchar(100),@Server varchar(100),@UserID varchar(100),@UserName varchar(100),@ActionRequired varchar(100),@SupportGroup varchar(100)',
@Account,@Server,@UserID,@UserName,@ActionRequired,@SupportGroup
initialize all the parameters in the main section as null, so that way you dont need to send null on the code, the rest of the code you are using a where without a condition, so i suggest you to set the first where with a constant like userstatus=active and then the rest of the if statment.
or
check where the first "and" and dont put AND
or
check where the first "and" and dont put AND
Just one correction:
set @sql = 'SELECT Account,Server,Userid,User Name,LastL ogin,Actio nRequired, AccountSta tus,Owner, Classifica tion,Modul eID,CheckI D,Descript ion,Harden Status,Sup port_group _name,supp ort_group_ location,U pload_Date from ALLUsers Where 1=1 ' --- note the space after 1=1
The 1=1 is there so that you don't have to determine when to use WHERE or to use AND (if where is already used). Just a minor trick.
Regards
set @sql = 'SELECT Account,Server,Userid,User
The 1=1 is there so that you don't have to determine when to use WHERE or to use AND (if where is already used). Just a minor trick.
Regards
ASKER
This worked for me....
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[searchanyfield]
@Account varchar(100),
@Server varchar(100),
@UserID varchar(100),
@UserName varchar(100),
@ActionRequired varchar(100),
@SupportGroup varchar(100)
AS
declare @sql nvarchar(max)
set @sql = 'SELECT Account,Server,Userid,User Name,LastL ogin,Actio nRequired, AccountSta tus,Owner, Classifica tion,Modul eID,
CheckID,Description,Harden Status,Sup port_group _name,supp ort_group_ location,U pload_Date from ALLUsers Where'
if @Account is not null
set @sql = @sql + ' Account = @Account and'
if @Server is not null
set @sql = @sql + ' Server= @Server and '
if @UserID is not null
set @sql = @sql + ' UserID = @UserID and '
if @UserName is not null
set @sql = @sql + ' UserName = @UserName and '
if @ActionRequired is not null
set @sql = @sql + ' ActionRequired = @ActionRequired and '
if @SupportGroup is not null
set @sql = @sql + ' Support_group_name = @SupportGroup and'
set @sql = SUBSTRING(@sql,0,LEN(@sql) -3)
--print @sql
exec sp_executesql @sql,N'@Account varchar(100),@Server varchar(100),@UserID varchar(100),@UserName varchar(100),@ActionRequir ed varchar(100),@SupportGroup varchar(100)',
@Account,@Server,@UserID,@ UserName,@ ActionRequ ired,@Supp ortGroup
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[searchanyfield]
@Account varchar(100),
@Server varchar(100),
@UserID varchar(100),
@UserName varchar(100),
@ActionRequired varchar(100),
@SupportGroup varchar(100)
AS
declare @sql nvarchar(max)
set @sql = 'SELECT Account,Server,Userid,User
CheckID,Description,Harden
if @Account is not null
set @sql = @sql + ' Account = @Account and'
if @Server is not null
set @sql = @sql + ' Server= @Server and '
if @UserID is not null
set @sql = @sql + ' UserID = @UserID and '
if @UserName is not null
set @sql = @sql + ' UserName = @UserName and '
if @ActionRequired is not null
set @sql = @sql + ' ActionRequired = @ActionRequired and '
if @SupportGroup is not null
set @sql = @sql + ' Support_group_name = @SupportGroup and'
set @sql = SUBSTRING(@sql,0,LEN(@sql)
--print @sql
exec sp_executesql @sql,N'@Account varchar(100),@Server varchar(100),@UserID varchar(100),@UserName varchar(100),@ActionRequir
@Account,@Server,@UserID,@