Valid, Validating & Validated Values

Cramming Valid Values Into A Parameter

Last week I managed to complete the Building Commands chapter that I talked about in my last column. As I worked though the section on building a Parameters collection, I stumbled over a problem that has been there since the first version of the .NET Framework.

The problem has to do with how (and when) the Parameter object's Value property is validated. In ADO classic when we tried to set the Parameter Value property with a value that was either too long or not the right type (for example, a string instead of a number), ADO threw an error immediately. This made the error handling a bit tough as you had to wrap the code that set the Parameter with an error handlerone for each Parameterbut at least you knew which column was in error. On the other hand, ADO.NET does not care what you put in the Value property. You could stick your granny’s nightie in there (if you could make it an object) and ADO.NET would not care. However, when you try to execute the Command, ADO.NET throws an exception that says it can’t convert, that there would be string truncation or some such, but what Parameter is it talking about? This fact it keeps to itself.

Validating Via The CLR

So, what should you do about this? Well, my co-author Peter Blackburn and I worked for several days coming up with a solution. We tried several (some rather complex) approaches. When you construct a Parameter object you can set the intended datatype and the size (if it’s a variable-length type like a VarChar), and these settings are exposed as properties. However, it’s not that easy to compare a system type with a provider-specific type such as a SqlType. I started by building a giant Case statement that made the comparison and ran a whole set of values through it. It worked, but it was like screwing a 4-ton air conditioner on the back of my computer cabinet to solve my heat problem. We needed a better solution, and we finally hit on something simple.

We created another variable of the intended type and set it to the intended value. This was done in a Try/Catch so it would fail if the variable was not the right type. Testing for length was far easier. It’s funny how this solution seems so obvious once you figure it out.

Validating At The Source

The real question is when should this test be made? Over the years I’ve learned that data validation is absolutely critical to data processing. I can’t count how many times a simple data overflow or type-check issue has brought an entire system to its knees. But data validation problems are best solved early in the processwhen the data is first created. If users are involved, you must consider that they might be the authority on the values. They provide the eyes to read the form or credit card number they’re transcribing. Only they know the birth date and the invoice or part number. Your application might (and should) provide lists of these values when they are known so the user simply has to choose from a date picker or a drop-down list. But when this is not possible, it’s up to you to validate this input dataand doing so just before you stick the value into a Command Parameter value is too late.

Validation needs to be done at the sourcelike in the TextBox control as the data is entered. This might mean creating a custom TextBox control that validates the keystrokes as they come in. This is not hard to do (we include a sample in the book), but it ensures that a numeric field only has numbers and a decimal pointonly if the numeric datatype is not an integer.

CLR languages such as Visual Basic .NET and even C# make it easy to create custom controls to validate data. There are a wealth of these available on the Internet, and because they provide the source, they’re easy to use and tune. I suggest you try building a few of these the next time you have to make sure that the object you’re trying to stick into that Command Parameter fitswithout worrying if the sleeves get cut off.