How do I handle assignment and retrieval dbnull value in timespan?

Question

0.00/5 (No votes)

See more:

I am learning to build Employee Time sheet which holds clock in and clock out before and after break.

Employee can clock in and clock out before they go for break and in that case I would like to insert DB Null value in PostBreakClockIn and PostBreakClockOut properties.

I assign nullable TimeSpan to these properties but I am not getting error to set DBNull value while insert operation.

I have also set Time(7) data type in my DB table and set it to allow null.

What I have tried:

public int Id { get; set; }
        public int Day { get; set; }
        public string EmployeeName { get; set; }
        public TimeSpan? PreBreakClockIn { get; set; }
        public TimeSpan? PreBreakClockOut { get; set; }
        public TimeSpan? PostBreakClockIn { get; set; }
        public TimeSpan? PostBreakClockOut { get; set; }

using (SqlConnection con = new SqlConnection(cs))
           {
               using (SqlCommand cmd = new SqlCommand(query.ToString(), con))
               {
                   cmd.CommandType = System.Data.CommandType.Text;

                   if (update)
                   {
                       cmd.Parameters.AddWithValue("@Id", model.Id);
                   }
                   cmd.Parameters.AddWithValue("@EmployeeName", model.EmployeeName);
                   cmd.Parameters.AddWithValue("@Day", model.Day);
                   cmd.Parameters.AddWithValue("@PreBreakClockIn", model.PreBreakClockIn);
                   cmd.Parameters.AddWithValue("@PreBreakClockOut", model.PreBreakClockOut);
                   cmd.Parameters.AddWithValue("@PostBreakClockIn", model.PostBreakClockIn);
                   cmd.Parameters.AddWithValue("@PostBreakClockOut", model.PostBreakClockOut??DBNull.Value);

                   con.Open();

                   rowAffected = cmd.ExecuteNonQuery();
               }
           }

Posted 23-Feb-20 15:15pm

istudent

Updated 23-Feb-20 18:47pm

Add a Solution

Comments

Jörgen Andersson 24-Feb-20 7:40am

Also, stop using AddWithValue!
https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

istudent · Answer 1 · 2020-02-23T15:39:00

Solution 1

I decided to assign New TimeSpan(0,0,0) as default value.

Is it good practice?

Posted 23-Feb-20 15:39pm

istudent

Comments

phil.o 24-Feb-20 0:49am

Please use the green Improve question widget when you want to bring some more details to the question. Do not use solutions for that purpose.

istudent 24-Feb-20 8:35am

Sorry, I did not know that. I would do it from next time. Thank you for educating me.

phil.o · Answer 2 · 2020-02-23T18:48:00

istudent wrote:
Employee can clock in and clock out before they go for break and in that case I would like to insert DB Null value in PostBreakClockIn and PostBreakClockOut properties.

It is not what your code is actually doing. It only set DBNull.Value for PostBreakClockOut. And since you defined other variables to be nullable too, you have to account for their nullity as well. And you should use DateTime values instead of TimeSpan values. A TimeSpan is a duration between two DateTimes.

C#

public DateTime? PreBreakClockIn { get; set; }
public DateTime? PreBreakClockOut { get; set; }
public DateTime? PostBreakClockIn { get; set; }
public DateTime? PostBreakClockOut { get; set; }

// ...

cmd.Parameters.AddWithValue("@PreBreakClockIn", model.PreBreakClockIn.HasValue ? model.PreBreakClockIn.Value : DBNull.Value);
cmd.Parameters.AddWithValue("@PreBreakClockOut", model.PreBreakClockOut.HasValue ? model.PreBreakClockOut.Value : DBNull.Value);
cmd.Parameters.AddWithValue("@PostBreakClockIn", model.PostBreakClockIn.HasValue ? model.PostBreakClockIn.Value : DBNull.Value);
cmd.Parameters.AddWithValue("@PostBreakClockOut", model.PostBreakClockOut.HasValue ? model.PostBreakClockOut.Value : DBNull.Value);

istudent wrote:
I decided to assign New TimeSpan(0,0,0) as default value.
Is it good practice?

It depends on the meaning of the value. If a zero duration could be meaningful, then it is probably not a good idea, because then you cannot differentiate between a non-set value and a zero duration anymore. Moreover, this defeats the purpose of having declared nullable columns and properties.