Simplify if else condition using timespan in C#

Question

I have to create a real time report. For that, I have to write conditions for each and every hour of a given day. In the code below, the condition checks for the current day of week and then check for the current time and based on that a report has to be generated.

protected void sample()
{
    TimeSpan zerothHour = new TimeSpan(00, 0, 0);
    TimeSpan firstHour = new TimeSpan(01, 0, 0);
    TimeSpan secondHour = new TimeSpan(02, 0, 0);
    TimeSpan thirdHour = new TimeSpan(03, 0, 0);
    TimeSpan fourthHour = new TimeSpan(04, 0, 0);
    TimeSpan fifthHour = new TimeSpan(05, 0, 0);
    TimeSpan sixthHour = new TimeSpan(06, 0, 0); 
    // and so on until the twentyfourth hour
    if (DateTime.Today.DayOfWeek == DayOfWeek.Monday)
    {
        if (DateTime.Now.TimeOfDay >= sixthHour && DateTime.Now.TimeOfDay <= seventhHour)
        {
            //MySql query here
            string MyConString = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
            MySqlConnection connection = new MySqlConnection(MyConString);
            string agentlogin = "SELECT agentlogin FROM agentdetails WHERE location = 'PNQ10-Pune' AND shift IN('6:00-15-00', '22:00-7:00') AND Mon = 'W'";
            MySqlCommand cmd = new MySqlCommand(agentlogin, connection);
            connection.Open();
            MySqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
               //lblagentlogin.Text += rdr["agentlogin"] + Environment.NewLine;
                sqlList.Add(Convert.ToString(rdr["agentlogin"]));
            }
        }
        else if(DateTime.Now.TimeOfDay >= seventhHour && DateTime.Now.TimeOfDay <= eigthHour)
        {

        }
        else if (DateTime.Now.TimeOfDay >= eigthHour && DateTime.Now.TimeOfDay <= ninthHour)
        {

        }
        else if (DateTime.Now.TimeOfDay >= ninthHour && DateTime.Now.TimeOfDay <= tenthHour)
        {

        }
        else if (DateTime.Now.TimeOfDay >= tenthHour && DateTime.Now.TimeOfDay <= eleventhHour)
        {

        }
        // and so on for the entire cycle of time
    }
}

The code above is only for Monday and I have to do the same thing for the other six days of week too. When I add the queries inside each conditions, it would be like hundreds of lines.

Is there a better way to get this done without having to write hundreds of lines of code?

Intumescence

What about for loops? Or using something like Quartz for task scheduling? – Lingenfelter

OT DateTime.Today.TimeOfDay <= seventhHour you want to use < instead of <= – Erethism

You could also use functions for simplifying this task. – Nihon

Are you sure this is the right approach? How do your MySQL queries differ based on the hours? – Brandebrandea

