I almost hate to ask this question seems like it has been asked a million times before but even with me researching the other question I still cant seem to figure this out in my case.
I read that DateTime is a nullable type and I tried a few of the examples but I am trying to figure out if it is NULL in the database my SQLDATAREADER is failing.
Error
System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot ‘be called on Null values.’
DetailsClass
private DateTime? startingDate;
public DateTime? StartingDate
{
get{ return startingDate; }
set{ startingDate = value; }
}
// constructor
Public DetailsClass(DateTime? startingDate)
{
this.startingDate = startingDate;
}
DBClass
using (SqlConnection con = new SqlConnection(connectionString))
using (SqlCommand cmd = con.CreateCommand())
{
List<DetailsClass> details = new List<DetailsClass>();
DetailsClass dtl;
try
{
con.Open();
cmd.CommandText = "Stored Procedure Name";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@MyParameter", myparameter);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
dtl = new DetailsClass((
reader.GetInt32(reader.GetOrdinal("MEMBERSHIPGEN"))),
reader.IsDBNull(1) ? null : reader.GetString(reader.GetOrdinal("EMAIL")),
reader.GetDateTime(reader.GetOrdinal("STARTINGDATE")));
details.Add(dtl);
}
reader.Close();
return details;
}
}
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
Here is a helper method to get the value out from the reader
public static class ReaderExtensions {
public static DateTime? GetNullableDateTime(this SqlDataReader reader, string name){
var col = reader.GetOrdinal(name);
return reader.IsDBNull(col) ?
(DateTime?)null :
(DateTime?)reader.GetDateTime(col);
}
}
Update on how to use in response to comment
using (SqlConnection con = new SqlConnection(connectionString))
using (SqlCommand cmd = con.CreateCommand())
{
List<DetailsClass> details = new List<DetailsClass>();
DetailsClass dtl;
try
{
con.Open();
cmd.CommandText = "Stored Procedure Name";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@MyParameter", myparameter);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
dtl = new DetailsClass((
reader.GetInt32(reader.GetOrdinal("MEMBERSHIPGEN"))),
reader.IsDBNull(1) ? null : reader.GetString(reader.GetOrdinal("EMAIL")),
reader.GetNullableDateTime("STARTINGDATE"));
details.Add(dtl);
}
reader.Close();
return details;
}
}
Also note you are using reader.IsDBNull(1) and then reader.GetOrdinal. Probably should be reader.IsDBNull(reader.GetOrdinal("EMAIL"))
Method 2
replace
DateTime startingDate;
with
DateTime? startingDate;
The question mark marks it as a nullable value and your reader should be able to set startingdate to null instead of throwing an exception.
you could also check for null values while your reader is working and replace null values with empty strings
while(reader.read())
{
//column is an int value of your column. I.e: if the column ist the 8th column, set column to 7 (0-based)
StartingDate = (reader.IsDBNull(column)) ? null : reader.GetOrdinal("STARTINGDATE"));
//instead of null you could also return a specific date like 1.1.1900 or String.Empty
}
Method 3
I know this question has been answered, but further simplifying the code to deal with other nullable variable type. The answer above only limits to one specific variable type. The nice thing about this is that it also allows you to include a default value rather than setting it as null.
public static T GetDataType<T>( this SqlDataReader r, string name, object def = null )
{
var col = r.GetOrdinal(name);
return r.IsDBNull(col) ? (T)def : (T)r[name];
}
then in the code, you can use such as
...
while(reader.Read())
{
data1Bool = reader.GetDataType<bool?>("data1"); // if it's null then we'll set it as null
data2intNotNull = reader.GetDataType<int>("data2", 0); // if the data is null, then we'll set to 0
data3date = reader.GetDataType<DateTime?>("data3", DateTime.Now); // same example as above, but instead of setting to 0, I can default it to today's date.
}
...
I had other issue regarding of null-able value object, and this saved me hours of trying to figure out what’s going on. (WPF doesn’t explain the issue when compiling runtime.)
Method 4
Can you please tell us exactly which line is throwing the error while debugging. It would be easier.
if (! reader.IsDBNull(reader.GetOrdinal("STARTINGDATE"))) {
obj.startingDate = reader.GetDateTime(reader.GetOrdinal("STARTINGDATE"));
}
no need to explicitly assign null if it is DBNull, because it is a nullable type so by default it will contain null.
Ok, as per your updated code (note the comments):
while (reader.Read()) {
dtl = new DetailsClass((reader.GetInt32(reader.GetOrdinal("MEMBERSHIPGEN"))),
// here you are checking null for email
reader.IsDBNull(1) ? null : reader.GetString(reader.GetOrdinal("EMAIL")),
// here you are not checking null for startingdate ?
reader.GetDateTime(reader.GetOrdinal("STARTINGDATE")));
details.Add(dtl);
}
Lets try it in more verbose way:
while (reader.Read()) {
dtl = new DetailsClass();
dtl.membershipgen = reader.IsDBNull(reader.GetOrdinal("MEMBERSHIPGEN")) ? null : reader.GetInt32(reader.GetOrdinal("MEMBERSHIPGEN"));
dtl.email = reader.IsDBNull(reader.GetOrdinal("EMAIL")) ? null : reader.GetString(reader.GetOrdinal("EMAIL")),
dtl.startingdate = reader.IsDBNull(reader.GetOrdinal("STARTINGDATE")) ? null : reader.GetDateTime(reader.GetOrdinal("STARTINGDATE")));
details.Add(dtl);
}
Method 5
Try this:
Make the startingDate variable nullable, like this:
DateTime? startingDate;
Now, when retrieving the value from the SqlDataReader object you need to use the IsDbNull method, which will determine if the value is NULL coming back from the database, like this:
if !reader.IsDBNull(reader.GetOrdinal("STARTINGDATE"))
{
startingDate = reader.GetDateTime(reader.GetOrdinal("STARTINGDATE"));
}
else
{
startingDate = null;
}
Note: I am not sure where you are assigning startingDate to the value read from the database, because it is not shown in the posted code. That is why I assigned it directly in my example, but you may need to adjust the placement of the example logic.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0