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:
Last Updated:
Posted by:
Total Views to date: 23303

10 Comments

- reav

nice! thanks a lot!

- 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 ?

- 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

- 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.

- Mike

@BenC,

You can concatenate it on to the inclause variable.

- sunny

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

- 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.

- 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?

- Gautam

Hi Mike,
I am very new to programming:

In the above example if I want to use a delete button along with the choose button.... is the below code ok and good programming practise or is there a better way?

if(IsPost)
categoryId = Request["categoryid"];

if(Request["button"].Equals("choose"))
//QueryIn code goes here

if(Request["button"].Equals("delete"))
//ExecuteIn code goes here

- Mike

@Gautam

That seems fine to me.

Recent Comments

Justin Kusuma 7/24/2015 3:38 AM
In response to Posting Data With jQuery AJAX In ASP.NET Razor Web Pages
Hi Mike, thanks much for sharing such an article :) Really help me a lot... further, I'd like to...

Michael Easterbrook 7/22/2015 5:35 PM
In response to Inline Razor Syntax Overview
I removed the @ symbols and I am still getting the same error. It only occurs when I have an "if" a...

Sujay 7/22/2015 1:36 PM
In response to ASP.NET MVC, Entity Framework, One-to-Many and Many-to-Many INSERTS
can you explain how to link two tables so that it forms many to many relationship?(Article and...

Max G 7/21/2015 9:29 PM
In response to Scheduled Tasks In ASP.NET With Quartz.Net
Hi, I've opted for this solution in one of my applications but i've found that the apppool is and I...

Michael Easterbrook 7/20/2015 4:31 PM
In response to Inline Razor Syntax Overview
When I have the following code: @foreach (var procRow in procRowDecade) { if (@procRow[3] +...

Shanice 7/18/2015 10:58 PM
In response to A Better Way To Export Gridviews To Excel
Hi. I'm working with mvc. I need to add the above code in the business logic layer, however the...

Matt 7/18/2015 6:29 PM
In response to Nested Layout Pages with Razor
Cheers sir, nice explanation :)...

Keshavan 7/17/2015 9:06 AM
In response to Scheduled Tasks In ASP.NET With Quartz.Net
Hi Mike, I have followed exactly as illustrated in blog, I get error "StdSchedulerFactory.cs" not...

Paul Thiel 7/16/2015 5:17 PM
In response to ASP.NET 5 By Numbers
Comments Below: "The new version of ASP.NET is called ASP.NET 5. It is a framework for developing...

saket singh 7/16/2015 8:42 AM
In response to Scheduled Tasks In ASP.NET With Quartz.Net
hi Mike, great tutorial on Quartz.net , but i have One Problem , Everything is working fine as as...