如何将DBnull插入到可为null的DateTime字段asp.net c#中

本文关键字:字段 DateTime asp net null DBnull 插入 | 更新日期: 2023-09-27 18:28:10

我需要在类字段datetime中插入null吗?这是

public class TimeData{
        public int TD_ID { get; set; }
        public int UD_ID { get; set; }
        public DateTime? TD_start { get; set; }
        public DateTime? TD_end { get; set; }
        public DateTime? timestemp { get; set; }
    public void Insert(int TD_ID, int UD_ID, DateTime? TD_start, DateTime? TD_end, DateTime? timestemp)
    {
        this.TD_ID=TD_ID;
        this.UD_ID = UD_ID;
        this.TD_start = TD_start;
        this.TD_end = TD_end;
        this.timestemp = timestemp;
    }
}

我使用页面中的存储过程访问了sql server。datetime字段的一些返回值为null,并返回为DBnull,不能存储在datetime?中?。我可以用很多if来检查返回值,但我更喜欢找到更优雅的方法来使其工作。sql服务器连接:

try
                {
                    String strConnString = ConfigurationManager.ConnectionStrings["Achi"].ConnectionString;
                    System.Data.SqlClient.SqlConnection SQLCon = new System.Data.SqlClient.SqlConnection();
                    SqlDataReader myReader = default(SqlDataReader);
                    SQLCon = new SqlConnection(strConnString);
                    SqlCommand sqlCmd = new SqlCommand("usp_TD_select_last_record_By_UD_ID", SQLCon);
                    sqlCmd.Parameters.AddWithValue("@UD_ID", user.UD_ID);
                    sqlCmd.CommandType = CommandType.StoredProcedure;
                    SQLCon.Open();
                if (SQLCon.State == ConnectionState.Open)
                {
                    myReader = sqlCmd.ExecuteReader();
                    if (myReader.HasRows)
                    {
                        while (myReader.Read())
                        {
                            timeData.Insert(Convert.ToInt32(myReader["TD_ID"].ToString()), Convert.ToInt32(myReader["UD_ID"].ToString()), Convert.ToDateTime(myReader["TD_start"]), Convert.ToDateTime(myReader["TD_end"]), Convert.ToDateTime(myReader["TD_timestemp"]));
                        }
                    }
                    else { newLine = true; }
                    myReader.Close();
                    SQLCon.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

DB中的表看起来是这样的:

[TD_ID] [int] IDENTITY(1,1) NOT NULL,
[UD_ID] [int] NULL,
[TD_start] [datetime] NULL,
[TD_end] [datetime] NULL,
[TD_timestemp] [datetime] NULL,

如何将DBnull插入到可为null的DateTime字段asp.net c#中

this.TD_start = (myReader["TD_start"] == DBNull.Value) ? 
                (DateTime?)null : Convert.ToDateTime(myReader["TD_start"]); 

您可以有这样的助手函数:

public static T GetValue<T>(this IDataRecord record, string name)
{
   var index = record.GetOrdinal(name);
   return record.IsDBNull(index) ? default(T) : (T)record.GetValue(index);
}

然后称之为:

timeData.Insert(myReader.GetValue<int>("TD_ID"), 
                .... myReader.GetValue<DateTime?>("TD_timestemp"), ...);

我想您在转换为日期时间时会遇到某种异常。请考虑使用Convert.IsDBNull方法。在你的情况下,你可以这样做:

Convert.IsDBNull(myReader["TD_start"]) ? (DateTime?)null : Convert.ToDateTime(myReader["TD_start"])