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:
Last Updated:
Posted by:
Total Views to date: 42156

11 Comments

- Satyabrata

Thanks for sharing.

- 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!

- im

many thanks for all your work!

- 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){
//
}
}

- oleg_harp

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

- Mike

@oleg

Thanks for spotting that.

- José IGor

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

- 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"

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

- michael wexler

ExcellentThank you!

- Oscar Duran

Thank you very much Mike, this post has been very useful to me.
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 I end up deleting quite a lot. The kind of things that will ensure your comment is deleted without ever seeing the light of day are as follows:

  • Requests to fix your code (post a question to forums.asp.net instead, please)
  • Gratuitous links to your own site or product
  • Anything abusive or libellous
  • Spam

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

Lorenzo 3/26/2015 8:21 AM
In response to iTextSharp - Introducing Tables
Hi Mike How can I add padding to all cells in the table? Kind Regards Lorenzo...

Satyabrata Mohapatra 3/25/2015 8:11 AM
In response to How To Send Email In ASP.NET MVC
Great article. Simple and up to the point....

Afzaal Ahmad Zeeshan 3/24/2015 8:17 PM
In response to How To Send Email In ASP.NET MVC
A great way to teach the MVC framework for sending the emails... Also, what I found helpful was the...

Jim H 3/24/2015 2:32 PM
In response to Migrating From Razor Web Pages To ASP.NET MVC 5 - Model Binding And Forms
Thank you. This helps....

wazz 3/22/2015 5:48 AM
In response to Posting Data With jQuery AJAX In ASP.NET Razor Web Pages
great info!!...

rael 3/21/2015 8:53 PM
In response to Getting the identity of the most recently added record
I spent hours trying to figure how to achieve this in C#. This article helped me. Thanks a lot...

Stephen 3/21/2015 8:48 PM
In response to Ajax with Classic ASP using jQuery
This was very helpful, thanks:)...

patrick voes 3/19/2015 10:19 AM
In response to iTextSharp - Introducing Tables
Thank you! very helpfull....

Bigmachini 3/19/2015 6:13 AM
In response to ASP.NET MVC DropDownLists - Multiple Selection and Enum Support
This just made my day, afternoon, evening, night... was looking for a way of doing this without to a...

Bobbyg 3/19/2015 4:14 AM
In response to HTML Helpers For Forms In Razor Web Pages
Nice article. There are bugs in MVC 5 with dropdowns retaining values from other screens and them. I...