How to insert DBnull to nullable DateTime field asp.net c#

Question

I need to insert null into a class field datetime? this is the class:

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;

    }
}

I made an access to sql server using stored procedures from a page. some of the return values of the datetime field are null and return as DBnull which cant be stored inside DateTime?. I can check the return values with a lot of if's but I prefer to find more elegant way to make it work. the sql server connection:

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

and the table in the DB look like that:

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

Vojtěch Dohnal · Accepted Answer · 2014-10-28 12:53:45Z

1

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

edited Oct 28, 2014 at 12:53

Vojtěch Dohnal

8,1123 gold badges53 silver badges115 bronze badges

answered Oct 28, 2014 at 12:08

eyalb

3,02210 gold badges45 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Magnus · Accepted Answer · 2014-10-28 12:15:35Z

0

You can have a helper function like this:

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

And then call it:

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

edited Oct 28, 2014 at 12:15

answered Oct 28, 2014 at 12:10

Magnus

47.3k8 gold badges88 silver badges128 bronze badges

Comments

Iurii Khrystiuk · Accepted Answer · 2014-10-28 12:05:39Z

0

I suppose you are getting some kind of exception when converting to date time. Consider using Convert.IsDBNull method. In your case you could do it like this:

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

answered Oct 28, 2014 at 12:05

Iurii Khrystiuk

613 bronze badges

Collectives™ on Stack Overflow

How to insert DBnull to nullable DateTime field asp.net c#

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related