CODEWITHSUNDEEP
c#sqlite
Justin
Justin

Reputation: 553

How do you properly check an SQLiteDataReader for null values?

I am trying to read some rows from a table in an SQLite database. I have a try/catch around my reader code:

try {
            using (SQLiteConnection dbCon = new SQLiteConnection("Data Source=" + dbPath)) {
                dbCon.Open();
                using (SQLiteCommand command = new SQLiteCommand(sqlcmd, dbCon)) {
                    using (SQLiteDataReader rdr = command.ExecuteReader()) {
                        while (rdr.Read()) {
}
catch(exception ex) { throw ex; }

Something like that.

My problem is that any way that I check the reader values, say like:

if (rdr[3].ToString() != "" && rdr[3] != null) {}

It will always pass that if and then still throw an exception of "The given key was not present in the dictionary."

I simply want to reliably check these values so that I can skip over one but without having to hit the catch every time.

Given the previous example of checking for null:

if (rdr[3].ToString() != "" || rdr[3] != null) {
int something = Convert.ToInt32(rdr[3]);
}

this will error everytime there is no value in that field, because it will also always pass the 2 checks in the if.

Upvotes: 5

Views: 7491

Answers (2)

user957902
user957902

Reputation: 3060

Since the data reader returns the singleton instance DBNull.Value a null column's value will be of the type DBNull. We can leverage this fact by using the as operator to convert our value to a reference null if the column value is null

String varcharValue = rdr[3] as String;

So here we would either have an instance of string or a null reference if the column value is null.

Unfortunately, this technique will not work directly with value types. The following code will generate a compilation error:

int numberVlaue = rdr[1] as int; // Will not compile

However, we can use the nullible version of the type to accomplish a similar conversion:

int? nullibleNumberValue = rdr[1] as int?;

and the later check the HasValue property to see if the type is null.

Upvotes: 2

Florian R. Klein
Florian R. Klein

Reputation: 1435

I once tried to check against the null-type, too. Actually, you have to check against the special type of DBNull:

if (rdr[3].GetType() != typeof(DBNull)) { }

Upvotes: 12

Related Questions