Category / Section
How to perform the CRUD operations in SfSchedule using SQL database?
2 mins read
This article explains how to connect the database with SfSchedule control and how to add, update, and delete data in database using SQL server.
Steps:
1. Add a new database connection by clicking the add icon button in Server Explorer, and the Add Connection wizard appears with the default data source as Microsoft SQL Server Database File (SqlClient). In the Data source field, click Change, and the Change Data Source dialog appears; in this dialog, select the type of data source that you need. Microsoft SQL Server (SqlClient) is used here. You can also use MySQL database. Refer to MySQL to connect the MySQL.
2. In the Server name field, click the Refresh button to select a server from the drop-down list. Enable Select or enter a database name radio button to select a database from the drop-down list under the Connect to a database option. Refer to Create a database in SQL server.
3. Click Test Connection to check whether the connection with your database is succeeded. After the connection issucceeded, click OK button to add database in your server explorer.
4. Drag Meeting_Table table into design view of MeetingDB.dbml. The Entity model diagram for meeting table is generated after it has been dropped into design view. You can enter the values in the Meeting_Table using the Show Table Data option. The meeting details are maintained as string in the database.
You can load the database created in previous step into SfSchedule using MeetingsDBDataContext, and it can be mapped to the custom appointments of schedule. Refer to this documentation to know more about schedule and custom appointments and mapping method.
C#
MeetingsDBDataContext = new MeetingsDBDataContext();
var MeetingsList = (from data in MeetingsDBDataContext.Meeting_Tables
select data).ToList();
for (int i = 0; i < MeetingsList.Count; i++)
{
Meetings.Add(new ScheduleMeeting()
{
MappedSubject = MeetingsList[i].Subject,
MappedStartTime = Convert.ToDateTime(MeetingsList[i].StartTime),
MappedEndTime = Convert.ToDateTime(MeetingsList[i].EndTime),
MappedColor = newSolidColorBrush((Color)ColorConverter.ConvertFromString(MeetingsList[i].Color)),
MappedIsAllDay = Convert.ToBoolean(MeetingsList[i].IsAllDay),
AppointmentID = MeetingsList[i].ID.ToString()
});
}
You can also add, update, and delete the data in database using MeetingsDBDataContext and Meeting_Table in AppointmentEditorClosed event, where you can get the edited appointment details.
private void Schedule_AppointmentEditorClosed(object sender, AppointmentEditorClosedEventArgs e)
{
MeetingsDBDataContext scheduleMeetings = new MeetingsDBDataContext();
Meeting_Table meeting;
if (e.Action == EditorClosedAction.Save)
{
var editedAppointment = (e.EditedAppointment as ScheduleMeeting);
var appointmentID = Convert.ToInt32(editedAppointment.AppointmentID);
if (!e.IsNew)
{
meeting = (from data in scheduleMeetings.Meeting_Tables
where data.ID == appointmentID
select data).First() as Meeting_Table;
meeting.Subject = editedAppointment.MappedSubject;
meeting.StartTime = editedAppointment.MappedStartTime.ToString();
meeting.EndTime = editedAppointment.MappedEndTime.ToString();
meeting.Color = editedAppointment.MappedColor.ToString();
meeting.IsAllDay = editedAppointment.MappedIsAllDay.ToString();
meeting.ID = appointmentID;
}
else
{
meeting = new Meeting_Table();
meeting.Subject = editedAppointment.MappedSubject;
meeting.StartTime = editedAppointment.MappedStartTime.ToString();
meeting.EndTime = editedAppointment.MappedEndTime.ToString();
meeting.Color = editedAppointment.MappedColor.ToString();
meeting.IsAllDay = editedAppointment.MappedIsAllDay.ToString();
meeting.ID = appointmentID;
scheduleMeetings.Meeting_Tables.InsertOnSubmit(meeting);
}
scheduleMeetings.SubmitChanges();
}
if (e.Action == EditorClosedAction.Delete)
{
var editedAppointment = (e.OriginalAppointment as ScheduleMeeting);
var appointmentID = Convert.ToInt32(editedAppointment.AppointmentID);
meeting = (from data in scheduleMeetings.Meeting_Tables
where data.ID == appointmentID
select data).First() as Meeting_Table;
scheduleMeetings.Meeting_Tables.DeleteOnSubmit(meeting);
scheduleMeetings.SubmitChanges();
}
}
}
Sample
You can find the sample in the following link: Sample
