CRUD operations in ComponentOne Scheduler

C1Scheduler supports flexible data bindings with different types of database vendors like, MsAccess, SqlServer, Oracle Db, etc. We can use different types of binding techniques like: 1. Data binding using ADO.NET objects, 2. Data binding through collections, 3. Data binding using lists, 4. Data binding using built-in XML data. To easily save, load, modify or delete appointments, categories, contacts, etc. With this blog post, we will discuss on how to perform CRUD operations in a bound C1Scheduler for WPF. C1Scheduler has separate DataStorage, which is a collection different-different EntityStorage like, AppointmentStorage, ContactStorage, LabelStorage, CategoryStorage, OwnerStorage, ResourceStorage, and StatusStorage. These storages are the collection of all the Mappings, DataSource, etc. Let’s walkthrough with a simple example, starting with a basic UI that contains a C1Scheduler for WPF, which shows the implementation of how to perform CRUD operations with a bounded scheduler on Appointments. Note: Before proceeding to how to bind a C1Scheduler with a database, keep the following points in mind: >>>> Design a database structure as is given here Now, our first aim is to initializing the objects, which requires an OleDbConnection, OleDbDataAdapter, OleDbCommand and a DataSet object, if we are using a MS Access DB. Here is the code snippet :

Initializing Objects

//declare ado objects to be used for binding  
OleDbConnection conn;  
OleDbDataAdapter adapter;  
OleDbCommand cmd;  
DataSet ds;  

public MainWindow()  
{  
  InitializeComponent();  
  //initialize ado objects  
  conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\\\..\\\Nwind.mdb");  
  adapter = new OleDbDataAdapter("select * from Appointments", conn);  
  ds = new DataSet();  
}  

After initialization, our first objective is to load and display the data from back end to front end. This is can be done by using MappingCollection provided by C1Scheduler through which we can map various properties of an Appointments, Contacts, Resources, Categories, Labels. The following code describe how to map various data entities with Appointment properties:

Loading the data

private void Grid_Loaded(object sender, RoutedEventArgs e)  
{  
  //populate the dataset with the selected records  
  adapter.Fill(ds, "Appointments");  

  //map the datafields for the Appointment entity  
  this.c1Scheduler.DataStorage.AppointmentStorage.Mappings.Body.MappingName = "Description";  
  this.c1Scheduler.DataStorage.AppointmentStorage.Mappings.Subject.MappingName = "Subject";  
  this.c1Scheduler.DataStorage.AppointmentStorage.Mappings.Location.MappingName = "Location";  
  this.c1Scheduler.DataStorage.AppointmentStorage.Mappings.Start.MappingName = "Start";  
  this.c1Scheduler.DataStorage.AppointmentStorage.Mappings.End.MappingName = "EndTime";  
  this.c1Scheduler.DataStorage.AppointmentStorage.Mappings.AppointmentProperties.MappingName = "Properties";  
  this.c1Scheduler.DataStorage.AppointmentStorage.Mappings.IdMapping.MappingName = "AppointmentId";  
  this.c1Scheduler.DataStorage.AppointmentStorage.DataSource = ds.Tables[0].DefaultView;  
}  

To create or add appointment into the database, the most appropriate event is C1Scheduler.AppointmentAdded which is raised just after an adding of a new appointment :

Adding Appointments

private void c1Scheduler_AppointmentAdded(object sender, C1.WPF.Schedule.AppointmentActionEventArgs e)  
{  
  object[] o = app.Key;  
  Guid g = (Guid)o[0];  
  string query = "Insert Into Appointments values(@AppointmentId, @Description, @EndTime, @Location, @Start, @Subject, @Properties);";  
  cmd = new OleDbCommand(query, conn);  
  cmd.Parameters.AddWithValue("@AppointmentId", g);  
  cmd.Parameters.AddWithValue("@Description", e.Appointment.Body);  
  cmd.Parameters.AddWithValue("@EndTime", e.Appointment.End);  
  cmd.Parameters.AddWithValue("@Location", e.Appointment.Location);  
  cmd.Parameters.AddWithValue("@Start", e.Appointment.Start);  
  cmd.Parameters.AddWithValue("@Subject", e.Appointment.Subject);  
  cmd.Parameters.AddWithValue("@Properties", e.Appointment.GetAppointmentProperties());  
  conn.Open();  
  cmd.ExecuteNonQuery();  
  conn.Close();  
  MessageBox.Show("Appointment added successfully!");  
}  

To update database after modifying an appointment, the most appropriate event is C1Scheduler.AppointmentChanged which occurs every time if any modification is made into an appointment :

Modifying Appointments

private void c1Scheduler_AppointmentChanged(object sender, C1.WPF.Schedule.AppointmentActionEventArgs e)  
{  
  object[] o = e.Appointment.Key;  
  Guid g = (Guid)o[0];  
  DateTime s = e.Appointment.Start;  
  cmd = new OleDbCommand("UPDATE Appointments SET Description = @Description, EndTime = @EndTime, Start = @Start, Location = @Location, Subject = @Subject, Properties = @Properties WHERE AppointmentId = @original_AppointmentId", conn);  
  cmd.Parameters.AddWithValue("@Description", e.Appointment.Body);  
  cmd.Parameters.AddWithValue("@End", e.Appointment.End);  
  cmd.Parameters.AddWithValue("@Start", e.Appointment.Start);  
  cmd.Parameters.AddWithValue("@Location", e.Appointment.Location);  
  cmd.Parameters.AddWithValue("@Subject", e.Appointment.Subject);  
  cmd.Parameters.AddWithValue("@Properties", e.Appointment.GetAppointmentProperties());  
  cmd.Parameters.AddWithValue("@AppointmentId", g);  
  conn.Open();  
  cmd.ExecuteNonQuery();  
  conn.Close();  
  MessageBox.Show("Appointment Updated!");  
}  

To delete the appointment record from database after deleting an appointment from C1Scheduler, use its AppointmentDeleted event as :

Deleting Appointments

private void c1Scheduler_AppointmentDeleted(object sender, C1.WPF.Schedule.AppointmentActionEventArgs e)  
{  
  object[] o = e.Appointment.Key;  
  Guid g = (Guid)o[0];  
  string query = "DELETE FROM Appointments WHERE AppointmentId = @AppointmentId ";  
  cmd = new OleDbCommand(query, conn);  
  cmd.Parameters.AddWithValue("@AppointmentId", g);  
  conn.Open();  
  cmd.ExecuteNonQuery();  
  conn.Close();  
  MessageBox.Show("Appointment Deleted!");  
}  

A complete sample which showcases this implementation could be downloaded by clicking the below image :