Blazor Scheduler Server-side CRUD
Setup The Blazor Application
Follow the Getting Started guide to set up your Blazor Application with Smart UI.
Bind to SQL
Follow our Binding to SQL
guide to set up the connection between your database and Smart UI.
At the end of the tutorial, the Smart.Scheduler will be bounded to a SQL DataBase:
![Basic Scheduler](sql-scheduler.png)
Create Methods
To enable adding new events to the Scheduler, we must first create the functions for the CRUD operations in the
EventsData class.
Navigate to EventsData.cs and implement the additional methods:
public Task<List<EventModel>> InsertEvent(string Label, string Description, DateTime DateStart, DateTime DateEnd) { string sql = "INSERT INTO [dbo].[SchedulerTable] ([Label], [Description], [DateStart], [DateEnd]) OUTPUT INSERTED.Id, INSERTED.Label, INSERTED.Description, INSERTED.DateStart, INSERTED.DateEnd VALUES (@Label, @Description, @DateStart, @DateEnd)"; return _db.LoadData<EventModel, dynamic>(sql, new { Label, Description, DateStart, DateEnd }); } public Task<List<EventModel>> DeleteEvent(int Id) { string sql = "DELETE FROM [dbo].[SchedulerTable] WHERE [Id]=@Id"; return _db.LoadData<EventModel, dynamic>(sql, new { Id }); } public Task<List<EventModel>> UpdateEvent(int Id, string Label, string Description, DateTime DateStart, DateTime DateEnd) { string sql = "UPDATE [dbo].[SchedulerTable] SET [Label] = @Label, [Description] = @Description, [DateStart] = @DateStart, [DateEnd] = @DateEnd WHERE [Id] = @Id"; return _db.LoadData<EventModel, dynamic>(sql, new { Label, Description, DateStart, DateEnd, Id }); }
Then, add the new methods to the IEventsData interface:
Task<List<EventModel>> GetEvents(); Task<List<EventModel>> DeleteEvent(int Id); Task<List<EventModel>> InsertEvent(string Label, string Description, DateTime DateStart, DateTime DateEnd); Task<List<EventModel>> UpdateEvent(int Id, string Label, string Description, DateTime DateStart, DateTime DateEnd);
Add Create functionality
Navigate to the Index.razor
page and create a "Add new event" Button.
Then create an AddEvent
function that creates a new Event and then fetches the updated SQL Table:
<Button OnClick="AddEvent">Add new event</Button> ..... @code{ ..... private async void AddEvent() { var dateStart = new DateTime(DateTime.Now.Year, 7, 31, 15, 0, 0); var dateEnd = new DateTime(DateTime.Now.Year, 7, 31, 17, 30, 0); EventModel newEvent = (await _db.InsertEvent("Update UI", "Improve website's design and accessability", dateStart, dateEnd))[0]; var events = await _db.GetEvents(); dataSource = events.Select(ConvertEventModelToSchedulerDataSource).ToList(); } }
![Scheduler with new event](scheduler-add-event.png)
The new Event is created in the SQL Table:
![SQL with new event](sql-new-event.png)
Add Delete functionality
Bind the "OnItemRemove" Scheduler Event to keep track of deleted items. The OnItemRemove Event will contain the id of the deleted item, so that you can update the SQL Table
@using Newtonsoft.Json.Linq <Scheduler DataSource="@dataSource" HourStart="@hourStart" OnItemRemove="OnItemRemove"></Scheduler> @code{ ..... private async void OnItemRemove(Event ev) { SchedulerItemRemoveEventDetail detail = ev["Detail"]; JObject removedItem = detail.Item; string id = removedItem.Root["id"].ToString(); await _db.DeleteEvent(int.Parse(id)); } }
The "New Brochures" Event is removed from the SQL Table after clicking the delete action:
![Scheduler with removed event](./scheduler-delete-task.png)
![SQL with removed event](./sql-deleted-task.png)
Add Update functionality
We will use the OnItemUpdate
Event to update the SQL Table each time a event has been modified:
@using Newtonsoft.Json.Linq <Scheduler DataSource="@dataSource" HourStart="@hourStart" OnItemRemove="OnItemRemove" OnItemUpdate="OnItemUpdate"></Scheduler> private async void OnItemUpdate(Event ev) { SchedulerItemUpdateEventDetail detail = ev["Detail"]; JObject updatedItem = detail.Item; int id = updatedItem.Root["id"].ToObject<int>(); string label = updatedItem.Root["label"].ToString(); string description = updatedItem.Root["description"].ToString(); DateTime dateStart = updatedItem.Root["dateStart"].ToObject<DateTime>(); DateTime dateEnd = updatedItem.Root["dateEnd"].ToObject<DateTime>(); await _db.UpdateEvent(id, label, description, dateStart, dateEnd); }
After editing, the changes are applied to the SQL Table:
![SQL with removed event](./sql-updated-task.png)