Use database data in C #-About DB NULL determination

Suddenly I was addicted to the judgment of NULL in the database

C # article

-Use MySQL in C #– 1. Development environment installation
-Use MySQL in C #– 2. SELECT / screen display
-Use MySQL in C #– 3. Additions and removals
–Determining whether database data is null in C # [this article]
-C # delegates and => (lambda expressions) are treated like pointers to C functions
-Dynamic add control– 1. Button click
-Dynamic add control– 2. Browse textbox

By convention in C #, I was writing the source code with the feeling that acquisition failure would be “null”. .. .. (´ • ̥ ̫ • ̥ `) Actually, I’ve been addicted to databases.

It is ** the treatment of NULL in the database of C # is different from null which means “complete zero” in C language and Java ** in the world of database (SQL). It means that I have unknowingly judged NULL of DB by “ == null".

In the DB world, NULL means an invalid indefinite value, so I regret that I can’t confuse it now …

environment

• OS：Windows 10 Pro
  –Development environment: Visual Studio 2019
  –Database: MySQL 5.7

Original source code state

This time, when MySQL was introduced in C #
I tried to build the program of, but the actual data is as follows.

id	name	memo
1	for test	Only for testing.
2	π	3.14159265358797323…
4	3.14	NULL
6	Super difficult	Difficult Testing
7	difficult	test
8	√5	2.2360679…

Actually, part of the memo contains ** NULL, so in the code below, it was displayed as blank when executed **.

listBox1.Items.Add(row[1]);
listBox2.Items.Add(row[2]);

”== null” could not be judged as NULL

Therefore, since the acquired memo corresponds to row [2], I used “row [2] == null” and “row [2]! = Null” to determine whether it was NULL …

listBox1.Items.Add(row[1]);
if (row[2] != null) {
	listBox2.Items.Add(row[2]);
}
else {
	listBox2.Items.Add("(Not set)");
}

It wasn’t compared well (´ • ̥ ̫ • ̥ `)

Correct DB NULL judgment

Database NULL is an indefinite value

Recently, I have deepened my understanding of NULL in DB, but ** In the database world, NULL means an indefinite value, not a completely invalid zero like C and Java. ** So, when it comes to DB NULL in C language, it’s like an uninitialized variable, which seems to be close to one that contains unstable values.

In that case, even if you try to compare NULL in DB with the “==” and “! =” Operators, it may not work. .. ..

How to make a correct comparison

By the way, as a correct comparison, ** DBNull.Value ** is prepared, so if it is equal to that value, it can be determined that the acquired data is NULL.

listBox1.Items.Add(row[1]);
if (!row[2].Equals(DBNull.Value)) {
	listBox2.Items.Add(row[2]);
}
else {
	listBox2.Items.Add("(Not set)");
}

Now you can compare the case of NULL (♥ ´꒳` \ *)

References

1. How to check if the value of [C #] DataRow is Null
2. DBNull.Value Field –Microsoft .NET Official