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!

Answer 1

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.