Do those blocks always end "on the hour"? Then you could use a switch on DateTime.Now.Hour` (by the way, DateTime.*Today* is always midnight) – Erethism

@JonasH I posted the question cuz I didn't feel this is right. – Intumescence

@HansKesting My bad, right I'll change it to DateTime.Now – Intumescence

@Intumescence I know, but if you show your SQL code maybe we can help you improve that too – Brandebrandea

@JonasH Ok I'll make the edit with mysql – Intumescence

How different are those "MySql query here" commands for the various days and hours? Could you (for instance) combine them into "weekend/workweek" and "inside office hours/outside"? Could you even use the current day/hour as parameter for those commands? – Erethism

@HansKesting I've updated the code with the mysql query only for the first condition. I think You'll get an idea of what I'm trying when you see that – Intumescence

You should try @Enigmativity's answer – Kayseri

@ikram Yep working on it – Intumescence

What does AND Mon ='W' indicate? Do you have a column per week day? – Brandebrandea

@JonasH Yep I have separate columns like Tue, Wed, Thu, Fri, Sat and Sun and 'W' indicates agent scheduled to work and 'OFF' indicates unscheduled – Intumescence

@Intumescence - Please don't edit the question so that it invalidates existing answers. You should only extend your question with new information that is relevant to answer the original question. If you find that the answers you're getting are raising more questions then please post them as a new question, don't deface the existing one. – Glasses

@Glasses Ok I've rolledback the changes, Thanks for letting me know :) – Intumescence

Answer 1

Does this work for you?

var sqls = new []
{
    "select x from y",
    "select w from q",
    // etc - 24 options
};

var sql = sqls[DateTime.Now.Hour];

Or even:

var sqls = new Action[]
{
    () => { /* sql for midnight */ },
    () => { /* sql for 1 am */ },
    // etc
    () => { /* sql for 11 pm */ },
};

var sql = sqls[DateTime.Now.Hour];

sql.Invoke();

---

If you want DayOfWeek and Hour then you could use this:

var sqls = new string[][]
{
    new [] { "select x from y", "select w from q", },
    new [] { "select x from y", "select w from q", },
    new [] { "select x from y", "select w from q", },
    new [] { "select x from y", "select w from q", },
    new [] { "select x from y", "select w from q", },
    new [] { "select x from y", "select w from q", },
    new [] { "select x from y", "select w from q", },
};

var sql = sqls[(int)DateTime.Now.DayOfWeek][DateTime.Now.Hour];

---

Based on the comments and other answers, here's a more succinct way of doing it:

string day = DateTime.Now.DayOfWeek.ToString().Substring(0, 3);

string[] shifts = new []
{
    "('22:00-7:00')",
    "('22:00-7:00', '6:00-15:00')",
    // 24
};

string shift = shifts[DateTime.Now.Hour];

string sql = $"SELECT agentlogin FROM agentdetails WHERE location = 'PNQ10-Pune' AND shift IN {shifts} AND {day} = 'W'";

Glasses

Nice approach. What if we make the array 2 dimensional, that shows days and hours – Kayseri

Sure, you can have a jagged array. – Glasses

It would be fit better to the question I think. Upvoted! – Kayseri

Still working on it, trying to get my query inside this method – Intumescence

@Intumescence - If you could provide a few more samples of the code that you're trying to actually write inside each part of the if it would make it easier to write the code that you need. It seems like it would be a fairly straight-forward refactoring exercise. – Glasses

Sure I'm making an update of what I'm trying now. Will edit on the question. – Intumescence

Please check the latest edit made along with Jonas H's method. It actually works but I'll have to write the conditions for each and every hour and it is alright, but if there is any better method I'd like to know that. – Intumescence

@Intumescence - Why go for the more complicated switch option that Jonas has put forward. My array approach does the same thing, but simpler. – Glasses

Ok I'll try it right away. Sorry didn't see the update. I'll let you know once done – Intumescence

Answer 2

It sounds like you can vastly simplify your code by generating your SQL dynamically. I am guessing a bit as I don't know your data model fully, but something along the following:

var dayColumns = new [] { "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat" };
var currentDayColumn = dayColumns[(int) DateTime.Now.DayOfWeek];

string shifts;

switch (DateTime.Now.Hour) {
  case 0:
    shifts = "('22:00-7:00')"
    break;
  case 6:
    shifts = "('22:00-7:00', '6:00-15:00')"
    break;
  //TODO - more cases
}

string sql = "SELECT agentlogin FROM agentdetails WHERE location = 'PNQ10-Pune' AND shift IN " + shifts + " AND " + currentDayColumn + " = 'W'";

If you can change the shift to two columns with the start and end hours, you can optimise it further like this:

var hour = DateTime.Now.Hour

string sql = "SELECT agentlogin FROM agentdetails WHERE location = 'PNQ10-Pune' AND " + hour + " >= shift_start_hour AND " + hour + " < shift_end_hour AND " + currentDayColumn + " = 'W'";

Keats

I think this would sort out the problem with the Day of week part, but I have more than 10 types of shifts in table. Would I have to create combinations of cases inside the switch? – Intumescence

That could work. But maybe your data model is also due for a rework (assuming you can change it). Would it be possible to change the shift column to two integer column for the number of the hour when the shift starts and ends? – Brandebrandea

I think using your method along with mine would work great. – Intumescence

Please check the latest edit made along with your method. It actually works but I'll have to write the conditions for each and every hour and it is alright, but if there is any better method I'd like to know that. And I can't change the column, I have no control over that. – Intumescence

Instead of hardcoding all hour/shift combinations, you could add another table that lists all applicable shifts by hour. Then join on that table based on the hour which will be passed as parameter – Erethism

@HansKesting yes that would work, I'll do that. Thanks :) – Intumescence

Answer 3

Assuming your SQL also depends on WeekDay + Hour (otherwise it wouldn't make much sense?) you can do something like this:

protected void sample()
{
    var now = DateTime.Now;
    var sql = GetSql(now.DayOfWeek, now.Hour);
    // execute sql
}

protected string GetSql(DayOfWeek dayofweek, int hour)
{
    // generate sql, using "(int)dayofweek" if needed
}

Prodrome