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: 30561

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

Gautam 11/20/2014 8:01 AM
In response to I'm Writing A Book On WebMatrix
Hello Mike, I read your book, loved it! However, I have a few request/suggestions: 1) an example...

Bret Dev 11/19/2014 8:39 PM
In response to The Difference Between @Helpers and @Functions In WebMatrix
Excellent post! One concern - where can you place global @Functions code within an MVC project to Is...

Rob Farquharson 11/19/2014 4:28 PM
In response to iTextSharp - Links and Bookmarks
How can I place text at an absolute position on the page? Also, how can I rotate text?...

Andy 11/17/2014 8:08 PM
In response to MVC 5 with EF 6 in Visual Basic - Sorting, Filtering and Paging
Hello I'm testing your sorting instructions above. This is great and I was able to get it to work...

Gautam 11/17/2014 5:51 PM
In response to WebMatrix - Database Helpers for IN Clauses
Hi Mike, I am very new to programming: In the above example if I want to use a delete button the...

donramon 11/17/2014 3:22 PM
In response to Entity Framework 6 Recipe - Alphabetical Paging In ASP.NET MVC
Congratulations on your new website look and the excellent articles. Thank you!...

Gautam 11/17/2014 11:26 AM
In response to Looking At The WebMatrix WebGrid
Hi Mike, I add the jquery script at the end of my html file.. when ajax attribute is added to the be...

Chet Ripley 11/15/2014 6:57 PM
In response to Adding A New Field
It appears the command is case sensitive. I had the same issue as Cameron. When I changed the to it...

Alvin 11/14/2014 12:49 PM
In response to Razor Web Pages E-Commerce - Adding A Shopping Cart To The Bakery Template Site
Great article Mike! When do you plan to extend the bakery shopping cart beyond this point?...

Gautam 11/14/2014 10:16 AM
In response to Web Pages - Efficient Paging Without The WebGrid
to get the count can we use only the below sql, why to join category and author table var sql =...