使用SQLDataReader可以为空的DateTime [英] Nullable DateTime with SQLDataReader

问题描述

我几乎不愿意问这个问题，好像它已经被问了一百万遍了，但是即使我研究了另一个问题，我仍然无法解决这个问题.

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.

我了解到DateTime是可为空的类型，我尝试了一些示例，但是我试图弄清数据库中SQLDATAREADER是否为NULL.

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.

错误

System.Data.SqlTypes.SqlNullValueException:数据为Null.不能在Null值上调用此方法或属性."

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;

                    }
                }

推荐答案

这是从阅读器中获取价值的辅助方法

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);
  }
}

更新有关如何使用评论

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;

                }
            }

还请注意，您正在使用reader.IsDBNull(1)，然后使用reader.GetOrdinal.可能应该是reader.IsDBNull(reader.GetOrdinal("EMAIL"))

Also note you are using reader.IsDBNull(1) and then reader.GetOrdinal. Probably should be reader.IsDBNull(reader.GetOrdinal("EMAIL"))

这篇关于使用SQLDataReader可以为空的DateTime的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！