CODEWITHSUNDEEP
c#asp.netsql-serverdatetimenullable
Segev Dagan
Segev Dagan

Reputation: 119

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

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,

Upvotes: 1

Views: 6770

Answers (3)

eyalb
eyalb

Reputation: 3022

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

Upvotes: 1

Magnus
Magnus

Reputation: 46909

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"), ...);

Upvotes: 0

Iurii Khrystiuk
Iurii Khrystiuk

Reputation: 61

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"])

Upvotes: 0

Related Questions