Calculate Number of days between two dates excluding holidays from Database using C# and VB.Net in ASP.Net

Hey maideen,

Please refer below sample.

HTML

StartDate :
<asp:TextBox runat="server" ID="txtStartDate" />
<br />
EndDate :<asp:TextBox runat="server" ID="txtEndDate" />
<br />
<asp:Button ID="Button1" Text="Calculate" runat="server" OnClick="Calculate" />

Namespaces

C#

using System.Data.SqlClient;
using System.Configuration;

VB.Net

Imports System.Data.SqlClient

Code

C#

protected void Calculate(object sender, EventArgs e)
{
    List<DateTime> holidays = new List<DateTime>();
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT HoliDayDate FROM HolidayList WHERE HoliDayDate BETWEEN @Date1 AND @Date2", con))
        {
            cmd.Parameters.AddWithValue("@Date1", txtStartDate.Text);
            cmd.Parameters.AddWithValue("@Date2", txtEndDate.Text);
            con.Open();
            using (SqlDataReader sdr = cmd.ExecuteReader())
            {
                while (sdr.Read())
                {
                    holidays.Add(Convert.ToDateTime(sdr[1]));
                }
            }
            con.Close();
            int days = 0;
            DateTime startDate = Convert.ToDateTime(txtStartDate.Text);
            DateTime endDate = Convert.ToDateTime(txtEndDate.Text);

            for (DateTime date = startDate; date <= endDate; date = date.AddDays(1))
            {
                if (startDate.DayOfWeek != DayOfWeek.Saturday && startDate.DayOfWeek != DayOfWeek.Sunday && !holidays.Contains(date))
                {
                    days++;
                }
                startDate = startDate.AddDays(1);

            }
            Response.Write("Number of days between " + Convert.ToDateTime(txtStartDate.Text).ToShortDateString() + " and "
                    + Convert.ToDateTime(txtEndDate.Text).ToShortDateString() + " excluding special holiday is " + days.ToString());

        }
    }
}

VB.Net

Protected Sub Calculate(ByVal sender As Object, ByVal e As EventArgs)
    Dim holidays As List(Of DateTime) = New List(Of DateTime)()
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand("SELECT HoliDayDate FROM HolidayList WHERE HoliDayDate BETWEEN @Date1 AND @Date2", con)
            cmd.Parameters.AddWithValue("@Date1", txtStartDate.Text)
            cmd.Parameters.AddWithValue("@Date2", txtEndDate.Text)
            con.Open()

            Using sdr As SqlDataReader = cmd.ExecuteReader()

                While sdr.Read()
                    holidays.Add(Convert.ToDateTime(sdr(1)))
                End While
            End Using

            con.Close()
            Dim days As Integer = 0
            Dim startDate As DateTime = Convert.ToDateTime(txtStartDate.Text)
            Dim endDate As DateTime = Convert.ToDateTime(txtEndDate.Text)
            Dim date1 As DateTime = startDate

            While date1 <= endDate

                If startDate.DayOfWeek <> DayOfWeek.Saturday AndAlso startDate.DayOfWeek <> DayOfWeek.Sunday AndAlso Not holidays.Contains(date1) Then
                    days += 1
                End If

                startDate = startDate.AddDays(1)
                date1 = date1.AddDays(1)
            End While

            Response.Write("Number of days between " & Convert.ToDateTime(txtStartDate.Text).ToShortDateString() & " and " + Convert.ToDateTime(txtEndDate.Text).ToShortDateString() & " excluding special holiday is " & days.ToString())
        End Using
    End Using
End Sub

Screenshot