Multi select List Box ‘OR’ search using Stored procedure

It is common requirement to allow multiple List Items selection and selecting the ‘Search’ button returns all the records where each List Item Text matches with record as explained below.

Example: Multiple selection List Box

User Interface

Selecting the Button should return all records WHERE VB.NET ‘OR’ ASP.NET ‘OR’ ‘VBA’ matches using IN clause as this is OR search rather than AND search.

Database:

Say ‘Technology’ column consists of below rows in database table.

Search results:- Search should return all the 5 rows from above table.

Lets explore further to achieve the above requirement using Stored procedure.

I. ASP.NET

<asp:ListBox ID="ddlTechnology" runat="server"     

SelectionMode="Multiple">
<asp:ListItem Value="" Text="Select single or multiple Systems" />
<asp:ListItem Value="ASP.NET" Text="ASP.NET" />
<asp:ListItem Value="VB.NET" Text="VB.NET" />
<asp:ListItem Value="VBA" Text="VBA" />
</asp:ListBox>

II. In code behind build a string with selected List Items Text delimited with a comma as below.

//Loop through List Items list and add selected items
// with comma in between
foreach (ListItem item in ddlTechnology.Items)
{
  if (item.Selected)
  {
  selectedTechnologies +=  "," + item.Text.Trim();
  selectedTechnologiesList.
Add(ddlTechnology.Items.IndexOf(item));
            }
        }
if (selectedTechnologies.Length != 0)
  {
selectedTechnologies = 
selectedTechnologies.Substring(1);
        }
//Store in session
Session["Technologies"] = selectedTechnologies;

//In this example selectedTechnologies String 
//consists of "VB.NET,ASP.NET,VBA"

III. Pass to Stored procedure as parameterized query

/// <summary>Pass search parameters to Stored procedure and ///return DataTable
/// </summary>
/// <returns>DataTable</returns>
private DataTable Search()
    {
string procedure = "spSearchTechnologies";

        SqlConnection con = new SqlConnection(ConnectionString);
//DataTable 
DataTable dtTechnologies = new DataTable();
try
{
SqlCommand cmd = new SqlCommand(procedure, con);
cmd.CommandType = CommandType.StoredProcedure;
//Pass search criteria with parameters
cmd.Parameters.AddWithValue("@Technologies", 
Convert.ToString(Session["Technologies"]));
//Data adapter
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
//fill the data table with results
adapter.Fill(dtTechnologies);
//Display number of records returned
if (dtTechnologies.Rows.Count != 0)
{
lblRecCount.Text = "Total Number of records returned:" 
+ " " + dtTechnologies.Rows.Count.ToString() + "\n";
}
else
{
lblRecCount.Text = "No records matched your search criteria";
}
}

catch (Exception ex)
{
lblRecCount.Text = ex.InnerException.Message;
}

finally
{
if (con != null)
{
con.Close();
}
}
//return the data table
return dtTechnologies;
}
#endregion

IV. Stored Procedure source

ALTER PROCEDURE [dbo].[spSearchTechnologies]
(
	--parameters
	@Technologies nvarchar(100)= NULL;
	
)
AS BEGIN

If @Technologies	IS NOT NULL AND 
Len(@Technologies)=0 Set @Technologies  = NULL

--Select
SELECT id,technologies	
FROM technology t 
WHERE

--StringSplit is T-SQL function that splits passed
--string into separate ones where COMMA (,) appears
--StringSplit function source is copied below
(@Technologies IS NULL OR t.technology
COLLATE DATABASE_DEFAULT IN (SELECT technology
FROM dbo.StringSplit(@Technologies,',') t1,
technology t WHERE 
CHARINDEX(t1.items,t.technology) > 0 ))
)
ORDER BY t.technology

END

V. StringSplit T-SQL Function

FUNCTION [dbo].[StringSplit](@String varchar(8000),
@Delimiter char(1))      
returns @temptable TABLE (items varchar(8000))      
as      
begin      
declare @idx int      
declare @slice varchar(8000)      
     
select @idx = 1      
if len(@String)<1 or @String is null  return      
     
while @idx!= 0      
begin      
set @idx = charindex(@Delimiter,@String)      
if @idx!=0      
set @slice = left(@String,@idx - 1)      
else      
set @slice = @String      
                
if(len(@slice)>0) 
insert into @temptable(Items) values(@slice)      
 
set @String = right(@String,len(@String) - @idx)      
if len(@String) = 0 break      
end  
return      
end

Thats it. Search returns DataTable object with accurate results.

There are number of approaches to handle the requirement, one of the good resources that I come across is at http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm