Check if Time Range intersects in C#

Question

I am currently developing a simple enrollment system in C# and I am thinking if there is a straightforward way to check if a certain time range (From - To) of my input session intersects with a time range in the database.

The scenario is as follows:

I have a table in my database named tblSessionList which stores different sessions per subject and I want to check each time the user adds a new session if a particular Room is occupied already in that particular time range. Technically, I want to check if the chosen time range does not conflict with the sessions in tblSessionList.

Add Session Form

tblSessionList

 | Subject Code |   Venue    |   Room   |        Date             |  From   |   To    |
 |              |            |          |                         |         |         |
 |   CRGOV401   |  GT Tower  | Room 3D  | Thursday, June 19, 2014 | 8:00 AM |10:00 AM |
 |   CRGOV401   |  GT Tower  | Room 59  | Sunday, June 29, 2014   | 1:00 PM |3:00  PM |
 |   GNBNK201   | Main Plaza | HR Hall  | Monday, June 30, 2014   | 9:00 AM |11:00 AM |
 |   GNBNK201   | Main Plaza | HR Hall  | Monday, June 30, 2014   | 1:00 PM |3:00     |

I have tried doing the following:

 private void btnAddSession_Click(object sender, EventArgs e)
    {
        bool proceedCopy = true;

        DataTable timeHolder = new DataTable();
        SqlDataAdapter selectTime = new SqlDataAdapter("select [From],[To] from tblSessionList where [Venue] = @venue and [Room] = @room", Connection.conn);
        selectTime.SelectCommand.Parameters.AddWithValue("@venue", venue.Text);
        selectTime.SelectCommand.Parameters.AddWithValue("@room", room.Text);

        selectTime.Fill(timeHolder);


        for(int i = 0; i < timeHolder.Rows.Count; i++)
       {

        if(timeHolder[i][0].toString() == From.Text) //checks if the 'From' of the query is the same with the 'From' of the new entry
              {
                MessageBox.Show("The room is already occupied on that time range");
                proceedCopy = false;
                break;
              }

        else if(timeHolder[i][1].toString() == To.Text) //checks if the 'To' of the query is the same with the 'To' of the new entry.
              {
                 MessageBox.Show("The room is already occupied on that time range");
                 proceedCopy = false;
                 break;
              }
        }
       if(proceedCopy == true)
           //insert code to insert new session to database here
    }

This is a hard-coded checking and this only checks the end points of the time range, not the time range itself. I'm thinking if there's a function in C# that I could use like Time only instead of DateTime.

Note: Every column in my database is a varchar. My apologies for having a dirty code for this is my first system ever.

Any help would be much appreciated.

Is this what you're looking for? http://stackoverflow.com/questions/13513932/algorithm-to-detect-overlapping-periods — Jim Mischel, Jun 18 '14 at 03:08
Typically you would hand this off to the database as part of your query. Tip: Use EXISTS rather than COUNT in the query. — HABO, Jun 18 '14 at 03:10
I'm quite not sure if it's applicable in my case but let me take time to analyze it but it is indeed a major help. Thank you! @JimMischel — Sai, Jun 18 '14 at 03:19

score 0 · Answer 1 · answered Jun 18 '14 at 06:24

0

You can use a interval tree to search on database . interval tree store a range (a,b) and builds a BST from it using comparison between two intervals and decide whether it comes to left or right. The search on interval tree can be done in O(logn).

answered Jun 18 '14 at 06:24

Vikram Bhat

6,106
3
20
19

Check if Time Range intersects in C#

1 Answers1