How to perform CRUD operation on JS Scheduler using Web Service?
The Schedule control in JavaScript supports CRUD operations (Create, Read, Update, and Delete) to be performed by making use of the Web Service methods either in an ASP.NET or MVC application.
The following steps helps you to perform the CRUD operations like adding, editing and deleting appointments on the Schedule control by using the web services in an ASP.NET application.
Step 1: Create an empty ASP.NET application and add a new HTML page with the default schedule rendering code. Also, add and refer the required scripts and stylesheets to it by referring here.
Step 2: Now, Create a database table with the necessary fields, as follows.
SQL Table Script
CREATE TABLE [dbo].[Appointments] ( [Id] INT NOT NULL, [Subject] NVARCHAR (50) NULL, [Description] NVARCHAR (100) NULL, [StartTime] DATETIME NULL, [EndTime] DATETIME NULL, [AllDay] BIT NULL, [Recurrence] BIT NULL, [RecurrenceRule] NVARCHAR (100) NULL, [StartTimeZone] NVARCHAR (50) NULL, [EndTimeZone] NVARCHAR (50) NULL, PRIMARY KEY CLUSTERED ([Id] ASC) );
You can create the table through Visual Studio as a local database or by using SQL Server Management Studio
Step 3: Add the connection String configuration in the Web.Config page. Refer to the following code example.
Web.Config
<configuration> ----------------- -------------------------- <connectionStrings> <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=aspnet-ScheduleCRUDWithWebServices-20150626103804;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnet-ScheduleCRUDWithWebServices-20150626103804.mdf" providerName="System.Data.SqlClient" /> <add name="ScheduleDataConnectionString" connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\ScheduleData.mdf;Integrated Security=True;MultipleActiveResultSets=True;Application Name=EntityFramework" providerName="System.Data.SqlClient" /> </connectionStrings> ---------------- ------------------------ </configuration>
Change the ConnectionString value based on the database connection. Here, the “local database” connection string is mentioned
Step 4: Create a web service with the CRUD operation functionality using the following steps:
- Create a web service by selecting the new item in the solution.
Figure 1: Creation of the webservice
- The WebService1.asmx.cs file opens with the details as shown in the screenshot.
Figure 2: WebService1.asmx.cs file
- Now, add the CRUD operation code to this page.
using System; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Web; using System.Web.Services; namespace ScheduleCRUDWithWebServices { /// <summary> /// Summary description for WebService1 /// </summary> [WebService(Namespace = "http://tempuri.org/")] [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)] [System.ComponentModel.ToolboxItem(false)] // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. [System.Web.Script.Services.ScriptService] public class WebService1 : System.Web.Services.WebService { ScheduleDataDataContext db = new ScheduleDataDataContext(); [WebMethod] public string HelloWorld() { return "Hello World"; } [WebMethod] public List<Appointment> GetData() { return db.Appointments.ToList(); } [WebMethod] public List<Appointment> Crud(List<Appointment> added, List<Appointment> changed, List<Appointment> deleted) { ScheduleDataDataContext db = new ScheduleDataDataContext(); if (added != null && added.Count > 0) { var value = added[0]; DateTime startTime = Convert.ToDateTime(value.StartTime); DateTime endTime = Convert.ToDateTime(value.EndTime); Appointment appoint = new Appointment(); appoint.Id = value.Id; appoint.Subject = value.Subject; appoint.StartTime = startTime; appoint.EndTime = endTime; appoint.StartTimeZone = value.StartTimeZone; appoint.EndTimeZone = value.EndTimeZone; appoint.AllDay = value.AllDay; appoint.Description = value.Description; appoint.Recurrence = value.Recurrence; appoint.RecurrenceRule = value.RecurrenceRule; db.Appointments.InsertOnSubmit(appoint); db.SubmitChanges(); } if (changed != null && changed.Count > 0) { var filterData = db.Appointments.Where(c => c.Id == Convert.ToInt32(changed[0].Id)); if (filterData.Count() > 0) { var value = changed[0]; DateTime startTime = Convert.ToDateTime(value.StartTime); DateTime endTime = Convert.ToDateTime(value.EndTime); Appointment appoint = db.Appointments.Single(A => A.Id == Convert.ToInt32(value.Id)); appoint.StartTime = startTime; appoint.EndTime = endTime; appoint.StartTimeZone = value.StartTimeZone; appoint.EndTimeZone = value.EndTimeZone; appoint.Subject = value.Subject; appoint.Description = value.Description; appoint.AllDay = value.AllDay; appoint.Recurrence = value.Recurrence; appoint.RecurrenceRule = value.RecurrenceRule; } db.SubmitChanges(); } if (deleted != null && deleted.Count > 0) { foreach (Appointment dele in deleted) { var app = db.Appointments.ToList().Where(c => c.Id == Convert.ToInt32(dele.Id)).FirstOrDefault(); if (app != null) { db.Appointments.DeleteOnSubmit(app); } } db.SubmitChanges(); } return db.Appointments.ToList(); } [WebMethod] public List<Appointment> add(Appointment value) { ScheduleDataDataContext db = new ScheduleDataDataContext(); int intMax = db.Appointments.ToList().Count > 0 ? db.Appointments.ToList().Max(p => p.Id) : 0; DateTime startTime = Convert.ToDateTime(value.StartTime); DateTime endTime = Convert.ToDateTime(value.EndTime); Appointment appoint = new Appointment() { Id = intMax + 1, StartTime = startTime, EndTime = endTime, StartTimeZone = value.StartTimeZone, EndTimeZone = value.EndTimeZone, Subject = value.Subject, AllDay = value.AllDay, Recurrence = value.Recurrence, RecurrenceRule = value.RecurrenceRule, }; db.Appointments.InsertOnSubmit(appoint); db.SubmitChanges(); return db.Appointments.ToList(); } [WebMethod] public List<Appointment> remove(string key) { ScheduleDataDataContext db = new ScheduleDataDataContext(); Appointment app = db.Appointments.Where(c => c.Id == Convert.ToInt32(key)).FirstOrDefault(); if (app != null) db.Appointments.DeleteOnSubmit(app); db.SubmitChanges(); return db.Appointments.ToList(); } [WebMethod] public List<Appointment> update(Appointment value) { ScheduleDataDataContext db = new ScheduleDataDataContext(); var filterData = db.Appointments.Where(c => c.Id == Convert.ToInt32(value.Id)); if (filterData.Count() > 0) { DateTime startTime = Convert.ToDateTime(value.StartTime); DateTime endTime = Convert.ToDateTime(value.EndTime); Appointment appoint = db.Appointments.Single(A => A.Id == Convert.ToInt32(value.Id)); appoint.StartTime = startTime; appoint.EndTime = endTime; appoint.StartTimeZone = value.StartTimeZone; appoint.EndTimeZone = value.EndTimeZone; appoint.Subject = value.Subject; appoint.AllDay = value.AllDay; appoint.Recurrence = value.Recurrence; appoint.RecurrenceRule = value.RecurrenceRule; } db.SubmitChanges(); return db.Appointments.ToList(); } public class EditParams { public string key { get; set; } public string action { get; set; } public List<Appointment> added { get; set; } public List<Appointment> changed { get; set; } public List<Appointment> deleted { get; set; } public Appointment value { get; set; } } } }
WebService1.aspx.vb
Imports System.Web.Services Imports System.Web.Services.Protocols Imports System.ComponentModel Imports System.Web.Script.Serialization Imports System.Data.SqlClient Imports System.Web.Script.Services ' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. ' <System.Web.Script.Services.ScriptService()> _ <System.Web.Services.WebService(Namespace:="http://tempuri.org/")> _ <System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _ <ToolboxItem(False)> _ <System.Web.Script.Services.ScriptService()> _ Public Class WebService1 Inherits System.Web.Services.WebService <WebMethod()> _ Public Function HelloWorld() As String Return "Hello World" End Function <WebMethod()> _ Public Function GetData() As List(Of ScheduleAppointment) Dim context As New ScheduleDataDataContext() Return context.ScheduleAppointments.ToList() End Function <WebMethod> _ Public Sub Crud(added As List(Of [ScheduleAppointment]), changed As List(Of [ScheduleAppointment]), deleted As List(Of [ScheduleAppointment])) Dim db As New ScheduleDataDataContext() If added IsNot Nothing AndAlso added.Count > 0 Then Dim value = added(0) Dim startTime As DateTime = Convert.ToDateTime(value.StartTime) Dim endTime As DateTime = Convert.ToDateTime(value.EndTime) Dim appoint As New ScheduleAppointment() appoint.Id = value.Id appoint.Subject = value.Subject appoint.StartTime = startTime appoint.EndTime = endTime appoint.AllDay = value.AllDay appoint.Description = value.Description appoint.Recurrence = value.Recurrence appoint.RecurrenceRule = value.RecurrenceRule db.ScheduleAppointments.InsertOnSubmit(appoint) db.SubmitChanges() End If If changed IsNot Nothing AndAlso changed.Count > 0 Then Dim filterData = db.ScheduleAppointments.Where(Function(c) c.Id = Convert.ToInt32(changed(0).Id)) If filterData.Count() > 0 Then Dim value = changed(0) Dim startTime As DateTime = Convert.ToDateTime(value.StartTime) Dim endTime As DateTime = Convert.ToDateTime(value.EndTime) Dim appoint As ScheduleAppointment = db.ScheduleAppointments.[Single](Function(A) A.Id = Convert.ToInt32(value.Id)) appoint.StartTime = startTime appoint.EndTime = endTime appoint.Subject = value.Subject appoint.Description = value.Description appoint.AllDay = value.AllDay appoint.Recurrence = value.Recurrence appoint.RecurrenceRule = value.RecurrenceRule End If db.SubmitChanges() End If If deleted IsNot Nothing AndAlso deleted.Count > 0 Then For Each dele As Object In deleted Dim app = db.ScheduleAppointments.ToList().Where(Function(c) c.Id = Convert.ToInt32(dele.Id)).FirstOrDefault() If app IsNot Nothing Then db.ScheduleAppointments.DeleteOnSubmit(app) End If Next End If End Sub <WebMethod> _ Public Sub add(value As [ScheduleAppointment]) Dim db As New ScheduleDataDataContext() Dim startTime As DateTime = Convert.ToDateTime(value.StartTime) Dim endTime As DateTime = Convert.ToDateTime(value.EndTime) Dim appoint As New ScheduleAppointment() appoint.Id = value.Id appoint.Subject = value.Subject appoint.Description = value.Description appoint.StartTime = startTime appoint.EndTime = endTime appoint.AllDay = value.AllDay appoint.Recurrence = value.Recurrence appoint.RecurrenceRule = value.RecurrenceRule db.ScheduleAppointments.InsertOnSubmit(appoint) db.SubmitChanges() End Sub <WebMethod> _ Public Sub remove(key As String) Dim db As New ScheduleDataDataContext() Dim app = db.ScheduleAppointments.ToList().Where(Function(c) c.Id = Convert.ToInt32(key)).FirstOrDefault() If app IsNot Nothing Then db.ScheduleAppointments.DeleteOnSubmit(app) End If db.SubmitChanges() End Sub <WebMethod> _ Public Sub update(value As [ScheduleAppointment]) Dim db As New ScheduleDataDataContext() Dim filterData = db.ScheduleAppointments.Where(Function(c) c.Id = Convert.ToInt32(value.Id)) If filterData.Count() > 0 Then Dim startTime As DateTime = Convert.ToDateTime(value.StartTime) Dim endTime As DateTime = Convert.ToDateTime(value.EndTime) Dim appoint As ScheduleAppointment = db.ScheduleAppointments.[Single](Function(A) A.Id = Convert.ToInt32(value.Id)) appoint.StartTime = startTime appoint.EndTime = endTime appoint.Subject = value.Subject appoint.Description = value.Description appoint.AllDay = value.AllDay appoint.Recurrence = value.Recurrence appoint.RecurrenceRule = value.RecurrenceRule End If db.SubmitChanges() End Sub End Class
Step 5: Build the sample and add the WebReference to your application using the following steps,
- Right-click Reference and select the Add ServiceReference option as shown in the following screenshot.
Figure 3: Select the Add ServiceReference option
- Click Discover to get the created WebServices details.
Figure 4: WebService details
- Click the Advanced button so that the Service Reference Settings window opens. Select Add Web Reference in that window.
Figure 5: Service Reference Settings window
- The Add Web Reference dialog opens as shown in the following screenshot.
Figure 6: Add Web Reference
- Select the Web Services in this solution option from the three options, and the available WebServices details are displayed.
Figure 7: WebServices details
- Select the WebService (Ex: WebService1) link and it displays the details of the created WebServices with the web reference name. For example, localhost is displayed here.
Figure 8: Details of the created WebServices
- Click the Add Reference button and the reference is successfully added to your application.
Figure 9: Reference added
Step 6: Now include the JavaScript code and also map the field names to the Schedule control’s appointmentSettings to perform the CRUD operation.
<!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <link href="http://cdn.syncfusion.com/13.4.0.53/js/web/flat-azure/ej.web.all.min.css" rel="stylesheet" /> <link href="http://cdn.syncfusion.com/13.4.0.53/js/web/responsive-css/ej.responsive.css" rel="stylesheet" /> <script src="http://cdn.syncfusion.com/js/assets/external/jquery-2.1.4.min.js"></script> <script src="http://cdn.syncfusion.com/js/assets/external/jquery.easing.1.3.min.js"></script> <script src="http://cdn.syncfusion.com/js/assets/external/jsrender.min.js"></script> <script src="http://cdn.syncfusion.com/13.4.0.53/js/web/ej.web.all.min.js"></script> </head> <body> <div id="Schedule1"></div> <script> var data = ej.DataManager({ url: "WebService1.asmx/GetData", // This will trigger to bind the appointments data to schedule control batchUrl: "WebService1.asmx/Crud", // This will trigger while saving the appointment through detail window insertUrl: "WebService1.asmx/add", // This will trigger while saving the appointment through quick window updateUrl: "WebService1.asmx/update", //This will trigger while saving the resize or drag and drop the appointment removeUrl: "WebService1.asmx/remove", // This will trigger to delete the single appointment adaptor: new ej.WebMethodAdaptor() }); $("#Schedule1").ejSchedule({ width: "100%", height: "525px", currentDate: new Date(2015, 5, 15), appointmentSettings: { dataSource: data, id: "Id", subject: "Subject", description: "Description", startTime: "StartTime", endTime: "EndTime", startTimeZone: "StartTimeZone", endTimeZone: "EndTimeZone", allDay: "AllDay", recurrence: "Recurrence", recurrenceRule: "RecurrenceRule" } }); </script> </body> </html>
Step 8: Run the sample and now you can perform CRUD operations on the schedule appointments which gets reflected appropriately in the Web Services too.