"Dynamic" WHERE clause in Query

Arg81

Arg81

Well-known member
Joined
Mar 11, 2005
Messages
949
Location
Midlands, UK
Programming Experience
1-3
I've been requested by users of one of my apps to allow them to search on a number of fields.
This isn't dymanic in the sense that the parameter can change, it's dynamic in the sense that what they want to search for can change.

I.E.
I have 5 fields that are searchable, all comboboxes with different values. I know how to get the SQL parameter query to work using whatever value is selected.

However, User A might want to select values for only 2 fields and leave the other 3 blank.
User B might want to select values for 3 fields and leave the other 2 blank.
User C might want to select values for all 5 fields


I've tried following Implementing Dynamic WHERE-Clause in Static SQL - CodeProject

but none of them seem to work.

Any advice greatly appriciated.
 
Sort by date Sort by votes
What are the data types of the Fields?
If they are all text, then you could do this for the SQL:

VB.NET: 
CREATE PROC dbo.SearchProducts
	@SearchText1 VARCHAR(100) = NULL,
	@SearchText2 VARCHAR(100) = NULL,
	@SearchText3 VARCHAR(100) = NULL,
	@SearchText4 VARCHAR(100) = NULL,
	@SearchText5 VARCHAR(100) = NULL,
AS
BEGIN
	SELECT
		*
	FROM tSearchTable
	WHERE SearchText1 LIKE '%'+@SearchText1+'%'
	AND SearchText2 LIKE '%'+@SearchText2+'%'
	AND SearchText3 LIKE '%'+@SearchText3+'%'
	AND SearchText4 LIKE '%'+@SearchText4+'%'
	AND SearchText5 LIKE '%'+@SearchText5+'%'
END
GO

And your Code for accessing the procedure could look like this:

VB.NET: 
    Public Shared Function SearchTable(ByVal pSearch As SearchText) As SearchResults
        Dim pResults As New SearchResults
        Dim sCommand As New SqlCommand()
        sCommand.CommandType = CommandType.StoredProcedure
        sCommand.CommandText = "dbo.SearchProducts"
        If pSearch.SearchText1 <> "" Then pCmd.Parameters.Add("@SearchText1", SqlDbType.VarChar, 100).Value = pSearch.SearchText1
        If pSearch.SearchText2 <> "" Then pCmd.Parameters.Add("@SearchText2", SqlDbType.VarChar, 100).Value = pSearch.SearchText2
        If pSearch.SearchText3 <> "" Then pCmd.Parameters.Add("@SearchText3", SqlDbType.VarChar, 100).Value = pSearch.SearchText3
        If pSearch.SearchText4 <> "" Then pCmd.Parameters.Add("@SearchText4", SqlDbType.VarChar, 100).Value = pSearch.SearchText4
        If pSearch.SearchText5 <> "" Then pCmd.Parameters.Add("@SearchText5", SqlDbType.VarChar, 100).Value = pSearch.SearchText5
        SetConnectionString = ConfigurationManager.ConnectionStrings("myConnection").ConnectionString

        Dim ds As New DataSet
        ds = FillDataSet(sCommand)

        For Each dr As DataRow In ds.Tables(0).Rows
            pResults.Add(ParseSearch(dr))
        Next

        Return pResults
    End Function

    Private Shared Function ParseSearch(ByVal pDR As DataRow) As Search
        Dim newSearch As New Search

        With newSearch
            .ID = pDR("id")
            ' other columns here
        End With

        Return newSearch
    End Function

I hope that helps
 
Upvote 0 Downvote
cool cheers.

2 are string (1 of which needs to be a wildcard)

1 is bit (True/False)

the others are integer as they take the combobox value (ID) and not the display (name).


I'll have a play around with what you have suggested.

With the "With newSearch" code, is it only the columns I want to search to be listed?
 
Upvote 0 Downvote
nah, you build a class with the columns that are returned, a bit like this:

