DayPilot Knowledge Base

AJAX Calendar/Scheduling Controls
DayPilot Pro (AJAX Calendar Control)
» DayPilot AJAX Calendar
DayPilot Pro (AJAX Monthly Calendar Control)
» DayPilot AJAX Monthly Calendar
DayPilot Pro (AJAX Scheduler Control)
» DayPilot AJAX Scheduler
DayPilot » Knowledge Base » How to load Scheduler resource tree from a database (SQL Server)

How to load Scheduler resource tree from a database (SQL Server)

Last revision: Jun 13, 2012

asp-net-ajax-scheduler-resources.png

Let's have the following SQL Server table (dbo.resource):

scheduler-resource-tree-sql-server-table.png

The parent_id column points to the parent resource (id column of the same table).

scheduler-resource-tree-sql-server-table-data.png

The resource tree can be loaded using the following code:

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

        private void loadResources()
        {
            DayPilotScheduler1.Resources.Clear();
            foreach (DataRow dr in loadRootResources().Rows)
            {
                int id = (int)dr["id"];
                string name = (string)dr["name"];
                Resource r = new Resource(name, id.ToString());
                r.Expanded = true;
                DayPilotScheduler1.Resources.Add(r);
                addChildren(r);
            }
        }

        private void addChildren(Resource parent)
        {
            foreach (DataRow dr in loadChildResources(parent.Value).Rows)
            {
                int id = (int)dr["id"];
                string name = (string)dr["name"];
                Resource r = new Resource(name, id.ToString());
                r.Expanded = true;
                parent.Children.Add(r);
                addChildren(r);
            }
        }

        private DataTable loadChildResources(string parentId)
        {
            SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM [resource] WHERE [parent_id] = @parent", ConfigurationManager.ConnectionStrings["daypilot"].ConnectionString);
            da.SelectCommand.Parameters.AddWithValue("parent", parentId);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        private DataTable loadRootResources()
        {
            SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM [resource] WHERE [parent_id] is null", ConfigurationManager.ConnectionStrings["daypilot"].ConnectionString);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

Notes

Related

How to set the time cell colors using database data in the Scheduler
How to show one month per cell in Scheduler (CellDuration = Month)
How to save the exported PNG image to a file (without a web page)
How to implement Copy & Paste in the Calendar