VS LightSwitch 2011 is released today to get the development/deployment process up and running in few minutes..
Read more at http://www.microsoft.com/visualstudio/en-us/lightswitch
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