Mar 22 2011

Using Nullable Types to Handle Possible NULL Values From Database

By: Brian Dobberteen

I’d like to describe a techinque that I’ve come up with to handle the ever-present issue of the easiest (best?) way to handle building an object from a database query that may or may not contain NULL values. I feel compelled to post this because I haven’t seen this approach detailed elsewhere online.

First, let’s create a simple class to demonstrate our needs:

Note the use of nullable value types on lines 5 and 7 above. For those not familiar with the concept of nullable types, have a look at: Using Nullable Types – MSDN.

In our class, we are anticipating that there may be NULL values stored for a Person’s Age and Last Modified Date.

Simple enough. But how to best build a Person object from a SqlDataReader? I’ve seen tons of different approaches, such as:

This approach certainly isn’t that bad, and seems to be a very common method for handling NULLs via nullable types.

But we’re not content with something that’s only ‘not bad,’ are we? Of course not.

Let’s try another way:

Check out lines 9 and 11 – we are using the ‘as’ operator rather than the clunky ternary, resulting in code that, IMHO, is more concise, clear, and therefore easier to read and maintain.

I’m surprised that I haven’t seen others doing this… I know I’m not the only one to have stumbled across this approach!

Mar 18 2011

Getting Value of NEWSEQUENTIALID() on Insert

By: Brian Dobberteen

Recently, I was writing a stored procedure that, at one point, performs an insert into a table that has a GUID (uniqueidentifier) primary key. The default value for this column is set to NEWSEQUENTIALID().

Further along in the stored procedure, I needed the value of this newly-inserted GUID to use in another INSERT statement. Fortunately, T-SQL provides us with precisely what we need – the OUTPUT clause.

A quick demo:

The table DDL:

Example portion of sproc:

Thanks to the bit highlighted above on lines 7 and 15, we now have a reference to the newly inserted GUID and can use it as needed.

Additionally, the use of newsequentialid() here lessens the impact of using a GUID clustered index.

Mar 6 2010

Check Existing Values with ASP.NET CustomValidator + jQuery $.ajax()

By: Brian Dobberteen


This was selected as an ASP.NET Article of the Day for June 18, 2010

‘SjAJ’ ASP.NET CustomValidator

SjAJ?! Sorry, I couldn’t resist. I had started with AJAX, but thinking now… hmmm, the method being described here is Synchronous jQuery And JSON.

Something tells me that SjAJ is not going to make it to buzzword status. Oh well, I tried.

We will try to quickly outline a method for using the ASP.NET CustomValidator control in conjunction with jQuery and the AJAX Control Toolkit.

Initially, I had tried to use other APIs to call my web services and/or page methods. Problem was, at least that I could tell, was that the asynchronous nature of these operations cause an issue inside the ClientValidationFunction specified in the CustomValidator control.  Eventually, I revisted the code in my earlier posts, and once again, jQuery came to the rescue! Details follow.

ASP.NET CustomValidator Control

For those not familiar with the CustomValidator control, let me give a brief explanation.

The bit we’re most concerned with here is the ClientValidationFunction. This will fire during client-side page validation, receiving two arguments.

source contains the <span> element rendered for the validation control. Since we are going to use the ASP.NET Ajax Control Toolkit’s ValidatorCalloutExtender we will ignore the argument source.

arguments is much more interesting here, as it contains two properties: IsValid and Value

As you might expect, Value contains the value contained in the ControlToValidate that we’ve specified in our validator control. IsValid is what we’ll use to indicate whether or not the value we’re checking exists or not.

Here’s a simple, albeit very contrived, example of how the ClientValidationFunction works:

Again, very contrived, and quite silly, actually – we would be much better off using a RegularExpressionValidator for this particular case… but I digress.

Briefly, this CustomValidator uses client-side code to test the value entered in a text box (ZipCodeTextBox in this case) against the regex /^\d{5}(-\d{4})?$/ which will allow values such as 95060 or 95060-1234 but not 950-0000 nor 95064-0.

Checking for An Existing Value

