April 6, 2009

This tutorial shows how to use DayPilot Scheduler in a web application. It includes both C# and VB.NET version.

It is a minimalistic version:

• One web page
• Year view, 1 cell = 1 day
• Connected to an SQLite database
• Resources loaded from a database
• Event moving enabled (drag&drop)
• Single user

Download

• Tutorial source code (TutorialDayPilotScheduler.zip, 1.16 MB) - ASP.NET Project (C# and VB.NET)

Installation

[Optional] You can add the DayPilot controls to the Visual Studio Toolbox:

• Toolbox Installation

Web Site

1. Create a new web site in Visual Studio or Visual Web Developer. Version 2005 or higher is required.
2. Create a Bin directory in the web site and copy DayPilot.dll there.

Web Form (Page)

1. Add a new page to the site (Default.aspx).

Adding the Scheduler Control

Add the following line to Default.aspx, right below <%@ Page ... %> line:

<%@ Register Assembly="DayPilot" Namespace="DayPilot.Web.Ui" TagPrefix="DayPilot" %>

Copy the following snippet to the page (inside <form> element):

C#

    <DayPilot:DayPilotScheduler 
        ID="DayPilotScheduler1" 
        runat="server" 
        
        DataStartField="eventstart" 
        DataEndField="eventend" 
        DataTextField="name" 
        DataValueField="id" 
        DataResourceField="resource_id" 
        
        CellGroupBy="Month"
        CellDuration="1440"
        StartDate="2009-01-01"
        Days="365"
        
        Width="100%"
        EventHeight="20"
        HeaderFontSize="8pt"
        EventFontSize="8pt"

        EventMoveHandling="CallBack" 
        OnEventMove="DayPilotScheduler1_EventMove" 
        >
    </DayPilot:DayPilotScheduler>

VB.NET

    <DayPilot:DayPilotScheduler 
        ID="DayPilotScheduler1" 
        runat="server" 
        
        DataStartField="eventstart" 
        DataEndField="eventend" 
        DataTextField="name" 
        DataValueField="id" 
        DataResourceField="resource_id" 
        
        CellGroupBy="Month"
        CellDuration="1440"
        StartDate="2009-01-01"
        Days="365"
        
        Width="100%"
        EventHeight="20"
        HeaderFontSize="8pt"
        EventFontSize="8pt"

        EventMoveHandling="CallBack" 
        >
    </DayPilot:DayPilotScheduler>

The following attributes are required by ASP.NET:

• id
• runat

The following attributes are required by DayPilot:

• DataStartField - specifies the data source column that contains event start (DateTime)
• DataEndField - specifies the data source column that contains event end (DateTime)
• DataTextField - specifies the data soruce column that contains event text (string)
• DataValueField - specifies the data source column that contains event id (string or integer)
• DataResourceField - specifies the data soruce column that contains event resource foreign key (string)

The following attributes specify the time scale:

• CellGroupBy - unit of the first column header row ( Month | Week | Day | Hour | None )
• CellDuration - duration of the time cell in minutes (1440 = one day)
• StartDate - first visible day
• Days - specifies the number of visible days

Basic styling:

• Width - control width in either pixel or percent (CSS format)
• EventHeight - event height in pixels (int)
• HeaderFontSize - font size of the header text (CSS format)
• EventFontSize - font size of the event text (CSS format)

Event handling attributes:

• EventMoveHandling - enables event moving (the action will be handled by an AJAX callback)

For C# we also define a EventMove event handler here (in VB.NET it's defined using Handles clause in the Default.aspx.vb):

• OnEventMove

Adding the Functionality

The code behind file (Default.aspx.cs or Default.aspx.vb) will contain two event handlers:

1. Page_Load
2. DayPilotScheduler1_EventMove

Page_Load

In Page_Load, we will do the following:

1. Load the resources (vertical axis) from the database.
2. Load the events from the database.
3. Set the initial scrollbar position to May 1, 2009.

C#

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            loadResources();
 

            DayPilotScheduler1.DataSource = dbGetEvents(DayPilotScheduler1.StartDate, DayPilotScheduler1.Days);
            DayPilotScheduler1.DataBind(); 
 

            DateTime mayFirst = new DateTime(2009, 5, 1);
            DayPilotScheduler1.SetScrollX(mayFirst);
        }
    }

VB.NET

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If (Not IsPostBack) Then
            loadResources()
 

            DayPilotScheduler1.DataSource = dbGetEvents(DayPilotScheduler1.StartDate, DayPilotScheduler1.Days)
            DayPilotScheduler1.DataBind()
 

            Dim mayFirst As New DateTime(2009, 5, 1)
            DayPilotScheduler1.SetScrollX(mayFirst)
        End If
    End Sub

Loading the resources

In the Page_Load method, we have called loadResources() method that fills the Resources collection from the database:

C#

    private void loadResources()
    {
        SQLiteDataAdapter da = new SQLiteDataAdapter("SELECT [id], [name] FROM [resource]", ConfigurationManager.ConnectionStrings["db"].ConnectionString);
        DataTable dt = new DataTable();
        da.Fill(dt);
 
        foreach (DataRow r in dt.Rows)
        {
            string name = (string)r["name"];
            string id = (string)r["id"];
 
            DayPilotScheduler1.Resources.Add(name, id);
        }
    }

VB.NET

    Private Sub loadResources()
        Dim da As New Data.SQLite.SQLiteDataAdapter("SELECT [id], [name] FROM [resource]", ConfigurationManager.ConnectionStrings("db").ConnectionString)
        Dim dt As New DataTable()
        da.Fill(dt)
 
        For Each r As DataRow In dt.Rows
            Dim name As String = DirectCast(r("name"), String)
            Dim id As String = DirectCast(r("id"), String)
 
            DayPilotScheduler1.Resources.Add(name, id)
        Next
    End Sub

