Setting a DataRow item to null

Question

I have a text file that I read into a data table and then perform a bulk insert into a SQL Server table. It's quite fast and it works great when all the imported values are treated as strings (dates, strings, ints, etc are all imported into string fields).

Now that I have the concept worked out, I'm going back to assign real datatypes in the database and my code. The database has the correct types assigned to the fields. I'm working on the code now.

I'm having a problem with dates. As I mentioned, everything is a string and gets converted to the correct type. In the following code, I want to test if the string value representing a date is null or whitespace. If it isn't null, then use the existing value. Otherwise, set it to null.

row[i] = !string.IsNullOrWhiteSpace(data[i]) ? data[i] : DBNull.Value;

I tried using null but get an error telling me to use DBNull instead. When I use DBNull, I get a message telling me there is no implicit conversion between string and System.DBNull.

The columns in the datatable have datatypes specified (in this case, DataType = Type.GetType("System.DateTime") ) and I set AllowDBNull = true for this column

How do I handle this?

Thanks!

score 20 · Accepted Answer · answered Feb 25 '11 at 18:10

The problem is because of the operation you are using. Since DBNull.Value is not a string, you can't use the conditional operator. This is because, from the conditional operator docs:

Either the type of first_expression and second_expression must be the same, or an implicit conversion must exist from one type to the other.

Try doing this:

if (!string.IsNullOrWhiteSpace(data[i]))
    row[i] = data[i];
else
    row[i] = DBNull.Value;

This bypasses the conversion requirements for both sides to be the same. Alternatively, you can cast both to a System.Object explicitly, and still use the conditional operator.

score 9 · Answer 2 · answered Feb 25 '11 at 18:09

9

You need to cast them both to objects like so:

row[i] = !string.IsNullOrWhiteSpace(data[i]) ? (object)data[i] : (object)DBNull.Value;

answered Feb 25 '11 at 18:09

Shane Andrade

2,655
17
20

8

You only need to cast one of them to `object`. – SLaks Feb 25 '11 at 18:11

score 3 · Answer 3 · answered Oct 17 '16 at 06:52

3

I'm working on Asp.Net MVC 5 C# Web Application and did like this and working fine

rw[6] = (qry.PODate != null) ? qry.PODate : (object)DBNull.Value;

answered Oct 17 '16 at 06:52

RAVI VAGHELA

877
1
10
12

score 0 · Answer 4 · answered Nov 28 '19 at 08:11

0

If you only want to check NULLs, you can also use this syntax:

row[i] = (object)data[i] ?? DBNull.Value;

answered Nov 28 '19 at 08:11

MAXE

4,978
2
45
61

Setting a DataRow item to null

4 Answers4