Lesson 6 - Bind a Scheduler to MS SQL Server Database (legacy)
- 6 minutes to read
Note
You are viewing documentation for the legacy WPF Scheduler control. If you’re starting a new project, we strongly recommend that you use a new control declared in the DevExpress.Xpf.Scheduling namespace. If you decide to upgrade an existing project in order to switch to the updated scheduler control, see the Migration Guidelines document.
This lesson demonstrates how to create a simple application with a scheduler control using the DevExpress Template Gallery, and bind the scheduler to a Microsoft SQL Server database.
This lesson consists of the following sections.
- Create a Scheduler Application
- Create a Database
- Add a Data Source
- Bind a Scheduler to Data
- Specify Mappings
- Result
Create a Scheduler Application
Click FILE |New | Project… to invoke the New Project dialog. In the invoked dialog, select the DevExpress: Visual C# group, select the DevExpress v18.2 Template Gallery pattern, and click OK.
In the invoked window, select the WPF platform and your preferred language. Then, select the MS Outlook inspired Solution item in the WPF Business Solutions group and click Create Project.
To specify the application theme, click the DXRibbonWindow‘s smart tag and select the preferred theme from the ApplicationTheme drop-down menu.
To set the ribbon style, click the Ribbon control’s smart tag and select the required style from the RibbonStyle drop-down menu.
The result is shown in the image below.
Create a Database
In the Visual Studio menu, click View | Server Explorer to invoke the Server Explorer pane, and click Connect To Database.
In the invoked window, change the data source to Microsoft SQL Server Database File, set the database name to SchedulerTestDataSet and click OK.
In the Server Explorer, right-click the SchedulerTest database icon and select the New Query item in the context menu.
Copy the following code to the New Query window.
CREATE TABLE [dbo].[Appointments] ( [UniqueID] [int] IDENTITY (1, 1) NOT NULL , [Type] [int] NULL , [StartDate] [smalldatetime] NULL , [EndDate] [smalldatetime] NULL , [AllDay] [bit] NULL , [Subject] [nvarchar] (50) NULL , [Location] [nvarchar] (50) NULL , [Description] [nvarchar](max) NULL , [Status] [int] NULL , [Label] [int] NULL , [ResourceID] [int] NULL , [ResourceIDs] [nvarchar](max) NULL , [ReminderInfo] [nvarchar](max) NULL , [RecurrenceInfo] [nvarchar](max) NULL , [CustomField1] [nvarchar](max) NULL CONSTRAINT [PK_Appointments] PRIMARY KEY CLUSTERED ( [UniqueID] ASC ) ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO CREATE TABLE [dbo].[Resources] ( [UniqueID] [int] IDENTITY (1, 1) NOT NULL , [ResourceID] [int] NOT NULL , [ResourceName] [nvarchar] (50) NOT NULL , [Color] [int] NULL , [Image] [image] NULL , [CustomField1] [nvarchar](max) NULL CONSTRAINT [PK_Resources] PRIMARY KEY CLUSTERED ( [UniqueID] ASC ) ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET IDENTITY_INSERT [dbo].[Resources] ON INSERT [dbo].[Resources] ([UniqueID], [ResourceID], [ResourceName]) VALUES (1, 10, N'Resource 1') INSERT [dbo].[Resources] ([UniqueID], [ResourceID], [ResourceName]) VALUES (2, 20, N'Resource 2') INSERT [dbo].[Resources] ([UniqueID], [ResourceID], [ResourceName]) VALUES (3, 30, N'Resource 3') SET IDENTITY_INSERT [dbo].[Resources] OFFClick the Execute button.
The Appointments and Resources tables are created as a result of query execution.
Create a Data Source
Click PROJECT | Add New Data Source… to invoke the Data Source Configuration Wizard.
In the invoked window, select the Database source type, then select the Dataset database model.
Next select the SchedulerTest.mdf data connection from the drop-down menu, click Next and then and click YES to copy the local data file to the project and modify the connection.
In the invoked window, select the Appointments and Resources tables, set the dataset name to SchedulerTest and click Finish.
To save appointments created in the application, select the SchedulerTest.mdf file in the Solution Explorer and set its Copy to output Directory to Copy if newer.
Bind a Scheduler to a Data
In this example, all appointment data is stored in the Appointments data table. This data should be provided to the AppointmentStorage, which is accessed using the SchedulerStorage.AppointmentStorage property. To bind the storage, set the SchedulerControl.Storage.AppointmentStorage.DataSource property to the Appointments data table. Also, in this example, all resources are stored in the Resource data table. This data should be provided to the ResourceStorage, which is accessed using the SchedulerStorage.ResourceStorage property. To bind the storage, set the SchedulerControl.Storage.ResourceStorage.DataSource property to the Resources data table.
You need to populate the underlying AppointmentsDataTable and ResourcesDataTables with data from the database. To do this, use the Fill method of the AppointmentsTableAdapter and ResourcesTableAdapter objects, respectively.
You also need to save changes in the Appointments data table. To do this, handle the SchedulerStorage.AppointmentsChanged, SchedulerStorage.AppointmentsDeleted and SchedulerStorage.AppointmentsInserted events.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;
using DevExpress.Xpf.Core;
using DevExpress.Xpf.Ribbon;
using DevExpress.Xpf.Bars;
using DevExpress.Xpf.Layout.Core;
using DevExpress.Xpf.Docking;
using DevExpress.Xpf.NavBar;
using DevExpress.XtraScheduler;
using System.Data;
using System.Data.SqlClient;
namespace DXApplication1 {
public partial class MainWindow : DXRibbonWindow {
SchedulerTestDataSet dataSet;
SchedulerTestDataSetTableAdapters.AppointmentsTableAdapter adapter;
SchedulerTestDataSetTableAdapters.ResourcesTableAdapter resourcesAdapter;
public MainWindow()
{
InitializeComponent();
this.dataSet = new SchedulerTestDataSet();
// Bind the scheduler storage to appointment data.
this.schedulerControl1.Storage.AppointmentStorage.DataSource = dataSet.Appointments;
// Load data to the 'SchedulerTestDataSet.Appointments' table.
this.adapter = new SchedulerTestDataSetTableAdapters.AppointmentsTableAdapter();
this.adapter.Fill(dataSet.Appointments);
// Bind the scheduler storage to resource data.
this.schedulerControl1.Storage.ResourceStorage.DataSource = dataSet.Resources;
// Load data to the 'ShedulerBindingTestDataSet.Resources' table.
this.resourcesAdapter = new SchedulerTestDataSetTableAdapters.ResourcesTableAdapter();
resourcesAdapter.Fill(dataSet.Resources);
this.schedulerControl1.Storage.AppointmentsInserted +=
new PersistentObjectsEventHandler(Storage_AppointmentsModified);
this.schedulerControl1.Storage.AppointmentsChanged +=
new PersistentObjectsEventHandler(Storage_AppointmentsModified);
this.schedulerControl1.Storage.AppointmentsDeleted +=
new PersistentObjectsEventHandler(Storage_AppointmentsModified);
}
void Storage_AppointmentsModified(object sender, PersistentObjectsEventArgs e)
{
this.adapter.Adapter.Update(this.dataSet);
this.dataSet.AcceptChanges();
}
}
}
Specify Scheduler Mappings
Specify mappings in the XAML code, as shown below.
Note
To associate an appointment with several resources, set the AppointmentStorage.ResourceSharing property to true. Refer to the Resources for Appointments article to learn more about sharing appointment resources.
<dxsch:SchedulerControl x:Name="schedulerControl1"
VerticalAlignment="Stretch"
GroupType="None"
BarManager="{Binding RelativeSource={RelativeSource AncestorType=dxb:BarManager}, Mode=OneTime}">
<dxsch:SchedulerControl.Storage>
<dxsch:SchedulerStorage>
<dxsch:SchedulerStorage.ResourceStorage>
<dxsch:ResourceStorage DataSource="{Binding}">
<dxsch:ResourceStorage.Mappings>
<dxsch:ResourceMapping
Caption="ResourceName"
Color="Color"
Id="ResourceID"
Image="Image" />
</dxsch:ResourceStorage.Mappings>
</dxsch:ResourceStorage>
</dxsch:SchedulerStorage.ResourceStorage>
<dxsch:SchedulerStorage.AppointmentStorage>
<dxsch:AppointmentStorage DataSource="{Binding}" ResourceSharing="True">
<dxsch:AppointmentStorage.Mappings>
<dxsch:AppointmentMapping
AllDay="AllDay"
AppointmentId="UniqueId"
Start="StartDate"
End="EndDate"
Subject="Subject"
Description="Description"
ResourceId="ResourceIDs"
Label="Label"
Location="Location"
RecurrenceInfo="RecurrenceInfo"
ReminderInfo="ReminderInfo"
Status="Status"
Type="Type" />
</dxsch:AppointmentStorage.Mappings>
</dxsch:AppointmentStorage>
</dxsch:SchedulerStorage.AppointmentStorage>
</dxsch:SchedulerStorage>
</dxsch:SchedulerControl.Storage>
</dxsch:SchedulerControl>
Result
The following image illustrates the running application.
