Nullable Types

[Steven Livingstone]

I've just blogged some stuff on Nullable types in net 2.0.
http://stevenr2.blogspot.com/2006/01/nullable-types-and-null-coalescing.html

Question however as to why you can't simply get an implcit conversion from a
..Net value type to to a Sql Server data type rather than having to use null
cheked and DBNull.Value ?

Does anyone have some explanation as to why this is so tricky as to be
included?

http://stevenR2.com

 

[Steven Livingstone]

of course i meant - as not to be included...

 

[Steven Livingstone]

FWIW, there was a discussion a while back where this was considered (at
least a part of VB and you'd have to assume C#).

http://blogs.msdn.com/vbteam/archive/2004/05/28/143813.aspx#159656

Seems MS considered it. So either i'm missing something and it's already
done, or they chose not to do it.

steven :: http://stevenR2.com

 

[Nicholas Paldino [.NET/C# MVP]]

Steven,

It's not tricky, really. The SQL nullable types were meant as a very
specific solution to a particular domain space. Nullable types are extended
to a much larger space.

Also, I believe that nullable types are meant to be a replacement, not
an interop mechanism.

I don't know that this necessarily justifies it, though.

If anything, I think that the SQL nullables will just go away (meaning,
they will be left in there for backwards compatability), now that a general
solution is in place.

Hope this helps.

 

[Randal]

Does anyone have some explanation as to why this is so tricky as to be
Will it ever be? In SQL Server NULL is a value type while null in C# is
an uninitialized object. So if I wanted to say this:

xmlDataSet.Tables[0].Rows[0]["CurrentSalary"] = null

Then I am setting that object to null, and not assigning the SQL server
column to the SQL value of NULL. The same goes with passing in a C#
null parameter to a stored proc. C# thinks I am passing in nothing (an
unintialized object) when what I really want to pass in is the SQL
value type of NULL.

_Randal

 

[Steven Livingstone]

Well the reason nullable value types were brought out were (in part) because
in C# it isn't always an unitialized *object* you may be passing - i can now
nullify an int? rather than the workarounds of yesteryear. This was created
in part to get round such problems.
But, in setting the value of a stored procedure parameter to NULL (or null
in c#) i think in almost every case you will do a check for null and then
set it to DBNull.Value - if you have exception cases i'd like to hear them.
It just means that i have to check for everything i do.

In your case

xmlDataSet.Tables[0].Rows[0]["CurrentSalary"] = null

this may set the object to null, but if i did (not saying i can, but if)

xmlDataSet.Tables[0].Rows[0]["CurrentSalary"].Value = null

then it would indicate to me that the actual value of the CurrentSalary was
set to null.

In the latter case, it is fairly trivial to then just use DBNull
implicitly - i'd have figured.

But i understand what you are saying and that makes sense also.

steven :: http://stevenR2.com

Will it ever be? In SQL Server NULL is a value type while null in C# is
an uninitialized object. So if I wanted to say this:

xmlDataSet.Tables[0].Rows[0]["CurrentSalary"] = null

Then I am setting that object to null, and not assigning the SQL server
column to the SQL value of NULL. The same goes with passing in a C#
null parameter to a stored proc. C# thinks I am passing in nothing (an
unintialized object) when what I really want to pass in is the SQL
value type of NULL.

_Randal

 

[Steven Livingstone]

Thanks Nicholas - i'd expect them to go too.

I've not read up too much on CLR data types in SQL Server 2005, but it would
have been nice for the C# null to just flow from code to data where maybe a
CLR null was the default NULL in your table columns (even it is were
configurable on install). Hell this would be nice to all the CLR datatypes
that can be supported in sql (int, shorts, strings and so on) - but i guess
going from nvarchar(50) to string requires some fundamenal changes and i'm
just dreaming Give me 5 mins and i'll have SQL 2005 being an OO database!

There may well be perfectly good reasons why this is not the case and i'm
never too old to be told why?!

Thanks for the help,
steven :: http://stevenR2.com

 

[Guest]

Getting into the guts of it, is it true that nullable types is a struct of an
Int that lives on the stack and has an extra field that says "I'm NULL"?
Anyone have anymore insight on the implementation?

Not sure if this helps but I hear that matching DB types with intrinsic
types is something the C# language team is working on (Whoohoo!) - See below

http://msdn.microsoft.com/msdntv/episode.aspx?xml=episodes/en/20040624csharpah/manifest.xml

Sorry I can't offer the correct syntax for you to use in your program. I
would want to do the same thing.

Will it ever be? In SQL Server NULL is a value type while null in C# is
an uninitialized object. So if I wanted to say this:

xmlDataSet.Tables[0].Rows[0]["CurrentSalary"] = null

Then I am setting that object to null, and not assigning the SQL server
column to the SQL value of NULL. The same goes with passing in a C#
null parameter to a stored proc. C# thinks I am passing in nothing (an
unintialized object) when what I really want to pass in is the SQL
value type of NULL.

_Randal

 

[William Stacey [MVP]]

If you ask me, all this null stuff is confusing as heck. I got along just
find with no null value types. First thing Jim Gray does on all his tables
is not allow nulls. Now we also have "IsNull" and "Is Null" to worry about
on CLR UDTs and nullable value types in c#, and Uggg. I would like the old
days (~last year) with value types and ref types and ref types can be null
and get rid of null value types on SQL all together (right, that will
happen).

--
William Stacey [MVP]

| Getting into the guts of it, is it true that nullable types is a struct of
an
| Int that lives on the stack and has an extra field that says "I'm NULL"?
| Anyone have anymore insight on the implementation?
|
| Not sure if this helps but I hear that matching DB types with intrinsic
| types is something the C# language team is working on (Whoohoo!) - See
below
|
|
http://msdn.microsoft.com/msdntv/episode.aspx?xml=episodes/en/20040624csharpah/manifest.xml
|
| Sorry I can't offer the correct syntax for you to use in your program. I
| would want to do the same thing.
|
|
| "Randal" wrote:
|
| > >>Does anyone have some explanation as to why this is so tricky as to be
| > >>included?
| >
| > Will it ever be? In SQL Server NULL is a value type while null in C# is
| > an uninitialized object. So if I wanted to say this:
| >
| > xmlDataSet.Tables[0].Rows[0]["CurrentSalary"] = null
| >
| > Then I am setting that object to null, and not assigning the SQL server
| > column to the SQL value of NULL. The same goes with passing in a C#
| > null parameter to a stored proc. C# thinks I am passing in nothing (an
| > unintialized object) when what I really want to pass in is the SQL
| > value type of NULL.
| >
| > _Randal
| >
| >

 

