none
How to check the selected from time and to time already present in the database before inserting a new row using asp.net c# RRS feed

  • Question

  • Hello Sir,

    I'm trying to compare the selected from time and to is already present in the database or no.

    Here is the comparison examples 

    in the database if the values stored are 12:00 am to 3:00am, then for the second time if i try to enter either 11:pm to 2:00am or 1:00am to 4:00 am the values should not get inserted . Before insertion im checking for at least 1 hour gap to be present and also the selected time must not lie between the values present in the database.

    Please help me out.

    Thanks

    Democloud

    Friday, May 17, 2019 8:33 AM

All replies

  • Hello Sir,

    I'm trying to compare the selected from time and to is already present in the database or no.

    Here is the comparison examples 

    in the database if the values stored are 12:00 am to 3:00am, then for the second time if i try to enter either 11:pm to 2:00am or 1:00am to 4:00 am the values should not get inserted . Before insertion im checking for at least 1 hour gap to be present and also the selected time must not lie between the values present in the database.

    Im checking the conflicts before insertion

    Please help me out.

    Thanks

    Democloud


    Friday, May 17, 2019 8:34 AM
  • Time comparisons can get tricky since you have to worry about daylight savings and whatnot but the core code is straightforward. The easiest way to visualize this is to have 2 rectangles (A and B) side by side. Now start dragging A over B until it is completely on the other side (B and A). These are all the scenarios you have to account for. The size of the rectangles matter as well. Additional scenarios open up for A being completely contained in B and vice versa.

    The code itself isn't that hard to write once you've identified the scenarios. To make it easier you should probably normalize the data such that A represents the earliest time. This simplifies the scenarios you need to account for. Take a look at the DateRange class I wrote years ago. Specifically look at the Overlap method which does what you want but it works with Date, instead of time. Fortunately the algorithm should be the same, just replace Date with TimeSpan and you should get the scenarios you want. Take a look at the unit tests to verify all the scenarios are covered as well.


    Michael Taylor http://www.michaeltaylorp3.net

    Friday, May 17, 2019 2:27 PM
    Moderator
  • Michael gave you an excellent method of visualizing what has to be done.  The meat of the matter can be put in one statement.  If your new range start time is before the record's end time, AND the new range end time is after the record's start time, then they overlap.

    You could do this in SQL or in C# code, whichever makes more sense for you.


    Tim Roberts | Driver MVP Emeritus | Providenza & Boekelheide, Inc.

    Friday, May 17, 2019 9:43 PM
  • Hello Sir,

    Im working on web application using asp.net c#

    Please help to solve.

    Thanks

    Saturday, May 18, 2019 4:35 AM
  • Hello Sir,

    Can i please share my code which im using to check overlaps of time?

    using my code overlaps are not getting checked for midnight times when the date/day is changed.

    Please help me

    Thanks


    • Edited by Democloud Saturday, May 18, 2019 4:39 AM
    Saturday, May 18, 2019 4:37 AM
  • The type of app shouldn't matter. I gave you the solution. Did you take a look at the code I linked to and try it? By changing the Date to a TimeSpan I suspect the code will just work. Grabbing the corresponding unit tests and running them should easily confirm.

    If you did and it still doesn't work then please post your code and the scenario(s) that are still failing.


    Michael Taylor http://www.michaeltaylorp3.net

    Saturday, May 18, 2019 4:00 PM
    Moderator
  • Hello Sir,

    Below is the code where i have changed Datetime with Timespan, im getting the errors

    public struct DateRange : IEquatable<DateRange>
        {
            #region Construction

            
            public DateRange ( TimeSpan startDate, TimeSpan endDate ) : this(new TimeSpan(startDate), new TimeSpan(endDate))
            {
            }

           
            public DateRange ( TimeSpan startDate, TimeSpan endDate ) : this()
            {
                //Silently handle a mismatched date intervals
                if (startDate > endDate)
                {
                    Start = endDate;
                    End = startDate;
                } else
                {
                    Start = startDate;
                    End = endDate;
                };
            }
            #endregion

            /// <summary>Gets the duration, in days, between the start and end dates.</summary>
            public int Duration
            {
                get { return End.Difference(Start); }
            }

            /// <summary>Gets the end date.</summary>
            public TimeSpan End { get; private set; }

            /// <summary>Gets the start date.</summary>
            public TimeSpan Start { get; private set; }

            /// <summary>Defines an empty data range.</summary>
            public static readonly TimeSpan Empty = new TimeSpan();

            #region Methods

           
            
            public bool Contains ( TimeSpan date )
            {
                return date.IsBetween(Start, End);
            }

            
            [ExcludeFromCodeCoverage]
            public override bool Equals ( object obj )
            {
                return Equals((TimeSpan)obj);
            }

           
            public bool Equals ( TimeSpan other )
            {
                return Start == other.Start && End == other.End;
            }

           
            [ExcludeFromCodeCoverage]
            public override int GetHashCode ()
            {
                return Start.GetHashCode() | End.GetHashCode();
            }

           
            public TimeSpan Intersect ( TimeSpan other )
            {
                if (this == other)
                    return this;

                //If they don't overlap then the intersection is empty
                if (!Overlaps(other))
                    return Empty;

                //Find the largest Start and the smallest End
                var start = (Start > other.Start) ? Start : other.Start;
                var end = (End < other.End) ? End : other.End;

                return new TimeSpan(start, end);
            }

           
            public DateRange Join ( DateRange other )
            {
                if (this == other)
                    return this;

                //Find the smallest start and the largest end
                var start = (Start <= other.Start) ? Start : other.Start;
                var end = (End >= other.End) ? End : other.End;

                return new TimeSpan(start, end);
            }

           
            public bool Overlaps ( DateRange other )
            {
                if (this == other)
                    return true;

               
                if (Contains(other.Start) || other.Contains(Start))
                    return true;

                return false;
            }

           
            public override string ToString ()
            {
                return String.Format("{0} - {1}", Start.TotalHours, End.TotalHours);
            }
            #endregion     
       
            #region Operators


            public static bool operator== ( TimeSpan left, TimeSpan right )
            {
                return left.Equals(right);
            }

            
            public static bool operator!= ( TimeSpan left, TimeSpan right )
            {
                return !left.Equals(right);
            }
            #endregion
        }

    Please help

    Thanks

    Sunday, May 19, 2019 2:53 AM
  • Hello Sir,

    Im checking the overlaps on click of check boxes, below is the code which I'm using to check the conflicts. The code is used in order to check the conflicts while creating a staff roaster. 

    protected void CheckBox1_CheckedChanged(object sender, EventArgs e)
        {
            try
            {            string query = "select * from crm_tbl_newalign where  name = '" + txt_rolename.Text + "' and rowstate='1'";
                SqlDataAdapter da = new SqlDataAdapter(query, con);
                DataTable dt = new DataTable();
                da.Fill(dt);
                if (dt.Rows.Count > 0)
                {
                    int i = 1;
                    foreach (DataRow dr in dt.Rows)
                    {
                        string key = "SweetAlert" + i.ToString();
                        fromdate = Convert.ToDateTime(dr[2].ToString());
                        fromdate1 = fromdate.ToShortDateString();
                        todate = Convert.ToDateTime(dr[3].ToString());
                        todate1 = todate.ToShortDateString();

                        fromtime = dr[4].ToString();
                        totime = dr[5].ToString();
                        ft = Convert.ToDateTime(dr[4].ToString()).TimeOfDay;
                        et = (Convert.ToDateTime(dr[5].ToString())).TimeOfDay;
                        pre1 = (Convert.ToDateTime(dr[4].ToString()).AddHours(-1)).TimeOfDay;
                        post1 = (Convert.ToDateTime(dr[5].ToString()).AddHours(1)).TimeOfDay;
                        nft = (Convert.ToDateTime(txtTime.Text)).TimeOfDay;
                        net = (Convert.ToDateTime(txt_totime.Text)).TimeOfDay;

                        if (nft <= net)
                        {
                            if (Convert.ToDateTime(txt_date.Text) > todate)
                            {
                                ScriptManager.RegisterStartupScript(this, this.GetType(), key, "swal('Warning!','Staff is available','warning');", true);
                            }
                            if (nft <= pre1 && net <= pre1 || nft >= post1)
                            {
                                // ScriptManager.RegisterStartupScript(this, this.GetType(), key, "alert('Available ');", true);
                            }
                            else
                            {
                                ScriptManager.RegisterStartupScript(this, this.GetType(), key, "swal('Warning!','Staff is already assigned','warning');", true);
                            }
                        }
                        else
                        {
                            if (Convert.ToDateTime(txt_date.Text) > todate)
                            {
                                ScriptManager.RegisterStartupScript(this, this.GetType(), key, "swal('Warning!','Staff is available','warning');", true);
                            }
                            if (nft >= pre1 && net >= pre1 || nft <= post1)
                            {
                                // ScriptManager.RegisterStartupScript(this, this.GetType(), key, "alert('Available ');", true);
                            }
                            else
                            {
                                ScriptManager.RegisterStartupScript(this, this.GetType(), key, "swal('Warning!','Staff is already assigned','warning');", true);
                            }
                        }
                        i++;
                    }
                }
                else
                {
                    //ScriptManager.RegisterStartupScript(this, this.GetType(), "SweetAlert", "swal('Warning!', 'No data!', 'warning');", true);
                }
            }
            finally
            {
                con.Close();            con.Dispose();        }
        }

    Before creating a new roaster to a particular staff i need to check its availability and also need to give an hour gap between each time slot.  I need to check every row in database for that staff.Please help Sir. Thanks

    Sunday, May 19, 2019 2:59 AM
  • Please post your code using the Insert Code Block in the editor. It is hard for us to read any of your code if you simply paste it in the middle of your post.

    Michael Taylor http://www.michaeltaylorp3.net

    Sunday, May 19, 2019 3:19 AM
    Moderator
  • You are trying to combine the logic of the DateRange class with TimeRange. What I meant by my original comment was the Overlap function is what you needed, so just extract it out. The TimeRange class would be useful here.

    class Program
    {
        static void Main ( string[] args )
        {
            var range = new TimeRange(new TimeSpan(12, 0, 0), new TimeSpan(16, 0, 0));
    
            var notInRange = new TimeRange(new TimeSpan(2, 0, 0), new TimeSpan(10, 0, 0));
            var inRange = new TimeRange(new TimeSpan(10, 0, 0), new TimeSpan(18, 0, 0));
    
            var shouldNotBeInRange = range.Overlaps(notInRange);
            var shouldBeInRange = range.Overlaps(inRange);
        }
    }
    
    public struct TimeRange : IEquatable<TimeRange>
    {
        public TimeRange ( TimeSpan startTime, TimeSpan endTime ) : this()
        {
            //Silently handle a mismatched time interval
            if (startTime > endTime)
            {
                Start = endTime;
                End = startTime;
            } else
            {
                Start = startTime;
                End = endTime;
            };
        }
        public TimeSpan Duration
        {
            get { return End - Start; }
        }
    
        public TimeSpan End { get; private set; }
            
        public TimeSpan Start { get; private set; }
            
        public override bool Equals ( object obj )
        {
            return Equals((TimeRange)obj);
        }
            
        public bool Equals ( TimeRange other )
        {
            return Start == other.Start && End == other.End;
        }
    
        public override int GetHashCode ()
        {
            return Start.GetHashCode() | End.GetHashCode();
        }
            
        public override string ToString ()
        {
            return String.Format("{0} - {1}", Start, End);
        }                       
    
        public static bool operator == ( TimeRange left, TimeRange right )
        {
            return left.Equals(right);
        }
    
        public static bool operator != ( TimeRange left, TimeRange right )
        {
            return !left.Equals(right);
        }    
    
        //From DateRange
        public bool Contains ( TimeSpan time )
        {
            return time.InRange(Start, End);
        }        
    
        public bool Overlaps ( TimeRange other )
        {
            if (this == other)
                return true;
    
            if (Contains(other.Start) || other.Contains(Start))
                return true;
    
            return false;
        }
    }
    
    //From TimeSpanExtensions
    public static class TimeSpanExtensions
    {        
        public static bool InRange ( this TimeSpan source, TimeSpan minimumValue, TimeSpan maximumValue )
        {
            return source >= minimumValue && source <= maximumValue;
        }
    }


    Michael Taylor http://www.michaeltaylorp3.net

    Sunday, May 19, 2019 3:32 AM
    Moderator
  • You're doing a lot of conversions here. You're also doing a lot in a single function which makes it a lot harder to debug. Break this up. You also have code that is a prime target for SQL injection attacks. Never take anything from the user and insert it directly into a DB. 

    protected void CheckBox1_CheckedChanged ( object sender, EventArgs e )
    {
        //Don't trust user input - use DateTime.TryParse...
        //These variable names are meaningless, give them better names
        var nft = Convert.ToDateTime(txtTime.Text).TimeOfDay;
        var net = Convert.ToDateTime(txt_totime.Text).TimeOfDay;
        var txtDate = Convert.ToDateTime(txt_date.Text);
    
        var timeRange = new TimeRange(nft, net);
    
        //Who is creating the connection? should be done locally
        using (var con = new SqlConnection("..."))
        {
            //You should never select * from a database - column orders are undefined and any DB change can cause
            //your app to fetch the wrong columns - always explicitly specify each column in the order you want them
            var cmd = new SqlCommand("select * from crm_tbl_newalign where name = nameof and rowstate = '1'", con);
            cmd.Parameters.AddWithValue("@name", txt_rolename.Text);
    
            var da = new SqlDataAdapter(cmd);
            var dt = new DataTable();
            da.Fill(dt);
    
            if (dt.Rows.Count > 0)
            {
                int i = 1;
                foreach (DataRow dr in dt.Rows)
                {
                    string key = "SweetAlert" + i.ToString();
    
                    //Assuming that the database is returning back columns as DATETIME you don't need to convert from string
                    var fromdate = dr.Field<DateTime>(2);
                    var todate = dr.Field<DateTime>(2);
    
                    //These names are meaningless, use descrptive names
                    var pre1 = fromDate.TimeOfDay.AddHours(-1);
                    var post1 = toDate.TimeOfDay.AddHours(1);
                    var keyRange = new TimeRange(pre1, post1);
                            
                    //This code appears in both cases of the IF block so appear to always run
                    if (txtDate > todate)
                    {
                        ScriptManager.RegisterStartupScript(this, this.GetType(), key, "swal('Warning!','Staff is available','warning');", true);
                    };
    
                    if (nft <= net)
                    {
                        //Is there where you need the overlap check??
                        //if (timeRange.Overlaps(keyRange))
                        if (!(nft <= pre1 && net <= pre1 || nft >= post1))
                            ScriptManager.RegisterStartupScript(this, this.GetType(), key, "swal('Warning!','Staff is already assigned','warning');", true);
                    } else
                    {
                        if (!(nft >= pre1 && net >= pre1 || nft <= post1))
                            ScriptManager.RegisterStartupScript(this, this.GetType(), key, "swal('Warning!','Staff is already assigned','warning');", true);
                    }
                    i++;
                }
            };
        };
    }
    
    //An additional method for TimeSpanExtensions class
    public static TimeSpan AddHours ( this TimeSpan source, int hours )
    {
        return source.Add(new TimeSpan(hours, 0, 0));
    }
    


    Michael Taylor http://www.michaeltaylorp3.net

    Sunday, May 19, 2019 3:48 AM
    Moderator
  • Hello Sir,

    The overlaps are not getting checked for midnight time .

    Please help

    Thanks

      protected void CheckBox1_CheckedChanged(object sender, EventArgs e)
        {
            //Don't trust user input - use DateTime.TryParse...
            //These variable names are meaningless, give them better names
            var nft = Convert.ToDateTime(txtTime.Text).TimeOfDay;
            var net = Convert.ToDateTime(txt_totime.Text).TimeOfDay;
            var txtDate = Convert.ToDateTime(txt_date.Text);
    
            var timeRange = new TimeRange(nft, net);
    
            //Who is creating the connection? should be done locally
            
                //You should never select * from a database - column orders are undefined and any DB change can cause
                //your app to fetch the wrong columns - always explicitly specify each column in the order you want them
                var cmd = new SqlCommand("select * from crm_tbl_newalign where name = @name and rowstate = '1'", con);
                cmd.Parameters.AddWithValue("@name", txt_rolename.Text);
    
                var da = new SqlDataAdapter(cmd);
                var dt = new DataTable();
                da.Fill(dt);
    
                if (dt.Rows.Count > 0)
                {
                    int i = 1;
                    foreach (DataRow dr in dt.Rows)
                    {
                        string key = "SweetAlert" + i.ToString();
    
                        //Assuming that the database is returning back columns as DATETIME you don't need to convert from string
                        var fromdate = dr.Field<DateTime>(2);
                        var todate = dr.Field<DateTime>(2);
    
                        //These names are meaningless, use descrptive names
                      var  pre1 = (Convert.ToDateTime(dr[5].ToString()).AddHours(-1)).TimeOfDay;
                      var  post1 = (Convert.ToDateTime(dr[6].ToString()).AddHours(1)).TimeOfDay;
    
                      
                        var keyRange = new TimeRange(pre1, post1);
    
                        //This code appears in both cases of the IF block so appear to always run
                        if (txtDate > todate)
                        {
                            ScriptManager.RegisterStartupScript(this, this.GetType(), key, "swal('Warning!','Staff is available','warning');", true);
                        };
    
                        if (nft <= net)
                        {
                            //Is there where you need the overlap check??
                            if (timeRange.Overlaps(keyRange))
                            //if (!(nft <= pre1 && net <= pre1 || nft >= post1))
                                ScriptManager.RegisterStartupScript(this, this.GetType(), key, "swal('Warning!','Staff is already assigned','warning');", true);
                        }
                        else
                        {
                            if (timeRange.Overlaps(keyRange))
                            //if (!(nft >= pre1 && net >= pre1 || nft <= post1))
                                ScriptManager.RegisterStartupScript(this, this.GetType(), key, "swal('Warning!','Staff is already assigned','warning');", true);
                        }
                        i++;
                    
                };
            };
        }
    
        //An additional method for TimeSpanExtensions class
        public static TimeSpan AddHours(this TimeSpan source, int hours)
        {
            return source.Add(new TimeSpan(hours, 0, 0));
        }

    Above code im using check overlaps in time

      protected void CheckBox1_CheckedChanged(object sender, EventArgs e)
        {
            //Don't trust user input - use DateTime.TryParse...
            //These variable names are meaningless, give them better names
            var nft = Convert.ToDateTime(txtTime.Text).TimeOfDay;
            var net = Convert.ToDateTime(txt_totime.Text).TimeOfDay;
            var txtDate = Convert.ToDateTime(txt_date.Text);
    
            var timeRange = new TimeRange(nft, net);
    
            //Who is creating the connection? should be done locally
            
                //You should never select * from a database - column orders are undefined and any DB change can cause
                //your app to fetch the wrong columns - always explicitly specify each column in the order you want them
                var cmd = new SqlCommand("select * from crm_tbl_newalign where name = @name and rowstate = '1'", con);
                cmd.Parameters.AddWithValue("@name", txt_rolename.Text);
    
                var da = new SqlDataAdapter(cmd);
                var dt = new DataTable();
                da.Fill(dt);
    
                if (dt.Rows.Count > 0)
                {
                    int i = 1;
                    foreach (DataRow dr in dt.Rows)
                    {
                        string key = "SweetAlert" + i.ToString();
    
                        //Assuming that the database is returning back columns as DATETIME you don't need to convert from string
                        var fromdate = dr.Field<DateTime>(2);
                        var todate = dr.Field<DateTime>(2);
    
                        //These names are meaningless, use descrptive names
                      var  pre1 = (Convert.ToDateTime(dr[5].ToString()).AddHours(-1)).TimeOfDay;
                      var  post1 = (Convert.ToDateTime(dr[6].ToString()).AddHours(1)).TimeOfDay;
    
                      
                        var keyRange = new TimeRange(pre1, post1);
    
                        //This code appears in both cases of the IF block so appear to always run
                        if (txtDate > todate)
                        {
                            ScriptManager.RegisterStartupScript(this, this.GetType(), key, "swal('Warning!','Staff is available','warning');", true);
                        };
    
                        if (nft <= net)
                        {
                            //Is there where you need the overlap check??
                            if (timeRange.Overlaps(keyRange))
                            //if (!(nft <= pre1 && net <= pre1 || nft >= post1))
                                ScriptManager.RegisterStartupScript(this, this.GetType(), key, "swal('Warning!','Staff is already assigned','warning');", true);
                        }
                        else
                        {
                            if (timeRange.Overlaps(keyRange))
                            //if (!(nft >= pre1 && net >= pre1 || nft <= post1))
                                ScriptManager.RegisterStartupScript(this, this.GetType(), key, "swal('Warning!','Staff is already assigned','warning');", true);
                        }
                        i++;
                    
                };
            };
        }
    
        //An additional method for TimeSpanExtensions class
        public static TimeSpan AddHours(this TimeSpan source, int hours)
        {
            return source.Add(new TimeSpan(hours, 0, 0));
        }


    Wednesday, May 22, 2019 11:17 AM
  • That is one of the fundamental problems with comparing times, they repeat if you cross days. If you need to handle crossing day boundaries then TimeSpan (0-23) is no longer sufficient.

    What does midnight mean to you? Midnight is the very first time of a day to most people. So if you ask someone whether 3 or midnight was greater most people would say 3. If this isn't the rule that you need to follow you'll have to adjust your comparison logic to take midnight into account. One option would be to detect midnight and set the TimeSpan's Day to 1. This would push it to the next day and so 3 would not be greater than midnight. But this wouldn't work if you have time ranges like 11 PM - 2 AM as, once again, you're crossing days. 

    If you need to support times that cross days you should switch up to DateTime as you'll want the date component. You could stick with TimeSpan but you'd have to "know" when ranges cross a boundary and adjust accordingly.

    Example, if you have range A which is from 11 PM to 2 AM then that crosses a day boundary. If you have another range B which is from midnight to 1 AM then it would overlap range A if you were limited to a single day but it wouldn't if you crossed day boundaries. Again, the issue is that a time is not unique across days. So midnight to 1 AM would be the very start of the first day or it could be the end of the first and the beginning of the second.

    You could code for the day differences but this is changing the semantics of the function now. Not fully tested but something like this.

    public struct TimeRange : IEquatable<TimeRange>
        {
            public TimeRange ( TimeSpan startTime, TimeSpan endTime ) : this()
            {
                Start = startTime;
                End = endTime;
            }
    
            //This doesn't work if start/end times cross day boundaries
            public TimeSpan Duration {
                get { return End - Start; }
            }
    
            public TimeSpan End { get; private set; }
    
            public TimeSpan Start { get; private set; }
    
            public override bool Equals ( object obj )
            {
                return Equals((TimeRange)obj);
            }
    
            public bool Equals ( TimeRange other )
            {
                return Start == other.Start && End == other.End;
            }
    
            public override int GetHashCode ()
            {
                return Start.GetHashCode() | End.GetHashCode();
            }
    
            public override string ToString ()
            {
                return String.Format("{0} - {1}", Start, End);
            }
    
            public static bool operator == ( TimeRange left, TimeRange right )
            {
                return left.Equals(right);
            }
    
            public static bool operator != ( TimeRange left, TimeRange right )
            {
                return !left.Equals(right);
            }
    
            //From DateRange
            public bool Contains ( TimeSpan time )
            {
                return time.InRange(Start, End);
            }
    
            public bool Overlaps ( TimeRange other )
            {
                if (this == other)
                    return true;
                            
                //Same day
                if (Start <= End)
                {
                    return Contains(other.Start) || other.Contains(Start);
                } else //Different days
                {
                    //Break into 2 ranges that we can test against
                    var rangeDay1 = new TimeRange(Start, new TimeSpan(23, 59, 59));
                    var rangeDay2 = new TimeRange(new TimeSpan(), End);
    
                    return rangeDay1.Overlaps(other) || rangeDay2.Overlaps(other);
                };
    
                return false;
            }
        }

    Note though that my TimeRange class I wrote is for arbitrary times and you're focusing it down to time of day which is a subset with different rules. Don't try to apply this logic when supporting true time ranges.


    Michael Taylor http://www.michaeltaylorp3.net

    Wednesday, May 22, 2019 2:10 PM
    Moderator
  • Based on the original post, you are validating on two conditions:

    1. The difference between the start and end times of the input must be >=1 hour.  This can be checked with a simple endTime.Subtract(startTime).TotalHours >= 1  (assuming you have logic that ensures that the end time is after the start time or you do an Abs() on the expression)

    2. Validate that the input time range doesn't overlap an existing range in the store.  This is a relatively verbose but filled-with-options, and the math behind them, SO post: https://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap


    ck

    Wednesday, May 22, 2019 9:14 PM