WebMatrix - Database Helpers for IN Clauses

5 (11 votes)

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"]);
    }
}

You might also like...

Date Posted:
Last Updated:
Posted by:
Total Views to date: 25101

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

- Robby

Would it also be possible to override the default query and querysingle methods to include the azure sql retry logic this way ?
See also: http://www.asp.net/aspnet/overview/developing-apps-with-windows-azure/building-real-world-cloud-apps-with-windows-azure/transient-fault-handling

- Mike

@Robby,

I know nothing about Azure. It's not a service I use. Sorry.

Recent Comments

Abolfazl Roshanzamir 14/09/2016 05:36
In response to Loading ASP.NET Core MVC Views From A Database Or Other Location
Nice article. Thanke you so much ....

cyrus 02/09/2016 15:12
In response to ASP.NET Web Pages vNext or Razor Pages
I've got some news. As Damian stated in this link: https://github.com/aspnet/Mvc/issues/5208 “We...

Simon 01/09/2016 08:00
In response to Loading ASP.NET Core MVC Views From A Database Or Other Location
Thanks Mike, nice post and exactly what I was looking for. Like you said, I think I'll opt to the...

dave 20/08/2016 14:57
In response to ASP.NET Web Pages vNext or Razor Pages
Do SimplemembershipProvider in viewpages is supported?...

Steven 18/08/2016 04:40
In response to Entity Framework Code First and Stored Procedures
Can you provide the directives (using statements) you're using for EF7 example?...

yousaid 17/08/2016 22:08
In response to ASP.NET Web Pages vNext or Razor Pages
Increasingly, learning a Microsoft tool is no longer worth the return on investment. Too many tools...

jared 12/08/2016 05:54
In response to ASP.NET Web Pages vNext or Razor Pages
hi mike, just for clarification, is viewpages something different from webpages? is webpages still...

Jocke 08/08/2016 20:37
In response to Loading ASP.NET Core MVC Views From A Database Or Other Location
Good post! If this was to be implemented in a CMS where users can change the view files, how would I...

cyrus 05/08/2016 19:49
In response to ASP.NET Web Pages vNext or Razor Pages
I think adding these features to webpages make it complicated. msft forget webpages goal so we move...

Curt Smith 27/07/2016 20:38
In response to ASP.NET Web Pages vNext or Razor Pages
I am only slightly disappointed to hear that WebMatrix is officially dead, because I suspected this...