VB.NET: 
Public Class Search
    Private _serverName As String
    Private _gameMode As String
    Private _leavers As String
    Private _players As String
    Private _location As String
    Private _launch As String
    Private _ipAddress As String
    Private _portNumber As String

    Public Property ServerName() As String
        Get
            Return _serverName
        End Get
        Set(ByVal value As String)
            _serverName = value
        End Set
    End Property

    Public Property GameMode() As String
        Get
            Return _gameMode
        End Get
        Set(ByVal value As String)
            _gameMode = value
        End Set
    End Property

    Public Property Leavers() As String
        Get
            Return _leavers
        End Get
        Set(ByVal value As String)
            _leavers = value
        End Set
    End Property

    Public Property Players() As String
        Get
            Return _players
        End Get
        Set(ByVal value As String)
            _players = value
        End Set
    End Property

    Public Property Location() As String
        Get
            Return _location
        End Get
        Set(ByVal value As String)
            _location = value
        End Set
    End Property

    Public Property LaunchGame() As String
        Get
            Return _launch
        End Get
        Set(ByVal value As String)
            _launch = value
        End Set
    End Property

    Public Property IPAddress() As String
        Get
            Return _ipAddress
        End Get
        Set(ByVal value As String)
            _ipAddress = value
        End Set
    End Property

    Public Property PortNumber() As String
        Get
            Return _portNumber
        End Get
        Set(ByVal value As String)
            _portNumber = value
        End Set
    End Property
End Class

and then a collection like this:

VB.NET: 
Public Class SearchResults
    Inherits System.Collections.ObjectModel.Collection(Of Search)
End Class

Obviously for the class Search, add your own column names :)
 
Upvote 0 Downvote
Next question:
Is it possible to do the above using Child tables?

Basically we have Projects ---- Recipes ---- Ingredients

I need to also search Projects based on an Ingredient used. My old search was easy, I just created the relationship in the DataTable Query UI.


Meh, I hate user requests. The system currently allows them to search what they require but only one search option at a time. They now want the ability to search all criteria, but maybe not all at the same time.... :rolleyes:
 
Upvote 0 Downvote
OK I may being really thick here.

I have created the SP on my SQL database (called spSearch)

However when I go to add the code to my form
VB.NET: 
Public Shared Function SearchTable(ByVal pSearch As SearchText) As SearchResults
        Dim pResults As New SearchResults
        Dim sCommand As New SqlCommand()
        sCommand.CommandType = CommandType.StoredProcedure
        sCommand.CommandText = "dbo.SearchProducts"
        If pSearch.SearchText1 <> "" Then pCmd.Parameters.Add("@SearchText1", SqlDbType.VarChar, 100).Value = pSearch.SearchText1
        If pSearch.SearchText2 <> "" Then pCmd.Parameters.Add("@SearchText2", SqlDbType.VarChar, 100).Value = pSearch.SearchText2
        If pSearch.SearchText3 <> "" Then pCmd.Parameters.Add("@SearchText3", SqlDbType.VarChar, 100).Value = pSearch.SearchText3
        If pSearch.SearchText4 <> "" Then pCmd.Parameters.Add("@SearchText4", SqlDbType.VarChar, 100).Value = pSearch.SearchText4
        If pSearch.SearchText5 <> "" Then pCmd.Parameters.Add("@SearchText5", SqlDbType.VarChar, 100).Value = pSearch.SearchText5
        SetConnectionString = ConfigurationManager.ConnectionStrings("myConnection").ConnectionString

        Dim ds As New DataSet
        ds = FillDataSet(sCommand)

        For Each dr As DataRow In ds.Tables(0).Rows
            pResults.Add(ParseSearch(dr))
        Next

        Return pResults
    End Function

    Private Shared Function ParseSearch(ByVal pDR As DataRow) As Search
        Dim newSearch As New Search

        With newSearch
            .ID = pDR("id")
            ' other columns here
        End With

        Return newSearch
    End Function

