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

If you execute a query against a database, you need to know if any data was returned. But what is the best way to test for the presence of data when using the Web Pages Database helper?

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

Method Return Type Remarks
Execute int A value that indicates the number of records affected by the command being executed.
QueryValue Object A single scalar value.
QuerySingle Object Represents a single row returned by the query, or the first of multiple rows.
Query IEnumerable<Object> Represents a collection of rows of data returned by the query.

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);
The Web Pages Database helper relies heavily on the dynamic data type. The dynamic type has some particular characteristics which catch the unwary out. Those peculiarities have been examined in this article, and some strategies have been presented to ensure that you can work trouble-free with the default data access framework provided by ASP.NET Web Pages.

 

Date Posted: Wednesday, July 3, 2013 2:01 PM
Last Updated: Sunday, October 27, 2013 7:54 AM
Posted by: Mikesdotnetting
Total Views to date: 33517

10 Comments

Thursday, July 4, 2013 3:51 PM - Satyabrata

Thanks for sharing.

Sunday, August 4, 2013 8:39 AM - Lee

Actually, the db.QueryValue() method return DBNull , so (result != null) always true. I try to use (result != DBNull.Value) to check the return data,it works when the query value is null,but when the query value is a string,the code (result != DBNull.Value) will throw a exception .Please check it.
And my problem is still there ,How to Check if db.QueryValue() method Returns Data ?
Best regards for your help!

Sunday, August 4, 2013 11:01 PM - im

many thanks for all your work!

Monday, August 5, 2013 6:07 AM - Mike

Lee,

The QueryValue method will return DBNull.Value if the underlying value in the database is NULL. If you suspect that could be the case, you should test for null first (there may be no matching row) and then cast result to object and compare it to DBNull.Value:

if(result!= null){
if((object)result != DBNull.Value){
//
}
}

Saturday, October 26, 2013 6:30 PM - oleg_harp

Mike, thank you for sharing. I'v found an error. Any() returns bool. The correct expression: if(products.Any()){}

Sunday, October 27, 2013 7:56 AM - Mike

@oleg

Thanks for spotting that.

Friday, January 24, 2014 5:42 AM - José IGor

Thanks for the tutorial! You help me in my project.

Monday, March 31, 2014 4:17 PM - Gavin

This does not work. I've tried this and get "Cannot convert dynamic to bool", so it's the exact same error I get if I try if (query==0), only that gives "Cannot convert dynamic to int"

Tuesday, April 1, 2014 8:19 AM - Mike

@Gavin

It works. However, your implementation obviously doesn't. You would get better assistance if you posted your issue to a forum (forums.asp.net or stackoverflow). That way you can show the code you are having problems with.

Thursday, November 13, 2014 5:45 PM - michael wexler

ExcellentThank you!
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.

Recent Comments

Allen Michaels 12/17/2014 4:37 PM
In response to Cascading DropDownLists with jQuery and ASP.NET
Fantastic thank you so much!...

Emily 12/17/2014 12:36 PM
In response to Parameterized IN clauses with ADO.NET and LINQ
Thanks, very helpful!!!! Can i use this for multiple in's ????? IN (.....) and IN(...) and IN...

sss 12/16/2014 3:06 PM
In response to Solving the Operation Must Use An Updateable Query error
good...

Gjuro 12/15/2014 10:30 PM
In response to Examining the Edit Methods and Edit View
You have one fromr (and it should be from, I suppose). :-)...

Gjuro 12/15/2014 10:27 PM
In response to Adding Search
Hi, thnx for all this C#->VB translations. Yet, the following code block is (slightly) in error it a...

Scot 12/14/2014 1:39 PM
In response to Entity Framework 6 Recipe - Alphabetical Paging In ASP.NET MVC
Thanks,Mike I found solution....

Gjuro 12/13/2014 10:52 PM
In response to Accessing Your Model's Data from a Controller
The article mentions "Creating an Entity Framework Data Model for an ASP.NET MVC Application" (at is...

Samuel 12/13/2014 8:40 AM
In response to Displaying The First n Characters Of Text
I have failed to use the extension because it throws an error that it doesn't recognise the chop be...

Ignas 12/12/2014 5:11 PM
In response to Cleaner Conditional HTML Attributes In Razor Web Pages
Any suggestions for Html Helper elements with HtmlAttributes, when you need to conditionally set it...

Gautam 12/11/2014 8:50 PM
In response to Validation In Razor Web Pages 2
Hi Mike Is this required for V3, non html helper input...