As mentioned earlier, I was having trouble getting things to work using a few asynchronous techniques. It seemed that if arguments.IsValid is set inside a callback function, it doesn’t register properly, and the control always appears valid.

Luckily, jQuery’s $.ajax() function has a handy option: async, which when set to false does what we need – it waits for the response from the server before continuing to our success function.  Without further ado…

In line 6, we specify the name of our PageMethod contained inside our Default.aspx page. This is only one of many ways to accomplish this, of course. See for my treatment of using PHP as a ‘web service’ of sorts.

Line 9 is the all-important async: false that seems to provide the needed magic to get this all working.

Finally, on line 14, we set our CustomValidator’s IsValid property to true only if the value we are checking for did not exist.

To make things easy, let’s use the ASP.NET membership system to check for an existing username. This has the benefit of providing the method for both server and client side validation. Here goes:

The logic here is very simple. On line 13, we use the ASP.NET Membership system to check for an existing username. This logic also serves us well for our OnServerValidate event handler, starting on line 16.

The .aspx page:

And finally, a screenshot of what the validator looks like when I enter ‘bdobberteen’ for the username – an account I created minutes earlier:

I am not sure if this degrades gracefully in the face of a non-javascript browser. Namely, the ValidatorCalloutExtender won’t display. A possible solution would be to add a ValidationSummary control, initially set its visibility to false, and, upon postback, if the server-side validation fails (!Page.IsValid), set that same ValidationSummary to visible, allowing downstream browsers to view the various error message(s).

Hope this helps someone!

– brian –

Sample Code for Article

Oct 17 2009

Calling a Web Service with ASP.NET AJAX Client Script

By: Brian Dobberteen

Wow! It’s been waaaaay too long since my last installment – been busy with latest programming contract, not that you care.

Since beating the proverbial dead horse is my specialty, this brief post is going to cover making a call to the Yahoo! Maps API via an ASP.NET web service (.asmx) which we then call from client script with the aid of the Microsoft ASP.NET AJAX library(ies).

In order to access the necessary JavaScript script libraries, we must first include the customary ScriptManager object in our page:

As you may have already guessed, this declaration creates a ScriptManager for our page and includes a reference to the web service (which we will create momentarily) used for fetching the City and State upon user input of a US Zip Code.

Without further ado, here’s the complete web service code:

After our ScriptManager registers this service, we now have access to proxy(ies) that can be called from the client side. To do so, we can create a wrapper to the service call:

It’s not as simple as just including the above call – we need to provide the methods named in the second and third parameters (the two lines highlighted in green above). Which would look something like:

Though we aren’t making any use of the context and methodName parameters being passed to our callback functions, I’ll just point out that the context is whatever was set when calling the webservice – in this case, the date and time were used. The context can be very useful to track asynchronous calls in a stateless environment.

The methodName, cleverly enough, returns the name of the web service method that we’ve called – in this case, methodName contains GetCityStateByZip which, as we all know, is the name of the only method exposed by our YahooMapsZipCodeLookup.ZipCodeService.

There is another option to writing explicit callback methods, and one that I prefer (probably due to my experience with jQuery). If we don’t want to write explicit methods, we can use anonymous functions as parameters in the call to our web service:

The two highlighted lines indicate where we’ve replace the names of functions with anonymous functions. NB: the line numbers in the above snippet are supposed to correspond with the actual aspx page.

To hook everything up on our actual page, we need to add an event handler to, in this case, a textbox:

I also snuck in the CityStateResult div that is the target of our DOM manipulation upon receipt of a response from the web service. I also added a do-nothing submit button to demonstrate that a button press will fire the textbox’s onchange event.

The entire aspx page:

In a nutshell, when the user triggers the onchange event of our textbox, the event is handled by our wrapper function getCityStateByZip(zip), to which we pass this.value, which contains the changed value of the textbox. This value is then passed to the web service, which returns a CityState object – ASP.NET AJAX handles all of the serialization and other such details for us in a nearly transparent way. We can refer to the CityState object returned by our web service as if the object was a normal .NET object, i.e. result.City or result.State.

