JavaScript Editor jscript editor     Web designer 
↑


Main Page

The Calendar control does not directly support data binding — that is, you do not bind the calendar as a whole to a data source. Instead, you write code to get the data you need, and then in the DayRender event, you can compare the currently rendered date against the data you have read from a data source.

To display database data in the Calendar control

  1. Use ADO.NET types to connect to a database and query for the dates to display.

  2. In the Calendar control's DayRender event, compare the date currently being rendered against the data you have retrieved from the database. If there is a match, customize the day display.

Example

The following example reads holiday information from a database into an ADO.NET dataset. The selection gets dates for the current month, defined as the range based on the Calendar control's VisibleDate property, which returns the first date of the current month. Each time the user navigates to a new month, the code reads the holidays for that month. In the DayRender event, code compares the date currently being rendered against the dates returned from the database. If a date matches, the day is marked with a special color.
Visual BasicВ CopyCode imageCopy Code
Protected dsHolidays As DataSet

Protected Sub Page_Load(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles Me.Load
    If Not IsPostBack Then
        Calendar1.VisibleDate = DateTime.Today
        FillHolidayDataset()
    End If
End Sub

Protected Sub FillHolidayDataset()
    Dim firstDate As New DateTime(Calendar1.VisibleDate.Year, _
         Calendar1.VisibleDate.Month, 1)
    Dim lastDate As DateTime = GetFirstDayOfNextMonth()
    dsHolidays = GetCurrentMonthData(firstDate, lastDate)
End Sub

Protected Function GetFirstDayOfNextMonth() As DateTime
    Dim monthNumber, yearNumber As Integer
    If Calendar1.VisibleDate.Month = 12 Then
        monthNumber = 1
        yearNumber = Calendar1.VisibleDate.Year + 1
    Else
        monthNumber = Calendar1.VisibleDate.Month + 1
        yearNumber = Calendar1.VisibleDate.Year
    End If
    Dim lastDate As New DateTime(yearNumber, monthNumber, 1)
    Return lastDate
End Function

Protected Sub Calendar1_VisibleMonthChanged(ByVal sender As Object, _
        ByVal e As System.Web.UI.WebControls.MonthChangedEventArgs) _
        Handles Calendar1.VisibleMonthChanged
    FillHolidayDataset()
End Sub

Function GetCurrentMonthData(ByVal firstDate As DateTime, _        ByVal lastDate As DateTime) As DataSet
    Dim dsMonth As New DataSet
    Dim cs As ConnectionStringSettings
    cs = ConfigurationManager.ConnectionStrings("ConnectionString1")
    Dim connString As String = cs.ConnectionString
    Dim dbConnection As New SqlConnection(connString)
    Dim query As String
    query = "SELECT HolidayDate FROM Holidays " & _
        " WHERE HolidayDate >= @firstDate AND HolidayDate < @lastDate"
    Dim dbCommand As New SqlCommand(query, dbConnection)
    dbCommand.Parameters.Add(New SqlParameter("@firstDate", firstDate))
    dbCommand.Parameters.Add(New SqlParameter("@lastDate", lastDate))

    Dim sqlDataAdapter As New SqlDataAdapter(dbCommand)
    Try
        sqlDataAdapter.Fill(dsMonth)
    Catch
    End Try
    Return dsMonth
End Function

Protected Sub Calendar1_DayRender(ByVal sender As Object, _
        ByVal e As System.Web.UI.WebControls.DayRenderEventArgs) _
        Handles Calendar1.DayRender
    Dim nextDate As DateTime
    If Not dsHolidays Is Nothing Then
        For Each dr As DataRow In dsHolidays.Tables(0).Rows
            nextDate = CType(dr("HolidayDate"), DateTime)
            If nextDate = e.Day.Date Then
                e.Cell.BackColor = System.Drawing.Color.Pink
            End If
        Next
    End If
End Sub
C#В CopyCode imageCopy Code
protected DataSet dsHolidays;

protected void Page_Load(object sender, EventArgs e)
{
    if(!IsPostBack)
    {
        Calendar1.VisibleDate = DateTime.Today;
        FillHolidayDataset();
    }
}

protected void FillHolidayDataset()
{
    DateTime firstDate = new DateTime(Calendar1.VisibleDate.Year, 
        Calendar1.VisibleDate.Month, 1);
    DateTime lastDate = GetFirstDayOfNextMonth();
    dsHolidays = GetCurrentMonthData(firstDate, lastDate);
}

protected DateTime GetFirstDayOfNextMonth()
{
    int monthNumber, yearNumber;
    if(Calendar1.VisibleDate.Month == 12)
    {
        monthNumber = 1;
        yearNumber = Calendar1.VisibleDate.Year + 1;
    }
    else
    {
        monthNumber = Calendar1.VisibleDate.Month + 1;
        yearNumber = Calendar1.VisibleDate.Year;
    }
    DateTime lastDate = new DateTime(yearNumber, monthNumber, 1);
    return lastDate;
}

protected DataSet GetCurrentMonthData(DateTime firstDate, 
     DateTime lastDate)
{
    DataSet dsMonth = new DataSet();
    ConnectionStringSettings cs;
    cs = ConfigurationManager.ConnectionStrings["ConnectionString1"];
    String connString = cs.ConnectionString;
    SqlConnection dbConnection = new SqlConnection(connString);
    String query;
    query = "SELECT HolidayDate FROM Holidays " + _
        " WHERE HolidayDate >= @firstDate AND HolidayDate < @lastDate";
    SqlCommand dbCommand = new SqlCommand(query, dbConnection);
    dbCommand.Parameters.Add(new SqlParameter("@firstDate", 
        firstDate));
    dbCommand.Parameters.Add(new SqlParameter("@lastDate", lastDate));

    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(dbCommand);
    try
    {
        sqlDataAdapter.Fill(dsMonth);
    }
    catch {}
    return dsMonth;
}

protected void Calendar1_DayRender(object sender, DayRenderEventArgs e)
{
    DateTime nextDate;
    if(dsHolidays != null)
    {
     foreach(DataRow dr in dsHolidays.Tables[0].Rows)
     {
            nextDate = (DateTime) dr["HolidayDate"];
            if(nextDate == e.Day.Date)
            {
                e.Cell.BackColor = System.Drawing.Color.Pink;
            }
     }
    }
}
protected void Calendar1_VisibleMonthChanged(object sender, 
    MonthChangedEventArgs e)
{
    FillHolidayDataset();
}

This example builds a query based on the dates in the currently displayed month. The VisibleDate property returns the first date of the current month. (The VisibleDate property is not set until the user has navigated in the calendar, so the first time the page is displayed, the code sets the VisibleDate property manually.) A helper function in the code calculates the first day of the next month based on the VisibleDate property, and can therefore be used to build a date range within the current month.

Compiling the Code

The code assumes that you are using a SQL Server database containing the table Holidays. The table has the column HolidayDate. The connection string required to connect to the database is stored in the Web.config file under the name ConnectionString1.

The code assumes that you have imported the namespaces System.Data and System.Data.SqlClient so that references to DataSet, SqlConnection, and other objects can be used without being fully qualified.

Robust Programming

When querying the database, you should always enclose the execution of the query (in this example, when calling the data adapter's Fill method) in a try-catch block.

See Also



JavaScript Editor jscript editor     Web designer 
→