Export (0) Print
Expand All

How to: Display Selected Dates from a Database in the Calendar Control 

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.

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

Community Additions

ADD
Show:
© 2014 Microsoft