I used a Dataset to map to my Access Database. Once Dataset is created, we can use it with LINQ like we use LINQ to SQL or LINQ to Entity. However, there is a problem. My database allowed to have columns with DB Null, especially in the case of Dates. Though it’s not a good idea to have nulls in the Date column, it’s required in certain situations. Next, I wrote the following LINQ Query.
How to handle DBNULL and Date in LINQ to Dataset
MSDS.ppMasterDataTable ppmtb = new MSDS.ppMasterDataTable();
MSDSTableAdapters.ppMasterTableAdapter ta = new LQ2DS.MSDSTableAdapters.ppMasterTableAdapter();
ta.Fill(ppmtb);
var q = from s in ppmtb
select new { s.ppID, s.ppDate };
Array arr = q.ToArray(); // Exception
When I tried executing var q to get an array, an exception was thrown because data columns had DBnull.
The problem was when I created Dataset; the date columns were mapped with System.DateTime, which doesn’t accept a null value. So whenever the query was executed, it tried to create an object by assigning a null value. It resulted in an exception.
Solution for handling DBNULL
I wasn’t able to find any direct solution for this, but there is an alternate method that worked for me.
System.DateTime doesn’t accept the date format but System.Data.SqlTypes.SqlDateTime does. I replaced all instances of the System.DateTime with System.Data.SqlTypes.SqlDateTime in Dataset I initially created to map the access database. You might also have to update the column property settings in the designer by allowing columns to accept null.
After this, there was no problem, and I had my var q executing for any of the cases, even for creating arrays.
What does the DBNull value mean?
A DBNull object represents the nonexistent column. It is possible, for instance, for a column in a table row not to contain any data. It means that the column does not exist at all, not just not having a value.
What is the DBNull value in the VB net?
Its primary use is identifying variables that contain missing or nonexistent values, such as database fields. Generally speaking, DbNull refers to variables that contain missing or nonexistent values in database fields.
