Is it possible to use a multi-parameter query in .NET
Here's the method
Public Function SearchCatalog(ByVal searchString As String, ByVal allWords As String) As OleDbDataReade
' Create the connection objec
Dim connection As New OleDbConnection(connectionString
Dim command As New OleDbComman
' We guard agains bogus values here - if we receive anythin
' different than "TRUE" we assume it's "FALSE
If allWords.ToUpper = "TRUE" The
' Create and initialize the command object and search all word
command.CommandText = "SearchCatalogAllWords
command.Connection = connectio
command.CommandType = CommandType.StoredProcedur
Els
' Create and initialize the command object and search any word
command.CommandText = "SearchCatalogAnyWords
command.Connection = connectio
command.CommandType = CommandType.StoredProcedur
End I
' We eliminate separation character
searchString = searchString.Replace(",", " "
searchString = searchString.Replace(";", " "
searchString = searchString.Replace(".", " "
' We create an array which contains the word
Dim words() As String = Split(searchString, " "
' wordsCount contains the total number of word
Dim wordsCount As Integer = words.Lengt
' index is used to parse the list of word
Dim index As Integer =
' this will store the total number of added words
Dim addedWords As Integer =
' We allow a maximum of 5 word
While addedWords < 5 And index <
' We add the @searchWord parameters her
If addedWords < wordsCount The
addedWords +=
' Add an input parameter and supply a value for i
command.Parameters.Add("@searchWord" + addedWords.ToString, words(index)
Els
addedWords +=
' Add a blank input paramete
command.Parameters.Add("@searchWord" + addedWords.ToString, ""
End I
index +=
End Whil
' Open the connectio
connection.Open(
' Return a SqlDataReader to the calling functio
Return command.ExecuteReader(CommandBehavior.CloseConnect ion
End Functio
Here is the query
SELECT Product.ProductID, Product.Name, Product.Description, Product.SKU, Product.Price, Product.ImagePat
FROM Produc
WHERE (((Product.Name) Like "*"+[@searchWord1]+"*")) OR (((Product.Description) Like "*"+[@searchWord1]+"*"))AND (((Product.Name) Like "*"+[@searchWord2]+"*")) OR (((Product.Description) Like "*"+[@searchWord2]+"*")) AND (((Product.Name) Like "*"+[@searchWord3]+"*")) OR (((Product.Description) Like "*"+[@searchWord3]+"*")) AND (((Product.Name) Like "*"+[@searchWord4]+"*")) OR (((Product.Description) Like "*"+[@searchWord4]+"*")) AND (((Product.Name) Like "*"+[@searchWord5]+"*")) OR (((Product.Description) Like "*"+[@searchWord5]+"*"))