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!
IsNull(DataColumn) Gets a value that indicates whether the specified DataColumn contains a null value.
Although the DataRow does not have a Columns property, it does have a Table that the column can be checked for. Save this answer. Show activity on this post. You can use the DataColumnCollection of Your datatable to check if the column is in the collection.
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;
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.
You need to cast them both to objects like so:
row[i] = !string.IsNullOrWhiteSpace(data[i]) ? (object)data[i] : (object)DBNull.Value;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With