?
Solved

Generalized Search Function

Posted on 2010-08-29
12
Medium Priority
?
269 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:Senz79
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +2
12 Comments
 
LVL 4

Expert Comment

by:thepanch
ID: 33555481
can you pls put an example of what you are doing? i really dont understand your question
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 2000 total points
ID: 33555978
Use dynamic SQL.
Pass all the parameters in, NULL if not present to an SP.
In the SP, progressively build the statement - this gets around parameter sniffing issues.

This example should get you started.
create proc searchanyfield
@Account varchar(100),
@Server varchar(100),
@UserID int
AS

declare @sql nvarchar(max)
-- ignore the 1=1. It's there to force a "WHERE" clause
set @sql = 'select * from mytable where 1=1 '

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'

exec sp_executesql @sql,N'@Account varchar(100),@Server varchar(100),@UserID int',
    @Account,@Server,@UserID
GO

Open in new window

0
 
LVL 30

Expert Comment

by:anarki_jimbel
ID: 33556018
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.
        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;
            }


        }

Open in new window

0
Independent Software Vendors: 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!

 
LVL 9

Expert Comment

by:puru1981
ID: 33556336
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.
0
 
LVL 4

Author Comment

by:Senz79
ID: 33563864
Hey Cyberkiwi
Can you elaborate your solution with the below parameters
string Account,string Server,string UserID, string UserName, string ActionRequired, string SupportGroup
 
0
 
LVL 4

Author Comment

by:Senz79
ID: 33563897
I urge the administrator to block these kind of message which gives us a negetive impact on our discussion.
0
 
LVL 4

Expert Comment

by:thepanch
ID: 33564014
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.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33564040
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.
0
 
LVL 4

Author Comment

by:Senz79
ID: 33564079
Hi CyberWiki
can you help me manipulate "and"
 
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
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

Open in new window

0
 
LVL 4

Expert Comment

by:thepanch
ID: 33564158
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
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33564161
Just one correction:

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 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
0
 
LVL 4

Author Comment

by:Senz79
ID: 33564182
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,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 + ' 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),@ActionRequired varchar(100),@SupportGroup varchar(100)',
@Account,@Server,@UserID,@UserName,@ActionRequired,@SupportGroup
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

764 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