And I think that covers it! No more posts on zip code lookups, I promise!

Before I forget, here is the source to the samples seen above: Calling a Web Service with ASP.NET AJAX Client Script Source Code

Jul 10 2009

Using LINQ to XML instead of XmlReader

By: Brian Dobberteen

Previously, I mentioned that I wasn’t sure if using an XmlReader was the best way to fetch the City and State values from the XML response from the Yahoo Maps API:

We were using the above to basically read the XML response line-by-line, checking for the two elements we are interested in, and storing their values in our simple CityState object ‘ret.’ Again, the XML we are processing looks like:

Though our XmlReader method works fine, it seemed a bit clunky to me, so I set out to check out LINQ to XML to see if we can clean the code up a little bit.

LINQ is still somewhat of a new concept for me, as I haven’t had much chance to work with it given the nature of much of the work I do. Its potential usefulness, however, seems pretty clear to me, and definitely look forward to working with it as much as possible – at least until the next latest, greatest thing comes along! I’ve always felt the LINQ syntax to be a bit unusual, and – unlike I’d first hoped – not that close to SQL. Of course, LINQ isn’t SQL, so I suppose I shouldn’t be surprised that LINQ doesn’t look more like:

Of course, this is an incredibly trivial example, and the only difference between a similar LINQ query is the arrangement of the three components of the query (SELECT, FROM, and WHERE). But in reading that I’ve done, LINQ can get pretty cryptic at times… though I used to feel the same way about complex SQL, which now is very easy for me to comprehend.

At any rate, we’ll be dropping our use of the XmlReader class and instead opt for a LINQ-to-XML XDocument object. We need to change our Imports declaration slightly, removing System.Xml and replacing it with System.Xml.Linq. Much the same as we did with the XmlReader, we use XDocument’s static function Load to grab our XML from the Yahoo Maps API (more about accessing the Yahoo Maps API).

Just like our earlier examples, this is contained within a PageMethod that accepts ‘zip’ as its only parameter and returns a simple CityState object. After the XDocument is loaded, we can now perform a LINQ query on it, allowing for us to easily specify what elements (City and State, in this case) we’d like the value of.

Because we’re interested in the City and State elements, which are descendants of the root ‘ResultSet’, we’re using the cleverly named property Descendants of our XDocument object. Descendants is an IEnumerable collection of XElement objects which is what makes our LINQ operations possible. As you can see, the LINQ query first specifies where to look for something (akin to the FROM clause in an SQL SELECT statement), then we set the criteria to be sure we find the ‘City’ element. I discovered that el.Name won’t work for us, as the Name property of an XElement object is an XName object. The LocalName property of an XElement refers to the elements unqualified name, without any namespace info included. The LINQ statement next ‘Selects’ the element matching our criteria. Finally, since we are only expecting a single city to exist in our XML response, we wrap the whole LINQ query in parentheses and use .Single to return the one-and-only value we’re after. .Single will throw an exception if more than one element was found by our query. To get the value of the State element, just replace Where el.Name.LocalName = “City” to Where el.Name.LocalName = “State”.

I’ve experimented with polling both the City and State elements in one pass, i.e.

But this just seemse to necessitate another query to be sure that we’re getting the right values from our new, albeit smaller, collection of XElements. So, for now, we’ll just perform two seperate LINQ operations… please let me know if there are better ways of going about this (and I’m sure that there are!)

Finally, let’s take a quick look at using a Lambda expression to streamline this whole thing a bit more. The following gives us the same result as the first LINQ query above:

The call to .Where from our xdoc.Descendants is a call to an ‘extension method.’ These extension methods are central to the functionality of LINQ. In this case, we’re passing an ‘anonymous function’ to the Where extension method that we will be using to apply our criteria. The anonymous function is passed el which is an XElement object. And again, we want an element with a LocalName of City, so the body of our anonymous function does just this, compares the LocalName to ‘City’, and if it matches, the expression continues by fetching the Single city element’s Value.

Clearly, we’ve only just scratched the surface of LINQ and LINQ to XML, but we gotta start somewhere, no?