Does that go onto the form that I am using for the search, or does it need to go into a Class? the latter is all new to me (in fact SP's are pretty much new to me)
 
Upvote 0 Downvote
Well, what I do is have 3 class libraries.

The Search class and Search results go in a class library called Application.Common
The database functions/subs go in a class library called Application.DataAccess (call the class something like SearchDataAccess.vb)
and then I create another class library called Application.Business with a class called SearchBusiness where you do all your log (for formating and calling your subs/functions from your DataAccess layer, etc)

I hope that makes sense.

And in answer to your other query about multiple tables, yeah you can have as many as you like, just build your statement correctly :)
SP's are easy to use, you build your query first and then just wrap it in the SP declaration :)
 
Upvote 0 Downvote
Yeah it does make sense. I remember years ago when I first started programming (well 2005 when I joined this site) I was looking into ASP.net instead of VB.net and the guides there were for the different layers of logic.

Thanks for the help so far, I'll see what I can do with class libraries and try to work it all out!!
 
Upvote 0 Downvote
I have attached a small project to this post so you can see what I mean.
 

Attachments

  • Search.zip
    49.1 KB · Views: 33
Upvote 0 Downvote
I'm not sure if it's easier (for me) but I've just found code very similar to your
VB.NET: 
CREATE PROC dbo.SearchProducts
	@SearchText1 VARCHAR(100) = NULL,
	@SearchText2 VARCHAR(100) = NULL,
	@SearchText3 VARCHAR(100) = NULL,
	@SearchText4 VARCHAR(100) = NULL,
	@SearchText5 VARCHAR(100) = NULL,
AS
BEGIN
	SELECT
		*
	FROM tSearchTable
	WHERE SearchText1 LIKE '%'+@SearchText1+'%'
	AND SearchText2 LIKE '%'+@SearchText2+'%'
	AND SearchText3 LIKE '%'+@SearchText3+'%'
	AND SearchText4 LIKE '%'+@SearchText4+'%'
	AND SearchText5 LIKE '%'+@SearchText5+'%'
END
GO

And instead of say
VB.NET: 
FROM tSearchTable
	WHERE SearchText1 LIKE '%'+@SearchText1+'%'
	AND SearchText2 LIKE '%'+@SearchText2+'%'

It's
VB.NET: 
FROM tSearchTable
	WHERE (@SearchText1 IS NULL OR SearchText1 LIKE '%'+@SearchText1+'%')
	AND (@SearchText2 IS NULL OR SearchText2 LIKE '%'+@SearchText2+'%')

It's what I had tried before posting on here; except I didn't try it as a stored procedure, I tried programming it directly into the VS GUI on the TableAdapter.


I've tested and it does what I want it to do (without the need for Classes)

Question is, should I put those classes in, or should I just call the stored procedure in normal code and pass the variables over?
 
Upvote 0 Downvote
it's totally up to you, I tend to like to seperate my logic from my data access, so I use classes and I dont like to transport data around in big bulky datasets, so I build my own collections, but tbh I am sure it wont matter that much.
There are many ways to skin a cat as it were :)

Shame you can't open the solution, I had built it all for you :)
 
Upvote 0 Downvote
Ah I see. If this was a full scale application I'd probably learn all the classes etc as I'd prefer to keep all the logic seperate like you.

Haha, if I did the same amount of development now as what I use to, I'd be on VS2008...unfortunately being an IT bod of "does everything thrown at him" it means dev work wasn't top of my list anymore.

Cheers for all the help anyhow, I learnt quite a bit from it which is always what I aim to do. I've upped your rep!
 
Upvote 0 Downvote
thanks alot mate and I am glad I could help :)
 
Upvote 0 Downvote

Similar threads

K
  • Question
Table relations issue
Replies
1
Views
1K
jdelano
J
A
  • Solved
Resolved Location values get changed
Replies
2
Views
649
aaaron
A
S
  • Solved
Question how to retrieve row, update and lock from another user?
Replies
1
Views
2K
servox
S
D
  • Question
Question Adding Items From 1 RichTextBox into Different Arrays
Replies
3
Views
2K
digitaldrew
D
J
  • Question
Perform search on Datagridview using SQL queries
Replies
0
Views
1K
joshface98
J

Share this page

Latest posts

Back
Top