[Jon Skeet [C# MVP]]

If you ask me, all this null stuff is confusing as heck. I got along just
find with no null value types. First thing Jim Gray does on all his tables
is not allow nulls. Now we also have "IsNull" and "Is Null" to worry about
on CLR UDTs and nullable value types in c#, and Uggg. I would like the old
days (~last year) with value types and ref types and ref types can be null
and get rid of null value types on SQL all together (right, that will
happen).

I'm perfectly happy with nullable types - but we're missing one part of
the quadrant, which is to be able to declare reference type variables
which can *never* be null:

string! x = "hello"; // Fine
string! x = null; // Compiler complains

(Just as an example.)

 

[William Stacey [MVP]]

| I'm perfectly happy with nullable types - but we're missing one part of
| the quadrant, which is to be able to declare reference type variables
| which can *never* be null:

It just seems to me the driving force for null value types was SQL. As
without that, we would not really need them as we got along fine before. I
can remember once where I really needed a nullable value type and coded
around it and that was ok. Sure options can be a good thing, but dang, the
code matrix explodes with all this null testing going on. I have a dream.
Create a version of sql with .Net types only (and user defined types). Have
value types (no nulls) and ref types (allow nulls). Would seem to simplify
things end-to-end.

| string! x = "hello"; // Fine
| string! x = null; // Compiler complains

That would seem reasonable.

 

[Jon Skeet [C# MVP]]

| I'm perfectly happy with nullable types - but we're missing one part of
| the quadrant, which is to be able to declare reference type variables
| which can *never* be null:

It just seems to me the driving force for null value types was SQL. As
without that, we would not really need them as we got along fine before. I
can remember once where I really needed a nullable value type and coded
around it and that was ok.

Yes, you can code around it - but if lots of people are coding around
something, even if it's only required fairly occasionally, isn't it
better to have support for that such that it's only written once?

Sure options can be a good thing, but dang, the
code matrix explodes with all this null testing going on. I have a dream.
Create a version of sql with .Net types only (and user defined types). Have
value types (no nulls) and ref types (allow nulls). Would seem to simplify
things end-to-end.

Well, you can make sure you don't have nulls in your database now. Of
course, the results of doing a sum which matches no values would then
be undefined, etc...

| string! x = "hello"; // Fine
| string! x = null; // Compiler complains

That would seem reasonable.

We can but hope

(It would get rid of a lot of nullity checks in method calls, too - you
just make your method parameter a string! and then you don't need to
worry...)

Jon

 

[Guest]

Speaking of quadrents and possibilities... it would be great to have a
reference of what can and can't be done in C# 1.0 and 2.0; Perrhaps with a
c++ overlay.

I'm sure that would get someone new to the language up to speed quick. If
anyone hasn't written such a grid already, I'll write one as long as someone
will critique it.