Aug 23, 2011

Passing jQuery MultiSelect values to an SQL Server Parameter


The Problem

There are various jQuery plugins out there that allow you to convert a simple dropdown list into a multi-select one. They all return either an array of the selected choices, or a comma-separated string like 'opt1,opt3'. We generally take this value and put it into a hidden field we can access on the server side and try and pass it to our store procedure or query as a parameter. All goes well till you find that the query/procedure doesn't give you the expected results anymore if it has the keyword "IN", and you're trying to filter your results based on your dropdown's selection.
When you pass a string 'Miami,Berlin,London' as a parameter, say @Cities, to a query like

SELECT Population FROM Cities WHERE Name IN @Cities

It will only produce the correct results if you select only one checkbox from the multiselect. The reason is SQL server needs a list to come after the “IN” keyword, and 'Miami,Berlin,London' is not a list, but a single string literal. The query will try and look for a single record where the city name is 'Miami,Berlin,London' as it currently looks like this:

SELECT Population FROM Cities WHERE Name IN ('Miami,Berlin,London')

What we need to send the query is this:

SELECT Population FROM Cities WHERE Name IN ('Miami','Berlin','London')

The Solution

We can create a simple table valued function that takes our comma-separated string and returns a list that the “IN” keyword can work with. Here’s a sample:

CREATE FUNCTION [dbo].[SplitListWithDelimiter](
@InputString VARCHAR(8000) 
, @Delimiter VARCHAR(2) = ',' 
) RETURNS @List TABLE (Item VARCHAR(1000))
 
BEGIN
DECLARE @Item VARCHAR(1000)
WHILE CHARINDEX(@Delimiter ,@InputString ,0) <> 0
BEGIN
SELECT
@Item=RTRIM(LTRIM(SUBSTRING(@InputString ,1,
CHARINDEX(@Delimiter ,@InputString ,0)-1))),
@InputString =RTRIM(LTRIM(SUBSTRING(@InputString ,
CHARINDEX(@Delimiter,@InputString ,0)+
LEN(@Delimiter ),LEN(@InputString ))))
IF LEN(@Item) > 0
INSERT INTO @List SELECT @Item
END
 
IF LEN(@InputString ) > 0
INSERT INTO @List SELECT @InputString 
RETURN
END

Now in our procedure or query, all we need to do is call this function and pass it our string:

SELECT Population FROM Cities WHERE Name IN
(SELECT Item FROM dbo.SplitListWithDelimiter(@Cities,','))

This will give us a result like this:
Item____
'Miami'
'Berlin'
'London'



Jul 26, 2011

Creating filter links for an ASP.Net MVC 3 List View


I’ve started dabbling with ASP.NET MVC 3 recently and have been following Jon Galloway’s “ASP.NET MVC Music Store Tutorial”. It’s a great place to start if you have little or no experience with ASP.NET MVC (like me).
The tutorial shows you how to create a music store application and has an administrative section that lets us add albums and classify them by genre. The code samples come with a substantial amount of seed data as well so I was a littler overwhelmed when I first created the landing page of the administration and saw a sprawling table and a huge vertical scroll.



I wanted to be able to filter down the list based on Genre because that’s what I base my collection of music on. So as my first experiment with MVC, I decided to put filter links on top and I may even put in paging and search features later and write posts about it to keep you updated Dear Reader. As of now, this is what is was aiming for:


To give you a little background details on the current implementation, I have an Album, Genre, and Artist class in my Model. The Genre class has a GenreID which I plan to use in my URL to filter the results that are displayed.
E.g.: If Classical has GenreID 1, the URL will look something like: /StoreManager/Index/1 and initially when no filter is applied, it will look like: /StoreManager
MVC follows a lot of “Convention over Configuration” so when you create an app with default route settings in Global.asax, the controller takes Index as the default action. My Index action presently looks like:










Now to filter this list based on GenreID, I’m going to do the following things:
  • Send a list of Genre to the View (To display the filter links)
  • Add Filter links to the View
  • Pass a parameter ID to the Index method which I’ll keep as nullable
  • Filter the list based on supplied ID (if present)
There are multiple ways you can send data from the controller to the view, I’m gonna using the ViewBag.
Then in my view index.cshtml (I’m using the razor view engine), I loop through the list to display my filter links:

I’ve used the ActionLink Html helper to display the links which show the Genre Name as the link text and redirect the user to the Index view with GenreID being passed to the Action as a parameter named ID. This would render a link like
/StoreManager/Index/1
Now in my Index method, I add a nullable integer parameter and filter the list based on the supplied value. My Index method now looks like:














Note the parameter declaration, the ? specifies that id is a nullable. As we may view the list without any filters, we must first check if id has a value and only then filter the list. Now when I browse to the URL StoreManager/Index/1, I see only classical albums and when I browse to /StoreManager I see all Genres.