WebMatrix - Database Helpers for IN Clauses

There are times when you want to query a database for records matching a range of values, but you do not know what those values are, or even how many there are at design time. You could dynamically build your WHERE clause with multiple ORs, or you can use the SQL IN clause - except that the current database helpers in WebMatrix don't make this easy. It's about time that was fixed....

Just in case you are not clear about the problem being solved by using the IN clause, here's an example scenario: You want to allow your users to select any number of items from a list, and to use their choice to filter your next databse query on. For example, you might decide to present a list of categories to a user, and then to find all books in all the categories they choose. Your starting point might look like this:

@{
    var db = Database.Open("Books");
    var categories = db.Query("Select CategoryId, Category FROM Categories");

}

<!DOCTYPE html>
<html>
    <head>
        <title></title>
    </head>
    <body>
    
    @if(!IsPost){
        <h3>Choose your categories</h3>
        <form method="post">
        @foreach(var item in categories){
            <input type="checkbox" name="categoryid" value="@item.CategoryId" /> @item.Category<br />
        }
        <input type="submit" value="Choose" />
        </form>
    }
    @if(IsPost){
        <h3>You chose:</h3>
        foreach(var item in categories){
            @item.Category<br />
        }
    }
    </body>
</html>

When run, the resulting page presents a series of checkboxes allowing the user to select from multiple categories:

Assuming the user chooses the first, third and fifth, the SQL to to fetch related books can come in two flavours. The first is as follows:

SELECT * FROM Books WHERE BookId = 1 OR BookId = 3 OR BookId = 5

Taking this approach means dynamically generating the SQL by concatenating multiple OR statements. The code to produce this kind of statement can get messy, but is commonly found, and most often leads to the developer concatenating user input directly into the SQL which is not safe. The second approach is to use an IN clause:

SELECT * FROM Books WHERE BookId IN (1,3,5)

An IN clause takes a comma-separated string of values, and if you look a the first code example, you see that each checkbox is given the same name attribute: "categoryId". When a form is posted with mutiple identically named elements selected, the result is passed as a comma-separated string, so on post back, Request["categoryId"] give us "1,3,5". However, simply plugging Request["categoryId"] in as a parameter value will not work. this will give you errors:

var books = db.Query("SELECT * FROM Books WHERE BookId IN (@0)", Request["categoryId"]);

Each value within the IN clause needs to be parameterised on its own. What you really need to end up with is something more like this:

var books = db.Query("SELECT * FROM Books WHERE BookId IN (@0, @1, @2)", value1, value2, value3);

Web Pages is clever enough to see that the arguments value1, value2 and value3 are separate items which need to be passed in to the parameter placeholders at runtime. This is because the second parameter of the Database.Query() method accepts an array of Objects. So the task is to generate the right number of parameter placeholders, and to pass in an array as the second argument. This is how you can do that, given a comma-separated string:

