How To Check If A Query Returns Data In ASP.NET Web Pages

In order to answer this question, an understanding of the various Database helper methods, and what they return is required. The principal methods are:

The information above is lifted directly from MSDN, but it does not tell the whole truth. The first method, Execute, is used for INSERT, UPDATE and DELETE statements. It will return an integer representing the number of rows that were inserted, updated or deleted. It will return -1 if the query did not affect any rows of data. QueryValue and QuerySingle actually return a dynamic type. Query returns IEnumerable<dynamic>. This can lead to some confusing behaviour.

Here's some code:

var db = Database.Open("Northwind");
var commandText = @"SELECT CategoryId FROM Categories WHERE CategoryName = @0";
var result = db.QueryValue(commandText, "Beverages");

If you hover over var where it precedes result in Visual Studio or Visual Studio Express, you can see that the compiler reports the return type of the QueryValue call as 'Dynamic'. The compiler also reports that this 'Represents an object whose operations will be resolved at runtime'. At compile time, the actual type that result will represent is not known. The compiler will allow all sorts of typos and odd stuff because dynamic effectively gives the compiler permission to ignore everything. For example, you could add code that seems to endow the result variable with magical methods:

var result = db.QueryValue(commandText, "Beverages");
var x = result.GetMyDinner();

There are no squiggly lines under result.GetMyDinner(). The code compiles. There is no GetMyDinner method but the compiler has been given the night off. You can fire up the site that contains this code with no problems - until someone requests the page that includes this code. Then the Runtime Binder will throw an exception, complaining quite rightly that 'int' does not contain a definition for 'GetMyDinner'. It is the job of the C# Runtime Binder to decide on the actual data type represented by the dynamic variable.

QueryValue returns a single object. If the SQL doesn't return any data that matches the criteria, the value of the variable that the result is assigned to will be null. Therefore to test if there is any data, you test for null:

var db = Database.Open("Northwind");
var commandText = @"SELECT CategoryId FROM Categories WHERE CategoryName = @0";
var result = db.QueryValue(commandText, "Beverages");
if(result != null){
    // result has a value
}

It is the same for QuerySingle, which also returns a single object:

var db = Database.Open("Northwind");
var commandtext = @"SELECT * FROM Employees WHERE EmployeeId = 5";
var person = db.QuerySingle(commandtext);
if(person != null){
    //person has a value
}

If the person variable in the above example is null, any attempt to reference its properties will result in a RuntimeBinderException: 'Cannot perform runtime binding on a null reference'.

The Query method always returns a collection. If you hover over var where it precedes products in the following snippet, you can see that the return type is IEnumerable<dynamic>. The Query method does not return null if there are no matching rows. If there are no matching rows, the resulting collection will be empty. It will not contain any elements. There are a couple of ways to test this, both of which make use of Enumerable extension methods. You can use Count():

var db = Database.Open("Northwind");
var commandtext = @"SELECT * FROM Products WHERE CategoryId = 1";
var products = db.Query(commandtext);
if(products.Count() > 0){
    // There are some rows
}

Notice that Count is a method, which is why it is followed by parentheses. However, unless you actually need the total number of elements in the sequence, this is not as efficient as using Any():

var db = Database.Open("Northwind");
var commandtext = @"SELECT * FROM Products WHERE CategoryId = 1";
var products = db.Query(commandtext);
if(products.Any()){
    // There are some rows
}

The Count method iterates all elements in the sequence in order to obtain the total number. The Any method stops at the first element because its job is to test for at least one element.

There is a situation where neither of these two methods work, and that is if you pass a dynamic type into the Query method. Here's an example:

var db = Database.Open("Northwind");
var commandText = @"SELECT CategoryId FROM Categories WHERE CategoryName = @0";
var result = db.QueryValue(commandText, "Beverages");
commandText = @"SELECT ProductName FROM Products WHERE CategoryId = @0";
var products = db.Query(commandText, result);

If you recall form the earlier example, the return type from the QueryValue method is a dynamic object. This in turn is passed in as a parameter to the Query method. If you hover over the var that precedes products in the last line using Visual Studio, this time instead of IEnumerable<dynamic>, you will see that the type is simply dynamic. If you pass a dynamic type into any method, this is what happens - the return type becomes dynamic if it is not explicitly set. There is another rule that concerns dynamic types - you can't use extension methods on them. So if you tried to use Any or Count on products, the dynamic runtime binder will throw an exception to the effect that 'ReadOnlyCollection<object> does not contain a definition for Any'.

So what are your options? ReadOnlyCollection (which is the type that the runtime binder chose) has a Count property. You can use this:

@{
    var db = Database.Open("Northwind");
    var commandText = @"SELECT CategoryId FROM Categories WHERE CategoryName = @0";
    var result = db.QueryValue(commandText, "Beverages");
    commandText = @"SELECT ProductName FROM Products WHERE CategoryId = @0";
    var products = db.Query(commandText, result);
}

@if(products.Count > 0){
    <div>Query returned @products.Count records</div>
}

Just to reinforce the point, there are no parentheses after Count. It is a property, not a method.

Alternatively, you can prevent the return type becoming dynamic. You can do this in one of two ways: you can either alter the type that gets passed in as a parameter or you can explicitly set the return type of the Query method call. You can alter the parameter type in two ways. You can explicitly set it instead of using var:

int result = db.QueryValue(commandText, "Beverages");

Or you can cast it:

var products = db.Query(commandText, (int)result);

And just to round off, here's how to explicitly set the return type so that you can use extension methods:

IEnumerable<dynamic> products = db.Query(commandText, result);