Lesson 5 - Bind a Scheduler to MS SQL Database at Design Time
- 7 minutes to read
This document describes how to bind a SchedulerControl to a sample MS SQL Server database at design time.
To bind a SchedulerControl to a BindingSource, follow the instructions below.
- Create a New Project
- Create a Database
- Connect to a Database
- Bind Data to the Scheduler
- Specify Initial Settings
- Post Data Back to the Database
- Result
Create a New Project
- Create a new Windows Forms Application project in Visual Studio.
Drop the SchedulerControl item from the DX.17.2: Scheduling toolbox tab onto the form.
Click the smart tag icon at the top right of the control (
) to display its actions list. Select Dock in Parent Container. This will stretch the SchedulerControl to fill the entire form.
Create a Database
In the Tools menu of the Visual Studio IDE select Connect to Database…. The Add Connection dialog is invoked.
Type in a name of the new database file - SchedulerTest, as illustrated in the picture above. Click OK to create a new database and a connection.
In the Server Explorer window right-click the newly created data connection and select New Query to open a new query window.
Switch to the form with the SchedulerControl, select the Scheduler Control, and click the smart tag icon at the top right of the control (
) to display its actions list. Click the Create Sample Database item to invoke a window that contains SQL script. Click the Copy to Clipboard and Close button.
Switch to the SQLQuery window and paste the script in a window. Execute the query.
The query will create tables for Appointments and Resources within the SchedulerTest database.
Connect to a Database
Switch to the Visual Studio project, select the Scheduler Control, and click the smart tag icon to display its actions list. Click the Appointments Data Source drop-down and select the Add Project Data Source… link to invoke the Data Source Configuration Wizard.
- Select the Database icon and click Next.
- On the following page, select the Dataset database model and click Next.
- Select a connection to the SchedulerTest database created in step 7 and click Next. You are prompted to copy the data file to your project, click Yes.
- In the next page, click Next to save the connection as SchedulerTestConnectionString.
When prompted to choose your database objects, select the Appointments and Resources tables.
- Click Finish.
You have copied the data file SchedulerTest.mdf to the project folder. Specify that it should be copied to the output directory only if the source file is newer than the working database, otherwise your data will be overwritten when the project runs next time. For this, change its property as illustrated in the picture below.
Bind a Scheduler to Data
In the SchedulerControl’s action list (invoked by the smart tag), click the Appointments Data Source drop-down and select the Appointments table.
As a result, Visual Studio will generate a set of classes and components. The following three components will appear in the tray: schedulerTestDataSet, appointmentsBindingSource and appointmentsTableAdapter.
Click Mappings Wizard… in the smart tag panel to invoke the Setup Appointment Storage window. Click the Generate button. All required mappings are generated automatically. Check to see if they are correct and click Finish.
To set up mappings later, in the SchedulerControl’s actions list (invoked by the smart tag), click the Mappings Wizard… link in the Appointments section.
Tip
Review the Mappings topic for more information on the mapping concept.
In the SchedulerControl’s actions list (invoked by the smart tag), click the Resources Data Source drop-down and select the Resources table.
As a result, two new components will appear in the tray - resourcesBindingSource and resourcesTableAdapter.
Click Mappings Wizard… in the smart tag panel to invoke the Setup Resource Storage window. Click the Generate button. All required mappings are generated automatically. Set the ParentId mapping to none since it is required for the Gantt View only. Check to see if mappings are correct and click Finish.
- Note that after the scheduler storage has been bound to data, the scheduler control will not automatically show its data at design time. Also, if the project is run at this stage, the scheduler control will also be empty, as the underlying schedulerTestDataSet does not contain data. The code used to populate table adapters at runtime is automatically added to the form’s Load event.
Specify Initial Settings
Change the Start Date
By default, the start date of the scheduler is set to the system date when the scheduler was created. To set another start date, change the SchedulerControl.Start property value.
Change the Active View
Initially, the SchedulerControl displays its data using the Day View. You can switch the scheduler to another view by change the SchedulerControl.ActiveViewType property value.
Set the Top Row Time
Set the time of the topmost row which is currently shown in the Day view using the DayView.TopRowTime property.
Group Appointments by Resources
To group scheduler data by resource, set the SchedulerControl.GroupType property value to SchedulerGroupType.Resource.
Set the Number of Displayed Resources
Use the SchedulerViewBase.ResourcesPerPage property to specify the number of resources shown on the screen at a time.
Display Time Indicator Above Appointments
To display the Time Indicator over appointments in the view, set the TimeIndicatorDisplayOptions.ShowOverAppointment property to true.
Post Data Back to the Database
You can run the project and start editing data. The changes will be saved in the underlying DataTable objects, but they will not be saved in the database. To complete the example, write the code that posts the changes in the schedulerTestDataSet back to the database. Handle the SchedulerStorageBase.AppointmentsChanged, SchedulerStorageBase.AppointmentsDeleted and SchedulerStorageBase.AppointmentsInserted events, and include the code that saves changes into the event hander.
Result
The code written by following this step-by-step guide is shown below.
Note
A complete sample project is available at https://github.com/DevExpress-Examples/lesson-5-bind-a-scheduler-to-ms-sql-database-at-design-time-t341685
using DevExpress.XtraScheduler;
using System;
using System.Windows.Forms;
namespace SchedulerDbExample {
public partial class Form1 : Form {
public Form1() {
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e) {
// TODO: This line of code loads data into the 'schedulerTestDataSet.Resources' table. You can move, or remove it, as needed.
this.resourcesTableAdapter.Fill(this.schedulerTestDataSet.Resources);
// TODO: This line of code loads data into the 'schedulerTestDataSet.Appointments' table. You can move, or remove it, as needed.
this.appointmentsTableAdapter.Fill(this.schedulerTestDataSet.Appointments);
schedulerControl1.Start = DateTime.Today;
schedulerControl1.ActiveViewType = DevExpress.XtraScheduler.SchedulerViewType.Day;
schedulerControl1.DayView.TopRowTime = new TimeSpan(10, 0, 0);
schedulerControl1.GroupType = DevExpress.XtraScheduler.SchedulerGroupType.Resource;
schedulerControl1.DayView.ResourcesPerPage = 2;
schedulerControl1.DayView.TimeIndicatorDisplayOptions.ShowOverAppointment = true;
this.schedulerStorage1.AppointmentsChanged += OnAppointmentChangedInsertedDeleted;
this.schedulerStorage1.AppointmentsInserted += OnAppointmentChangedInsertedDeleted;
this.schedulerStorage1.AppointmentsDeleted += OnAppointmentChangedInsertedDeleted;
}
private void OnAppointmentChangedInsertedDeleted(object sender, PersistentObjectsEventArgs e) {
appointmentsTableAdapter.Update(schedulerTestDataSet);
schedulerTestDataSet.AcceptChanges();
}
}
}
Run the project. The following image shows the running application.
