Re: DBNull class – something very confusing about it
- From: Pavel Minaev <int19h@xxxxxxxxx>
- Date: Thu, 19 Feb 2009 17:38:37 -0800 (PST)
On Feb 19, 2:43 pm, beginwithl <beginwi...@xxxxxxxxx> wrote:
From SQL book:
“SQL, however, doesn’t allow for data to hold no value. Fields with no
specified value actually do have a value: NULL. NULL is not the same
thing as nothing; NULL represents the unknown.
But MSDN claims:
“The DBNull class represents a nonexistent value. In a database, for
example, a column in a row of a table might not contain any data
whatsoever. That is, the column is considered to not exist at all
instead of merely not having a value. A DBNull object represents the
nonexistent column.”
a) So MSDN essentially claims that Sql table may have a column in a
row that doesn’t have any value assign to it ( not even NULL ) and
thus column isn’t even considered to exist?
But Sql book claims that all fields in a DB must have a value assigned
to them. So which is true?
This is purely a semantical issue. However, the SQL book is definitely
not correct, if you cite it as is, because NULL is not a value. We can
say that "field is NULL", and, according to the SQL spec at least, it
is supposed to mean that "value of this field is unknown" (but can
really mean anything - it's up to the schema designer in practice;
quite often people just use it as a "not specified" marker, for
example). To that extent, it is incorrect to say that "field has a
value of NULL". This is reflected in ANSI SQL syntax in that you
cannot use NULL keyword in all places where a value could go, and you
rather get some special operators (such as "IS NULL" and "IS NOT
NULL") to deal with it.
On the other hand, MSDN description is also quite misleading. I'm not
sure what is meant by the "missing column" there, or how it might be
different from "no value". In any case, SQL boolean semantics make it
very clear that NULL really means "unknown", and nothing else.
* I assume if Sql book is correct, then DBNull instance represents an
unknown value?
DBNull simply represents SQL NULL. What SQL NULL means is another
matter entirely. In practice, whenever you'd get a record from the
database where a field IS NULL, you'll see DBNull.Value as a value of
that field in ADO.NET.
2) “If a database field has missing data, you can use the
DBNull..::.Value property to explicitly assign a DBNull object value
to the field. However, most data providers do this automatically. “
Above quote suggests data providers would automatically assign null
value to the field located in a database itself. When would data
providers do this automatically?
When you're doing an INSERT, and the field in the table is not marked
as IS NOT NULL, and you do not specify any value for that field, then
it will be NULL. This also works for indirect INSERTs, such as when
you use a DataTable/DataAdapter. I don't know any SQL RDBMS that would
behave differently.
When reading from the table, you will _always_ get DBNull for any
NULL.
3) “DBNull is a singleton class, which means only this instance of
this class can exist.”
What is the reason for only allowing one instance of DBNull class to
exist? Couldn’t that cause potentional problems in multithreaded apps?
The reason is that there's no point in having more than one DBNull
instance, and that, since there's only one, you can use referential
equality (==) to check for it. Keep in mind that, as DBNull is a
class, if there could be more than one instance of it, then == would
give false for two DBNull instances, even though logically they both
mean the same thing.
It doesn't cause any problems with multithreading because DBNull is
purely a marker value and has no state. Only shared _mutable_ state
may cause multithreading problems, when two or more threads try to
modify it at the same time, or one thread modifies while another one
reads. For immutable state, there is no concern - and lack of state
is a special case of immutable state, as there's nothing to mutate :)
.
- References:
- DBNull class – something very confusing about it
- From: beginwithl
- DBNull class – something very confusing about it
- Prev by Date: How do I properly close Excel when used with C# COM objects?
- Next by Date: Re: test question 2
- Previous by thread: Re: DBNull class – something very confusing about it
- Next by thread: Re: DBNull class – something very confusing about it
- Index(es):
Relevant Pages
|
