Tough question on database NULLs.

[Karen Hill]

Suppose you have a textBox control. It is empty and you send the
this.textBox1.Text.ToString() value into a database. The control does
not send a NULL but instead a "".

What is the _CORRECT_ way (e.g. the way the C# language designers at MS
would do it) to handle this situation. I know you could just test for
a "" and then have code that would insert a NULL value like this:

if (this.textBox1.Text.ToString() == '')
{
//pseudo code ! I know you should use parameters!
insert into tableFOO(NULL);

}else
{
insert into tableFOO(this.textBox1.Text.ToString();

}

 

[Michael Nemtsev]

Hello Karen,

What you don't like in this code?

PS: use == String.Empty in lue of == ''

KH> Suppose you have a textBox control. It is empty and you send the
KH> this.textBox1.Text.ToString() value into a database. The control
KH> does not send a NULL but instead a "".
KH>
KH> What is the _CORRECT_ way (e.g. the way the C# language designers at
KH> MS would do it) to handle this situation. I know you could just
KH> test for a "" and then have code that would insert a NULL value
KH> like this:
KH>
KH> if (this.textBox1.Text.ToString() == '')
KH> {
KH> //pseudo code ! I know you should use parameters!
KH> insert into tableFOO(NULL);
KH> }else
KH> {
KH> insert into tableFOO(this.textBox1.Text.ToString();
KH> }
KH>
---
WBR,
Michael Nemtsev :: blog: http://spaces.msn.com/laflour

"At times one remains faithful to a cause only because its opponents do not
cease to be insipid." (c) Friedrich Nietzsche

 

[Karen Hill]

Hello Karen,

What you don't like in this code?

PS: use == String.Empty in lue of == ''

I think that if textBox.Text has not been assigned a value, it should
be NULL!

So in theory these two should be equal _if_ textBox.Text has not been
assigned a value!

insert into tableFOO ( this.textBox.Text.ToString());

should be equal to :

insert into tableFOO ( null );

 

[Mythran]

Suppose you have a textBox control. It is empty and you send the
this.textBox1.Text.ToString() value into a database. The control does
not send a NULL but instead a "".

What is the _CORRECT_ way (e.g. the way the C# language designers at MS
would do it) to handle this situation. I know you could just test for
a "" and then have code that would insert a NULL value like this:

if (this.textBox1.Text.ToString() == '')
{
//pseudo code ! I know you should use parameters!
insert into tableFOO(NULL);

}else
{
insert into tableFOO(this.textBox1.Text.ToString();

}

Well, where we work, we have methods on a utility class that perform the
checks...

public static void TrimColumn(DataRow Row, DataColumn Column)
{
if (!Row.IsNull(Column) && Row[Column].ToString() == string.Empty &&
Column.AllowDBNull) {
Row[Column] = DBNull.Value;
}
}

or something to that effect...then we can use our typed datasets to trim our
columns:

in our ui layer:

Row.LastName = txtLastName.Text;

in our business logic layer:

TrimColumn(Row, Row.TypedTable.LastNameColumn);

Note, we use a custom data set generator that generates a TypedTable
property as well as typed columns. You would need to modify the above code
(which is not our code directly, just typed off top of my head) to
suit...such as passing the column name instead of the actual column.

HTH,
Mythran

 

[Jon Skeet [C# MVP]]

PS: use == String.Empty in lue of == ''

That's very much a matter of taste - I find == "" to be easier to read
than == String.Empty, personally. Another alternative (but one I'm not
as fond of) is

if (someTextExpression.Length==0)

 

[Mark Wilden]

PS: use == String.Empty in lue of == ''

I've heard this recommendation before. What is its rationale?

///ark

 

[Adam Clauss]

I think that if textBox.Text has not been assigned a value, it should
be NULL!

The thing is - since a textbox represents a string - it must be able to represent ALL forms of a string. This includes the empty
string (length = 0). NULL is not a form of a string.

 

[Michael Nemtsev]

Hello Jon Skeet [C# MVP],
J> That's very much a matter of taste - I find == "" to be easier to
J> read than == String.Empty, personally. Another alternative (but one
J> I'm not as fond of) is
J> if (someTextExpression.Length==0)

I find String.Empty is more unique, who knows how empty string could be represented
maybe empty string on MacOs is not an "" at all.
String.Empty just encapsulates this.

---
WBR,
Michael Nemtsev :: blog: http://spaces.msn.com/laflour

"At times one remains faithful to a cause only because its opponents do not
cease to be insipid." (c) Friedrich Nietzsche

 

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

Karen,

The best way, in my opinion, is to use what the framework already offers
you.

When you create the binding to the TextBox (or to any property on any
control), use the FormattingEnabled and the NullValue property to indicate
what value should return null to you.

Assuming you have the binding, you would do this:

// The binding.
Binding binding = <some code to get binding>;

// Enable formatting, this is what allows the binding to handle nulls.
binding.FormattingEnabled = true;

// Set the NullValue to a value that is to be interpreted as null:
binding.NullValue = "";

You have to make sure that you set the NullValue property to "", as it
is null by default (and the textbox always uses the empty string, not null,
to represent empty text).

Once you do that, you will notice that your property will be set to null
(if it is a reference type), or to DbNull (if it is a value type).

If you need a specific value type to be assigned to your property when
the textbox is null (like say, a SqlInt which is null), then you can set the
DataSourceNullValue on the Binding instance to the value that the property
on the data source should be set to.

Use what's in the framework, it's your friend. =) This way is much,
much cleaner.

Hope this helps.

 

[Jesse Houwing]

* Mythran wrote, On 10-7-2006 20:11:

Suppose you have a textBox control. It is empty and you send the
this.textBox1.Text.ToString() value into a database. The control does
not send a NULL but instead a "".

What is the _CORRECT_ way (e.g. the way the C# language designers at MS
would do it) to handle this situation. I know you could just test for
a "" and then have code that would insert a NULL value like this:

if (this.textBox1.Text.ToString() == '')
{
//pseudo code ! I know you should use parameters!
insert into tableFOO(NULL);

}else
{
insert into tableFOO(this.textBox1.Text.ToString();

}

Well, where we work, we have methods on a utility class that perform the
checks...

public static void TrimColumn(DataRow Row, DataColumn Column)
{
if (!Row.IsNull(Column) && Row[Column].ToString() == string.Empty &&
Column.AllowDBNull) {
Row[Column] = DBNull.Value;
}
}

or something to that effect...then we can use our typed datasets to trim
our columns:

in our ui layer:

Row.LastName = txtLastName.Text;

in our business logic layer:

TrimColumn(Row, Row.TypedTable.LastNameColumn);

I've seen people using something very similar, with a few small changes:

public static void SetColumnValue(DataRow Row, DataColumn Column, string
value)
{
if (value == string.Empty &&
Column.AllowDBNull) {
Row[Column] = DBNull.Value;
}
else
{
Row[Column] = value;
}
}

This makes assignments even better, or at least shorter

SetColumnValue(Row, Row.TypedTable.LastNameColumn, txtLastName.Text);

I've also seen customers with a little change in the Enterprise Library
that does this check in the low level storage code. There's even a line
in the OracleDatabase class which is commented out, but does this by
default. This is due to the fact that Oracle handles strings differently
by default.

Jesse

 

[Bruce Wood]

I think that if textBox.Text has not been assigned a value, it should
be NULL!

This is a matter of interpretation. That is to say, this depends upon
your application, and it depends upon what "text box not filled in"
means in your problem domain.

In some domains, for some programs, leaving a text box blank quite
clearly means "this value is a blank string." For example, if I ask the
user to enter a report title and they leave the text box blank, I could
interpret that as the user saying, "Here is your title: it's a blank
string."

In other cases, leaving a text box blank quite clearly means
"information not supplied." For example, if I prompt for a customer
name and the user leaves the text box blank, it's reasonable to say
that an empty string is not a valid customer name and so the user
declined to enter that information.

In the first case, the user IS entering a value... it just happens to
be a blank value. In the second case the user is declining to enter a
value. Which is meant by leaving a text box blank isn't something that
can be decided globally: you have to decide on a case-by-case basis.

For those cases in which a blank text box means "information not
supplied," what you're really talking about is where should the code be
that mediates between the UI (text box) and the business layer (which
should also have a null). If you're using bindings, you should probably
have a custom binding that maps a blank text box to a null in the
business layer. If you are mediating between the controls on the screen
and your internal business (or data) objects in open code, then there's
nothing wrong with what you wrote.

 

[Mark Wilden]

I find String.Empty is more unique, who knows how empty string could be
represented maybe empty string on MacOs is not an "" at all.

"" is an empty string. That just comes from the definition of "" and the
definition of "empty". It's not a representation issue, as "" does not
specify a reprensentation.

Do you also recommend using Decimal.Zero?

Finally, one thing can't be "more unique" than another thing.

(I'm sure there is a good use for String.Empty and Decimal.Zero - probably
having to do with a situation where you want to use a field rather than a
literal - I just don't think it's a good idea for all cases.)

///ark

 

[Barry Kelly]

Suppose you have a textBox control.

There's the problem right there. A TextBox doesn't have any way to
distinguish between "" and null. There's no way of representing the
user's decision to leave the box untouched versus actually entering an
empty value.

One can take up an angle using the same reasoning as online web forms
with Javascript validation: they take the value "" as being "not
filled". That implies that where a not-null constraint exists in the
underlying data model, the validation considers "" as null. As a result,
different idioms have arisen to represent null: "na" for postcodes in
countries which don't have postcodes, for example.

-- Barry

 

[Jon Skeet [C# MVP]]

I find String.Empty is more unique, who knows how empty string could
be represented : maybe empty string on MacOs is not an "" at all.
String.Empty just encapsulates this.

As Mark says, how could it be anything else? "" is a literal with no
characters in it - how could there be any other type of empty string?
What else could "" represent? I guess you *could* have a zero-width
Unicode character in your source code - but in that case I think there
are big problems afoot.

 

[Ciaran]

When a function is JIT compiled, the compiler 'interns' all the strings in
it and uses a single instance of each unique hard coded string for that
function. Empty strings are replace for string.Empty at this point. The
reason string.Empty exists is because string is a reference type, it saves
memory only having one copy of an empty string object in memory.

Ciaran
There are 10 types of people in this world, those that understand binary,
and those that don't.

 

[Mark Wilden]

When a function is JIT compiled, the compiler 'interns' all the strings in
it and uses a single instance of each unique hard coded string for that
function. Empty strings are replace for string.Empty at this point. The
reason string.Empty exists is because string is a reference type, it saves
memory only having one copy of an empty string object in memory.

I don't understand, I'm afraid. Since string lterals are interned, isn't
there only one copy of an empty string in memory anyway?

 

[Barry Kelly]

I don't understand, I'm afraid. Since string lterals are interned, isn't
there only one copy of an empty string in memory anyway?

There can be more than one empty string in memory, but you have to go to
some lengths (typically a StringBuilder) to get an instance of one. If
you use string's overloaded '==' operator, you end up with a value
comparison rather than a reference comparison, so normally one would
never notice the difference.

---8<---
using System;
using System.Text;

class App
{
static void Main()
{
StringBuilder sb = new StringBuilder();
string s = sb.ToString();
Console.WriteLine(object.ReferenceEquals(s, string.Empty));
Console.WriteLine(s == "");
Console.WriteLine(string.IsNullOrEmpty(s));
}
}
--->8---

Prints:

---8<---
False
True
True
--->8---

-- Barry

 

[Jon Skeet [C# MVP]]

There can be more than one empty string in memory, but you have to go to
some lengths (typically a StringBuilder) to get an instance of one. If
you use string's overloaded '==' operator, you end up with a value
comparison rather than a reference comparison, so normally one would
never notice the difference.

And to get back to Mark's point, if you use "" everywhere in your code,
that will still only be one object - so using String.Empty wouldn't buy
you anything.

 

[Michael Nemtsev]

You know, I agree that String.Empty could hardly be smth else then just
"".
I think it's more matter of one's habit.
However, as for me, String.Empty is more conventient way to show empty
string for one reason - you needn't to peer at it to find out whether
it empty string or apostrophe (') withing the "". It's very easy to
miss ' in that case when u review code.

 

[Mark Wilden]

you needn't to peer at it to find out whether
it empty string or apostrophe (') withing the "". It's very easy to
miss ' in that case when u review code.

True - I'll give you that!

///ark