Monday, June 05, 2006

nullable C# SQL gotcha's

I've been looking forward to redesigning our code to use the new nullable value types in C# 2.0 since we made the switch. The types themselves are not a disappointment, but they don't work as well with SQL Server as one would expect. Hard to believe Microsoft didn't think about the large developer community that would be using this with SQL Server 2000/2005.

Two really subtle "gotcha" things I ran into I thought I’d pass along. (int?)row[“field”] This does not evaluate to null when the field is null. It throws an exception. int? fieldValue = null;params.Add(“@Field”, SqlDbType.Int).Value = fieldValue; This does not call stored process with @Field = null when calling SQL Server, it calls stored procs with @Field = default.
My coworker (Bill Ziss) says "This is a major detraction to nullable types' usefulness. This would've been THE major use of nullable types, imo." I tend to agree.

Almost equally annoying (but far more obvious) is the SqlTypes used a property called IsNull and nullable types use a property called HasValue. This means if I convert my code, I have to change all the !o.IsNull to o.HasValue and o.IsNull to !o.HasValue. This makes it way to easy to forget to change an ! and introduce bugs, especially when changing this in 10,000+ lines of code. With as many people already using the SqlDbTypes (even for nullable value types in non-SQL related code) why would they change this?

Overall, I think I am still going to use the new nullable types, rather than keep using the SqlTypes. Having to use a SqlType for a variable not being passed to Sql Server seems silly, and I really don't like having to mix/match both.

I am very disappointed by the lack of support for nullable types in XML datasets in .NET, but after using them in a real project, I have decided they are still too immature for production anyway. They aren't unusable, but they are so unstable that there is no net gain of time developing software. Take a look at my post about FDBK49338 on Microsoft's Product Feedback page (http://lab.msdn.microsoft.com/ProductFeedback/viewfeedback.aspx?feedbackid=32136c55-90d2-4fdc-9b2c-a984b87de7eb). The XSD editor is just plain buggy - I cannot believe this could even make it into BETA 1, let alone 3 betas and released software. They should have left it as is (it was almost as useless in 2003, but less buggy).

1 Comments:

Anonymous Anonymous said...

Your website has a useful information for beginners like me.
»

8:46 PM  

Post a Comment

<< Home