@{
    var db = Database.Open("Books");
    var categories = db.Query("Select CategoryId, Category FROM Categories");
    if(IsPost){
        var temp = Request["categoryId"].Split(new[]{','}, StringSplitOptions.RemoveEmptyEntries);
        var parms = temp.Select((s, i) => "@" + i.ToString()).ToArray();
        var inclause = string.Join(",", parms);
        var sql = "SELECT Category FROM Categories WHERE CategoryId IN ({0})";
        categories = db.Query(String.Format(sql, inclause), temp);
    }

The code takes the comma-separated string and generates an array from it, which is stored in the variable "temp". A second array is created containing strings starting a "@0", and then this array is converted to a string representing the parameter placeholders in the SQL. This is then melded with the core SQL using string.Format, and the "temp" array passed in. And it works:

However, it's a little untidy, so a Helper method would be of use here. Create a folder called App_Code and within that, add a new C# class file. I called mine DatabaseExtensions. The full code for that file is as follows:

using System;
using System.Collections.Generic;
using WebMatrix.Data;
using System.Linq;

public static class DatabaseExtensions
{
    public static IEnumerable<dynamic> QueryIn(this Database db, string commandText, string values)
    {
        if(string.IsNullOrEmpty(values))
            throw new ArgumentException("Value cannot be null or an empty string", "values");
        var temp = values.Split(new[]{','}, StringSplitOptions.RemoveEmptyEntries);
        var parms = temp.Select((s, i) => "@" + i.ToString()).ToArray();
        var inclause = string.Join(",", parms);
        return db.Query(string.Format(commandText, inclause), temp);
    }
    
    public static int ExecuteIn(this Database db, string commandText, string values)
    {
        if(string.IsNullOrEmpty(values))
            throw new ArgumentException("Value cannot be null or an empty string", "values");
        var temp = values.Split(new[]{','}, StringSplitOptions.RemoveEmptyEntries);
        var parms = temp.Select((s, i) => "@" + i.ToString()).ToArray();
        var inclause = string.Join(",", parms);
        return db.Execute(string.Format(commandText, inclause), temp);
    }
}

These methods extend the Database class to provide support for IN clauses. They effectively add two new methods - Database.QueryIn() and Database.ExecuteIn(). The first parameter in both methods is prefixed with the word "this", which denotes what object you want to extend. The rest of the methods takes all that code concerned with creating arrays out of your Razor section in the actual .cshtml file so that it can be replaced like this:

@{
    var db = Database.Open("Books");
    var categories = db.Query("Select CategoryId, Category FROM Categories");
    if(IsPost){
        var sql = "SELECT Category FROM Categories WHERE CategoryId IN ({0})";
        categories = db.QueryIn(sql, Request["categoryId"]);
    }
}

Date Posted: Saturday, September 11, 2010 9:35 AM
Last Updated: Tuesday, February 21, 2012 7:59 AM
Posted by: Mikesdotnetting
Total Views to date: 16821

8 Comments

Saturday, September 11, 2010 1:46 PM - reav

nice! thanks a lot!

Saturday, October 16, 2010 8:13 AM - camus

i dont have a C# background , but i found the behaviour very strange ( i'm talking about the helper methods ) how can you add new methods to Database simply by putting a this ? what the name of that technique so i can learn more about it ?

Saturday, October 16, 2010 8:29 AM - Mike

@camus

They are called extension methods. A bit more of an explanation of how extension methods works can be found here: Displaying The First n Characters Of Text

Tuesday, June 12, 2012 10:16 PM - BenC

Mike, as ever, very useful and clear article.

I have a form that has both checkboxes (for which this article has been helpful) and a set of radio buttons. How would I go about passing another parameter (i.e. from the radio buttons) into this query/code. Have made numerous attempts but no success. Thanks in advance.

Thursday, June 14, 2012 9:55 PM - Mike

@BenC,

You can concatenate it on to the inclause variable.

Tuesday, August 21, 2012 8:49 AM - sunny

We need to Add
Request.Form["Name of the identifier"] whenever we use form method in new webmatrix 2.

Thursday, August 23, 2012 8:48 AM - Mike

@sunny,

No you don't. You can still use the shorter Request["Name of identifier"]. Nothing has changed. That's a standard part of the ASP.NET framework.

Friday, March 15, 2013 1:59 PM - Michael

I'm late to the game on this one, but this has helped me immensely. One quick question: I see you can concatenate on to the inclause variable. I have a form with 3 different checkbox lists that users can choose from. Can you explain a bit more about how I would add those to the inclause variable so that the query accepts more than one parameter?
Add your comment

If you have any comments to make about this article, please use this form to do so. Make sure that your comment relates specifically to the article above. More general comments can be posted through the form on the Contact page.

Please note, all comments are moderated, and some may not be published. The kind of things that will ensure your comment is deleted without ever seeing the light of day are as follows:

  • Not relevant to the article
  • Gratuitous links to your own site or product
  • Anything abusive or libellous
  • Spam
  • Anything in a language I don't understand including gibberish.

I do not pass email addresses on to spammers, so a valid one will assist me in responding to you personally if required.