Retrieving Null Values from a database Using Strongly Typed Dataset
I came across this little doozy on my current project.
The Situation
We have an application that is a multitiered application, with an ASP.NET front end, a Business Logic Layer, and a Data Access Layer. We are using strongly typed datasets (that are created from Oracle Views which correspond to the cursors that are output by the stored procedures). In some situations we have some fields that can be null in the databasae, but are represented by reference types in .NET (e.g. DateTime, Integer, etc). The problem arises when these values are null is that an exception is thrown when a OracleDataAdapter.Fill operation occurs.
The Solution
So, after a lot of searching, I came up with a solution,
- Right click the dataset and select "Open With"
- Select the XML editor
- Find the reference field that you require to be able to be null
- For DateTime fields, add the following to the xs:element:
- msprop:nullValue="0001-01-01T00:00:00.0000000"
- For other reference types, set to a value that you will identify as null (e.g. -1)
- Save and recompile
In the end,the solution is very simple, but very hard to find a definitive solution to. Note that I am using Visual Studio 2005 with .NET 2.0
Hope that helps someone out there.