DayPilotCalendar1_EventMove

We will update the DB to actually move an event and send an updated event set back to the client.

C#

    protected void DayPilotScheduler1_EventMove(object sender, DayPilot.Web.Ui.Events.EventMoveEventArgs e)
    {
        string id = e.Value;
        DateTime start = e.NewStart;
        DateTime end = e.NewEnd;
        string resource = e.NewResource;
 
        dbUpdateEvent(id, start, end, resource);
 
        DayPilotScheduler1.DataSource = dbGetEvents(DayPilotScheduler1.StartDate, DayPilotScheduler1.Days);
        DayPilotScheduler1.DataBind();
        DayPilotScheduler1.Update();
    }

VB.NET

    Protected Sub DayPilotScheduler1_EventMove(ByVal sender As Object, ByVal e As DayPilot.Web.Ui.Events.EventMoveEventArgs) Handles DayPilotScheduler1.EventMove
 
        Dim id As String = e.Value
        Dim start As DateTime = e.NewStart
        Dim [end] As DateTime = e.NewEnd
        Dim resource As String = e.NewResource
 
        dbUpdateEvent(id, start, [end], resource)
 
        DayPilotScheduler1.DataSource = dbGetEvents(DayPilotScheduler1.StartDate, DayPilotScheduler1.Days)
        DayPilotScheduler1.DataBind()
        DayPilotScheduler1.Update()
    End Sub

Database

In this tutorial, we are using SQLite embedded database and SQLite ADO.NET Provider to limit the external dependencies (both the ADO.NET driver and the database engine are in a single file - System.Data.SQLite.DLL - in Bin directory).

The database file (daypilot.sqlite) can be found in App_Data directory. The database structure can be examined using SQLite Administrator.

The database contains a two tables with the following structure:

CREATE TABLE event (
  id VARCHAR(50),
  name VARCHAR(50),
  eventstart DATETIME,
  eventend DATETIME,
  resource_id VARCHAR(50)
);

 
CREATE TABLE resource (
  id VARCHAR(50),
  name VARCHAR(200)
);

Loading events

C#

    private DataTable dbGetEvents(DateTime start, int days)
    {
        SQLiteDataAdapter da = new SQLiteDataAdapter("SELECT [id], [name], [eventstart], [eventend], [resource_id] FROM [event] WHERE NOT (([eventend] <= @start) OR ([eventstart] >= @end))", ConfigurationManager.ConnectionStrings["db"].ConnectionString);
        da.SelectCommand.Parameters.AddWithValue("start", start);
        da.SelectCommand.Parameters.AddWithValue("end", start.AddDays(days));
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

VB.NET

    Private Function dbGetEvents(ByVal start As DateTime, ByVal days As Integer) As DataTable
        Dim da As New Data.SQLite.SQLiteDataAdapter("SELECT [id], [name], [eventstart], [eventend], [resource_id] FROM [event] WHERE NOT (([eventend] <= @start) OR ([eventstart] >= @end))", ConfigurationManager.ConnectionStrings("db").ConnectionString)
        da.SelectCommand.Parameters.AddWithValue("start", start)
        da.SelectCommand.Parameters.AddWithValue("end", start.AddDays(days))
        Dim dt As New DataTable()
        da.Fill(dt)
        Return dt
    End Function

Updating an event

C#

    private void dbUpdateEvent(string id, DateTime start, DateTime end, string resource)
    {
        using (SQLiteConnection con = new SQLiteConnection(ConfigurationManager.ConnectionStrings["db"].ConnectionString))
        {
            con.Open();
            SQLiteCommand cmd = new SQLiteCommand("UPDATE [event] SET eventstart = @start, eventend = @end, resource_id = @resource WHERE id = @id", con);
            cmd.Parameters.AddWithValue("id", id);
            cmd.Parameters.AddWithValue("start", start);
            cmd.Parameters.AddWithValue("end", end);
            cmd.Parameters.AddWithValue("resource", resource);
            cmd.ExecuteNonQuery();
        }
    }

VB.NET

    Private Sub dbUpdateEvent(ByVal id As String, ByVal start As DateTime, ByVal [end] As DateTime, ByVal resource As String)
        Using con As New Data.SQLite.SQLiteConnection(ConfigurationManager.ConnectionStrings("db").ConnectionString)
            con.Open()
            Dim cmd As New Data.SQLite.SQLiteCommand("UPDATE [event] SET eventstart = @start, eventend = @end, resource_id = @resource WHERE id = @id", con)
            cmd.Parameters.AddWithValue("id", id)
            cmd.Parameters.AddWithValue("start", start)
            cmd.Parameters.AddWithValue("end", [end])
            cmd.Parameters.AddWithValue("resource", resource)
            cmd.ExecuteNonQuery()
        End Using
    End Sub

Main menu

• Home
• Open-Source
• Features
• Try
• Buy
• Documentation
• Tutorials
  • Database & Event Moving (Calendar)
  • Integration with Navigator (Calendar)
  • Database & Event Moving (Scheduler)
  • SQL Server, UpdatePanel, ModalPopupExtender (Calendar)
  • Hotel Room Booking (Scheduler)
  • Recurrent Events (Calendar)
  • SQL Server, UpdatePanel, ModalPopupExtender (Month)
• Compatibility
• Roadmap
• History
• Support
• Contact