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