How to perform the CRUD operations in WPF Scheduler (Calendar) using PostgreSQL database
In the WPF scheduler, perform the CRUD operation using SQL local data base in the Scheduler by using OnAppointmentEditorClosing event.
In order to perform the CRUD operations using PostgreSQL local data base connect the pgAdmin server and create the database to connect (using connection string) into the WPF scheduler.
STEP 1: To perform CRUD operations using PostgreSQL local data base, install Postgre SQL exe from the following link as prerequisites.
https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
Configure the pgAdmin settings to connect the server and create the database.
Connect PostgreSQL
https://docs.microsoft.com/en-us/azure/postgresql/connect-csharp#step-1-connect-and-insert-data
STEP 2: Create a local database and add the required code snippets for DB connections.
public static string connectionString = @"Server=localhost;Port=5432;User id=postgres;Password=postgres;Database=SchedulerDB";
public static string ConnectionString
{
get
{
return connectionString;
}
}
public static NpgsqlConnection GetDBConnection()
{
NpgsqlConnection connection = new NpgsqlConnection(ConnectionString);
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
return connection;
}
STEP 3: Create a table in the database and add the required fields as shown in the below code snippet.
|
STEP 4: Get the data from the table and populate the Meetings collection in SchedulerViewModel.
class SchedulerViewModel
{
public List<Meetings> meetings { get; set; }
public SchedulerViewModel()
{
try
{
var dataTable = ConnectPSQL.GetDataTable("select * from Meetings");
meetings = new List<Meetings>();
meetings = (from DataRow dr in dataTable.Rows
select new Meetings()
{
Subject = dr["Subject"].ToString(),
StartTime = dr["StartTime"] as DateTime? ?? DateTime.Now,
EndTime = dr["EndTime"] as DateTime? ?? DateTime.Now
}).ToList();
}
catch
{
// Handle exceptions
}
}
}
STEP 6: Bind the Meetings collection to the SchedulerViewModel as the DataContext.
<Window.DataContext>
<local:SchedulerViewModel/>
</Window.DataContext>
<Grid>
<scheduler:SfScheduler x:Name="scheduler" ViewType="Week" ItemsSource="{Binding meetings}">
<scheduler:SfScheduler.AppointmentMapping>
<scheduler:AppointmentMapping Subject="Subject"
StartTime="StartTime"
EndTime="EndTime"/>
</scheduler:SfScheduler.AppointmentMapping>
<interactivity:Interaction.Behaviors>
<local:Behavior/>
</interactivity:Interaction.Behaviors>
</scheduler:SfScheduler>
</Grid>
STEP 6: Using OnAppointmentEditorClosing event and SQL query update the DB for the required actions.
private void AssociatedObject_AppointmentEditorClosing(object sender, AppointmentEditorClosingEventArgs e)
{
if (e.Action == AppointmentEditorAction.Add)
{
string psqlAdd = "insert into Meetings (Subject,StartTime,EndTime)values('" + e.Appointment.Subject + "', '" + e.Appointment.StartTime.ToString("yyyy-MM-dd HH:mm:ss") + "' , '" + e.Appointment.EndTime.ToString("yyyy-MM-dd HH:mm:ss") + "')";
ConnectPSQL.ExecutePSQLQuery(psqlAdd);
}
else if (e.Action == AppointmentEditorAction.Delete)
{
string psqlDelete = "delete from Meetings where Subject ='" + e.Appointment.Subject + "';";
ConnectPSQL.ExecutePSQLQuery(psqlDelete);
}
else if (e.Action == AppointmentEditorAction.Edit)
{
string psqlUpdate = "update Meetings set StartTime='" + e.Appointment.StartTime + "',EndTime='" + e.Appointment.EndTime + "' where Subject='" + e.Appointment.Subject + "';";
ConnectPSQL.ExecutePSQLQuery(psqlUpdate);
}
}
